Search Console bulk data export to BigQuery

This guide is built from Search Console Help: About bulk data export, Start a new bulk data export, Manage and monitor bulk data exports, Table guidelines and reference and Query guidelines and sample queries, plus the Search Central blog post that announced the feature. Quotes are Google's wording; the last section is our own practice.

What the bulk export gives you

Google describes it as a way to "schedule a daily export of your Search Console performance data to BigQuery". The export holds "all the performance data available to Search Console for your property, with the exception of anonymized queries." The announcement adds that it "is not affected by the daily data row limit", which is the main reason to use it: the report table and the API both stop at a fixed number of rows (our Performance report guide explains those limits).

Google aims it at big sites: it "could be particularly helpful for large websites with tens of thousands of pages, or those receiving traffic from tens of thousands of queries a day (or both!)." It also says "small and medium sites already have access to all their data" through the interface, the Looker Studio connector or the Search Analytics API. If the report already shows you every query, the export adds cost and work but little data.

Two things to know before you start. The export only runs forward: "If you want to see historical data that precedes your initial setup, use the Search Console API or the reports." And only owners can switch it on: "You must have owner permissions to the property in Search Console to start or stop a bulk data export" (see Search Console roles).

Step 1: prepare the Google Cloud project

You need "a Google Cloud project with billing and enable BigQuery". Google notes: "There is a free usage level, but storage and query usage above the free quota will be charged." Then, in Google Cloud Console:

1. Switch to the project you want the data in.
2. Go to APIs & Services > Enabled APIs & Services. If BigQuery isn't enabled, click + Enable APIs and services and enable both the BigQuery API and the BigQuery Storage API.
3. Go to IAM and Admin, click + Grant access and add this principal: [email protected]
4. Give it two roles, BigQuery Job User and BigQuery Data Editor, and save.

That service account is Google's, not one you create. If your Cloud organization limits sharing by domain, Google's error list says you "need to force account access during set up".

Step 2: start the export in Search Console

1. Open Settings > Bulk data export for the property.
2. Paste the Cloud project ID into the Cloud project ID field. Google stresses the ID, "not the project number".
3. Pick a dataset name. The default is searchconsole, and the name "always starts with the string searchconsole, even when you customize it." If several properties export into one project, each needs its own dataset: "Two properties can't write to the same dataset."
4. Choose a dataset location. Choose carefully: "you can't easily change this location later once your exports have begun."
5. Click Continue.

Search Console then tests the setup. Per the announcement, if that test passes all property owners get an email; if it fails, "you'll receive an immediate alert on the issue detected". The help page says "the first export will happen up to 48 hours after your successful configuration" and "includes data for the day of the export". After that, Search Console "will export each day's data on a daily basis until you stop the export. The exact time of the export may vary."

The three tables

The export creates these tables in your dataset:

Table	What it holds	Fields worth knowing
searchdata_site_impression	Performance data "aggregated by property"	data_date, site_url, query, is_anonymized_query, country, search_type, device, impressions, clicks, sum_top_position
searchdata_url_impression	Performance data "aggregated by URL"	The same keys plus url, is_anonymized_discover, search appearance flags such as is_job_listing, and sum_position
ExportLog	One row per successful export to each table	namespace (which table), data_date, epoch_version (goes up if Google re-saves a day), publish_time

The two data tables count position differently, the same way the report does for property totals versus page totals (see property versus page). Positions are zero-based, so Google's formula for average position is SUM(sum_top_position)/SUM(impressions) + 1 on the site table, and the same with sum_position on the URL table.

Other details from the reference: dates are in Pacific Time, country uses three-letter ISO codes, and search_type is one of web, image, video, news, discover or googleNews. Search appearance types live only in the URL table, as true/false columns.

Anonymized queries and Discover rows

Rare queries are hidden to protect users. In the export they are not dropped but blanked: "An anonymized query is reported as a zero-length string in the table", flagged by is_anonymized_query. Google warns they "often make up the single most common query for a site", so a top-queries list should filter them out with WHERE query != '', while totals should keep them.

Discover has its own flag. When is_anonymized_discover is true, the row is below the Discover anonymization threshold and "some other fields (like URL and country) will be missing to protect user privacy."

Querying without double counting

