FoundConf 2025 - Turbulence

This deck is about using critical thinking skills, Looker Studio, and Google BigQuery to find opportunities in Google analytics, and GSC data. I also walk through a number of different data analyses to show how I perform analysis.

Transcript

1. noahlearner | Sterling Sky It sure would be cool if you could talk about what you do around here. - Joy Hawkins
2. noahlearner | Sterling Sky Me fl ying to Japan
3. noahlearner | Sterling Sky I feel Like this
4. noahlearner | Sterling Sky Out of control. And a little afraid.
5. noahlearner | Sterling Sky We all experience Turbulence
6. noahlearner | Sterling Sky We experience turbulence every day
7. noahlearner | Sterling Sky This deck is about
8. noahlearner | Sterling Sky Controlling the controllables
9. noahlearner | Sterling Sky Seeing wider
10. noahlearner | Sterling Sky Thinking bigger
11. noahlearner | Sterling Sky What we see
12. noahlearner | Sterling Sky Go deep into the data
13. noahlearner | Sterling Sky
14. noahlearner | Sterling Sky Google Analytics
15. noahlearner | Sterling Sky How do you make money?
16. noahlearner | Sterling Sky What are you doing in each channel to drive revenue?
17. noahlearner | Sterling Sky What’s a channel?
18. noahlearner | Sterling Sky Channels are how we categorize tra ffi c sources to our websites
19. noahlearner | Sterling Sky Primary dimensions:
20. noahlearner | Sterling Sky Source where the tra ffi c originated google, facebook, newsletter
21. noahlearner | Sterling Sky Medium the general category of the source organic, cpc, email, referral
22. noahlearner | Sterling Sky Campaign the speci fi c marketing campaign name (if tagged)
23. noahlearner | Sterling Sky Secondary dimensions: Source platform the platform that sent the tra ff i c Campaign ID unique identi fi er for campaigns Google Ads parameters for paid Google tra ff i c
24. noahlearner | Sterling Sky Google’s list of sources + categories
25. noahlearner | Sterling Sky Important LLM sources are not on the list
26. noahlearner | Sterling Sky We SEOs are obsessed with Organic Search
27. noahlearner | Sterling Sky We need to understand how all the channels work on our sites
28. noahlearner | Sterling Sky Organic Search Source matches a list of search sites listed under "SOURCE_CATEGORY_SEARCH" OR Medium exactly matches organic
29. noahlearner | Sterling Sky Organic Social Source matches a regex list of social sites OR Medium is one of (“social”, “social-network”, “social-media”, “sm”, “social network”, “social media”)
30. noahlearner | Sterling Sky Organic Video Source matches a list of video sites OR Medium matches regex ^(.*video.*)$
31. noahlearner | Sterling Sky Referral Medium is one of ("referral", "app", or "link")
32. noahlearner | Sterling Sky Email Source = email|e-mail|e_mail|e mail OR Medium = email|e-mail|e_mail|e mail
33. noahlearner | Sterling Sky Direct Source exactly matches "(direct)" AND Medium is one of ("(not set)", "(none)")
34. noahlearner | Sterling Sky What about LLMs?
35. noahlearner | Sterling Sky Enter Custom Channels How to build them
36. noahlearner | Sterling Sky
37. noahlearner | Sterling Sky LLM Channel - source matches Regex ^.*ai|.*\.openai.*|.*copilot.*|.*chatgpt.*|.* gemini.*|.*gpt.*|.*neeva.*|.*writesonic.*|.* nimble.*|.*outrider.*|.*perplexity.*|.*googl e.*bard.*|.*bard.*google.*|.*bard.*|.*edge services.*|.*astastic.*|.*copy.ai.*|.*bnngpt. *|.*gemini.*google.*$
38. noahlearner | Sterling Sky LLM Channel Drag to top
39. noahlearner | Sterling Sky
40. noahlearner | Sterling Sky Grain is the amount of time we measure our data by Week Month
41. noahlearner | Sterling Sky
42. noahlearner | Sterling Sky Use calculated fi elds to reduce cognitive load
43. noahlearner | Sterling Sky
44. noahlearner | Sterling Sky
45. noahlearner | Sterling Sky Drilldown
46. noahlearner | Sterling Sky
47. noahlearner | Sterling Sky
48. noahlearner | Sterling Sky 1 2 3 4
49. noahlearner | Sterling Sky Cross filtering allows us to go from a haystack to a needle
50. noahlearner | Sterling Sky
51. noahlearner | Sterling Sky Filter from channel to page + see where + when key events occur
52. noahlearner | Sterling Sky 1 2 3 4 5
53. noahlearner | Sterling Sky Revenue = Tra ffi c X Conv rate X Avg order / Lead Value
54. noahlearner | Sterling Sky Organic search tra ffi c is down
55. noahlearner | Sterling Sky For Revenue to grow we must
56. noahlearner | Sterling Sky replace lost tra ff i c
57. noahlearner | Sterling Sky AND improve conversion rates
58. noahlearner | Sterling Sky AND increase avg order / lead values too
59. noahlearner | Sterling Sky Where does your audience spend time?
60. noahlearner | Sterling Sky Are you active there?
61. noahlearner | Sterling Sky Are you putting your marketing budget where your audience is?
62. noahlearner | Sterling Sky How well do you know your customers?
63. noahlearner | Sterling Sky Next we look at Referrals The future demands we go wider
64. noahlearner | Sterling Sky 1 2 3 4
65. noahlearner | Sterling Sky 1 2 3
66. noahlearner | Sterling Sky 1 2 3
67. noahlearner | Sterling Sky
68. noahlearner | Sterling Sky Zoom In + Zoom Out
69. noahlearner | Sterling Sky Let’s take it a step further The Unassigned Channel
70. noahlearner | Sterling Sky
71. noahlearner | Sterling Sky Unassigned is the value Google Analytics uses when no other channel rules match the event data.
72. noahlearner | Sterling Sky Remember grain + drill down?
73. noahlearner | Sterling Sky
74. noahlearner | Sterling Sky
75. noahlearner | Sterling Sky It’s an entirely new story now
76. noahlearner | Sterling Sky With a near weekly shape. Is it BOT TRAFFIC?
77. noahlearner | Sterling Sky Zoom In on Aug 4
78. noahlearner | Sterling Sky What kind of bots would visit our site each week?
79. noahlearner | Sterling Sky We found the cause
80. noahlearner | Sterling Sky
81. noahlearner | Sterling Sky ???
82. noahlearner | Sterling Sky
83. noahlearner | Sterling Sky
84. noahlearner | Sterling Sky Less impressions = Less queries being tracked
85. noahlearner | Sterling Sky How much less?
86. noahlearner | Sterling Sky In a sample site I saw
87. noahlearner | Sterling Sky
88. noahlearner | Sterling Sky 20% less queries tracked
89. noahlearner | Sterling Sky 39% less rows of information
90. noahlearner | Sterling Sky Less queries tracked = Less data
91. noahlearner | Sterling Sky Less data = Lower costs to store
92. noahlearner | Sterling Sky Less data = Lower costs to query
93. noahlearner | Sterling Sky Num=100 deprecation = Perfect time for GSC Bulk Export
94. noahlearner | Sterling Sky GSC Bulk Export
95. noahlearner | Sterling Sky
96. noahlearner | Sterling Sky
97. noahlearner | Sterling Sky
98. noahlearner | Sterling Sky
99. noahlearner | Sterling Sky
100. noahlearner | Sterling Sky GSC Bulk Export is 3 tables •ExportLog •searchdata_url_impression •searchdata_site_impression
101. noahlearner | Sterling Sky site_impression
102. noahlearner | Sterling Sky url_impression
103. noahlearner | Sterling Sky export_log
104. noahlearner | Sterling Sky It includes a ton of data
105. noahlearner | Sterling Sky GSC Bulk Export includes •All countries •All devices •All search types •All search appearances (booleans) •Anonymized queries •No row limits
106. noahlearner | Sterling Sky Total storage costs through 36 months: $0.12
107. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! ALL Search Types BOOM!
108. noahlearner | Sterling Sky it supercharges our num=100 exploration
109. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! WITH A AS ( SELECT query, SUM(clicks) as clicks, SUM(impressions) as impressions FROM `project`.`searchconsole`.`searchdata_url_impression` WHERE data_date BETWEEN '2025-09-03' AND '2025-09-09' -- Week 1 GROUP BY query ), B AS ( SELECT query, SUM(clicks) as clicks, SUM(impressions) as impressions FROM `project`.`searchconsole`.`searchdata_url_impression` WHERE data_date BETWEEN '2025-09-10' AND '2025-09-16' -- Week 2 GROUP BY query ) -- Queries in A but not in B SELECT A.* FROM A LEFT JOIN B ON A.query = B.query WHERE B.query IS NULL ORDER BY A.impressions DESC; Missing URL Queries after NUM=100
110. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! WITH A AS ( SELECT query, SUM(clicks) as clicks, SUM(impressions) as impressions FROM `project`.`searchconsole`.`searchdata_site_impression` WHERE data_date BETWEEN '2025-09-03' AND '2025-09-09' -- Week 1 GROUP BY query ), B AS ( SELECT query, SUM(clicks) as clicks, SUM(impressions) as impressions FROM `project`.`searchconsole`.`searchdata_site_impression` WHERE data_date BETWEEN '2025-09-10' AND '2025-09-16' -- Week 2 GROUP BY query ) -- Queries in A but not in B SELECT A.* FROM A LEFT JOIN B ON A.query = B.query WHERE B.query IS NULL ORDER BY A.impressions DESC; Missing Site Queries after NUM=100
111. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! An agency Specialization
112. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! WITH A AS ( SELECT url, SUM(clicks) as clicks, SUM(impressions) as impressions, SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) as avg_position FROM `project`.`searchconsole`.`searchdata_url_impression` WHERE data_date BETWEEN '2025-09-03' AND '2025-09-09' -- Week 1 AND query IS NULL GROUP BY url ), B AS ( SELECT url, SUM(clicks) as clicks, SUM(impressions) as impressions, SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) as avg_position FROM `project`.`searchconsole`.`searchdata_url_impression` WHERE data_date BETWEEN '2025-09-10' AND '2025-09-16' -- Week 2 AND query IS NULL GROUP BY url ) -- Pages in Week 1 but not in Week 2 (lost visibility) SELECT A.url, A.impressions as week1_impressions, A.clicks as week1_clicks, A.avg_position as week1_avg_position FROM A LEFT JOIN B ON A.url = B.url WHERE B.url IS NULL ORDER BY A.impressions DESC; Pages w lost anonymous queries Week over week
113. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! SELECT url,is_anonymized_query, SUM(clicks) as clicks, SUM(impressions) as impressions, SAFE_DIVIDE(SUM(sum_position), SUM(impressions)) as avg_position FROM `project`.`searchconsole`.`searchdata_url_impression` WHERE data_date BETWEEN '2024-09-03' AND '2025-09-09' and is_anonymized_query=true GROUP BY url, is_anonymized_query Pages with anonymous Queries
114. noahlearner | Sterling Sky Take it up to 11 with Branch Explorer
115. noahlearner | Sterling Sky there weren’t [m]any data viz tools set up for the Bulk Export yet
116. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! With Explorer we can see more:
117. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! With Explorer we can see our data: •Brand vs Non Brand
118. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! With Explorer we can see our data: •Brand vs Non Brand •Funnel stage (top, middle, bottom)
119. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! With Explorer we can see our data: •Brand vs Non Brand •Funnel stage (top, middle, bottom) •Is GBP URL (url has GBP related UTMS)
120. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! With Explorer we can see our data: •Brand vs Non Brand •Funnel stage (top, middle, bottom) •Is GBP URL (url has GBP related UTMS) •Segment our site by URL patterns
121. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you!
122. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! With Explorer we can see: •Brand vs Non Brand •Funnel stage (top, middle, bottom) •Is GBP URL (url has GBP related UTMS) •Segment our site by URL patterns •Which Directories, pages, + queries are driving change on our sites.
123. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you!
124. noahlearner | Sterling Sky
125. noahlearner | Sterling Sky
126. noahlearner | Sterling Sky
127. noahlearner | Sterling Sky Find Your Way to Explore the Data that work for you! It’s not about having more data, it’s about having more ways to see it.
128. noahlearner | Sterling Sky The Best data viz book I know
129. noahlearner | Sterling Sky How we think
130. noahlearner | Sterling Sky Critical thinking
131. noahlearner | Sterling Sky Use a decision making process
132. noahlearner | Sterling Sky Problem Solving 101, Ken Watanabe
133. noahlearner | Sterling Sky The Two Octobers way
134. noahlearner | Sterling Sky Define the problem
135. noahlearner | Sterling Sky Look for 2nd + 3rd order consequences
136. noahlearner | Sterling Sky https://fs.blog/first-principles/
137. noahlearner | Sterling Sky Gather + explore data
138. noahlearner | Sterling Sky Extract insights
139. noahlearner | Sterling Sky Form hypothesis
140. noahlearner | Sterling Sky Execute test
141. noahlearner | Sterling Sky Track + continuously improve
142. noahlearner | Sterling Sky Learn about + remove bias https://thedecisionlab.com/biases-index
143. noahlearner | Sterling Sky Learn to prioritize
144. noahlearner | Sterling Sky What should we do and when should we do it?
145. noahlearner | Sterling Sky Impact High Low Easy Hard Ease of implementation
146. noahlearner | Sterling Sky
147. noahlearner | Sterling Sky Find your Ikigai
148. noahlearner | Sterling Sky See wider
149. noahlearner | Sterling Sky Think bigger
150. noahlearner | Sterling Sky
151. noahlearner | Sterling Sky