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

Using Google BigQuery to dissect GSC data & get...

Marco Giordano
October 04, 2024
610

Using Google BigQuery to dissect GSC data & get actionable insights

Marco Giordano

October 04, 2024
Tweet

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. A data warehouse solution by Google. You can store your

    GSC data there via the free connector. @GiordMarco96
  4. 1. The Bulk Export connector gives access to much more

    rows (due to anonymized queries). It’s the purest data you can get! @GiordMarco96
  5. Site VS Url Tables Site: aggregating by property. Url: aggregating

    by page. Double counts impressions and contains the page column. @GiordMarco96
  6. % Anonymized Queries To gauge how many queries you miss.

    Not actionable but needed as a benchmark. @GiordMarco96
  7. % 0 Clicks Gauge how many pages get no traffic

    from Google. Numbers above 40% are bad! @GiordMarco96
  8. % 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
  9. Best Pages By Clicks/Impressions What are the best performing pages

    for organic traffic? P.S. Remember that traffic is often vanity 👀 @GiordMarco96
  10. Evergreen Content Find which pages are your most reliable performers.

    Evergreen content should be repurposed and used to research new topics. @GiordMarco96
  11. 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
  12. Joining With GA4 In practice, you will check GA4 and

    GSC together. P.S. Full explanation in my article, it’s quite complex! @GiordMarco96
  13. 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
  14. Business > All All work is dependent on the business.

    This is where many fail. @GiordMarco96
  15. Business Questions What drives your analysis. It’s always about money,

    remember! How can we make money? What KPIs can I target? @GiordMarco96
  16. Descriptive Questions What we’ve seen today. Asking what happened, purely

    descriptive. What are some common patterns? Can I split pages into groups? @GiordMarco96
  17. 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
  18. Prescriptive Questions Being prescriptive means preventing issues or creating opportunities.

    Which KPIs will I impact? Can we prevent problems? @GiordMarco96
  19. What We Learned ✅ BigQuery stores your data limitlessly and

    effortlessly ✅ The 3-4 analyses to run on your SEO data @GiordMarco96
  20. 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
  21. 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