Validating Search Console Bulk Export Partitions and Retries
When Search Console sends performance data to BigQuery, it writes each day's data into date-based partitions, which keep each date's rows together.…
If you build features around Google Search Console, you can get its performance data in two ways: query the Search Analytics API, or have Search Console export data each day to BigQuery, Google’s cloud data warehouse. The API works well for features that users connect to their properties and for targeted queries. BigQuery works well for recurring datasets and larger analyses.
Your choice determines how your platform gets permission, updates data, and stores results. The API uses a user’s Google authorization and may return only some available rows. For bulk export, a Search Console owner configures the BigQuery destination and grants access before Search Console sends data there. With either option, first store incoming data in a durable landing area, then transform it for reports or product features.
Use the Search Analytics API when users need to connect a Search Console property to your platform or when your product needs selected data on demand. Your backend requests data for a property and date range, then saves the response in storage you control. The API lets you request results in pages, but Google sets a limit of 50,000 rows per day per search type and warns that results may include only the top rows. Requesting more pages does not guarantee a complete dataset. Read the query reference before designing reports around API results.
Choose BigQuery bulk export when you need a recurring dataset for warehouse queries and later processing. After the property owner sets up the export, Search Console writes daily data to BigQuery tables. This avoids scheduled API requests for individual rows, but requires a Google Cloud project and access settings. The export excludes anonymized queries, and BigQuery storage and query use may incur charges. Google’s bulk export documentation explains setup and prerequisites.
Your platform can use both options. For example, it can use OAuth and the API to let a customer connect a property, while a separate warehouse workflow handles bulk export for customers who need broad, recurring analysis. Keep the two data feeds separate and document how their coverage and update timing differ.
| Requirement | Starting point |
|---|---|
| User connects a property within your platform | Search Analytics API with user OAuth |
| Daily data for warehouse analysis | BigQuery bulk export |
| Data from before bulk export began | Search Analytics API or Search Console reports |
| Application-specific reports or database tables | Either source, followed by a transformation step |
Google directs users to the API or reports for historical data from before bulk export started. If your platform compares data from different sources, account for differences in coverage rather than assuming the rows will match exactly.
For an API integration, use Google OAuth 2.0 to let a Search Console user authorize read-only access to their properties and let your backend call Google’s API on their behalf. An API key does not replace this user authorization.
Enable the Search Console API in your Google Cloud project, set up OAuth credentials and a redirect URI, and request the https://www.googleapis.com/auth/webmasters.readonly scope. Follow Google’s server-side OAuth guidance and use a Google OAuth client library. Request offline access so your backend can get a new access token when the user is away. Store the refresh token securely, outside browser code and logs.
After Google returns the user to your redirect URI, your backend exchanges the authorization response for tokens. It then calls sites.list to get the Search Console properties the user can access. Let the user choose a property from that list, and save its identifier with the connection. Checking the returned list prevents your backend from treating a property name supplied by the client as proof of access. The sites list method returns property identifiers and the user’s permission level.
For scheduled work, the backend uses the refresh token to obtain an access token and sends that token in the API request’s Authorization: Bearer header. If the user revokes access or the property permission changes, the request can fail; record the failure and ask the user to reconnect or restore access rather than marking the sync as successful.
The Search Analytics API accepts an authorized POST request. Its URL includes the property identifier, which must match the Search Console property format. URL-encode that identifier when placing it in the request path.
POST https://www.googleapis.com/webmasters/v3/sites/{URL_ENCODED_PROPERTY}/searchAnalytics/query
Authorization: Bearer {ACCESS_TOKEN}
Content-Type: application/json
{
"startDate": "2026-11-10",
"endDate": "2026-11-10",
"dimensions": ["date", "page", "device"],
"type": "web",
"rowLimit": 25000,
"startRow": 0
}
The dates are interpreted in Pacific Time. rowLimit cannot exceed 25,000, and startRow is a zero-based offset for the next page. Each response row contains a keys array whose values follow the order of the requested dimensions, along with metrics such as clicks, impressions, click-through rate, and average position.
A worker can query one date and search type at a time. When a response has a full page of rows, it requests the next page and advances startRow by the number of rows returned. Google’s performance data guidance explains daily queries and pagination. If the worker reaches the API’s daily row limit, it must mark the result as potentially incomplete. An empty final page shows that the query returned no more rows; it leaves open whether Search Console made every row available.
First, write each response to a staging table, a durable holding area for incoming data, or an equivalent store. Keep the property, date range, search type, requested dimensions, and metrics. Record the ingestion time and sync-run identifier too. After the worker checks the pages, merge the rows into a reporting table using a key made from the property, date, search type, and dimension values. A rerun can then replace matching records without adding duplicates. Store job status and row counts separately so you can tell an empty result from a failed or unfinished query.
Search Console performance data typically becomes available after two to three days. Schedule a daily job to check a recent date range again, since the previous day’s data may be incomplete. When the worker fetches a date again, merge the response so repeated updates do not create duplicate rows. Choose a date range based on how current your product’s data needs to be. Google does not guarantee a fixed period for revising data.
The query’s dataState setting controls whether results include fresh data. If you omit the setting or set it to final, the query returns finalized data; setting it to all includes fresh data. Record the setting used for each dataset, and track the difference when combining fresh and finalized results.
Requests that group or filter results by page or query use more API capacity. Longer date ranges also use more capacity. Keep date ranges and dimensions as narrow as your feature allows, and watch for quota errors. Google lists request limits on its usage limits page, but a query that stays within those limits can still exceed available service capacity.
Bulk export uses a service-account permission grant rather than a user OAuth token. In Search Console’s bulk-export settings, the property owner selects the Google Cloud project that will receive the export, names the dataset, and sets its location. The owner also grants search-console-data-export@system.gserviceaccount.com the BigQuery Job User and BigQuery Data Editor roles on the destination project. The setup requires the BigQuery and BigQuery Storage APIs.
Choose the dataset location before starting the export because Google says it cannot easily be changed after exports begin. If several properties write to the same project, give each one a different dataset name. Google says the first export can take up to 48 hours after successful setup. Daily exports also arrive at different times for different tables. Have downstream jobs check for new data instead of relying on a fixed arrival time.
Search Console creates searchdata_site_impression for property-level performance, searchdata_url_impression for URL-level performance, and ExportLog to record successful exports. A key can appear in multiple rows because Search Console adds performance data over time. Google’s table reference recommends adding metric values in queries instead of treating each row as a unique final record.
Keep the exported tables as the landing layer, then build tables or views that select and combine the fields your reports need. If your product stores data elsewhere, schedule a separate transformation to copy those results to its database or another destination.
For example, this query calculates daily web totals from the property-level table:
SELECT
data_date,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
SAFE_DIVIDE(SUM(sum_top_position), SUM(impressions)) + 1
AS avg_position
FROM <code>PROJECT_ID.DATASET_ID.searchdata_site_impression
WHERE search_type = 'WEB'
AND data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
AND CURRENT_DATE()
GROUP BY data_date
ORDER BY data_date DESC;
The date filter limits how much data the query scans in the date-partitioned table, helping control query costs. The position calculation adds one because the stored top-position measure is zero-based. Google provides further query guidance and examples.
For reports that show named queries, filter out empty query strings. Search Console uses zero-length strings for anonymized queries in the export, so that filter also removes those records. Make the exclusion visible in the report’s description, because the resulting query totals do not include anonymized records.
Check that the exported tables keep receiving new data. ExportLog records successful exports, not failed attempts, so it cannot be your only way to detect failures. Google says property owners and full users receive messages for non-transient errors. The Search Console settings page shows the latest attempt, and Cloud Logs Explorer can show more detail. Your platform should flag stale data when the expected tables stop receiving updates. For setup checks and failure handling, see verifying a bulk export and handling failed exports.
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