Pitched; Beyond the basics - the real value of GSC data in BigQuery.

Background

It's been quite a while that SEOs have the possibility to export Search Console data right to BigQuery and analyse the stuff. Even when things keep changing in the data schema of the export, the value of diving deep into the GSC data is enormous. So TL;DR: I activated the export exactly a year ago and since then I'm working on that data intensively and build a lot of useful monitoring for you daily SEO routines. It helped me to elaborate on the right strategy and derive new measures to improve performance. That's basically the scope of my planned session.

Covered topics

Currently I pitched it for brightonSEO in April'25, but I'm not sure if its gonna be accepted, as I really focussed on skipping the basics. (Update 14th Nov: see this talk at bSEO in April). I go directly deep into the topic. I think some previous speakers in other conferences already made a good intro:

Marco Giordano talked about it some days ago and goes through the process, explains the schema and much much more.

I want to focus on providing practical guidance for advanced visualisations for professionals looking to take SEO monitoring to the next level. I will not mention differences between the GSC API and UI. After my session you should be working on building your very own operational SEO monitoring and I will share all the queries we are using in our day to day SEO monitoring.

You can make monitoring daily trends very easy like:

{
  SELECT 
    current_week.data_date,
    current_week.click_count AS current_week_clicks,
    previous_week.click_count AS previous_week_clicks,
    (current_week.click_count - IFNULL(previous_week.click_count, 0)) AS difference,
    current_week.data_date as label_curr,
    previous_week.data_date as label_pre

FROM 
    (SELECT data_date, SUM(clicks) AS click_count
     FROM `searchconsole.searchdata_site_impression`
     WHERE data_date BETWEEN (CURRENT_DATE -2) - INTERVAL 4 WEEK AND CURRENT_DATE-2
     GROUP BY data_date) AS current_week
LEFT JOIN 
    (SELECT data_date, SUM(clicks) AS click_count
     FROM `searchconsole.searchdata_site_impression`
     WHERE data_date BETWEEN (CURRENT_DATE-2) - INTERVAL 8 WEEK AND (CURRENT_DATE -2) - INTERVAL 4 WEEK
     GROUP BY data_date) AS previous_week
ON current_week.data_date = previous_week.data_date + INTERVAL 4 WEEK
ORDER BY current_week.data_date;
}

Go deeper and tailor SEO monitoring to your needs

Now imagine the GSC UI with much more granular data and the charts are tailor made for your page segments, keyword clusters and your customers behaviour. Especially for large and complex site structures its not possible to keep an eye an every change in every segment - until you have a custom monitoring and alerting.

One last example before I spoiler too much as everyone seems to recognize now the issue: The share of anonymized queries in GSC is growing steadily every month and unfortunately Google is actively hiding more and more information for SEOs. But here you can see how we track it and drill down into our own page segments.

Outcome:

By the end of the session, you'll be equipped with all the possibilities to run advanced GSC data queries in BigQuery, allowing you to gain richer insights of your SEO performance, make data-driven decisions, and monitor your overall SEO performance on a very granular level.

I have roughly 70 custom made slides in Looker Studio for our monitoring and will hopefully share most of the report and the SQL queries with you.

PS: if my pitch won't be accepted, I'll pitch it for the next SEOkomm and maybe somewhere else. So stay tuned.