Using Google
BigQuery to dissect
GSC data & get
actionable insights
Marco Giordano
SEOTISTICS
/marco-giordano96/
@GiordMarco96
https://speakerdeck.com/marcogiordano96
Slide 2
Slide 2 text
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
Slide 3
Slide 3 text
I struggle to report on SEO
data
😭
@GiordMarco96
Slide 4
Slide 4 text
I have to manually
download GSC data
😭
@GiordMarco96
Slide 5
Slide 5 text
I can’t do YoY reports
😭
@GiordMarco96
Slide 6
Slide 6 text
You will know why BigQuery
is the real deal...
@GiordMarco96
Slide 7
Slide 7 text
✅ How to analyze GSC
data properly
@GiordMarco96
Slide 8
Slide 8 text
✅ How analysis fits into
the business
@GiordMarco96
Slide 9
Slide 9 text
✅ Processes for this data
@GiordMarco96
Slide 10
Slide 10 text
What Is BigQuery?
@GiordMarco96
Slide 11
Slide 11 text
A data warehouse solution by
Google.
You can store your GSC data there
via the free connector.
@GiordMarco96
Slide 12
Slide 12 text
@GiordMarco96
Slide 13
Slide 13 text
Why BigQuery?
@GiordMarco96
Slide 14
Slide 14 text
1. The Bulk Export connector gives access to much
more rows (due to anonymized queries).
It’s the purest data you can get!
@GiordMarco96
Slide 15
Slide 15 text
2. You can reproduce and document what you do
easily.
@GiordMarco96
Slide 16
Slide 16 text
3. Bypass GSC 16-month limit and sampling.
@GiordMarco96
Slide 17
Slide 17 text
How Does It Work?
@GiordMarco96
Slide 18
Slide 18 text
Write queries in SQL to get data
from BigQuery.
@GiordMarco96
Slide 19
Slide 19 text
You write queries in SQL and get back an output.
@GiordMarco96
Slide 20
Slide 20 text
AI can help you with writing basic SQL code.
@GiordMarco96
Slide 21
Slide 21 text
@GiordMarco96
Slide 22
Slide 22 text
Content Auditing
Framework
@GiordMarco96
Slide 23
Slide 23 text
@GiordMarco96
Slide 24
Slide 24 text
@GiordMarco96
Full Framework on
https://seotistics.com/content-
auditing-framework/
Slide 25
Slide 25 text
Inspecting GSC Data
@GiordMarco96
Slide 26
Slide 26 text
What You Get In BigQuery
@GiordMarco96
Slide 27
Slide 27 text
@GiordMarco96
Slide 28
Slide 28 text
The GSC Data
@GiordMarco96
Slide 29
Slide 29 text
Much like in Looker Studio when using GSC data:
@GiordMarco96
Slide 30
Slide 30 text
Site VS Url Tables
Site: aggregating by property.
Url: aggregating by page. Double counts impressions
and contains the page column.
@GiordMarco96
Slide 31
Slide 31 text
Pick the Url Table
It contains the url column, so just choose it!
@GiordMarco96
Slide 32
Slide 32 text
@GiordMarco96
Slide 33
Slide 33 text
@GiordMarco96
Slide 34
Slide 34 text
P.S. Remember to specify that you
want WEB results!
@GiordMarco96
Slide 35
Slide 35 text
The average position and the CTR
are NOT available by default.
@GiordMarco96
Slide 36
Slide 36 text
You need to calculate them when
aggregating data.
@GiordMarco96
Slide 37
Slide 37 text
Analyzing Data
@GiordMarco96
Slide 38
Slide 38 text
@GiordMarco96
BigQuery Handbook
[Free] ➡️
Slide 39
Slide 39 text
Site Analysis
@GiordMarco96
Slide 40
Slide 40 text
% Anonymized Queries
To gauge how many queries you miss.
Not actionable but needed as a benchmark.
@GiordMarco96
Slide 41
Slide 41 text
% 0 Clicks
Gauge how many pages get no traffic from Google.
Numbers above 40% are bad!
@GiordMarco96
Slide 42
Slide 42 text
% 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
Slide 43
Slide 43 text
Page/Cluster Analysis
@GiordMarco96
Slide 44
Slide 44 text
Best Pages By Clicks/Impressions
What are the best performing pages for organic
traffic?
P.S. Remember that traffic is often vanity
👀
@GiordMarco96
Slide 45
Slide 45 text
Evergreen Content
Find which pages are your most reliable performers.
Evergreen content should be repurposed and used to
research new topics.
@GiordMarco96
Slide 46
Slide 46 text
@GiordMarco96
Slide 47
Slide 47 text
Query Analysis
@GiordMarco96
Slide 48
Slide 48 text
Top 5 Queries Per Page
Analyze the best queries for every page!
@GiordMarco96
Slide 49
Slide 49 text
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
Slide 50
Slide 50 text
Integrating With GA4
@GiordMarco96
Slide 51
Slide 51 text
Joining With GA4
In practice, you will check GA4 and GSC together.
P.S. Full explanation in my article, it’s quite complex!
@GiordMarco96
Slide 52
Slide 52 text
Conversions, sales and custom
events/dimensions are the MVPs!
@GiordMarco96
Slide 53
Slide 53 text
What do GA4 & GSC have in
common?
@GiordMarco96
Slide 54
Slide 54 text
They both have a URL column!
@GiordMarco96
Slide 55
Slide 55 text
@GiordMarco96
Slide 56
Slide 56 text
@GiordMarco96
Slide 57
Slide 57 text
@GiordMarco96
Slide 58
Slide 58 text
@GiordMarco96
A Page-Centric table with GA4 + GSC.
Slide 59
Slide 59 text
The Page-centric table can be
summarized like this:
@GiordMarco96
Slide 60
Slide 60 text
@GiordMarco96
Slide 61
Slide 61 text
1 row per page, more info as
possible.
@GiordMarco96
Slide 62
Slide 62 text
@GiordMarco96
Page/Query-Centric (aka GSC)
Remember: most queries will be NULL (anonymized).
Slide 63
Slide 63 text
Optimizations
@GiordMarco96
Slide 64
Slide 64 text
Want To Save Money?
@GiordMarco96
Slide 65
Slide 65 text
@GiordMarco96
Slide 66
Slide 66 text
Filter by DATA_DATE to get
notable improvements.
@GiordMarco96
Slide 67
Slide 67 text
BQ will only use those partitions
(i.e. less money spent and faster).
@GiordMarco96
Slide 68
Slide 68 text
@GiordMarco96
Slide 69
Slide 69 text
@GiordMarco96
Taken From
https://cloud.google.com/bigquery/doc
s/clustered-tables
Slide 70
Slide 70 text
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
Slide 71
Slide 71 text
Always ask your Data Engineer(s)
first!
@GiordMarco96
Slide 72
Slide 72 text
Business Side
@GiordMarco96
Slide 73
Slide 73 text
Business > All
All work is dependent on the business.
This is where many fail.
@GiordMarco96
Slide 74
Slide 74 text
Business value starts from relevant
questions...
@GiordMarco96
Slide 75
Slide 75 text
@GiordMarco96
Slide 76
Slide 76 text
@GiordMarco96
Link in the next slide >>
Slide 77
Slide 77 text
@GiordMarco96
Full Framework on
https://seotistics.com/web-analytics-
business-framework/
Slide 78
Slide 78 text
Business Questions
What drives your analysis.
It’s always about money, remember!
How can we make money?
What KPIs can I target?
@GiordMarco96
Slide 79
Slide 79 text
Descriptive Questions
What we’ve seen today.
Asking what happened, purely descriptive.
What are some common patterns?
Can I split pages into groups?
@GiordMarco96
Slide 80
Slide 80 text
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
Slide 81
Slide 81 text
Prescriptive Questions
Being prescriptive means preventing issues or
creating opportunities.
Which KPIs will I impact?
Can we prevent problems?
@GiordMarco96
Slide 82
Slide 82 text
@GiordMarco96
Slide 83
Slide 83 text
Insight ➡️ Action
Pages with lowest clicks
⬇️
Optimization/Pruning
@GiordMarco96
Best pages
⬇️
Distribution/Repurposing
Slide 84
Slide 84 text
Managing Risk
@GiordMarco96
Slide 85
Slide 85 text
Set up triggers and warnings in
BigQuery!
@GiordMarco96
Slide 86
Slide 86 text
Document the most common issues!
@GiordMarco96
Slide 87
Slide 87 text
@GiordMarco96
Slide 88
Slide 88 text
Takeaways
@GiordMarco96
Slide 89
Slide 89 text
What We Learned
✅ BigQuery stores your data limitlessly and effortlessly
@GiordMarco96
Slide 90
Slide 90 text
What We Learned
✅ BigQuery stores your data limitlessly and effortlessly
✅ The 3-4 analyses to run on your SEO data
@GiordMarco96
Slide 91
Slide 91 text
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
Slide 92
Slide 92 text
Resources
@GiordMarco96
Slide 93
Slide 93 text
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