Search Console Bulk Data Export to BigQuery for Marketers

#01How does it work?

From Google's bulk export setup guide, table reference and management help:

For GA4 exports see GA4 BigQuery export on the free tier.

Our view: set it up early; with no backfill, every month you wait is lost.

#02Sample queries

Top queries last 28 days:

SELECT query, SUM(clicks) AS clicks, SUM(impressions) AS impressions
FROM `project.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
  AND NOT is_anonymized_query
GROUP BY query ORDER BY clicks DESC LIMIT 100;

Average position by page:

SELECT url, SUM(sum_position)/SUM(impressions)+1 AS avg_position
FROM `project.searchconsole.searchdata_url_impression`
GROUP BY url;

(Use sum_top_position on the site table and sum_position on the URL table, as Google's reference describes.)

#03Troubleshooting

#04More SQL for marketers

Replace project.searchconsole with your project and dataset. Always filter on data_date to limit the data scanned and reduce cost.

Non-branded clicks by week

SELECT DATE_TRUNC(data_date, WEEK) AS week,
  SUM(IF(REGEXP_CONTAINS(query, r'(ledgerline|ledger line)'), clicks, 0)) AS branded,
  SUM(IF(NOT REGEXP_CONTAINS(query, r'(ledgerline|ledger line)'), clicks, 0)) AS non_branded
FROM `project.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 180 DAY)
  AND NOT is_anonymized_query
GROUP BY week ORDER BY week;

Question queries and the pages they land on

SELECT url, COUNT(DISTINCT query) AS questions, SUM(clicks) AS clicks
FROM `project.searchconsole.searchdata_url_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
  AND REGEXP_CONTAINS(query, r'^(what|how|why|which|can|does)\b')
GROUP BY url ORDER BY clicks DESC LIMIT 50;

Pages losing clicks: last 28 days vs previous 28

WITH p AS (
  SELECT url,
    SUM(IF(data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY), clicks, 0)) AS recent,
    SUM(IF(data_date < DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY), clicks, 0)) AS prior
  FROM `project.searchconsole.searchdata_url_impression`
  WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 56 DAY)
  GROUP BY url)
SELECT url, prior, recent, recent - prior AS change
FROM p WHERE prior > 0 ORDER BY change ASC LIMIT 50;

Discover performance by page

SELECT url, SUM(impressions) AS impressions, SUM(clicks) AS clicks
FROM `project.searchconsole.searchdata_url_impression`
WHERE LOWER(search_type) = 'discover'
  AND data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY)
GROUP BY url ORDER BY impressions DESC;

Check the exact casing of search_type values in your own table before filtering; the table reference lists them, and a quick SELECT DISTINCT search_type confirms.

#05Controlling cost

• Partitioning. Tables are partitioned by date; always filter on data_date.
• Select only needed columns. BigQuery charges by data scanned per column.
• Preview before running. The query editor estimates bytes processed.
• Partition expiration. If you only need a rolling window, set expiration (Google's minimum is 14 days), but remember there's no backfill.
• Scheduled summary tables. Run a daily scheduled query that writes small aggregates, and point dashboards at those.

#06Edge cases

• Repeated keys. Several rows can share the same date, query and URL. Always SUM and GROUP BY.
• Anonymised queries have no query text but do have clicks. Include them for totals, exclude them for query analysis.
• Location choice. Choose the dataset location to match your other data; changing it later is hard.
• ExportLog records each successful export. Check it first when data seems missing.
• Export stops after about a month of failures. Usually a removed permission. Re-grant the roles and re-enable.

#07Ledgerline walkthrough: setting it up

1. The data team created a Cloud project with billing and enabled the BigQuery and BigQuery Storage APIs.
2. They granted the Search Console export service account BigQuery Job User and BigQuery Data Editor on the project.
3. The SEO lead, a property owner, opened Settings > Bulk data export, entered the project ID and a dataset name starting with "searchconsole", and chose the EU location to match other data.
4. Two days later, ExportLog showed the first export. They ran the decliners query and built a scheduled summary table for Data Studio.

#08Questions practitioners ask

Can I export a Domain property? Yes; the export works per property you own.

Can I export several properties into one dataset? Each property needs its own dataset; use a view or scheduled query to combine them.

Does the export include generative AI report data? The tables follow the documented schema. Check Google's table reference for any new fields rather than assuming.

How do I stop it? Deactivate it in Settings > Bulk data export; Google says it takes effect within about 24 hours, and existing data stays in BigQuery.

#09More SQL recipes

Monthly clicks by section from the URL table:

SELECT
  FORMAT_DATE('%Y-%m', data_date) AS month,
  CASE
    WHEN url LIKE '%/blog/%' THEN 'Blog'
    WHEN url LIKE '%/product/%' THEN 'Product'
    ELSE 'Other'
  END AS section,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions
FROM `project.searchconsole.searchdata_url_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 12 MONTH)
GROUP BY month, section
ORDER BY month, section;

