Technical Insights

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

Written by Luka Cempre | Mar 16, 2023 7:16:00 PM
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.
 
Search Console - Menu
 

2. Under General settings, select the Bulk data export

 
 

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.

 

*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.

 
 
 
 

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.

 
 

Export Overview

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

 
 
  • 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
 
  • 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.
View full post