Big Query Google Search Console Exports – Is There More Data?

In March, Google quietly launched one of Google Search Console most useful features which is the nightly export of data from Google Search Console to BigQuery

Conversations on Twitter with people such Robin Allenson led us to understand that the Big Query exports could provide substantially

Unlike the Google Search Console API where you need to specify exactly the type of data that you want to extract, the Big Query gives you everything into one table.

Setting it up is a relatively simple process providing you’re setup as owner on the Google Search Console account and you’ve setup a Google cloud account

In this post, you discover

Benefits of Storing it in Big Query

There are several clear advantages of having it in BigQuery

Permanent Storage
Unlike the API where the data is only available for 16 months, once it’s in BigQuery, it’s in there for as long as you continue to pay for it.

At 2p per gigabyte per month, this isn’t going to break the bank and you can store around 5 million rows per gigabyte

Fast Querying
Querying tens or hundreds of millions of rows of data is done in seconds and not something that would even be possible in Excel or Google Sheets

Regular Expressions Out of the Box
SQL supports regular expressions, so you can use statements such as

REGEXP_CONTAINS(query, r'(who|what|where)’)

to extract all the phrases that can contain question words such as who, what and where

Do I Get More Queries?

Twitter exchanges with experts such as Robin Allenson indicated that this was worth exploring

twitter post

Liam O Farrell
A watercolours and oils artist website with 60,000 impressions per month on Google

GoSimpleTax
A UK tax return software company with 2.7 million impressions per month on Google

Major UK Ecommerce Retailer
For NDA reasons, we can’t name them, however they have 27 million impressions per month

How We Ran The Tests

For each account, we did the following to assess how many unique queries we would get over a 30 day period

Google Search Console API
Using the Analytics Edge plugin for Excel we made 180 calls to the API to extract the mobile, desktop and tablet queries for each of the 30 days and set it to only extract UK traffic. We’ve found in the past this extracts the most data

Big Query
We ran the following SQL on the exported data to grab all the data where the query is not anonymized and the country is set as GBR (UK) across a specified 30 day period,,

The GROUP BY dedupes the data and ORDER BY sorts it into an order

SELECT
query,
SUM(impressions) AS total_impressions,
SUM(clicks) AS total_clicks
FROM
clientdatabase.searchconsole.searchdata_url_impression
WHERE
is_anonymized_query = false
AND data_date >= '2023-08-20'
AND data_date <= '2023-09-18'
And country = 'gbr'

GROUP BY
query
ORDER BY
total_clicks DESC

The Results: Up to 297% More Data via Big Query

For each client, we are seeing substantially more data than within the API

The biggest was for our ecommerce retailer where it looks like Google Search Console is providing substantially more long tail traffic than we could ever extract from the API

For each the following, we have listed the unique queries over 30 days

Liamofarrell.comUnique Queries
GSC API: 1,995
Big Query Export:4,767
% change138%+
GoSimpleTax
GSC API:70,278
Big Query Export104,009
% change48%+
UK Ecommerce Retailer
Client has asked us to round up to the nearest 1,000
GSC API:149,000
Big Query:590,000
% change: 297%

Added Bonus – More Queries with Clicks

In each of the cases, we saw far more queries with clicks than was reported by the API which helps to better understand which search queries are performing well

Can You Help Us Setup And Analyse The Data?

We certainly can, either contact charles@digitalnation.co.uk, call us on 0207 993 5482 or complete our form