How to Export Search Console Data to BigQuery

Search Console bulk data export is a Search Console feature that writes a property's performance data to Google BigQuery tables every day. BigQuery is Google Cloud's data warehouse, queried with SQL (Structured Query Language). The exported tables hold all the performance data Search Console has for the property, except the text of anonymised queries.

How does bulk data export differ from the interface and the API?

Bulk data export differs from the interface and the API in row limits, data history and query method. The Performance report table shows at most 1,000 rows. The Search Analytics API (application programming interface) returns at most 50,000 rows per day, per search type, per property. Google explains both limits in its deep dive into performance data. The interface date filter goes back 16 months at most.

The table below compares the three access routes on four features.

Bulk data export brings no data from before the setup day. For earlier periods, Google points to the API or the reports.

Who needs the BigQuery export, and who does not?

The BigQuery export is most useful for sites with tens of thousands of pages or tens of thousands of daily queries. Google named those two site types in its February 2023 announcement. Per that announcement, small and medium sites already get all their data from the interface, the Data Studio connector or the API.

Four situations justify setting up the export.

• You hit the 1,000-row or the 50,000-row limit.
• You need data older than 16 months.
• You join Search Console data with other sources, such as a site crawl or sales data.
• You analyse long-tail queries.

If none applies, the interface and the API are enough. My article on data analysis in SEO shows how to merge several tools' data into one table.

How do you export Search Console data to BigQuery?

You export Search Console data to BigQuery in two stages: prepare a Google Cloud project, then set the destination in Search Console. Only a property owner can set up the export. The seven steps below follow Google's bulk data export setup guide and quote its menu and role names.

1. Open the Google Cloud Console and switch to a project with billing enabled, creating one if needed.
2. In the sidebar, go to APIs & Services > Enabled APIs & Services. If BigQuery is not enabled, click + ENABLE APIS AND SERVICES and enable BigQuery API and BigQuery Storage API.
3. In the sidebar, open IAM and Admin and click + GRANT ACCESS.
4. Paste search-console-data-export@system.gserviceaccount.com into New Principals.
5. Grant the account two roles, BigQuery Job User (bigquery.jobUser) and BigQuery Data Editor (bigquery.dataEditor), then click Save.
6. In Search Console, open Settings > Bulk data export and enter the project ID, not the project number, in the Cloud project ID field.
7. Choose a dataset name and location, then click Continue. The default name is searchconsole, and the location cannot easily be changed once exports begin.

Which tables and fields does the BigQuery export create?

The BigQuery export creates three tables in your dataset: searchdata_site_impression, searchdata_url_impression and ExportLog. The first holds performance data aggregated by property, the second aggregated by URL. ExportLog records every successful export. The full field list is in Google's table guidelines and reference.

The table below lists the twelve fields used in this article's queries.

The URL table also carries boolean search appearance fields such as is_amp_top_stories. Google states that performance data is accumulated incrementally, which produces rows with repeated keys. That is why every query aggregates its metrics with SUM.

How do anonymised queries affect BigQuery totals?

Anonymised queries appear in BigQuery as rows with an empty query string, and their clicks and impressions count towards the totals. Google anonymises queries issued by no more than a few dozen users over a two-to-three month period. In those rows is_anonymized_query is true and query is a zero-length string.

BigQuery totals may differ from the interface for three reasons.

• The interface query table omits anonymised queries, while the chart totals include them.
• The interface table stops at 1,000 rows, and the chart totals do not.
• The site table counts by property and the URL table by page. Two of your pages shown for one query make one impression for the property and two for the pages.

The sample query below shows the share of anonymised rows.

SELECT
  is_anonymized_query,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions
FROM `YOUR_PROJECT_ID.YOUR_DATASET.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'WEB'
GROUP BY is_anonymized_query;

Compare the interface chart with the site table, because both aggregate by property.

What determines the cost of the BigQuery export?

Two things determine the cost of the BigQuery export: the data stored in the tables and the data your queries process. Google offers a free usage level and charges for storage and queries above it. Under on-demand pricing, BigQuery bills a query by the bytes it reads.

The partition expiration controls storage. The tables are partitioned by date, and data accumulates forever unless you set an expiration. Google recommends an expiration on the partition, not on the table, because a table expiration deletes all your data. The expiration must be 14 days or longer, and a schema change such as an added column makes the export fail.

The Cloud Console interface cannot update a partition expiration, so set it with SQL. The sample statement below deletes each partition of the URL table after 480 days. Partitions older than the new value expire as soon as the statement runs.

ALTER TABLE `YOUR_PROJECT_ID.YOUR_DATASET.searchdata_url_impression`
SET OPTIONS (partition_expiration_days = 480);

A date filter lowers the query cost. With a data_date range in the WHERE clause, BigQuery scans only the matching partitions, and pruned partitions do not count towards the bytes scanned. The Cloud Console query validator estimates the bytes before you run a query.

How do you query Search Console data in BigQuery with SQL?

You query Search Console data in BigQuery by three rules: aggregate metrics with SUM, filter on data_date, and include or exclude anonymised rows on purpose. The rules come from Google's query guidelines and sample queries. The four queries below are samples, and YOUR_PROJECT_ID and YOUR_DATASET are placeholders.

Google's samples write the search type as 'WEB', the device as 'MOBILE' and the country as 'usa'. Check the spelling in your own tables in the Preview tab, which runs no query.

Clicks and impressions by query for the last 28 days

Clicks and impressions by query come from the site table. The sample covers the 28 days ending yesterday and leaves out anonymised rows.

SELECT
  query,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions
FROM `YOUR_PROJECT_ID.YOUR_DATASET.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'WEB'
  AND is_anonymized_query = FALSE
