BigQuery bulk export: complete guide

Configure daily exports, understand the schema, and retain large-scale performance history.

Partially automatable Advanced

Search Console bulk data export writes daily Performance data into a BigQuery dataset. It is the preferred source when API top-row limits omit important long-tail combinations, the team needs retained history, or Search data must be joined to a governed warehouse.

It is an ongoing export, not a historical download or real-time stream. Configure it before the data is needed, monitor every day, and query its partitioned tables with the correct aggregation math.

What bulk export includes

Search Console creates three tables in the chosen dataset:

Table	Grain and purpose
`searchdata_site_impression`	Performance rows aggregated at property level
`searchdata_url_impression`	Performance rows with URL-level dimensions and page-oriented search features
`ExportLog`	Records successful exports to the data tables

The data tables include dimensions such as date, property, Search type, country, device, query, and supported search-appearance flags, plus clicks, impressions, and summed position fields. The URL table also includes the URL.

Bulk export is not constrained by the Search Analytics API’s 50,000-row daily limit. Query text that Google anonymizes for privacy is not revealed. Its metrics are represented with is_anonymized_query and a blank query value, allowing totals to include protected activity without identifying the query.

Decide whether the operational cost is justified

Bulk export is valuable when one or more of these are true:

API extracts regularly reach their daily row cap;
the property has large page-query or rich-result cardinality;
more than 16 months of retained history is required;
SQL transformations and joins are already governed in BigQuery;
multiple dashboards or models need one durable source;
long-tail data materially changes content, product, or technical decisions.

It can be unnecessary for a small property whose important data fits comfortably in Search Console or the API. Search Console does not charge to send the export, but BigQuery storage and query processing are billable beyond free usage.

Prerequisites and permissions

You need:

owner permission on the Search Console property;
a Google Cloud project with billing configured;
BigQuery API and BigQuery Storage API enabled;
permission to grant IAM roles in that project;
a deliberate dataset location and retention policy.

Grant [email protected] these project roles:

BigQuery Job User (roles/bigquery.jobUser);
BigQuery Data Editor (roles/bigquery.dataEditor).

These are write permissions for the export service. Keep analyst and dashboard access separate and read-only where possible.

Configure the export

In Search Console → Settings → Bulk data export:

enter the Google Cloud project ID, not its project number;
choose a dataset name, which begins with searchconsole;
choose the dataset location;
confirm and let Search Console test detectable settings.

Use a distinct dataset for each Search Console property. Two properties cannot export to the same destination dataset. The location is difficult to change after export begins, so align it with governance, residency, and downstream datasets before saving.

The first export can take up to 48 hours after successful configuration. It begins with the day of the first export; it does not backfill earlier Search Console history. Retrieve available prior periods separately through the API if needed and keep their coverage limitations explicit.

Understand the daily pipeline

Search Console writes each data table daily, not necessarily at the same time. ExportLog commonly receives separate success rows for the site and URL tables.

Important fields include:

data_date: the Search performance date in Pacific Time and the data-table partition date;
namespace: which table was exported;
publish_time: when that export completed;
epoch_version: starts at zero and increments if Google republishes a table/date correction.

Do not mark a day ready merely because one table appeared. Require the expected successful namespace entries and use the published partition, not the current wall-clock date, as the processing contract.

Unsuccessful attempts are not recorded in ExportLog. Monitor Search Console’s bulk export settings, owner/full-user messages, and Cloud Logs as well as the table.

Never assume rows are pre-consolidated

Google warns that rows are not guaranteed to be consolidated by date, URL, site, or any combination of keys. Always aggregate metrics.

A property-level daily query should resemble:

SELECT
  data_date,
  SUM(clicks) AS clicks,
  SUM(impressions) AS impressions,
  SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
  SAFE_DIVIDE(SUM(sum_top_position), SUM(impressions)) + 1 AS avg_position
FROM `project.searchconsole.searchdata_site_impression`
WHERE search_type = 'WEB'
  AND data_date BETWEEN DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 1 DAY)
GROUP BY data_date
ORDER BY data_date;

