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

Scale up SEO with Google BigQuery

Scale up SEO with Google BigQuery

Nabeel

April 25, 2024
Tweet

Other Decks in Marketing & SEO

Transcript

  1. Classified as Internal Scaling up your SEO with Google BigQuery

    Nabeel Tanveer Global SEO Manager Electrolux Group @nabeelt87 linkedin.com/in/nabeelt87
  2. Classified as Internal How I transitioned from Excel to BigQuery

    #BrightonSEO @nabeelt87 linkedin.com/in/nabeelt87
  3. Classified as Internal Countries Languages # of Stores No. of

    Indexed Pages 2.1 M 50+ @nabeelt87 linkedin.com/in/nabeelt87 70+ 450+ Est. Traffic Monthly 19.3 M
  4. Connect & dump all GSC data into a big table

    in BigQuery. Use SQL to take valuable SEO insights from massive data. GSC BULK DATA EXPORT TO BIGQUERY
  5. 1- Manual process. 2- Data limitations. (1000 records max.) Why

    Do We need it? @nabeelt87 linkedin.com/in/nabeelt87
  6. Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 Useful for Small/Large Websites. 1.

    Access to Unlimited Historical Data 2. Its cloud based. (Scalable) 3. Easy to Learn. 4. PERKS
  7. 1- You’ll need property owner rights. 2- You will also

    need to set up billing. How to Set Up?
  8. Classified as Internal The first 1 TiB of query data

    processed per month is free. Partially Free! Processing https://cloud.google.com/bigquery/pricing#free
  9. Classified as Internal Performing a SQL Query is like Shopping.

    Analogy @nabeelt87 linkedin.com/in/nabeelt87
  10. Classified as Internal BASIC SYNTAX SELECT Date, Total.visits, Total.transactionRevenue FROM

    'bigquery-public- data.google_analytics_sample.ga_sessions_20170801' WHERE channelGrouping = ‘Organic Search’
  11. Classified as Internal Find values within a certain range.. SQL

    QUERY - BETWEEN Page Views 50 > but <100
  12. Classified as Internal SELECT geoNetwork.country, DATE, channelGrouping FROM 'bigquery-public- data.google_analytics_sample.ga_sessions_20170801'

    WHERE geoNetwork.country NOT IN ("Germany", "Poland", "Brazil") AND totals.pageviews BETWEEN 50 AND 100 SQL QUERY - BETWEEN
  13. Classified as Internal COUNT SUM MIN MAX AVG What if

    you wish to aggregate? AGGREGATE FUNCTIONS
  14. Classified as Internal Group values into a summary row. Usually

    used with Aggregate functions. SQL QUERY – GROUP BY
  15. Classified as Internal SELECT DATE, geoNetwork.country, SUM(totals.pageviews) as Page_Views, channelGrouping

    FROM 'bigquery-public- data.google_analytics_sample.ga_sessions_20170801' WHERE channelGrouping = "Organic Search" GROUP BY Date, ChannelGrouping, geoNetwork.country SQL QUERY – GROUP BY
  16. Classified as Internal SELECT DATE, geoNetwork.country, SUM(totals.pageviews) as Page_Views, channelGrouping

    FROM 'bigquery-public-data.google_analytics_sample.ga_sessions_20170801' WHERE channelGrouping = "Organic Search" GROUP BY Date, ChannelGrouping, geoNetwork.country HAVING SUM(totals.pageviews) > 100 SQL QUERY – HAVING
  17. Classified as Internal SQL QUERY - LIKE Find specific patterns..

    URL Patterns Granular Keywords Specific Country
  18. Classified as Internal SQL QUERY - LIKE SELECT h.page.pagePath, DATE,

    channelGrouping as Channel FROM 'bigquery-public- data.google_analytics_sample.ga_sessions_20170801' WHERE h.page.pagePath LIKE "%/mens/%"
  19. Classified as Internal SQL QUERY - LIKE SELECT h.page.pagePath, DATE,

    channelGrouping as Channel, h.referer FROM 'bigquery-public- data.google_analytics_sample.ga_sessions_20170801' WHERE h.referer LIKE "%fr/"
  20. Classified as Internal n = (m - p + 1)

    ^ k Possible Combinations SQL QUERY – WILDCARDS
  21. Classified as Internal The CASE statement is SQL's way of

    handling if/then logic. SQL QUERY - CASE
  22. Classified as Internal SQL QUERY - CASE SELECT column_names CASE

    WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ELSE end_result END AS column_name
  23. Classified as Internal SQL QUERY - CASE SELECT column_names CASE

    WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ELSE end_result END AS column_name
  24. Classified as Internal SQL QUERY - CASE SELECT Date, H.page.pagePath

    AS URLs, totals.transactions, CASE WHEN H.page.pagePath LIKE '%/mens/%' THEN 'MEN_Group' WHEN H.page.pagePath LIKE '%/kids/%' THEN 'KIDS_Group' ELSE 'Other Groups' END AS Content_Type FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`,
  25. Classified as Internal SQL QUERY - CASE SELECT SUM(totals.transactions) AS

    Total_Sale, channelGrouping, CASE WHEN H.page.pagePath LIKE '%/mens/%' THEN 'MEN_Group' WHEN H.page.pagePath LIKE '%/kids/%' THEN 'KIDS_Group' ELSE 'Other Groups' END AS Content_Type FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`, WHERE channelGrouping = "Organic Search" GROUP BY Content_Type, channelGrouping
  26. Classified as Internal SELECT FROM WHERE ORDER BY GROUP BY

    JOIN LIMIT Areej Abu Ali https://www.slideshare.net/areejabuali/lo ndonseo-2020-bigquery-sql-for-seos BIGQUERY/SQL BEGINNER GUIDE What she has Covered @areej_abuali
  27. Classified as Internal Trevor Fox Google Search Console Bulk Export

    for BigQuery: The Complete Guide Dom Woodman How to use BigQuery for Large- Scale SEO @dom_woodman @RealTrevorFaux MORE USEFUL RESOURCES
  28. Classified as Internal 2 3 1 Enter a prompt 1.

    Your prompt 2. SQL Query. 3.
  29. Classified as Internal SQL Resources Udacity – SQL for Data

    Analysis 1. Cacheworthy – Learn BigQuery SQL 2. Mode – SQL Tutorial 3. OnCrawl – Why SEO’s Learn SQL 4. SQL Practice – Online SQL Practice (Free Resource) 5.
  30. Classified as Internal IN BETWEEN GROUP BY HAVING LIKE CASE

    RECAP @nabeelt87 linkedin.com/in/nabeelt87
  31. Classified as Internal Not Covered DISTINCT WINDOW WITH NESTED- QUERIES

    @nabeelt87 linkedin.com/in/nabeelt87 IN BETWEEN GROUP BY HAVING LIKE CASE