GSC BigQuery Export: Setup, the Three Tables, and Your First Queries

There is a version of your Search Console data that is deeper than the interface, deeper than the API, and permanent. Google will stream it to you daily, free, forever. The catch is one line in the documentation most people read too late: it starts from zero on the day you enable it.

That is the bulk data export to BigQuery, shipped in February 2023 and still the least-used serious feature in Search Console. This guide is the setup done right: the Cloud project, the switch, the three tables you get, the starter SQL, and the one arithmetic gotcha that flatters every ranking by one position.

What the export gives you that nothing else does

Three things, each unavailable anywhere else in the product:

• Depth. The export carries more of the query long tail than the API returns, so the "small" queries that never make your 25,000 API rows still land in your tables.
• The anonymized aggregate. Anonymized queries get one combined row per day, clicks and impressions with a null query string. It is the only place their size is visible at all.
• Permanence. The interface forgets everything older than 16 months. Your dataset forgets nothing, which is the retention argument covered in keeping GSC data past the 16-month limit.

Step 1: Prepare the Google Cloud project

The export needs somewhere to write. You need Owner permission on the GSC property and admin rights on a Cloud project:

1. Create or pick a Google Cloud project and note its project ID, the lowercase-with-hyphens one, not the display name.
2. Enable billing on the project. The export will not run without it, even when usage lands inside the free allowances.
3. Grant Search Console's service account its two BigQuery roles in that project, exactly as the GSC setup screen names them. The screen shows the account address to grant; copy it rather than retyping.
4. Set a billing alert now, while you are in the console. A threshold you never hit costs nothing; a surprise invoice costs trust in the whole pipeline.

Step 2: Switch the export on in Search Console

1. In Search Console, open Settings → Bulk data export.
2. Paste the project ID from step 1.
3. Pick the dataset location deliberately. Changing it later means a new dataset, so choose the region your other data lives in.
4. Confirm, then wait up to 48 hours for the first daily tables. Empty until then is normal.

Step 3: Learn the three tables

Once data flows, the dataset holds three tables, and knowing which one answers which question saves most of the early confusion:

The practical split: property questions ("how did clicks trend this quarter?") read the site table, page questions ("which URLs are decaying?") read the URL table, and "is this thing still running?" reads ExportLog. The URL table is much larger, so point exploratory queries at the site table first and your bill will thank you.

Step 4: Run your first three queries

Three starters cover the first month of questions. Replace the dataset name with yours:

1. Daily clicks trend, the sanity check that data is arriving and charts match the UI roughly:

SELECT data_date, SUM(clicks) AS clicks
FROM `yourproject.searchconsole.searchdata_site_impression`
GROUP BY data_date
ORDER BY data_date DESC
LIMIT 28;

2. Top queries with correct positions, including the plus-one:

SELECT query,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SUM(sum_position) / SUM(impressions) + 1 AS avg_position
FROM `yourproject.searchconsole.searchdata_site_impression`
WHERE is_anonymized_query = false
  AND data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
GROUP BY query
ORDER BY clicks DESC
LIMIT 50;

3. Your anonymized share, the number the interface never states:

SELECT
  SUM(IF(is_anonymized_query, clicks, 0)) / SUM(clicks) AS hidden_share
FROM `yourproject.searchconsole.searchdata_site_impression`
WHERE data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY);

The zero-based position gotcha

The export's sum_position counts the top ranking as 0, not 1. Average position is therefore sum_position / impressions + 1, and the plus one is not optional: without it, every ranking you report is one position better than Search Console shows, your "position 1" rows are impossible, and someone eventually notices in a meeting.

Step 5: Put verification on a calendar

A retention pipeline's worst failure is silent death. Monthly, thirty seconds:

• Check ExportLog has rows for last month. That is the whole check; save the query from step 4 style and rerun it.
• Spot-compare one number against the Search Console UI, same property and date range. Expect close, not identical: anonymized handling and provisional recent days differ by design.
• Re-annotate property changes. A move to a new domain property starts a new data lineage, and the note belongs in your SEO annotations the day it happens.

Tips before the dataset grows

• Query the site table by default. It answers most questions at a fraction of the URL table's scan cost.
• Always filter by data_date. The tables are date-partitioned, and a date filter is the difference between scanning a month and scanning the archive.
• Decide the anonymized row's fate per query. Include it for totals, exclude it for query-level analysis, and be consistent, or your own numbers will disagree with each other.
• Save every query you run twice. The saved-query list becomes your reporting layer faster than you expect.
• Chart it if SQL fatigues you. Looker Studio pointed at your own dataset shows unlimited history, which the stock GSC connector never can.

Why the export looks broken, and what to check

• Dataset empty past 72 hours? The setup failed quietly. Recheck the pasted project ID, the service account roles, billing enablement, and that your GSC permission is Owner, then redo the Bulk data export screen.
• Numbers never quite match the UI? By design. Anonymized queries, provisional recent days and different aggregation levels each contribute small gaps. Alarm at large divergence, not at pennies.
• Costs creeping up? Almost always unfiltered queries over the URL table. Add date filters, query the site table where possible, and check nothing scheduled is scanning the full archive nightly.
• A gap in the dates? Check ExportLog for the missing days, and whether the window is still inside GSC's 16 months. If it is, an API pull patches it; if not, the gap is permanent, and it argues for the monthly verification you skipped.

Where this fits in the toolchain

• The export is the archive: raw, complete, permanent, and yours to query.
• CrawlRaven is the reading layer for decisions: the same Search Console data synced daily, joined to GA4 and a 200-point crawl, returned as a ranked plan with annotations on the timeline. The warehouse tells you what happened; the join tells you what to fix first, like which archive rows are really striking distance keywords worth a push this month.
• Free, on this site: the GSC regex generator builds the brand patterns your WHERE clauses will borrow for the branded split.

Enable it this week, run the three starter queries, put the verification on the calendar, and let it grow. The export is the rare SEO task you do once and benefit from for the life of the site.