Search Console to BigQuery Export: The Complete SEO + GEO Guide

The Search Console interface shows you 1,000 rows per report and keeps 16 months of history. For an 80-article site, that's plenty. For a 5,000-page site, or as soon as you want to join your queries with anything other than Search Console data, you end up exporting CSVs by hand. The bulk data export to BigQuery fixes that: Google pushes your performance data into a database every day, you query it in SQL, and you keep it as long as you want.

Here is how to wire it up without breaking the export, how to read the schema correctly (the average position trap catches everyone), how to write three useful queries, and how to avoid being misled by the bill or by anonymized data.

Why BigQuery instead of the interface

Three Search Console limits push people to export:

• 1,000 rows per view: on a large site, the long tail of queries stays invisible.
• 16 months of history at most: you can't compare three years of seasonality if you archived nothing.
• No joins: you can't cross a query with revenue, CRM data, or server logs.

The bulk export, announced by Google in February 2023, removes all three. It contains all the performance data available to Search Console for your property, with one important exception: anonymized queries are excluded (more on that below). It doesn't replace the API either: if you need a light 500-row dashboard, the API is enough. The export pays off when volume or retention becomes the problem.

Setup in 10 short steps

On the Google Cloud side

1. Open your Google Cloud project (or create one, with billing enabled).
2. Under APIs & Services, enable the BigQuery API and the BigQuery Storage API.
3. Under IAM & Admin, add the service account search-console-data-export@system.gserviceaccount.com with two roles: BigQuery Job User (bigquery.jobUser) and BigQuery Data Editor (bigquery.dataEditor).

You need to be a project owner to grant these. Add only those two roles, at the intended project level, nothing more.

On the Search Console side

4. Go to Settings > Bulk data export for your property.
5. Enter the project ID (not the project number).
6. Choose the dataset name (it starts with searchconsole).
7. Pick the dataset location. Think about it: once the export starts, you can't easily change it.
8. Confirm. The export is scheduled.

Afterwards

9. Wait up to 48 hours before the first table shows up. That's normal, don't reconfigure in the meantime.
10. Check that the tables appear, then set partition expiration right away (see the costs section).

What the dataset contains

Three objects to know:

Key columns in both data tables: data_date (the day, in Pacific Time, so it may be offset from your local reports), query, is_anonymized_query, country, search_type (web, image, video, news, discover, googleNews), device, impressions, and clicks. The URL table adds url and booleans for search appearances.

The average position trap

There is no "position" column. There is sum_top_position (property table) and sum_position (URL table), which are sums you must divide by impressions, adding 1 because the value is zero-based. The formula is documented in the Search Console table reference:

SUM(sum_top_position) / SUM(impressions) + 1

If you run AVG(sum_top_position), your result is wrong, and nobody will notice until you compare against the interface. Always sanity-check against a known query.

Three queries worth running

1. Striking-distance queries

Queries between positions 8 and 20 with enough impressions: these are the pages to push first.

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

Sorting by impressions * (21 - position) surfaces queries that are both visible and close to page one. Filter on search_type if you want to isolate web from images or Discover.

2. Pages competing with each other (cannibalization)

SELECT
  query,
  COUNT(DISTINCT url) AS nb_urls,
  SUM(impressions) AS impressions
FROM `my-project.searchconsole.searchdata_url_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
  AND is_anonymized_query = FALSE
GROUP BY query
HAVING nb_urls > 1 AND impressions >= 100
ORDER BY impressions DESC
LIMIT 50;

One query served by several URLs isn't always a problem (a home page and an article can coexist), but it's the starting list for the audit described in our guide to keyword cannibalization.

3. Measure the hidden share: anonymized queries

SELECT
  is_anonymized_query,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions
FROM `my-project.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
GROUP BY is_anonymized_query;

This tells you what share of your clicks comes from queries Google won't show you. It's the most misunderstood blind spot of the export: your per-query analyses never cover 100% of your traffic.

Illustrative mini case: recovering forgotten traffic

Take a fictional case, for orders of magnitude. An e-commerce site with 3,000 pages sees a query long tail truncated at 1,000 rows in the interface. After a quarter with the export running, the striking-distance query returns 120 queries in positions 8 to 20 with at least 50 impressions, 40 of which appeared in no interface report. Three pages hold half the impressions concerned: that's where you rewrite the title, intro, and internal linking first. The gain isn't guaranteed, but the priority list is finally complete.

Costs and guardrails

BigQuery storage and queries are billed beyond Google Cloud's free quota. Three reflexes:

• Set partition expiration: with no setting, data accumulates forever. Google recommends keeping at least 14 days; keep far more if the goal is archiving, but decide it, don't suffer it.
• Don't change the schema of generated tables: create views or derived tables instead.
• Always query with a data_date filter: the table is date-partitioned, so a SELECT * without a filter reads the entire history and costs accordingly.

Also watch the ExportLog table: it tells you what was actually saved. A non-persistent error is retried the next day, but a lasting access problem has to be fixed on your side (roles, billing).

What about GEO?

Search Console doesn't tell you whether ChatGPT or Perplexity cites you. But the export helps in two ways: you can isolate long conversational queries (eight words and more) that look like prompts, and you keep years of history to spot the effect of AI Overviews on your clicks. For Google's own AI reporting, also see our article on the Search Console AI report.

Checklist before you close the tab

1. BigQuery API and BigQuery Storage API enabled.
2. Service account added with exactly two roles.
3. Project ID (not the number) entered, location chosen knowingly.
4. Partition expiration set.
5. First table present after 48 hours, ExportLog checked.
6. Position recomputed with SUM(sum_top_position) / SUM(impressions) + 1, verified against the interface.
7. Anonymized query share measured.

If you want to know where your site stands before all this, get your score /100 for free: the audit flags the pages to fix on the technical SEO and GEO side, and the full PDF report details the actions. For the business measurement behind your data, also read how to measure SEO ROI with GA4.

FAQ

Is the BigQuery export free?

Storage and queries in BigQuery are billed beyond Google Cloud's free quota, and a billing account is required. Set partition expiration and always filter by date.

How long before I see data?

Up to 48 hours after successful setup for the first export. After that the export is daily. Data isn't retroactive: you only get history generated from activation onward, which is why it's worth enabling early.

Why don't my numbers match the interface?

Three common causes: anonymized queries are excluded from the export, data_date is in Pacific Time, and average position must be recomputed as the sum divided by impressions, plus one.

Can I change the dataset location later?

Not easily once the export has started. Choose it before confirming, based on where the other data you want to join lives.

Key Takeaways

• The bulk export pushes your Search Console data into BigQuery every day, with no 1,000-row limit and no 16-month cap.
• Setup: two APIs, a service account with two roles, project ID, dataset, location, then a 48-hour wait.
• Two data tables: by property and by URL; recompute position with SUM(sum_top_position) / SUM(impressions) + 1.
• Anonymized queries are excluded: measure their share before drawing conclusions.
• Set partition expiration, leave the schema alone, and always filter on data_date.