GROUP BY query
ORDER BY total_clicks DESC
LIMIT 1000;

My article on user intent in SEO explains how to group the query list by intent.

Calculating average position correctly

Average position is the sum of positions divided by the sum of impressions, plus 1, because the stored position is zero-based. The field is sum_position in the URL table and sum_top_position in the site table. NULLIF prevents a division-by-zero error.

SELECT
  url,
  SUM(impressions) AS total_impressions,
  SUM(sum_position) / NULLIF(SUM(impressions), 0) + 1 AS avg_position
FROM `YOUR_PROJECT_ID.YOUR_DATASET.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'WEB'
GROUP BY url
ORDER BY total_impressions DESC
LIMIT 1000;

Country and device breakdown

A country and device breakdown groups the site table by country and device. Keep anonymised rows here, because their clicks and impressions belong to the country totals. The ctr column is the click-through rate (CTR), clicks divided by impressions.

SELECT
  country,
  device,
  SUM(clicks) AS total_clicks,
  SUM(impressions) AS total_impressions,
  SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr
FROM `YOUR_PROJECT_ID.YOUR_DATASET.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'WEB'
GROUP BY country, device
ORDER BY total_clicks DESC
LIMIT 100;

Pages with high impressions and a low click-through rate

A HAVING clause on the URL table returns pages with high impressions and a low CTR. The thresholds, 1,000 impressions and a 1 per cent CTR, are sample values to adjust for your site.

SELECT
  url,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  SUM(clicks) / NULLIF(SUM(impressions), 0) AS ctr,
  SUM(sum_position) / NULLIF(SUM(impressions), 0) + 1 AS avg_position
FROM `YOUR_PROJECT_ID.YOUR_DATASET.searchdata_url_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
                    AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY)
  AND search_type = 'WEB'
GROUP BY url
HAVING SUM(impressions) >= 1000
   AND SUM(clicks) / NULLIF(SUM(impressions), 0) < 0.01
ORDER BY total_impressions DESC
LIMIT 500;

How do you visualise the BigQuery data in Data Studio?

You visualise the BigQuery data in Data Studio through its BigQuery connector, which reads a table, a view or a custom SQL query. Data Studio was called Looker Studio until Google announced the return to the Data Studio name in April 2026.

Create a report in Data Studio, select the BigQuery connector, then pick the project, dataset and table. On a date-partitioned table, set the partition column as the main date filter. Google says partitioned tables render charts faster and minimise query costs.

The direct Search Console connector in Data Studio shares the API's 50,000-row daily limit, while the BigQuery connector reads the exported tables. The measurement section of my SEO guide lists the Search Console reports used for measurement. Bulk data export carries performance data only, so I cover AI answers in my article on measuring AI search visibility.

What are the most common Search Console query mistakes in BigQuery?

The most common Search Console query mistakes in BigQuery are the five below.

• Averaging the average position. An average of daily or row-level averages weights every row equally. Divide the sum of positions by the sum of impressions, then add 1.
• Leaving out the date filter. A query without a data_date condition scans every partition.
• Ignoring anonymised rows. Kept in a query list, the empty row can rank first. Dropped from a total, it leaves clicks and impressions short.
• Mixing up the site and URL tables. The two tables count impressions differently and name their position fields differently. Use the site table for queries and countries, the URL table for pages.
• Reading metrics without aggregating. One query, date and search type can span several rows, so a query without SUM returns single rows, not the total.

How do you check that the bulk data export is working?

You check that the bulk data export is working in two places: the Search Console settings page and the ExportLog table. The Settings page shows the status of the latest attempt next to Bulk data export. The first export happens up to 48 hours after a successful configuration and includes data for the day of the export.

ExportLog records successful exports only, so expect two rows per day, one per data table. The sample query below lists the latest records.

SELECT
  namespace,
  data_date,
  epoch_version,
  publish_time
FROM `YOUR_PROJECT_ID.YOUR_DATASET.ExportLog`
ORDER BY publish_time DESC
LIMIT 20;

namespace names the table written to, and publish_time is the completion time. epoch_version starts at 0 and rises by 1 each time Search Console updates that day's data.

Failed attempts never reach ExportLog. On a non-transient error, Search Console emails property owners and full users and shows the error on the settings page. Search Console retries a day's data for about a week, and stops the export entirely after about a month of failures.