Upgrade to Pro
— share decks privately, control downloads, hide ads and more …
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
610
Using Google BigQuery to dissect GSC data & get actionable insights
Marco Giordano
October 04, 2024
Tweet
Share
Featured
See All Featured
"I'm Feeling Lucky" - Building Great Search Experiences for Today's Users (#IAC19)
danielanewman
226
22k
Building an army of robots
kneath
302
44k
The Straight Up "How To Draw Better" Workshop
denniskardys
232
140k
Building Applications with DynamoDB
mza
91
6.1k
Practical Tips for Bootstrapping Information Extraction Pipelines
honnibal
PRO
10
810
Building a Modern Day E-commerce SEO Strategy
aleyda
38
7k
Build The Right Thing And Hit Your Dates
maggiecrowley
33
2.4k
Fantastic passwords and where to find them - at NoRuKo
philnash
50
2.9k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
169
50k
Reflections from 52 weeks, 52 projects
jeffersonlam
347
20k
Learning to Love Humans: Emotional Interface Design
aarron
273
40k
For a Future-Friendly Web
brad_frost
175
9.4k
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