Export Search Console data to BigQuery: setup checks
Read the conclusion first
Use bulk export when you need a durable, queryable Search Console dataset, and budget for Cloud storage and queries. Setup is not a successful export. Verify the first saved date and both performance tables, then aggregate repeated rows correctly. The setup does not backfill your earlier history.
Applicable conditions
Applies to large-scale analysis or long-term data warehouses; not required for every small site.
Before you open the report
The configured project/dataset, export start date, service permissions, export logs and partition dates.
Use one concrete URL or report first. Keep account credentials and private visitor data out of shared notes.
Steps and verification
Decide what the export needs to solve
A team needing monthly page-query analysis beyond small top-row reports has a reason to retain a warehouse. A team that only needs one weekly chart may not need another paid system. Define the property, retention period, people who can query it and a cost budget before enabling export.
Historical data before setup needs a separate, clearly labelled collection from reports or the API. Keep its limits and schema separate from the native daily export; loading an API extract into BigQuery does not make that extract complete.
Configure the destination deliberately
Use a Cloud project with billing and BigQuery enabled. Give Google's documented export service account the BigQuery Job User and BigQuery Data Editor roles. In Search Console an owner configures Bulk data export with the project ID, dataset name and location. A project number is not the project ID.
Check the location and organization restrictions before confirming. The first export can take up to 48 hours after successful setup. Record the configuration time and wait for actual tables and dates rather than treating the confirmation screen as a completed transfer.
Prove that a day was saved, then query it
Check searchdata_site_impression, searchdata_url_impression and ExportLog. The site and URL tables use different aggregation, and each can complete separately. ExportLog records successful exports; absence there needs investigation alongside the latest export status, not an invented zero-traffic day.
Aggregate repeated keys with SUM and filter the data_date partitions for the requested interval. Calculate CTR from total clicks divided by total impressions, not an average of row CTRs. Position uses the relevant summed position field divided by impressions, plus one because the stored position basis is zero-based.
Retain privacy, cost and failure boundaries
Anonymized-query flags identify rows whose query text is withheld. Do not label an empty query as a user's literal search or claim you recovered hidden words. Named-query rankings and all-traffic totals answer different questions.
Set an intentional partition retention period of at least 14 days for the exported tables and avoid altering their schema. Review scanned bytes before large queries. For a failed export, preserve affected dates, table and error, correct the specific permission or configuration issue, and verify saved partitions after recovery. Do not silently fill missing exports with zeros.
Common mistake: Call export file all user search terms
Anonymized queries and aggregation boundaries still need to be preserved.
How to decide when you encounter different situations
Match your actual observation to a row before changing the page. If it does not match, collect the missing evidence first.
| What you see | Next step | How to confirm |
|---|---|---|
| Only need small-scale daily observation | Evaluate existing APIs first | Do not impose warehouse costs |
| Export interrupted | Check configuration and time interval | Cannot fill zeros and pass it off as normal |
| Need historical backfill | Check officially supported scope | Missing history clearly marked unknown |
A missing export day must stay missing
Teaching scenario. Names, URLs and numbers illustrate the method; they are not customer or measured site results.
- What you observe
- Example: a daily export has no partition for Tuesday. The dashboard inserts zero and reports a complete traffic collapse.
- What it establishes
- No partition is an ingestion issue until checked, not a valid observation of zero searches. Export start and available history also limit coverage.
- What to do next
- Inspect export logs and the relevant project permissions before changing queries. Maintain a date-coverage ledger and prevent calculations from silently treating missing dates as observed zero.
What counts as a checked result
Confirm the repaired partition and its source period when available. Report any unrecovered gap explicitly; do not invent historical backfill.
If a required check fails, keep the error and return to the matching decision branch. A blank record means unverified, not passed.
Leave with a checked result, not another vague instruction
- Your deliverable
- A proved export date/table inventory and a cost-bounded query, not just a configured destination.
- Stop and resolve this first
- Do not treat a missing export partition as zero search traffic or an API upload as native full export.
Record missing evidence in the worksheet below. An unresolved test is useful information; a blank field is not a pass.
Checklist for this page
Fill in your own check results, then decide the next step. Blank means not yet verified, not normal. Content is saved only in the current local session and is not submitted to the server or AI.
Frequently asked questions
Has AISEO Site integrated BigQuery?
No, it currently uses Search Analytics queries.
Is BigQuery necessary for a small site’s daily review?
Not automatically. Evaluate your reporting need, access and cost before adding a warehouse workflow.
Official materials and usage boundaries
This page provides operational judgment and checking methods, and does not show customer success cases. Calculation examples and request samples are teaching materials, not actual website metrics. Google's reports, conditions, and interfaces may change; please refer to the current official documentation.
Read the corresponding Google official documentationExport setup, timing and historical-data boundary ↗
Continue with the relevant tool
AISEO Site saves analysis reports for review; BigQuery bulk export is a separate setup. Choose it when your own storage and query workflow requires it.
See how it works →