Google Search Console BigQuery Bulk Export: The Complete Setup Guide

If you have spent any time analyzing search performance, you already know the frustrations of working with Google Search Console at scale. The Performance report in the UI only goes back 16 months, every table caps at 1,000 rows, and even the API samples and truncates large result sets. For a small site this rarely matters. For anyone managing tens of thousands of URLs, or anyone who wants a multi-year view of how queries shift over time, those limits turn into a real wall.

The Bulk Data Export to BigQuery is Google's official answer to that wall. It is a feature you enable inside Search Console that streams your raw, unsampled performance data into a BigQuery dataset every day. Unlike the UI and unlike the Search Analytics API, there is no row cap and no sampling. Once it is running, you accumulate a complete daily record of impressions, clicks, positions, and CTR that you can keep indefinitely and query with standard SQL.

This guide walks through exactly what the Bulk Data Export is, how it differs from the API, what you need to set it up, the tables and fields it creates, and a set of runnable SQL queries you can adapt on day one. It also covers the part most tutorials skip: when BigQuery is genuinely worth the effort versus when the UI or the API is the smarter choice.

What the Bulk Data Export actually is

The Bulk Data Export is a recurring, automated dump of your Search Console performance data into a Google BigQuery dataset that you own. You configure it once in Search Console settings, and from that point forward Google writes a fresh batch of data to your dataset every day. The data is the same underlying performance data you see in the Performance report, but delivered raw rather than aggregated and capped.

Two characteristics make it different from every other access method. First, it is unsampled. The export contains every row Google has for your property's anonymized query and URL data, not a sampled subset. Second, it is export-going-forward. This is the single most important thing to understand before you start: enabling the export does not backfill your historical data. Google begins collecting from roughly the day you turn it on. There is no way to recover the previous 16 months into BigQuery after the fact, so the practical advice is simple: enable it now, even if you do not have an immediate use, so that your historical archive starts accumulating today.

The data lives in your own Google Cloud project, which means you control retention, access, and how you join it against other datasets. Once data is in BigQuery it never expires on Google's schedule, only on yours.

How it differs from the Search Analytics API

People often assume the Bulk Data Export and the Search Analytics API are interchangeable. They solve overlapping problems but behave very differently.

The API is a request-response interface. You ask for a specific date range and set of dimensions, and Google returns up to 25,000 rows per request, which you paginate through. For very large properties the API applies sampling and will not return the full long tail of queries. It is also subject to the same 16-month historical window as the UI, so it cannot help you build an archive older than what Google currently retains.

The Bulk Data Export is a push, not a pull. You do not request data; Google delivers it on a schedule into tables you own. There is no row limit and no sampling, and because you store it yourself, your archive grows past 16 months for as long as you keep the export running. The trade-off is that it requires a Google Cloud project, a BigQuery dataset, and a willingness to write SQL.

| Capability | UI (Performance report) | Search Analytics API | BigQuery Bulk Export | |---|---|---|---| | Historical depth | 16 months | 16 months | Unlimited, from enable date forward | | Row limit | 1,000 rows per table | ~25,000 rows per request, paginated | No row limit | | Sampling on large sites | Yes | Yes | No (unsampled) | | Setup effort | None | Moderate (OAuth + code) | Higher (Cloud project + BigQuery) | | Backfill of past data | N/A | N/A | No, going forward only | | Best for | Quick checks, exploration | On-demand pulls, dashboards, automation | Long-term archive, large-scale analysis | | Cost | Free | Free | BigQuery storage + query costs |

For most day-to-day work the API is plenty, and if you just need data out of the UI without code, see our guide on exporting Search Console data to JSON. The BigQuery export is the right tool specifically when you have outgrown both the 1,000-row limit and the 16-month data limit.

Prerequisites

Before you can enable the export, you need three things in place.

First, a Google Cloud project. If you do not already have one, create it in the Google Cloud Console. You will need a project with billing enabled, because BigQuery storage and queries are billed (more on cost below, but it is very cheap for typical Search Console volumes).

Second, the BigQuery API enabled in that project. Search Console writes into BigQuery, so the API must be active. You can enable it from the APIs and Services section of the Cloud Console.

Third, you must grant Search Console's export service account permission to write into your project. Search Console pushes data using a dedicated service account, and you authorize it by adding that account as a principal on your Cloud project with the BigQuery Job User and BigQuery Data Editor roles. The exact service account address is shown to you during the setup flow in Search Console, so you copy it from there rather than guessing it. This grant is what allows Google to create the dataset and tables and write daily batches.