The most important rule in Google's query guide is to aggregate. Rows are "not guaranteed to be consolidated by date, URL, site, or any combination of keys", because data "is accumulated by Search Console incrementally, resulting in table rows with repeated keys". Google's example has the query "fat cats" spread over three rows: reading one row gives 5,000 impressions, while SUM(impressions) gives the real 6,200. Always use SUM or COUNT with GROUP BY.

The second rule is to limit dates. The tables are partitioned by date, and "a good way to minimize query costs is to use a WHERE clause to limit the date range". Google's sample queries all filter on data_date, for example the last 14 days with DATE_SUB(CURRENT_DATE(), INTERVAL 14 day).

Storage, expiry and query costs

Costs sit on the BigQuery side: "Data is subject to Google Cloud storage and query costs, but there is a free usage level." Storage grows without limit unless you act: "Data will be accumulated forever for your project, unless you set an expiration time for your data."

Google's advice is to expire partitions, not the table, after "a month, six months, twelve months, or whatever is reasonable for your needs". An expiry on the whole table "is probably not what you want, as it will delete all your data." Keep the partition expiry at "14 days or longer", because the export can't write to a partition that is about to expire. And don't change the schema: adding a column makes the export fail with a schema mismatch.

If you already use the GA4 BigQuery export, that guide covers the BigQuery sandbox and free tier in more detail.

Monitoring and export errors

The latest status shows next to Bulk data export in Settings. When a lasting error occurs or is fixed, Search Console emails all owners and full users; it does not send messages for short-lived errors such as connection problems. Failures are not written to ExportLog; Google points to the Cloud Logs Explorer, searching for search-console-data-export.

Fix errors quickly. Search Console retries a failed day for about a week, then drops that day, and "after about a month of failed export attempts, Search Console will stop the bulk export entirely." After a fix, the Test button checks the easy settings, but it does not start an export or test quotas, so Google suggests checking again about a day later. Common errors from Google's list:

• Missing permissions in Cloud project: the service account roles from step 1 are missing.
• Missing billing information in Cloud project: without valid billing "the write will fail (even if you granted permissions)."
• Missing BigQuery API in Cloud project: enable both BigQuery APIs.
• Cloud project does not exist: usually a typo, or the project number used instead of the ID.
• Another bulk data export already exists for this destination dataset: give each property its own dataset.
• Partition expiration too short and Schema mismatch: see the costs section above.

Stopping, restarting and moving an export

To stop, open the bulk data export settings, click Deactivate export and confirm. "Bulk exports will stop in the next 24 hours", so one more day may arrive. To stop at once, remove the Search Console principal's permissions under IAM in the Cloud project. Either way, "existing tables will not be deleted".

To restart, repeat the setup with the same project ID and dataset location; if the old tables still exist, new data is added to them. A location move isn't supported directly. Google's workaround is to stop the export, wait 24 hours, check the last export succeeded, copy the dataset to the new location in BigQuery, and start a new export there, accepting that "you might miss a day or two of data".

How we use it

This part is our own practice, not Google guidance. For most small business sites we don't switch the export on: the Performance report, regex filters and the GA4 Search Console integration cover what they need. We suggest it when a site has more queries or pages than the report's row limits show, or when Search data has to be joined with other data such as CRM or product tables.

When we set it up, we use the client's own Cloud project and billing account so the data stays theirs, set a partition expiry they have agreed to, and save the standard queries with a date filter and SUM built in. We note the start date in reporting, because nothing before it is in BigQuery.

Frequently asked questions

Is the Search Console bulk data export free?

Search Console doesn't charge for it, but the data lives in BigQuery, which needs a Cloud project with billing. Google says there is a free usage level, and storage and queries above the free quota are charged.

Can I export past Search Console data to BigQuery?

No. The first export includes data for the day of the export. For earlier dates Google points to the Search Console API or the reports.

Why don't my BigQuery totals match a single row?

Rows can repeat the same keys, because Search Console adds data incrementally. Google says to always aggregate with SUM or COUNT and GROUP BY.

Does the export include anonymized queries?

Their clicks and impressions are included, but the query text is a zero-length string and is_anonymized_query is true. Filter with query != '' for query lists.

Who can set up the bulk data export?

Only owners of the Search Console property can start or stop it. Error emails go to all owners and full users.