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
530
Using Google BigQuery to dissect GSC data & get actionable insights
Marco Giordano
October 04, 2024
Tweet
Share
Featured
See All Featured
Side Projects
sachag
452
42k
Facilitating Awesome Meetings
lara
49
6k
ReactJS: Keep Simple. Everything can be a component!
pedronauck
664
120k
Templates, Plugins, & Blocks: Oh My! Creating the theme that thinks of everything
marktimemedia
26
2.1k
実際に使うSQLの書き方 徹底解説 / pgcon21j-tutorial
soudai
167
49k
Fantastic passwords and where to find them - at NoRuKo
philnash
50
2.8k
"I'm Feeling Lucky" - Building Great Search Experiences for Today's Users (#IAC19)
danielanewman
226
22k
StorybookのUI Testing Handbookを読んだ
zakiyama
26
5.2k
Understanding Cognitive Biases in Performance Measurement
bluesmoon
26
1.4k
Making the Leap to Tech Lead
cromwellryan
132
8.9k
Imperfection Machines: The Place of Print at Facebook
scottboms
264
13k
CSS Pre-Processors: Stylus, Less & Sass
bermonpainter
355
29k
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