Lock in $30 Savings on PRO—Offer Ends Soon! ⏳
Speaker Deck
Features
Speaker Deck
PRO
Sign in
Sign up for free
Search
Search
Using Google BigQuery to dissect GSC data & get...
Search
Marco Giordano
October 04, 2024
2
590
Using Google BigQuery to dissect GSC data & get actionable insights
Marco Giordano
October 04, 2024
Tweet
Share
Featured
See All Featured
Teambox: Starting and Learning
jrom
133
8.8k
Typedesign – Prime Four
hannesfritz
40
2.4k
[Rails World 2023 - Day 1 Closing Keynote] - The Magic of Rails
eileencodes
33
1.9k
GraphQLとの向き合い方2022年版
quramy
44
13k
No one is an island. Learnings from fostering a developers community.
thoeni
19
3k
Optimizing for Happiness
mojombo
376
70k
We Have a Design System, Now What?
morganepeng
51
7.2k
Fantastic passwords and where to find them - at NoRuKo
philnash
50
2.9k
Dealing with People You Can't Stand - Big Design 2015
cassininazir
365
24k
Bootstrapping a Software Product
garrettdimon
PRO
305
110k
Reflections from 52 weeks, 52 projects
jeffersonlam
346
20k
Done Done
chrislema
181
16k
Transcript
Using Google BigQuery to dissect GSC data & get actionable
insights Marco Giordano SEOTISTICS /marco-giordano96/ @GiordMarco96 https://speakerdeck.com/marcogiordano96
About Me Data/Web Analyst with a background in BA, Computer
Science and Analytics Specialized in Python, BigQuery, Google Cloud and R Runs a newsletter and website called Seotistics Active on LinkedIn and X
I struggle to report on SEO data 😭 @GiordMarco96
I have to manually download GSC data 😭 @GiordMarco96
I can’t do YoY reports 😭 @GiordMarco96
You will know why BigQuery is the real deal... @GiordMarco96
✅ How to analyze GSC data properly @GiordMarco96
✅ How analysis fits into the business @GiordMarco96
✅ Processes for this data @GiordMarco96
What Is BigQuery? @GiordMarco96
A data warehouse solution by Google. You can store your
GSC data there via the free connector. @GiordMarco96
@GiordMarco96
Why BigQuery? @GiordMarco96
1. The Bulk Export connector gives access to much more
rows (due to anonymized queries). It’s the purest data you can get! @GiordMarco96
2. You can reproduce and document what you do easily.
@GiordMarco96
3. Bypass GSC 16-month limit and sampling. @GiordMarco96
How Does It Work? @GiordMarco96
Write queries in SQL to get data from BigQuery. @GiordMarco96
You write queries in SQL and get back an output.
@GiordMarco96
AI can help you with writing basic SQL code. @GiordMarco96
@GiordMarco96
Content Auditing Framework @GiordMarco96
@GiordMarco96
@GiordMarco96 Full Framework on https://seotistics.com/content- auditing-framework/
Inspecting GSC Data @GiordMarco96
What You Get In BigQuery @GiordMarco96
@GiordMarco96
The GSC Data @GiordMarco96
Much like in Looker Studio when using GSC data: @GiordMarco96
Site VS Url Tables Site: aggregating by property. Url: aggregating
by page. Double counts impressions and contains the page column. @GiordMarco96
Pick the Url Table It contains the url column, so
just choose it! @GiordMarco96
@GiordMarco96
@GiordMarco96
P.S. Remember to specify that you want WEB results! @GiordMarco96
The average position and the CTR are NOT available by
default. @GiordMarco96
You need to calculate them when aggregating data. @GiordMarco96
Analyzing Data @GiordMarco96
@GiordMarco96 BigQuery Handbook [Free] ➡️
Site Analysis @GiordMarco96
% Anonymized Queries To gauge how many queries you miss.
Not actionable but needed as a benchmark. @GiordMarco96
% 0 Clicks Gauge how many pages get no traffic
from Google. Numbers above 40% are bad! @GiordMarco96
% Clicks By Top 10% Pages By Clicks How much
do the top 10% pages affect my website? A drop in that top 10% will affect you a LOT. @GiordMarco96
Page/Cluster Analysis @GiordMarco96
Best Pages By Clicks/Impressions What are the best performing pages
for organic traffic? P.S. Remember that traffic is often vanity 👀 @GiordMarco96
Evergreen Content Find which pages are your most reliable performers.
Evergreen content should be repurposed and used to research new topics. @GiordMarco96
@GiordMarco96
Query Analysis @GiordMarco96
Top 5 Queries Per Page Analyze the best queries for
every page! @GiordMarco96
Best Queries By Clicks/Impressions What are the best performing queries
for organic traffic? P.S. Queries with low clicks, high impressions are interesting! @GiordMarco96
Integrating With GA4 @GiordMarco96
Joining With GA4 In practice, you will check GA4 and
GSC together. P.S. Full explanation in my article, it’s quite complex! @GiordMarco96
Conversions, sales and custom events/dimensions are the MVPs! @GiordMarco96
What do GA4 & GSC have in common? @GiordMarco96
They both have a URL column! @GiordMarco96
@GiordMarco96
@GiordMarco96
@GiordMarco96
@GiordMarco96 A Page-Centric table with GA4 + GSC.
The Page-centric table can be summarized like this: @GiordMarco96
@GiordMarco96
1 row per page, more info as possible. @GiordMarco96
@GiordMarco96 Page/Query-Centric (aka GSC) Remember: most queries will be NULL
(anonymized).
Optimizations @GiordMarco96
Want To Save Money? @GiordMarco96
@GiordMarco96
Filter by DATA_DATE to get notable improvements. @GiordMarco96
BQ will only use those partitions (i.e. less money spent
and faster). @GiordMarco96
@GiordMarco96
@GiordMarco96 Taken From https://cloud.google.com/bigquery/doc s/clustered-tables
Clustering Use columns that you query often, e.g. url, query,
search_type. Up to 4 columns are accepted! ✅ This is usually done by engineers though... @GiordMarco96
Always ask your Data Engineer(s) first! @GiordMarco96
Business Side @GiordMarco96
Business > All All work is dependent on the business.
This is where many fail. @GiordMarco96
Business value starts from relevant questions... @GiordMarco96
@GiordMarco96
@GiordMarco96 Link in the next slide >>
@GiordMarco96 Full Framework on https://seotistics.com/web-analytics- business-framework/
Business Questions What drives your analysis. It’s always about money,
remember! How can we make money? What KPIs can I target? @GiordMarco96
Descriptive Questions What we’ve seen today. Asking what happened, purely
descriptive. What are some common patterns? Can I split pages into groups? @GiordMarco96
Predictive Questions Finding the factors that affect the future output
the most. Machine Learning sits here. Which metrics are the most important to predict SEO success? @GiordMarco96
Prescriptive Questions Being prescriptive means preventing issues or creating opportunities.
Which KPIs will I impact? Can we prevent problems? @GiordMarco96
@GiordMarco96
Insight ➡️ Action Pages with lowest clicks ⬇️ Optimization/Pruning @GiordMarco96
Best pages ⬇️ Distribution/Repurposing
Managing Risk @GiordMarco96
Set up triggers and warnings in BigQuery! @GiordMarco96
Document the most common issues! @GiordMarco96
@GiordMarco96
Takeaways @GiordMarco96
What We Learned ✅ BigQuery stores your data limitlessly and
effortlessly @GiordMarco96
What We Learned ✅ BigQuery stores your data limitlessly and
effortlessly ✅ The 3-4 analyses to run on your SEO data @GiordMarco96
What We Learned ✅ BigQuery stores your data limitlessly and
effortlessly ✅ The 3-4 analyses to run on your SEO data ✅ Ask questions and connect insights to actions @GiordMarco96
Resources @GiordMarco96
Additional Resources Learn Analytics for SEO [Course] - 30% Off
(Until Oct. 15) Learn Analytics for SEO [Ebook] - 50% Off (Until Oct. 12) BigQuery Training By Google Google Documentation Seotistics Blog (for Web Analytics) @GiordMarco96
@GiordMarco96
Thank You! /marco-giordano96/ @GiordMarco96 seotistics.com