Setting Up Search Console Bulk Exports Without Data Loss

Google Search Console’s bulk data export sends daily Search performance data to BigQuery, Google Cloud’s data warehouse. Large sites can use the export to store and query Search performance data beyond the daily row limit in Search Console’s interface. The export leaves out anonymized queries, so it does not include every query counted in the site’s performance totals. Google explains this in its overview of bulk data export.

The main risk is failing to notice an export problem. Search Console retries missed dates for about a week, then drops data for those dates. After about a month of failed attempts, it stops exporting. These timeframes are approximate, so investigate a missing export promptly. Prepare Cloud permissions before activation, then check that each table has an export for each date.

Prepare the Cloud project

Choose a Google Cloud project to hold the BigQuery dataset, a collection of tables. Confirm that the project’s billing is active, then enable the BigQuery API and BigQuery Storage API. Search Console’s export service needs both of these permissions on the project:

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

Grant these roles to search-console-data-export@system.gserviceaccount.com, the Google-managed account that writes the exports. The Search Console property owner configures the export. A Cloud project administrator can grant permissions to this account, and does not have to be the property owner. Follow Google’s export setup requirements when checking the project and access.

Choose the dataset name and location before you activate the export. Search Console asks for the Cloud project ID, not the project number. The default dataset name is searchconsole. If several Search Console properties export to one Cloud project, give each property its own dataset name that begins with searchconsole.

Choose the dataset location deliberately. Search Console creates the dataset with the first export, and changing its location later requires a migration rather than an in-place setting change. Google warns that a migration can cause one or two days of missed data. Check any organization-level location restrictions before starting.

Activate the export

A Search Console property owner can open Settings → Bulk data export, enter the project ID, dataset name, and location, then submit the configuration. The first export can take up to 48 hours after successful configuration, so allow that period before treating the absence of tables as a failure.

Search Console creates three tables in the dataset:

• searchdata_site_impression groups performance data by property.
• searchdata_url_impression groups performance data by URL.
• ExportLog records which table was saved and for which date.

Search Console can send the two performance tables at different times. Check each table separately; seeing one does not mean the other has arrived. After the first export completes, confirm that all three tables appear in BigQuery and check ExportLog.

Bulk export does not backfill dates from before activation. Google points to Search Console reports or the Search Console API for earlier data, but the API has a limit of 50,000 rows per day per search type per property. Do not assume that it can replace a full bulk export for a large site.

Keep the destination writable

Leave the schemas of Search Console’s export tables unchanged. Adding a column, for example, can cause a schema mismatch and stop writes. Put custom transformations in separate tables or views so the export tables remain compatible with Search Console.

Check how long BigQuery keeps each table partition, or section of a table. Google recommends setting expiration to 14 days or longer. A shorter period can prevent Search Console from writing to a partition that is close to expiring or has already expired. By default, tables and partitions remain indefinitely unless project or organization settings specify another retention period. Choose a retention period deliberately and avoid setting an unintended expiration for the whole table.

Record the project ID, dataset name, and location. Also record the service-account roles and which dataset belongs to each property. Anyone investigating a failure can use this information to check the destination and permissions before changing the export configuration.

Check for missing exports

Use ExportLog, which records successful deliveries, to check what arrived. Its data_date field uses Pacific Time, and its namespace field names the table that was saved. Since the tables can arrive at different times, check each table and date separately. Do not flag the current day as missing until its exports have had time to finish.

First, check the table names recorded in the namespace field:

SELECT DISTINCT namespace
FROM <code>PROJECT_ID.DATASET_ID.ExportLog
ORDER BY namespace;

Use the returned values in the expected-table list below because the recorded names may differ in spelling or capitalization from the table names.

This BigQuery query finds successful exports missing from the last 30 complete days. It ends with yesterday in Pacific Time, so it does not report the current, incomplete day:

WITH expected_namespaces AS (
  SELECT namespace
  FROM UNNEST([
    'searchdata_site_impression',
    'searchdata_url_impression'
  ]) AS namespace
),
expected_dates AS (
  SELECT data_date
  FROM UNNEST(
    GENERATE_DATE_ARRAY(
      DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY),
      DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 1 DAY)
    )
  ) AS data_date
),
successful_exports AS (
  SELECT DISTINCT data_date, namespace
  FROM <code>PROJECT_ID.DATASET_ID.ExportLog
  WHERE data_date BETWEEN
    DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY)
    AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 1 DAY)
)
SELECT
  d.data_date,
  n.namespace AS missing_namespace
FROM expected_dates AS d
CROSS JOIN expected_namespaces AS n
LEFT JOIN successful_exports AS e
  ON e.data_date = d.data_date
 AND e.namespace = n.namespace
WHERE e.data_date IS NULL
ORDER BY d.data_date, missing_namespace;

Replace PROJECT_ID and DATASET_ID with the destination project and dataset. If your first query returns different namespace values, use those in the expected list. An empty result means ExportLog has a success record for every expected table and date in the range; each returned row identifies a missing success record but does not explain why the export is missing.

ExportLog records only successful saves. Google also notes that Search Console can write the two performance tables separately. The epoch_version value may increase when Search Console updates data for a date. Check the table reference before treating an updated version as a duplicate or a gap.

As another check, look at which dates appear in each performance table:

SELECT DISTINCT data_date
FROM <code>PROJECT_ID.DATASET_ID.searchdata_site_impression
WHERE data_date BETWEEN
  DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 30 DAY)
  AND DATE_SUB(CURRENT_DATE('America/Los_Angeles'), INTERVAL 1 DAY)
ORDER BY data_date;

Run the same query against searchdata_url_impression. If a date seems to be missing, compare it with ExportLog, then check the latest export status in Search Console and look for related activity in Cloud Logs Explorer. ExportLog is the primary delivery check; use the table query to confirm any gaps in the data.

Respond to export failures promptly

If a date or table is missing, open Settings → Bulk data export in Search Console and read the latest status and error details. Check Cloud Logs Explorer for related search-console-data-export activity. Search Console’s status page shows only the latest attempt, and ExportLog leaves out failed attempts. Neither source gives you a complete history of failures on its own.

Compare the error with the destination settings. Google lists several possible causes: a missing API, a billing or permission problem, or a quota limit. Other causes include an invalid dataset ID, a location mismatch or restriction, or a schema change. Fix the reported problem, then use Test report to check items such as the project name and Cloud access. A successful test does not start an export or check every condition, including quota. Check the next scheduled export in Search Console and ExportLog.

Search Console retries missed daily exports during later scheduled runs. Google says Search Console keeps the missed data for about a week, then drops that date. After about a month of failed attempts, it stops the export. These periods are approximate, not guaranteed deadlines. Search Console emails property owners and full users about errors that are not temporary and about fixes. Temporary errors may not trigger an email. Keep an automated completeness check running alongside email alerts. See Google’s guidance for monitoring and recovering from export errors.

Google’s published guidance does not describe a general way to recover a date after Search Console drops it at the end of the retry period. Escalate a continuing failure before then. Restarting the export is not known to restore missing dates. Keep the failure times and error details for investigation.

What I'm building

Delegate tasks. Get software.

Give Vroni a GitHub issue, bug report, spec, or rough idea. It reads the repo, plans the change, writes code, runs checks, and works toward a review-ready pull request.

Take a look at vroni.com