Search Console Bulk Data Export to BigQuery: Setup and Uses

Have you ever hit the 1,000-row limit in the Performance report? Or watched your oldest data drop off the 16-month window? The Search Console BigQuery export fixes both. The search console bigquery export (Google calls it bulk data export) sends a daily copy of your Performance data into your own BigQuery project. There is no row cap, and you keep the data for as long as you choose.

In this guide I cover why I think it is worth setting up, the exact setup steps, and what each table holds. I also share queries you can copy, how to keep costs low and how to put the data into Looker Studio.

What the Search Console BigQuery Export Really Is

Bulk data export is a feature built into Search Console. It has been available since early 2023. Once you set it up, Search Console writes your Performance data for each day into a BigQuery dataset (a folder of tables) that you own.

It is not a connector or a third-party tool. Google sends the data to you once a day. You then query it with SQL, store it, or feed it into dashboards.

It covers the same search types you see in the Performance reports. That means web, image, video and news results in Google Search, plus Discover and Google News.

Why Use Bulk Export Instead of the Interface or the API

Search Console already gives you data in three ways: the reports, CSV exports and the Search Analytics API. Each one has limits that bulk export removes.

• No row limits. The report tables show at most 1,000 rows. Google’s help also says Performance data is limited to 50,000 rows per day per search type per property. That affects API pulls on bigger sites. Bulk export does not have these caps, so long-tail queries and deep pages show up.
• Anonymized queries are counted. Rare queries are anonymized to protect user privacy. In the export, their query text is hidden, but the rows are still there. They have a flag (is_anonymized_query) and an empty query string. So your totals add up, and you can measure how much traffic sits in anonymized queries.
• History you control. The interface only keeps 16 months. Exported data keeps building up forever unless you set an expiry date. That makes year-over-year and multi-year comparisons easy.
• Joins with other data. Because the data sits in BigQuery, you can join it with GA4 exports, CRM data or a list of page templates.

The Catch: History Starts on the Day You Set It Up

Bulk export does not backfill. Data from before your setup date is not included. The export only starts collecting from the day you turn it on.

So my most practical advice in this guide is simple: set it up now, even if you won’t query it for months. I turn it on for every site I work on that has a Google Cloud project. If you need older data, you can still pull it from the reports or the API for the 16 months Search Console keeps.

Who It Is For

• Sites with thousands of pages or queries, where sampling and row caps hide the long tail.
• Teams that already use BigQuery, for example for the GA4 export.
• Agencies and in-house SEOs who want reporting that does not depend on spreadsheet pulls.

A small brochure site with a few dozen pages may not need it. The Performance report already covers everything a site like that has.

How to Set Up Search Console Bulk Data Export

You need owner permission on the Search Console property. You also need access to a Google Cloud project with billing turned on. The setup takes about 15 minutes.

1. Get a Google Cloud Project Ready

1. Create a project in the Google Cloud console, or pick an existing one.
2. Make sure billing is turned on. BigQuery has a free usage level, but the export needs a billing account on the project.
3. Go to APIs & Services > Enabled APIs & services. Check that both the BigQuery API and the BigQuery Storage API are enabled. If either is missing, enable it.
4. Write down the project ID (for example acme-seo-data), not the project number. You will need it in Search Console.

2. Give Permissions to the Search Console Service Account

1. In the Cloud console, open IAM & Admin > IAM and click Grant access.
2. Add this principal (the account Google uses to send the data): search-console-data-export@system.gserviceaccount.com
3. Give it two roles: BigQuery Job User and BigQuery Data Editor.
4. Save.

Without these two roles, Search Console cannot create the dataset or write to it. “Missing permissions in Cloud project” is one of the most common setup errors. It is the first thing I check when an export does not show up.

3. Set Up the Export in Search Console

1. In Search Console, open Settings > Bulk data export for your property.
2. Enter the Cloud project ID.
3. Choose a dataset name. The default is searchconsole, and any custom name must also start with searchconsole. If you export several properties into one project, give each property its own dataset. Two properties cannot write to the same dataset.
4. Pick a dataset location, such as US or EU. Choose carefully, because you can’t easily change the location once exports start.
5. Click Continue.

Google’s step-by-step instructions are in the help article Start a new bulk data export.

4. Wait for the First Export and Check It

The first export arrives up to 48 hours after a successful setup. After that, data lands once a day. The tables may not update at exactly the same time.

The status of the latest export shows next to the setting in Search Console. Property owners and full users also get an email when an export fails or is fixed.

The Three Tables Explained

