Upgrade to Pro — share decks privately, control downloads, hide ads and more …

FoundConf 2025 - Turbulence

Sponsored · Your Podcast. Everywhere. Effortlessly. Share. Educate. Inspire. Entertain. You do you. We'll handle the rest. →

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.

Noah Learner

October 22, 2025

Transcript

151 slides
  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 Source where the tra ffi c

    originated google, facebook, newsletter
  3. 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
  4. noahlearner | Sterling Sky Organic Search Source matches a list

    of search sites listed under "SOURCE_CATEGORY_SEARCH" OR Medium exactly matches organic
  5. 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”)
  6. noahlearner | Sterling Sky Organic Video Source matches a list

    of video sites OR Medium matches regex ^(.*video.*)$
  7. 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.*$
  8. noahlearner | Sterling Sky Grain is the amount of time

    we measure our data by Week Month
  9. noahlearner | Sterling Sky Revenue = Tra ffi c X

    Conv rate X Avg order / Lead Value
  10. noahlearner | Sterling Sky Unassigned is the value Google Analytics

    uses when no other channel rules match the event data.
  11. noahlearner | Sterling Sky GSC Bulk Export is 3 tables

    •ExportLog •searchdata_url_impression •searchdata_site_impression
  12. noahlearner | Sterling Sky GSC Bulk Export includes •All countries

    •All devices •All search types •All search appearances (booleans) •Anonymized queries •No row limits
  13. noahlearner | Sterling Sky Find Your Way to Explore the

    Data that work for you! ALL Search Types BOOM!
  14. 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
  15. 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
  16. noahlearner | Sterling Sky Find Your Way to Explore the

    Data that work for you! An agency Specialization
  17. 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
  18. 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
  19. noahlearner | Sterling Sky Find Your Way to Explore the

    Data that work for you! With Explorer we can see more:
  20. 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
  21. 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)
  22. 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)
  23. 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
  24. 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.
  25. 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.

More Decks by Noah Learner

Other Decks in Marketing & SEO