Share of impressions from anonymised queries:

SELECT
  SUM(IF(is_anonymized_query, impressions, 0)) / SUM(impressions) AS anonymised_share
FROM `project.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY);

#10Comparing two periods in SQL

SELECT
  url,
  SUM(IF(data_date BETWEEN '2026-09-01' AND '2026-09-30', clicks, 0)) AS clicks_sep,
  SUM(IF(data_date BETWEEN '2026-08-01' AND '2026-08-31', clicks, 0)) AS clicks_aug
FROM `project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN '2026-08-01' AND '2026-09-30'
GROUP BY url
ORDER BY clicks_sep - clicks_aug ASC
LIMIT 50;

This lists the 50 URLs with the biggest click drops between the two months. Swap the order for gains.

#11Where this fits in the monthly routine

Once the export is running, analysts can answer questions the UI can't, such as 16-month comparisons or long-tail query themes. Our view: keep a small set of saved queries (section trend, period comparison, anonymised share) and run them in week 1.

#12Keeping query costs predictable

Filter on data_date every time; the tables are partitioned by date. Select only the columns you need, write regular results to small summary tables with scheduled queries, and set a budget alert in Google Cloud billing.

#13Quick checks before you move on

• The export is active and new tables arrive daily.
• You know which table to use for totals (site) and which for pages (URL).
• Saved queries for section trend, period comparison and anonymised share run without errors.
• A budget alert is set.

#14Worked example: Ledgerline's export

Ledgerline is a fictional accounts-payable software company. Details and numbers are illustrative.

Ledgerline's data team enabled bulk export in a new Cloud project. After the first export arrived, the SEO lead ran the top queries SQL and matched it with the Performance report trend.

Walkthrough

1. Create or choose a Google Cloud project with billing.
2. Enable the BigQuery API and BigQuery Storage API.
3. Grant search-console-data-export@system.gserviceaccount.com BigQuery Job User and BigQuery Data Editor.
4. In Search Console, open Settings > Bulk data export.
5. Enter the project ID, dataset name (starting with "searchconsole") and location.
6. Wait up to 48 hours for the first export.
7. Check the ExportLog table.

#15Try it yourself

Set up bulk export (or plan it with your data team). Once data arrives, run the top queries SQL and compare it with the Performance report.

#16Quick quiz

1. Is there a backfill?

Answer: No.

2. Which tables are exported?

Answer: searchdata_site_impression, searchdata_url_impression and ExportLog.

3. Who can set it up?

Answer: Owners.

4. How is average position calculated?

Answer: Sum of positions divided by impressions, plus 1.

#17What should you check before the next lesson?

• Bulk export is configured or planned.
• You ran a sample query.
• Read GA4 BigQuery export.

Next: The API and AI analysis with Claude and ChatGPT. Or return to the Search Console for Marketers track.

#18Common questions

Does it cost money? BigQuery storage and queries may incur charges.

Can I change the location later? It is hard to change; choose carefully.

Can I change the schema? No.