Slide 1

Slide 1 text

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

Slide 94

Slide 94 text

@GiordMarco96

Slide 95

Slide 95 text

Thank You! /marco-giordano96/ @GiordMarco96 seotistics.com