The export creates three tables in your dataset. Both data tables are partitioned (split into daily chunks) by data_date, which is the date in Pacific Time.

searchdata_site_impression

This table is grouped by property. It counts data the same way the Performance report does when you don’t filter by page. Key fields:

• data_date, site_url, query, is_anonymized_query
• country (three-letter ISO code, such as usa or gbr), search_type, device
• impressions, clicks
• sum_top_position: the sum of the site’s highest position for each impression

Use it for site-level totals, query trends and country or device breakdowns.

searchdata_url_impression

This table is grouped by URL. So one query can show up several times if it shows several of your pages. It has the same core fields, plus:

• url: the page the searcher lands on
• is_anonymized_discover: marks Discover rows below the anonymization threshold, where some fields may be missing
• A set of is_ true/false fields for search appearances, such as is_tpf_faq, is_job_listing or is_amp_top_stories
• sum_position instead of sum_top_position

Use it for page-level analysis, matching queries to pages and rich result performance. Those search appearance flags are handy if you track the SERP features your pages earn.

ExportLog

This table logs every successful export. It shows the date of the data, which table was written, and when the export finished. It also has an epoch_version that goes up if Google revises a day’s data.

Failed exports are not recorded here. For failures, check the status in Search Console settings, or search Cloud Logging for search-console-data-export.

Three Rules Before You Write Any Query

1. Always aggregate. Rows are not guaranteed to be combined. The same query, date and URL can show up in more than one row. Use SUM() with GROUP BY.
2. Add 1 to average position. Positions in the export start at zero. Average position is SUM(sum_position) / SUM(impressions) + 1 (or sum_top_position in the site table).
3. Always filter on data_date. This limits how many partitions get scanned. It is the main way to control cost.

In my experience, forgetting the +1 on position is the mistake I see most often in first dashboards.

Example SQL Queries You Can Copy

Replace acme-seo-data.searchconsole with your project ID and dataset name. These run in the query editor in the BigQuery console.

Daily Clicks, Impressions, CTR and Position for Web Search

SELECT
  data_date,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SUM(sum_top_position) / SUM(impressions) + 1 AS avg_position
FROM `acme-seo-data.searchconsole.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
  AND search_type = 'WEB'
GROUP BY data_date
ORDER BY data_date DESC;

This rebuilds the Performance report chart. I always run it first, to check the export matches what I see in Search Console.

How Much Traffic Sits in Anonymized Queries

SELECT
  SUM(IF(is_anonymized_query, clicks, 0)) AS anonymized_clicks,
  SUM(clicks) AS total_clicks,
  SAFE_DIVIDE(SUM(IF(is_anonymized_query, clicks, 0)), SUM(clicks)) AS anonymized_share
FROM `acme-seo-data.searchconsole.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
  AND search_type = 'WEB';

A high share tells you how much of your traffic comes from rare, long-tail searches you can’t see one by one. It also explains gaps when you add up query rows in other tools.

Top Query and Page Pairs

SELECT
  url,
  query,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SUM(sum_position) / SUM(impressions) + 1 AS avg_position
FROM `acme-seo-data.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
  AND search_type = 'WEB'
  AND query != ''
GROUP BY url, query
ORDER BY clicks DESC
LIMIT 1000;

The query != '' filter removes anonymized rows. Look for queries that send clicks to two or more URLs. That is often a sign of pages competing for the same search intent.

Brand Versus Non-Brand Clicks

SELECT
  IF(REGEXP_CONTAINS(LOWER(query), r'acme|acme widgets'), 'brand', 'non-brand') AS segment,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions
FROM `acme-seo-data.searchconsole.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
  AND search_type = 'WEB'
  AND query != ''
GROUP BY segment;

Edit the regular expression (the search pattern) to include your brand name, common misspellings and product names. This split is one of the most requested SEO reports, and it is awkward to build in the interface. It is usually the first report I build from the export.

Pages Losing the Most Clicks, Period Over Period

SELECT
  url,
  SUM(IF(data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE(), clicks, 0)) AS clicks_last_28,
  SUM(IF(data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 56 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 29 DAY), clicks, 0)) AS clicks_prev_28
