Understanding the BigQuery Search Console Schema

Google Search Console exports clicks and impressions to BigQuery in tables that combine data across searches. Each row summarizes activity for a group, so it does not necessarily represent one search, query, or page visit. To interpret the numbers, choose the table and grouping that match your question, then sum the metric values across rows.

A Search Console property is the site or URL scope that Search Console tracks. A dimension describes how data is grouped, such as by date, country, query, or page. A metric measures performance within those groups, such as clicks or impressions. Keeping dimensions and metrics separate helps explain why totals change when you group by page instead of property, and why image or video figures do not always match a general web-search report.

Choose the table for the question

The export has two main performance tables. searchdata_site_impression reports data grouped by Search Console property. [searchdata_url_impression] reports data for individual URLs and includes page URLs and fields that identify how a result appeared. Use the site table for summaries at the property level. Use the URL table when you need landing-page data or a flag showing how a URL appeared in search. The two tables' totals are not automatically interchangeable.

The dataset name is configurable; Google documents searchconsole as the default. The export also includes ExportLog, which records successful exports and revisions to saved data. Google can revise a previously exported day, so the log helps identify updates when a report does not match an earlier query result.

The names searchdata_site_impression and searchdata_url_impression refer to the two performance tables, not individual search events. Google does not guarantee that each combination of visible dimensions appears only once. Sum metric values across matching rows instead of relying on one row or assuming that a combination of dimensions identifies a single row.

Aggregate metrics from the rows

When a query selects a dimension and a metric, it should generally sum the metric. Several rows can share a query term while representing different dates, countries, devices, or search types. Returning one row without summing the others can leave out part of the term's data. Google’s query guidance and examples show how to sum exported metrics before calculating rates.

Calculate click-through rate (CTR) by dividing total clicks by total impressions. Do not average row-level CTR values unless you weight them by impressions. For average position, divide the sum of the relevant position field by total impressions, then add 1. The exported position sum is zero-based, while Search Console displays position starting at 1. Average position describes the average topmost result position across impressions; it is not a fixed rank for a page.

This site-level query groups results by date and search type. Replace the project and dataset placeholders with the names in your BigQuery account:

SELECT
  data_date,
  search_type,
  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 DATE '2026-10-01' AND DATE '2026-10-31'
  AND UPPER(search_type) = 'WEB'
GROUP BY data_date, search_type
ORDER BY data_date;

For page-level analysis, query searchdata_url_impression, include url in the grouping, and use sum_position to calculate average position. Filter data_date to limit the dates BigQuery scans. The Search Console schema reference and examples use inconsistent capitalization for search_type values, so check your table's values before writing a filter that distinguishes uppercase from lowercase. The query above uses UPPER to handle capitalization.

Why grouped totals change

Property and page groupings count appearances differently. When several URLs from one property appear for the same search, property-level aggregation counts the property once and records its topmost position. Page-level aggregation counts each URL separately. As a result, page-level impressions can exceed property-level impressions without indicating a data error. Google describes these different counting rules in its explanation of Search Console performance data.

The Search Console interface adds another distinction: its chart remains property-aggregated when a table is grouped by pages or search appearance. Comparing the chart total directly with the table total therefore compares different groupings. Query anonymization also affects comparisons. Search Console includes anonymized queries in chart totals unless a query filter is applied, even though those queries do not appear as ordinary named terms in the table.

Google’s documentation describes anonymized query values inconsistently: it refers to both an empty string and NULL. To identify those rows in a BigQuery query, account for both representations, for example with COALESCE(query, '') = '', and use the is_anonymized_query field where available. Excluding anonymized queries makes sense when you analyze named terms, but label the result so readers know it is not the full site total.

Read image and video fields in context

Image metrics refer to the host page, the page that contains the image. Search Console does not report each image file as a separate page for clicks, impressions, and position; when several images from one host page appear for a query, the documented rule counts one impression for that host-page URL. Image search and web search also remain separate search types, so state which surface the analysis covers. The definitions of impressions, position, and clicks explain how result layout affects these measures, especially for image search.

Video reporting uses two different classifications. search_type = VIDEO identifies the Video tab search type. The URL-table flag is_video identifies a video appearance in places such as web results or Discover. Filtering on one does not mean the same thing as filtering on the other.

The Video indexing report is separate from the Performance report and counts video data at the page level, so its impression count does not necessarily match a Performance report total. Google says the Performance report's counts vary by search type and search appearance, and by whether you view the chart or table. Some views group data by property. Treat the Video indexing report as a separate report, not as a total that should match exactly. Google explains this distinction in its Video indexing report documentation.

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