Better Quality Data? Google Search Console to Big Query

After we published our Does the Google Search Console to Big Query have more data several people asked us whether the additional search queries that we got were evenly distributed or there were far more on the second page and lower and therefore possibly less useful.

TLDR: While for smaller sites, there is no more than the API, it’s definitely worth setting up for small sites, see why in the Is It Worth Setting Up The Big Query Export? section

Using the same three websites as last time which were

  • Liamofarrell.com – artist web site – 5,000 visits a month from Google
  • Gosimpletax.com – SaaS tax return web site – 50,000 visits a month
  • Major UK Ecommerce Site – 160,000 visits a month

As stated in our last blog post, for NDA reasons, we can use the data from the major ecommerce web site, but not name them.

Our Test Methodology

As discussed in the last blog post using the Google Search Console User Interface will only give you 1,000 at a time, so we discounted this

For our tests, we use the following variables

  • Date: 1st to 31st of October
  • Search Type: Web
  • Country: GBR

We then extracted the data using 3 different methods

  1. Single Query via the API for the whole 30 day period
  2. 93 Queries to the API broken down by date and device
  3. One Single Query to Big Query

Calculating Average Position

For the 93 API query and Big Query query, the data is returned on a daily basis with an average position for that day for each query

To get the overall monthly position, we ran the following calculation for each query

(Impressions * Position) / 31

which gave us the average over the month

We then broke the queries down into 4 position groups

  • 1 to 3
  • 4 to 5
  • 6 to 10
  • Second Page +

The Results

For the Google Search Console queries, we’ve compared these to the to the big Query Export

Single Google Search Console API Call vs Big Query

For the lower traffic sites, there was very little difference in the total number of overall queries and in come cases such as Go Simple Tax, there were more unique queries via the API for positions 1 to 3

For our major UK ecommerce site, there was noticeable difference and overall a 287% difference in the number of queries and a similar amount for impressions

92 Search Console API Call vs Big Query

A similar pattern is repeated and while there differnet number of queries per position group, the total number of queries are the same. It seems that Google has a hard limit on a per property to extract the queries

Is It Worth Setting Up The Big Query Export?

Absolutely and for following reasons

  • It takes seconds to query the data even across 10’s of Gigabytes of daata
  • There is no 16 months time limit constraint on the data
    • Google Search Console only holds the last 16 months of data
  • It’s relatively cheap to store and query at $0.02 per Gigabye per month
    • We have 4 months of data for GoSimpleTax, which is 0.5Gb at a total cost of $0.04
  • The data is relatively accessible via some simple SQL commands
  • Results can be exported to CSV or Google Sheets

The Google Search Console API is great if you have coding skills and you can access the API via your code. Moz.com has a good guide to getting started

Alternatively you can use a tool such as Analytics Edge to extract the data directly into Excel or Supermetrics into Google sheets.

However both of them charge an annual subscription for their services to pull data down from the Google Search Console

The annual costs are

  • Analytics Edge is $150
  • Supermetrics is $840

You would need to be storing and querying a lot of data to get anywhere near those costs

We’re massive fans of Analytics Edge and have been using it over 6 years as it has transformed how we handle every day data transformation tasks and for us it’s worth every penny