WriteMySEO / Blog / Search Console's BigQuery Export Stores Position as a Sum
SEO data

Search Console's BigQuery Export Stores Position as a Sum

The bulk export has no average position column, two tables that count impressions differently, and NULL queries that still carry clicks. Here's how to query it correctly.

If you turn on Search Console's bulk data export and run the obvious query, you get numbers that look wrong. There is no position column. There are two tables with different impression counts for the same day. A large block of rows has a NULL query but real clicks attached to it. And the totals don't match the UI you were trying to escape.

None of this is a bug. The export is a raw event-aggregate feed, not a report, and it stores things in the form that makes aggregation possible rather than the form that makes a dashboard easy. Once you know the storage model, the discrepancies resolve.

Why is there no average position column?

Average position can't be stored per row, because rows are already aggregated. A row in the export is a bucket: one date, one query, one country, one device, one search type, and (in the URL table) one URL. That bucket might contain 40 impressions at positions ranging from 4 to 19. Storing "average 7.3" would be lossy — you could never re-aggregate it across devices or dates without impression weighting, and most people would forget to weight it.

So Google stores sum_position instead: the sum of the positions over all impressions in that bucket. And Google documents that the value is zero-based. Position 1 in the SERP is stored as 0. To get the average position the UI would show, you divide and add one:

SELECT
  query,
  SUM(impressions)                              AS impressions,
  SUM(clicks)                                   AS clicks,
  SUM(sum_position) / SUM(impressions) + 1      AS avg_position
FROM `project.searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN '2025-01-01' AND '2025-01-28'
  AND search_type = 'WEB'
GROUP BY query

Two failure modes are common here. The first is AVG(sum_position), which averages bucket totals — meaningless. The second is forgetting the + 1, which shifts every number one position better than reality and makes your export look systematically more optimistic than the UI. If your export shows an average position of 6.2 where Search Console says 7.2, that's the off-by-one, not a data problem.

The site-level table uses sum_top_position, which is also documented as zero-based and gets the same treatment.

Which of the two tables should you query?

The export writes two fact tables, and they are not drill-downs of each other. They count different events.

searchdata_site_impressionsearchdata_url_impression
URL columnNoYes (url)
Position columnsum_top_positionsum_position
Counting unitOne impression per query, per propertyOne impression per URL
Search appearance flagsNoYes, as is_* booleans

If two of your URLs rank for the same query on the same SERP, the site table records one impression and the topmost of the two positions. The URL table records two impressions and two positions. That is the same property-versus-page aggregation Search Console has always applied; the export just exposes both sides of it in separate tables instead of hiding the switch behind a report tab.

The practical rule: use site_impression for anything you report as site-level query performance, and url_impression for anything that needs a URL. Do not sum the URL table and call it site traffic, and do not join the two tables on query expecting the impressions to reconcile.

What are the rows with a NULL query?

Search Console withholds queries that are rare enough to be potentially identifying. In the UI those impressions silently vanish from the query list while staying in the totals. In the export they are explicit: is_anonymized_query = true and query IS NULL, with the clicks and impressions still attached.

This is better, because you can measure the gap instead of guessing at it. It also means WHERE query IS NOT NULL quietly deletes a real and often substantial share of your clicks. Report the anonymized bucket as its own line rather than dropping it, and track it over time — if it grows, your long tail is fragmenting, which changes how you should read query-level trends.

Why doesn't the export match the Search Console UI?

Several reasons, in rough order of how often they bite:

What does the export let you do that the API doesn't?

The search appearance booleans on the URL table are the underrated part. In the UI, appearance is a filter — you can see how video results performed, but you can't group by appearance and have the parts sum to the whole, because one impression can carry several appearance types at once. In the export each row carries its flags, so you can decide your own counting rule: count an impression once per flag, or pick a precedence order, or measure overlap directly.

The other gain is joins. Query-level data sitting in the same warehouse as your crawl output, log sample, or conversion data turns "which queries did this template win" into one SQL statement instead of three CSV exports.

One cost note: both tables are partitioned on data_date. Always filter on it. Without a partition filter, every exploratory query scans the entire history.

What to do with this

  1. Enable the export now on every property you care about, even if you have no immediate use — the history starts accumulating from that date and cannot be recovered later.
  2. Build one view per table that already applies + 1 to the position arithmetic and filters site_url, so nobody downstream reinvents the off-by-one.
  3. Decide explicitly, per report, whether the question is site-level or URL-level, and query the matching table.
  4. Keep the anonymized-query rows in your totals and surface their share as a metric.
  5. Rebuild the last two to three weeks of any materialised aggregate on every run, rather than appending.
  6. Reconcile against the UI once, on a single day, with search type pinned to WEB — then stop reconciling and trust the export.
Search ConsoleBigQuerymeasurement

WriteMySEO produces marketing content, not legal, medical, financial, or compliance advice. Figures cited reflect publicly reported industry data at time of writing and shift over time.

Get started

We write this well about your industry, every month.

AI-drafted, human-reviewed SEO content on a flat subscription. Blog posts, metadata, schema, and internal links, shipped on a monthly rhythm.

See plans

More from the blog