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.…
When you move Google Search Console performance data into BigQuery, the table you query determines what a reported click or impression represents. Search Console provides one table for property-level reporting and another for URL-level reporting. A property is the Search Console site you measure; a URL is an individual page.
Choose the table that matches the report, then sum clicks and impressions at the report’s intended grain: what one output row represents. For example, a row might represent one property per day, one query per day, or one URL–query pair per day. If you choose the wrong table or grain, a dashboard can show totals that look plausible but describe a different metric.
Use searchdata_site_impression for property-level totals and query reporting that should not split results by page. Use searchdata_url_impression for URL-level reporting, including page totals and page–query reports. Google defines the tables at these aggregation levels. It also warns that exported rows are not always combined by date, URL, or property. Rows may also remain separate when those fields are combined. Sum the metric columns in your query even when you group by the fields that define one output row. See Google’s table reference and query guidelines.
| Reporting question | Starting table | Output grain |
|---|---|---|
| How many clicks and impressions did the property receive each day? | searchdata_site_impression |
Property-day |
| Which queries performed for the property each day? | searchdata_site_impression |
Query-day |
| Which URLs performed each day? | searchdata_url_impression |
URL-day |
| Which queries performed for each URL each day? | searchdata_url_impression |
URL–query–day |
The two tables do not provide interchangeable ways to calculate the same total. For standard Search results, property aggregation counts an impression for the property when multiple pages from that property appear for the same query on one results page. Page aggregation counts each unique URL separately. Clicks can also differ: property aggregation can count one click where page aggregation counts clicks to multiple URLs. Google describes these property and page counting rules.
As a result, adding URL-level impressions does not generally recreate the property-level total. Use the site table for property totals, and label URL totals as page-level metrics.
For a daily property report, group by date. Add query when the report needs a property-level query breakdown. For page reporting, group by url, adding query when the report needs page–query detail.
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.searchconsole.searchdata_site_impression
WHERE data_date BETWEEN @start_date AND @end_date
AND search_type = 'WEB'
GROUP BY data_date
ORDER BY data_date;
This query produces one row per date for the selected search type. The site table uses sum_top_position for its position calculation. The URL table uses sum_position, so a page report must use that field instead:
SELECT
data_date,
url,
query,
SUM(impressions) AS impressions,
SUM(clicks) AS clicks,
SAFE_DIVIDE(SUM(clicks), SUM(impressions)) AS ctr,
SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) + 1
AS avg_position
FROM <code>PROJECT.searchconsole.searchdata_url_impression
WHERE data_date BETWEEN @start_date AND @end_date
AND search_type = 'WEB'
GROUP BY data_date, url, query
ORDER BY data_date, clicks DESC;
Search Console stores position values on a zero-based scale, while its reports display positions starting at one. The formula divides the summed position values by summed impressions, then adds one. Use SAFE_DIVIDE so a zero-impression group returns NULL instead of causing a division error. Google’s sample queries document these aggregation patterns and table-specific position fields.
CTR and average position are rates or averages, not additive counts. When you combine rows, calculate CTR from total clicks divided by total impressions. Calculate average position from the summed position field and summed impressions. Averaging row-level CTR or position values gives each row equal weight, even when the rows have different impression counts.
A list of named queries does not necessarily account for all clicks or impressions. Search Console omits anonymized query text to protect privacy. It can also limit the query rows it exports, so a query table may contain only part of the available query data. The dimension and data grouping documentation explains these limits.
Exclude anonymized and blank query values from named-query reports. For example, a site-table query can use:
AND is_anonymized_query IS NOT TRUE
AND COALESCE(query, '') != ''
Do not apply that filter automatically to page totals. A page-level report should retain rows with no visible query when the goal is to report all page performance. Google’s bulk-export and table documentation describe anonymized queries in different ways: as excluded rows, a null query, or an empty query string. Check the exported table’s schema and values, and preserve the anonymity flag where it exists. Do not present named-query sums as a complete reconciliation of property totals.
Use the exported url value to identify pages in Search Console reports. Google assigns much performance data to the canonical URL it selects, and the page dimension reflects the final URL linked from a result after redirects. Replacing the exported URL with a separately normalized URL changes the grouping. If you normalize URLs for another purpose, keep that as an explicit transformation rather than silently mixing it into Search Console-attributed page reporting.
Keep site-level and URL-level metrics in separate tables or views, and state what one row represents in each. When a report needs both, group each table to the level of detail used to match rows before joining them. When you join property-level rows directly to URL-level rows, the join repeats the property metrics for every page. A dashboard that sums those repeated values then reports inflated totals.
Also keep the report’s filters consistent when comparing BigQuery with Search Console. Match the property, date range, search type, and any relevant dimensions such as country or device. Google’s date field uses Pacific Time, and collected data is usually available after two to three days; compare completed dates when checking totals. The tables are date-partitioned, so filter data_date to limit the data BigQuery scans.
To investigate a difference, first state what one result row should represent. Then check that the query uses the right table, sums clicks and impressions, recalculates rates from those totals, and uses the same filters and date range as the comparison. If query totals are lower than the property total, check whether anonymized queries or truncated query rows explain the gap before treating it as an aggregation error. For more on interface comparisons, see why Search Console charts and tables do not match.
The standard property-versus-page explanation applies to Search results; do not assume it applies to every search type. Google documents special handling for Discover and Google News, including unsupported property aggregation in the Search Analytics API. Check the selected search type before interpreting differences across those reports.
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