What the Search Console BigQuery Export Really Gives You

Most teams turn on the Search Console bulk data export expecting the same report they already have, minus the 1,000-row cap. Then the first query returns a property-level impression count that doesn't match the URL-level sum, an average position of 3.4 when the UI says 4.4, and a January that starts on the day someone clicked the button.

None of that is a bug. The export is a different data shape with a different grain and different conventions, and it accumulates history the UI throws away. Understanding the four places it diverges is the difference between a warehouse you trust and one you quietly stop using.

What the export actually is

When you configure bulk data export in Search Console settings, Google begins writing daily dumps into a BigQuery dataset in a Google Cloud project you own. The default dataset is named searchconsole and it contains three tables: searchdata_site_impression, searchdata_url_impression, and ExportLog.

Three properties of the pipeline matter more than anything in the schema:

• There is no backfill. Data starts flowing from the day you enable the export. If you want history, you enable it now on every property you own, even ones you have no immediate analysis plan for. The cost of an unused property is a few dollars of storage.
• It is not capped at 16 months. The UI and the Search Analytics API expire data at roughly 16 months. BigQuery does not. This is the single biggest reason to run the export: year-over-three-years-ago comparisons are impossible any other way.
• You pay for it. Storage and query costs land on your GCP bill. For most sites this is trivial; for very large properties the searchdata_url_impression table grows fast, and a careless SELECT * over all partitions is how you get an unpleasant invoice.

The export is also not a replacement for the API when you need today's data. Rows arrive with a lag, and a date's partition can appear before you'd expect it to be complete.

Why the site table and the URL table don't add up

The two data tables are not a summary and a detail view of the same thing. They are two different aggregations of search results, and reconciling them by summing is a category error.

searchdata_site_impression counts impressions at the property level. If a single results page shows three of your URLs for one query, that is one impression for the property.

searchdata_url_impression counts impressions at the URL level. That same results page produces three rows, one per URL, each with one impression.

So the URL table's impressions will be greater than or equal to the site table's for the same period, and the gap widens on sites that frequently occupy multiple slots — sitelinks, image packs, multiple deep pages for one head term. Use the site table for "how much search demand did this property capture" and the URL table for anything page-level. Never mix them in one metric.

Position is a sum, and it starts at zero

The export does not store average position. It stores the summed position across impressions, and — this trips up nearly everyone — Google's positions in this table are zero-based. The documented formula is:

SELECT
  url,
  SUM(impressions) AS impressions,
  SUM(clicks)      AS clicks,
  SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1 AS avg_position
FROM `myproject.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN '2025-01-01' AND '2025-01-31'
  AND search_type = 'WEB'
GROUP BY url

Forget the + 1 and every position in your dashboard is optimistically off by exactly one. It is a small enough error to look plausible and large enough to change decisions, which makes it the most dangerous mistake in the whole pipeline. Store the corrected value in a view so no analyst ever touches the raw column.

The same applies to sum_top_position in the site table. Note also that summing sum_position and dividing is the only correct way to aggregate — averaging pre-computed averages across days or devices weights every day equally regardless of impression volume.

The anonymized-query rows are data, not noise

Google withholds queries that are rare enough to identify a person. In the UI those impressions simply vanish from the query report, which is why query totals never sum to the property total.

The export handles it differently and far more usefully: the row still exists. The query field is null and is_anonymized_query is true, but the URL, date, country, device, impressions, clicks, and position are all there. That means you can measure the hidden portion instead of guessing at it:

SELECT
  data_date,
  SUM(IF(is_anonymized_query, impressions, 0)) / SUM(impressions) AS anon_share
FROM `myproject.searchconsole.searchdata_url_impression`
WHERE data_date >= '2025-01-01' AND search_type = 'WEB'
GROUP BY data_date
ORDER BY data_date

Two analyses this unlocks. First, true per-URL totals: a page's real impression and click count including long-tail queries you'll never see named. Second, long-tail concentration by template: if one content type carries a much higher anonymized share than another, its traffic is coming from many low-volume queries rather than a few head terms, which changes how you'd expect it to respond to both algorithm shifts and AI-generated answers.

Discover and Google News rows show up here too, under their own search_type values, with no query at all — Discover has no query to report.

How to tell whether a day is actually complete

The ExportLog table records what Google wrote and when. Treat it as your source of truth for completeness rather than assuming a partition with rows in it is finished. A practical pattern:

1. Query ExportLog for the date range you're about to report on and confirm each expected table-date pair is present.
2. Build reporting tables on a trailing window that excludes the most recent few days, and label anything more recent as provisional.
3. Rebuild the last two weeks of any daily rollup on every run rather than appending once, so late or corrected data propagates.
4. Monitor for gaps. A missing date in a time series looks exactly like a traffic collapse on a line chart, and someone will escalate it.

Also always filter on data_date — the tables are date-partitioned, and a query without a partition filter scans everything you have ever exported.

What to do this week

• Enable the export on every property you own, today. Missing history cannot be recovered later, and the setup takes ten minutes.
• Create two views — one per grain — that apply the + 1 position correction and expose clean column names. Point every dashboard at the views, never the raw tables.
• Write one reconciliation query that compares site-table property totals against the Search Console UI for the same range. Investigate any gap beyond rounding before you build on the data, not after a stakeholder finds it.
• Add anonymized share as a standing metric per template or directory. It is the cheapest available read on how much of your traffic lives in the long tail.
• Set a partition expiration only if you are sure you want to, and set it long. The whole point of this pipeline is the history the UI won't keep.