Site-level position is zero-based in sum_top_position; adding one after impression-weighted division converts it to the familiar one-based average. The URL table uses its documented summed position field. Never average row CTR or position values.

Choose the right table

Use searchdata_site_impression for property totals and query/country/device analysis that does not need a URL. Use searchdata_url_impression when the question requires a landing URL or URL-level search appearance.

Do not calculate a property total by combining both tables. Site-impression and URL-impression aggregation follow different Search Console rules and answer different questions. Keep the source table in every modeled table’s lineage.

Search types are separate. Filter or group by search_type; do not silently add Web, Discover, Google News, image, video, and News-tab activity into a single metric.

Query anonymized activity correctly

If the analysis needs named queries, exclude protected rows using the documented flag or blank query value. If it needs total performance, include them.

Label named-query coverage separately:

named query share = named-query impressions / all impressions

Do not rename the blank value to “(not provided)” and then treat it as a real query category in clustering. It combines privacy-protected activity whose individual strings cannot be recovered.

Control query and storage costs

The performance tables are partitioned by data_date. Every routine query should include a bounded date predicate so BigQuery can prune partitions.

Also:

select only required columns rather than SELECT *;
create tested aggregate tables for repeated dashboard grains;
inspect bytes processed before running exploratory queries;
use budgets and billing alerts;
set an appropriate partition expiration if indefinite retention is unnecessary;
schedule transformations only after source partitions are complete;
keep destination datasets in the same compatible location.

If a partition expiration is applied to Search Console tables, Google recommends at least 14 days. Do not alter the exported table schema. Added or changed columns can break future exports.

Monitor and recover from failures

Search Console retries non-transient failures on later daily runs and retains failed-export data for about a week. After enough failed attempts, a date can be abandoned; after roughly a month of ongoing failures, the bulk export can stop.

Alert on:

an expected ExportLog namespace missing after its normal arrival window;
the latest Search Console export status showing an error;
permission, billing, API, quota, location, or schema failures;
a source partition with implausibly zero rows;
an unexpected epoch_version change that requires downstream reprocessing.

Fix errors promptly. The Test report action checks readily testable configuration but does not trigger an export and cannot guarantee the next scheduled run will succeed.

Common failure causes include missing billing, disabled BigQuery APIs, revoked service-account roles, Cloud quota exhaustion, dataset-location mismatch, organization policies, an expiration shorter than permitted, and schema modification.

Build an idempotent transformation layer

Treat each namespace + data_date + epoch_version as a source event. When an epoch changes, rebuild that date in downstream aggregates. Prefer MERGE or partition replacement over unguarded INSERT operations.

A safe daily sequence is:

wait for both expected source-table success records;
validate row counts and required schema;
replace the affected modeled partitions;
run metric reconciliation tests;
publish a freshness marker for dashboards;
alert only after the pipeline’s expected arrival window.

Keep raw export tables untouched. Put normalization, URL taxonomy, query classification, and business joins in separate datasets so Google can evolve the managed schema without colliding with custom changes.

Governance and access

Bulk data can expose sensitive business performance, URLs, markets, and query themes. Apply least-privilege IAM, dataset-level access, audit logs, retention policy, and documented ownership.

Avoid granting dashboard viewers direct edit access. If a report uses owner credentials, its owner becomes a critical dependency; production dashboards are safer with intentionally managed identities and reviewable permissions.

Record:

source Search Console property;
Cloud project, dataset, and location;
data owner and pipeline owner;
retention and deletion policy;
expected daily arrival window;
alert recipients and recovery runbook;
transformations and metric definitions.

Common bulk-export mistakes

Enabling export after the desired historical period and expecting backfill.
Sending multiple properties to one dataset.
Querying without a date partition filter.
Reading raw metric columns without SUM.
Averaging CTR or position.
Combining site and URL tables into one total.
Excluding anonymized rows from totals without disclosing the coverage loss.
Altering the managed table schema.
Monitoring only ExportLog, which contains successes but not failed attempts.
Ignoring an error until Search Console abandons unrecoverable dates.