You also need to be an owner of the Search Console property. Only verified property owners can configure a bulk export.

Step-by-step setup in Search Console

Once the prerequisites are ready, the configuration itself takes a few minutes.

1. Open the export settings. In Search Console, go to Settings for your property, then open Bulk data export.

2. Enter your Cloud project ID. Search Console asks for the ID of the Google Cloud project that will receive the data. Use the project ID, not the display name.

3. Grant the service account access. Search Console displays the service account it will use to write data. In the Google Cloud Console, add that service account as a principal on your project and assign it the BigQuery Job User and BigQuery Data Editor roles. Without this grant the export validation will fail.

4. Choose or confirm the dataset. You specify the dataset name and the dataset location (region). Pick the region deliberately, because it cannot be changed later without recreating the export, and it affects where you can join other data.

5. Validate and save. Search Console runs a quick check to confirm it can write to the project. If everything is configured, the export is enabled and the first data typically appears within a day or two.

After that, the export runs automatically. You do not need to touch it again unless you want to disable it or change the destination.

The tables the export creates

The export populates a small set of tables inside your dataset. There are three you will work with.

searchdata_site_impression holds property-level aggregated data. Each row represents performance for a query (and other dimensions like country and device) summed across all URLs on the property. This is the right table when you want totals by query without caring which specific page ranked.

searchdata_url_impression holds URL-level data. Each row is tied to an individual URL, so you can see which page earned the impressions and clicks for a given query. This table is larger because it is more granular, and it carries additional fields describing the search appearance of each result.

ExportLog is a bookkeeping table. Each row records that a given table was successfully exported for a given date. It is genuinely useful for sanity checks: querying it tells you which dates have landed, so you can confirm the pipeline is healthy and detect gaps before they surprise you in analysis.

Key fields to know

Across the two searchdata_* tables you will see a consistent set of fields. The core metrics and dimensions include:

• data_date — the date the performance occurred (your main partition/filter field).
• query — the anonymized search query. Note that some queries are withheld for privacy and appear as null; these are the "anonymized" queries, and you should account for them rather than assume the data is incomplete.
• is_anonymized_query — a boolean flagging rows where the query was withheld.
• country, device, search_type — the standard dimension breakdowns.
• impressions and clicks — the raw counts.
• sum_position and sum_top_position — position is stored as a sum, not an average. To get average position you divide sum_position by impressions and, by Google's convention, add 1. This catches almost everyone the first time.

The searchdata_url_impression table additionally carries a url field and a set of boolean columns describing search appearance and result features (for example, whether the result appeared as a rich result type, in Discover, on an AMP page, and similar). Because exact appearance field names evolve over time, inspect your table schema in BigQuery (the schema tab on the table) to see the precise set available in your export rather than hard-coding assumptions.

Example SQL queries

These queries assume your data is in a dataset called searchconsole in a project called my-project. Replace those identifiers, and replace the date filters with ranges that exist in your export. Always filter on data_date to keep query costs down.

Top queries by clicks over the last 28 days. This uses the site-level table since we want totals across all URLs.

SELECT
  query,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SUM(sum_position) / SUM(impressions) + 1 AS avg_position
FROM `my-project.searchconsole.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
  AND is_anonymized_query = FALSE
GROUP BY query
ORDER BY clicks DESC
LIMIT 100;

Striking-distance queries (positions ~8 to 20 with real demand). These are the keywords sitting just off page one where small content improvements often pay off fastest. We compute average position and keep queries with meaningful impressions but low clicks.

SELECT
  query,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SUM(sum_position) / SUM(impressions) + 1 AS avg_position
FROM `my-project.searchconsole.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY) AND CURRENT_DATE()
  AND is_anonymized_query = FALSE
GROUP BY query
HAVING avg_position BETWEEN 8 AND 20
   AND impressions >= 100
ORDER BY impressions DESC
LIMIT 200;

CTR by page, to find under-performing titles. This uses the URL-level table so we can attribute performance to specific pages, then surface pages with strong impressions but weak click-through.

SELECT
  url,
  SUM(impressions) AS impressions,
  SUM(clicks) AS clicks,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SUM(sum_position) / SUM(impressions) + 1 AS avg_position
