Send Google Search Console Data to BigQuery With The Bulk Data Export

Subscribe to our monthly newsletter to get the latest updates in your inbox
Google recently announced a Search Console data export to BigQuery called the bulk data export. Previously exporting data from search ads was only possible via an API (or a service on top of it). This simplifies the lift to get access to the raw data powering the Search Console dashboard with just a few clicks. The export itself comes at no additional cost*.
 

*Once the new data is in BigQuery it is subject to BigQuery's storage and processing cost.

How to set up the export?

  1. In your search console navigate to settings in the right menu.
Google Search Console – Menu
 
Search Console - Menu
 

2. Under General settings, select the Bulk data export

Bulk data export in BigQuery
 
 

3. In your Google Cloud Project, you will have to add a new principal to the IAM page. Adding the search-console-data-export@system.gserviceaccount.com will allow Search Console to write new data into your BigQuery account. If at any point, the user gets removed, the export will stop. Make sure to add BigQuery Job User and BigQuery Data Editor permissions.

Adding principals and roles in Google Cloud Project
 

*If you do not have a Google cloud project yet, you can get started for free.

4. Confirm your export settings and set up the export. If permissions were set properly and the project id is correct, you will see a "Setup completed successfully" message.

Search Console review your settings screen
 
 
BigQuery automated export setup complete screen
 
 

6. As stated in the message it may take up to 48h for the export to start, however, you will see a new dataset (searchconsole), with a small easter egg from Google's team in your BigQuery.

BigQuery new dataset easter egg message after dataset export
 
 

Export Overview

Once the export becomes active, you will notice three new tables:

BigQuery Search Console active tables
 
 
  • An export log (ExportLog), where you will find information about the export time and data date
An export log (ExportLog), where you will find information about the export time and data date
 
 
  • Day partitioned site impressions table (searchdata_site_impressions), which includes impressions, clicks, and position sum as the three metrics and information about the search as the dimensions.
Schema of impressions table
 
Schema of impressions table
 
  • Day partitioned individual url impressions table (searchdata_url_impressions), which includes the same three metrics as the site impression table, but focuses on individual pages of your site. Beside the specific url it also includes page descriptors such as is_amp_story, is_video, etc.

Simple queries to get you started

1. Looking at total impressions, clicks and average position per day for the whole site:

1
2
3
4
5
SELECT data_date, sum(impressions) as impressions, sum(clicks) as clicks, sum(sum_top_position)/sum(impressions) as position
FROM `searchconsole.searchdata_site_impression`
WHERE data_date BETWEEN "2023-03-01" AND "2023-03-07"
GROUP BY 1
ORDER BY data_date

 

2. Finding the top ten queries with at least five clicks, sorted by the Click Through Rate descending

1
2
3
4
5
6
7
SELECT query, round(100*sum(clicks)/sum(impressions), 2) as CTR, sum(impressions) as impressions, sum(clicks) as clicks
FROM `searchconsole.searchdata_site_impression`
WHERE data_date BETWEEN "2023-02-10" AND "2023-03-14"
GROUP BY 1
HAVING clicks > 5
ORDER BY CTR DESC
LIMIT 10

 

3. Most popular pages (measured by click) outside of the US

1
2
3
4
5
6
SELECT url, sum(clicks) as clicks
FROM `searchconsole.searchdata_url_impression`
WHERE country != "usa"
GROUP BY 1
ORDER BY 2 DESC
LIMIT 50

 

4. By exporting search console data and your GA4 property, you can now join the two on the page URL. In the query below we look at organic sessions, clicks and impressions for individual pages.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
WITH
GA4_Organic_Sessions AS (
  SELECT
    (SELECT value.string_value FROM unnest(event_params) WHERE key = "page_location") as url,
    count(distinct user_pseudo_id ||  (SELECT value.int_value FROM unnest(event_params) WHERE key = "ga_session_id"))  as sessions
  FROM `analytics_206551716.events_*`
  WHERE (SELECT value.string_value FROM unnest(event_params) WHERE key = "medium") = "organic" AND
        (SELECT value.string_value FROM unnest(event_params) WHERE key = "source") = "google"
  AND _table_suffix BETWEEN "20230301" AND "20230307"
  GROUP BY 1
  ORDER BY 2 DESC),
 
SearchConsole AS (
  SELECT url, sum(impressions) as impressions, sum(clicks) as clicks
FROM `searchconsole.searchdata_url_impression`
WHERE data_date BETWEEN "2023-03-01" AND "2023-03-07"
GROUP BY 1
)
 
SELECT * FROM
  GA4_Organic_Sessions
  FULL OUTER JOIN
  SearchConsole
  USING(url)
 
  ORDER BY sessions DESC

*For more information about the GA4 export visit our GA4 export guide.

As you can see from the few examples above, the export holds very useful information about your site's search performance and rankings. Joining this data with other sources, such as your site's analytics allows you to understand user behavior on and off the site in the same report. So make sure to turn the export on as soon as possible since it does not come with a backfill.

If you have any questions or would like us to help you set up the new export feel free to reach out.

Related Insights
Securing Your Cloud Run Applications with Identity-Aware Proxy (IAP)
Blog Post
5 New BigQuery Features
Blog Post
WYNTK About BigQuery Editions and Recent Billing Announcements
Blog Post