Using Google BigQuery to dissect GSC data & get actionable insights

Transcript

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