FROM `my-project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY url
HAVING impressions >= 500
ORDER BY ctr ASC
LIMIT 100;

Confirm which dates have landed. Use the log table to verify the export is healthy and spot missing days.

SELECT
  table_name,
  data_date
FROM `my-project.searchconsole.ExportLog`
ORDER BY data_date DESC, table_name
LIMIT 50;

A few practical notes. Use SAFE_DIVIDE for CTR so a zero-impression edge case returns null instead of erroring. Remember the position math: divide the summed position by impressions and add 1. And whenever you aggregate the site-level table by query, decide deliberately whether to include anonymized rows; filtering them out gives clean per-query totals but slightly understates true impression volume.

Cost considerations

BigQuery bills on two axes: storage of the data, and the amount of data scanned by your queries. For the volume Search Console produces, storage is typically negligible, often a few cents to a few dollars a month even for large sites. The cost you actually need to manage is query scanning.

The single most effective habit is to always filter on data_date. The export tables are partitioned by date, so a query restricted to a 28-day window scans only those partitions instead of years of history. A SELECT * with no date filter on a multi-year searchdata_url_impression table is the classic way to scan far more data than you intended. BigQuery's free tier covers a generous amount of query processing each month, and disciplined date filtering keeps most analytics workloads comfortably within or near it. You can also set table expiration or partition expiration if you decide you do not want to retain data indefinitely, though the whole point of the export is usually to keep it.

When to use BigQuery vs the API vs the UI

Reach for the UI when you want a quick answer: how a page is trending, which queries spiked this week, whether a fix moved the needle. It is free, instant, and requires no setup. Its limits only bite when you need depth or breadth it cannot provide.

Reach for the API when you want repeatable, programmatic access without standing up a data warehouse, for example powering a dashboard, scheduling weekly pulls, or feeding another tool. It handles far more than the UI's 1,000 rows and is the natural fit for automation within the 16-month window.

Reach for the BigQuery Bulk Export when your needs are genuinely large or long-term: archiving data beyond 16 months, analyzing the complete unsampled long tail of queries, joining Search Console against other datasets, or running heavy SQL across years of history. It is the most powerful option and the only one that breaks every cap, but it is also the one that asks the most of you in setup and SQL fluency.

If the BigQuery route sounds heavier than you need right now, our free Search Console Tools can run the most common analyses (striking-distance queries, CTR opportunities, and content briefs from your GSC data) straight from a Google OAuth login with no Cloud project or BigQuery setup required. It is a good way to get the insights first and graduate to BigQuery later when scale demands it. You can also compare approaches in our roundup of the best Google Search Console tools for 2026.

Frequently Asked Questions

Does the Bulk Data Export include my historical Search Console data?

No. The export only collects data going forward from the date you enable it. It does not backfill the prior 16 months, and there is no way to recover that historical data into BigQuery later. Because of this, the best practice is to enable the export as early as possible so your archive starts growing immediately, even if you have no immediate analysis planned.

How is the Bulk Data Export different from the Search Analytics API?

The API is a pull model where you request specific data and receive paginated, sometimes sampled results within the 16-month window. The Bulk Data Export is a push model where Google writes complete, unsampled data into your own BigQuery dataset every day with no row limit. The export is for large-scale, long-term storage, while the API is better for on-demand, programmatic pulls.

How much does the BigQuery export cost?

For typical Search Console data volumes, storage costs are usually only a few cents to a few dollars per month. The cost to watch is query scanning, which BigQuery bills by the amount of data read. Always filtering your queries on data_date keeps costs low because the tables are partitioned by date, and BigQuery's monthly free tier covers a substantial amount of processing.

Why is the position field stored as a sum instead of an average?

The export stores sum_position so you can aggregate correctly across rows. To compute average position, divide sum_position by total impressions and add 1, following Google's convention. Storing the sum rather than a pre-averaged value lets you roll positions up across any grouping without distorting the math.

What are anonymized queries in the export?

Some search queries are withheld by Google for privacy, typically rare or personally identifying terms. In the export these rows have a null query and is_anonymized_query set to true. They still carry impression and click counts, so decide deliberately whether to include them: filtering them out gives clean per-query breakdowns but slightly understates total volume.

Can I change the BigQuery dataset region after setup?

No, the dataset location is fixed when you create the export and cannot be changed without recreating it. Choose the region carefully at setup time, especially if you plan to join the Search Console data against other BigQuery datasets, since cross-region joins are restricted. If you must change it, you would disable the export, create a new dataset in the desired region, and re-enable, accepting that the new dataset starts fresh from that point forward.