Search Console BigQuery export: keeping your data past 16 months
Search Console keeps 16 months of performance data and permanently deletes the rest. The moment you need a two-year comparison, you find out that the first eight months no longer exist. A bulk data export to BigQuery solves this, but only from the day you switch it on. That is why the right time to read this article is before you hit these limits. When I am about to do SEO for a client, this is the first thing I ask. How much data do we actually have, and over what period?
What the 16-month limit really costs you
The limit sounds fairly academic until a real question runs into it. Was this April’s drop worse than the April drop two years ago? Did last year’s category rebuild bring more than seasonal growth? How much did this section make before the migration 18 months ago? Each of these questions is a comparison across more than 16 months, and Search Console answers all of them the same way. You can no longer get that data in the reports or through the API.
There is also a second, less visible cost: limited granularity. The Search Console interface limits exports to a thousand rows, and the much better Search Analytics API still stops at 50,000 rows per day, per search type and per property. On a large site, the long tail beyond those rows is real traffic you simply never see. The bulk export has no row limit, you get the whole table.
What the bulk export is, and its only hard limitation
Since February 2023, Search Console can send a daily export of performance data to a BigQuery project you own. Once it is switched on, three tables appear, and each day they grow by one day of data.
searchdata_site_impression, performance aggregated by property, meaning query, country, device and search type, along with clicks, impressions and position. If two of your URLs appear for one query, it counts here as one impression.searchdata_url_impression, the same, aggregated by URL. The bigger and more useful table, this is where page-level analysis, section totals and migration analysis happen, including boolean flags for rich result types.ExportLog, a record of every successful daily export. Note the word successful, failed exports are not written here, which determines how you monitor the pipeline (more below).
You have to accept one thing before you start planning anything. The export runs from the day you switch it on, and it cannot fill in data retroactively.
The first export happens up to 48 hours after successful setup and includes data for the day of the export. You can fill in older data only as far as it is still available in Search Console, and that window keeps moving. That is the strongest argument for switching the export on right now on every property that matters, even if you are not planning any analysis. Storage for this data costs very little, and the history it collects is something you cannot buy later.
My tip from practice. I switch on the bulk export for every new client property right at the start of the engagement, before any analysis is even commissioned. The reason is that if I do not know how the project has done over the last few years, it is hard to find the causes of a performance drop or to plan further growth. If the data is never needed, it cost only the setup time. If it is needed, whether for an analysis of site performance, the effect of a migration to a new CMS, a comparison around an algorithm update or a seasonality model, you have it at hand. If we have the data, we do not have to guess.
What the export does not contain
This is where you need to be precise, because this gap surprises people in the middle of a project. The bulk export carries only performance data, meaning queries, URLs, clicks, impressions and position.
It does not contain the Page indexing report, crawl stats or any other Search Console report. If the question is “which URLs dropped out of the index”, the search tables in BigQuery cannot answer it.
That data comes from the URL Inspection API (limited to 2,000 inspections per day and 600 per minute per property) or from the Page indexing report. My complete monitoring uses both, the export for performance history and checks of selected URLs for indexing status. They come together in BigQuery, but each arrives from a different place.
One more built-in gap is anonymized queries. Anonymized queries do not disappear from the export: the row stays, only the query field has the value null and the row is marked with the is_anonymized_query flag.
Query-level analysis therefore works with a subset, and on sites with a large long tail that subset is noticeably smaller than the whole. During setup, compare the totals with the Search Console reports once. That way you check the share of anonymized queries in your own data.
And whatever you find in Search Console, answers from assistants such as ChatGPT or Perplexity are not in it at all. How your brand appears in them is a separate measurement with its own process.
Export, API or both? The decision
| You need | Use | Why |
|---|---|---|
| Ongoing performance data history from today onward | Bulk export | No row limits, daily, no maintenance once switched on |
| Capture the last 16 months before they expire | One-off backfill through the API | The export does not reach back, the API does, within its row limits |
| Small site, occasional questions | API pulls as needed | Row limits rarely matter on small sites, and a data warehouse can be unnecessary overhead |
| Large site, serious ongoing analysis | Both | The API fills in the past once, the export covers everything from today onward |
Combining both is the practical answer for most sites where this matters. Run a one-off API backfill of the last 16 months into a staging table, switch on the export in the same week, and join the two sets on date. From then on, the pipeline runs without intervention.
How to run it without surprises
- You can usually handle the basic setup within a few hours. A project in Google Cloud with billing enabled and the BigQuery API, then grant the Search Console service account (
search-console-data-export@system.gserviceaccount.com) the BigQuery Job User and BigQuery Data Editor roles and, in the property settings, switch on the export. Only a property owner can do this. Google simulates the first export and emails the owners, and ongoing exports start within about 48 hours. - Handle storage costs by design. The tables are partitioned by
data_date. Always filter on the partition column, and set partition expiration or table expiration only if you really want the history to expire (the whole point here is usually that you do not). For most sites the monthly costs are small. For very large properties, check the daily table growth in the first week and decide on retention deliberately. - Watch for missing days, not just errors. You will hear about most errors. Google emails every property owner and every full user when an export error occurs and when it is resolved. This does not apply to transient errors, such as server connection errors. A failed export is not written to
ExportLog, and Google only retries a missing day for about a week. A check query for the missing partition is your safeguard in case the email gets lost or the day is dropped after a week. A reliable check is therefore a scheduled query that asks whether the expected partition exists and has a plausible row count. - Build a layer of database views on top of the raw tables, not dashboards directly on them. A thin layer of views (URLs grouped into sections, flags for branded and non-branded queries, and time dimensions) keeps every downstream report consistent and every fix in one place. This is what turns a data export into SEO management instead of a one-off setup.
What the check query looks like
This is the monitoring job from point 3 above. Schedule it for every morning and set up an alert when it returns an empty result or a suspiciously low row count. Just replace the project and dataset name.
SELECT
data_date,
COUNT(*) AS row_count,
SUM(clicks) AS total_clicks,
SUM(impressions) AS total_impressions
FROM `your-project.searchconsole.searchdata_url_impression`
WHERE data_date = DATE_SUB(CURRENT_DATE("America/Los_Angeles"), INTERVAL 2 DAY)
GROUP BY data_date
Two details matter. I ask for the day before yesterday, not yesterday, because the export has a delay and yesterday’s partition may still legitimately be missing. And the WHERE is on the partition column, so the query scans one day of data, not two years. Skip this filter and you get your first unexpectedly high BigQuery bill from exactly the check job that was supposed to confirm everything is fine.
What to avoid
- Do not put off switching on the export until “the analytics project gets going”. Every month of delay is a month of history that will not exist. Switch it on first, plan later.
- Do not promise stakeholders completeness at query level. Anonymized queries are excluded by design. Report totals from totals, query analysis from the visible subset, and always label which is which.
- Do not expect indexing or crawl data in these tables. Performance only. Indexing status is a separate pipeline with its own quota, and so is what your server logs know about Googlebot.
- Do not query without a partition filter.
SELECT *across two years of thesearchdata_url_impressiontable on a large property is how surprising BigQuery bills happen. - Do not compare BigQuery numbers with the Search Console interface and panic over small differences. The interface itself shows filtered views and leaves out anonymized queries. The export is the more complete record, not the other way around.
- Do not switch on the export for a different property from the one you analyze. An export on the domain property and analysis on a URL-prefix property mean the numbers will not match. Decide on the source of truth up front.
- Do not set expiration to save money. A year later, you find the history has disappeared just like in Search Console. Costs are controlled through table size, not through expiration.
- Do not build dashboards directly on the raw table. After a change to the site structure, every chart falls apart. A layer of views with URLs grouped into sections belongs between the data and the report.
What opens up once the data builds up
A data pipeline pays off for questions that could not be answered before. A real year-over-year comparison at URL and query level, windows before and after a site migration or a Google algorithm update measured on identical query sets. You can also compare section trends joined with revenue data and analyze the long tail.
Migrations come with one limitation. The data shows the effect of a migration, but you find the cause in the redirect map, and that has to be verified right after launch, not from a drop in the chart three months later.
None of this requires complex SQL. Everything rests on one decision that has to come in time. Whoever makes it today will have the data a year from now. Whoever puts it off until it is needed will still have only the last 16 months a year from now, just a different period than today.
If you want to do quality SEO for clients, this is where I would start.
Want your search data to work as an asset, not a screenshot?
I set up the export, backfill what can still be saved and build monitoring on top of it. The next hard question will then have an answer.