FROM `acme-seo-data.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 56 DAY) AND CURRENT_DATE()
  AND search_type = 'WEB'
GROUP BY url
ORDER BY clicks_last_28 - clicks_prev_28 ASC
LIMIT 100;

The most recent day or two may not have landed yet. So compare matching periods when you report on fresh data. Moving both windows back by two days avoids a false dip. For a quick look at the most recent hours, I use the 24-hour view in Search Console instead.

Check That Exports Are Arriving

SELECT namespace, MAX(data_date) AS latest_date, MAX(publish_time) AS last_export
FROM `acme-seo-data.searchconsole.ExportLog`
GROUP BY namespace;

Run this when a dashboard looks out of date. If the latest date is several days old, check the export status in Search Console.

Google shares more examples, including Discover and FAQ rich result queries, in its query guidelines and sample queries.

What Bulk Export Costs and How to Keep It Low

Search Console does not charge for the export. You pay normal BigQuery rates for storage and for the data your queries scan.

Google Cloud’s pricing page lists a monthly free tier of 10 GiB of storage and 1 TiB of query processing. Many small and mid-sized sites stay inside it. Large sites with millions of query rows a day can go over it, mostly through careless queries and dashboards.

Simple Ways to Control Cost

• Filter on data_date every time. The tables are split by date, so a date filter means BigQuery reads only the days you ask for.
• Select only the columns you need. BigQuery bills by the columns scanned. SELECT * on the URL table reads every search appearance column too.
• Don’t rely on LIMIT. It makes the output shorter, but it does not reduce the data scanned.
• Read the estimate. The query editor shows how much data a query will process before you run it. I make a habit of glancing at it every time.
• Set a cap. Use the “maximum bytes billed” query setting, or custom quotas on the project, so one bad query can’t run up a bill.
• Build summary tables. A scheduled query that writes a small daily summary (for example clicks and impressions by URL and date) is far cheaper for dashboards than the raw tables.

Partition Expiration

By default, data is kept forever. To control storage, set a partition expiration on the tables. Google’s minimum is 14 days.

Set expiration on partitions, not on the table. A table expiration deletes the whole table and all its data. Also, don’t change the table schema (its list of columns), for example by adding a column, because that breaks the export.

Connecting the Export to Looker Studio

Looker Studio has a built-in BigQuery connector. So dashboards built on the export need no third-party tools.

1. In Looker Studio, create a report and choose the BigQuery connector.
2. Pick your project, the searchconsole dataset and the table, or choose Custom query.
3. When you connect a table directly, use data_date as the date range dimension. Then the report only scans the dates selected.
4. Set up average position as a calculated field: SUM(sum_position) / SUM(impressions) + 1. Don’t average a ready-made position column, because that gives the wrong result.
5. Set the billing project to the one that should pay for dashboard queries.

Using a Custom Query With Date Controls

Custom queries let you group the data ahead of time, so each chart scans less. Turn on date range parameters in the data source, then use them in SQL:

SELECT
  data_date,
  url,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SUM(sum_position) AS sum_position
FROM `acme-seo-data.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN PARSE_DATE('%Y%m%d', @DS_START_DATE)
                    AND PARSE_DATE('%Y%m%d', @DS_END_DATE)
  AND search_type = 'WEB'
GROUP BY data_date, url;

Keep sum_position and impressions as raw sums. That way the position calculated field stays correct at any level of the report.

Every chart refresh runs a query against BigQuery. For dashboards that many people open, I’d point Looker Studio at a summary table, not the raw export.

If you already report on organic sessions and conversions, this works well with the GA4 organic search reports you may have built. Expect the numbers to differ, because the two tools measure different things. If you also want GA4 data next to it in BigQuery, I cover that setup in my guide to the GA4 BigQuery export.

Common Problems and Fixes

• No data after two days: check the export status in Search Console settings. Most first-time failures are missing roles, the BigQuery APIs not being turned on, or no billing account.
• “Another bulk data export already exists for this destination dataset”: two properties point to the same dataset. Use a different dataset name for each one.
• “Schema mismatch”: someone added or changed a column. Undo the change if you can.
• Numbers differ slightly from the interface: check that you are using the right table. Site-level totals belong to searchdata_site_impression, and page-level totals to searchdata_url_impression.
• Need to move to another region: this is not supported directly. Google’s workaround is to stop the export, copy the dataset to the new location and start a fresh export there. You accept a day or two of missing data.

Search Console retries failed days for about a week. If exports keep failing for about a month, the export stops completely. So act on error emails quickly.

Getting Value From the Search Console BigQuery Export

The search console bigquery export gives you complete Performance data with no caps. You own it and keep it for as long as you like. The setup is a one-time job: a Cloud project with billing, two roles for Google’s service account, and a few fields in Search Console settings.

History only starts from the day you turn on the search console bigquery export, so I’d set it up today and add queries later. A good first project is to map which pages win which queries. Then check your rich result flags, like the ones in our guide to FAQ and HowTo schema, to see which markup still earns impressions.