Uncover More GSC Data Using API and BigQuery

In March 2023, Google announced a new functionality: bulk data export from Google Search Console to BigQuery. The setup is straightforward: 

• Create a Google Cloud project 

• Set the export in Google Search Console 

• Data will then be added daily to your project in BigQuery.

You need to understand that this integration is not retroactive, though. While the GSC API has a 16-month data retention period, BigQuery has no limitations but you won’t be able to get data before this setup. 

When you look at the data structure, you will quickly realize that we have plenty of fields not available in the API, which is great. Although, and to be fair, most of them won’t be useful for your project unless you are in a specific industry. 

We also finally have access to the metrics for the anonymized queries, which should, in theory, remove the discrepancies we observed with the API. 

Let’s run a couple of queries to check that. As data is stored in BigQuery, you’ll need to write SQL, use connected Sheets or use the GSC library I mentioned before, which has built-in functions to query directly your BQ dataset. Remember when we got the number of clicks for January 2024 (which was 893,691 with the API)? Let’s get that info again!

First, be aware that, unlike the API, querying your data in BigQuery has a cost. It is usually low, but if you run the following query, you’ll get an estimate of what it will cost you.

(
   conn
   .query
   .range('2024-01-01',"2024-01-31")
   .get()
   .group_data_by_period('M')

)

In my case, €0.0016, which is more than acceptable. Now, let’s run the actual query: 

(
   conn
   .query
   .range('2024-01-01',"2024-01-31")
   .get()
   .define_estimate_cost(value=False)
   .group_data_by_period('M')

)

The total: 923,847 clicks, 3.4% higher than what we had in the API. To be honest, I didn’t expect a difference here, but 3.4% is not enough to justify using a paid product vs. a free API. 

Let’s move on with the examples we used for the API: branded and non-branded queries. 

• Branded: 50,189 clicks (3.2% difference vs API) 

• Non-branded: 523,744 clicks (35% difference) 

• Anonymized: 349,914 clicks (-25% difference)

• Total: 923,847 clicks  

We can see one key difference here: while we still have anonymized queries, the percentage is lower, and we can access better data. If you rely on accurate keyword data, BigQuery is definitely the data source you need to use.

Moreover, for this example, I’ve actually found that the API returned around 40,000 unique queries where the site was shown, while BigQuery showed 350,000. A huge difference that I was not expecting. 

This data exhaustively can impact some analyses we may want to perform. Let’s, for instance, calculate the CTR yield curve for the same set of pages using the API and BigQuery, and let’s see the difference. 

As you can see, there are some significant differences between both data sources, which could impact our project prioritization based on the SEO impact we can forecast for any given action. 

This difference may surprise some of you, but it has a simple explanation. The API underreports queries and prioritizes the ones with the most clicks in the API. Let’s look at an example. 

We recently launched a new set of pages. If I look at the data for these pages by query: 

• With the API I only get 24 clicks and 252 impressions for 47 queries. The average CTR would be huge: around 10%. 

• With BQ I get 79 clicks and 44,274 impressions for 5384 queries. 

The data exhaustively changes completely the way we look at this information. With the API, we may think that our project is a minimal part of the markets (which can make sense for a small player), while BQ tells us that the potential is 200 times higher and that we could expect to generate more traffic in the medium term if we rank better.