Scale up SEO with Google BigQuery

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. Mostly working with small to medium size sites. AGENCY-SIDE @nabeelt87 linkedin.com/in/nabeelt87
4. @nabeelt87 linkedin.com/in/nabeelt87
5. AGENCY CLIENT-SIDE @nabeelt87 linkedin.com/in/nabeelt87
6. 6 Any guess? @nabeelt87 linkedin.com/in/nabeelt87
8. 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
9. Classified as Internal Small site Enterprise Site @nabeelt87 linkedin.com/in/nabeelt87
10. @nabeelt87 linkedin.com/in/nabeelt87
11. @nabeelt87 linkedin.com/in/nabeelt87
12. @nabeelt87 linkedin.com/in/nabeelt87
13. Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 Automation Cloud Language Python
14. @nabeelt87 linkedin.com/in/nabeelt87 True Story
15. Classified as Internal
16. Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 Automation Cloud Language Big Query
17. Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 Automation Cloud Language Big Query SQL
18. Classified as Internal The learning Curve wasn’t easy...
19. @nabeelt87 linkedin.com/in/nabeelt87
20. Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 Automation Cloud Language Big Query
21. Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 App Engine Kubernetes Engine Network & Security Data Storage (BigQuery) AI & IOT Google Cloud
22. DATA WAREHOUSE
23. Classified as Internal Peta Bytes of Data BigQuery Local Disk
24. @nabeelt87 linkedin.com/in/nabeelt87 INTEGRATION
25. Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 Automation Cloud Language SQL
26. Classified as Internal SQL (Structured Query Language) @nabeelt87 linkedin.com/in/nabeelt87
27. Classified as Internal GSC API + BigQuery
28. GSC BULK DATA EXPORT TO BIGQUERY @nabeelt87 linkedin.com/in/nabeelt87
29. 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
30. 1- Manual process. 2- Data limitations. (1000 records max.) Why Do We need it? @nabeelt87 linkedin.com/in/nabeelt87
31. 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
32. 1- You’ll need property owner rights. 2- You will also need to set up billing. How to Set Up?
33. Settings Bulk Data Export https://trevorfox.com/2023/03/google-search-console-bulk-export-for-bigquery/ How to Set Up?
34. Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87
35. 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
36. Classified as Internal Let’s get started with BigQuery Console.. @nabeelt87 linkedin.com/in/nabeelt87
37. Classified as Internal DataSets
38. Classified as Internal Query Editor DataSets
39. Classified as Internal Query Editor Results DataSets
40. @nabeelt87 linkedin.com/in/nabeelt87
41. Classified as Internal Columns & Data Types
42. @nabeelt87 linkedin.com/in/nabeelt87
43. Classified as Internal https://support.google.com/analytics/answer/7586738#zippy=%2Cin-this-article
44. Classified as Internal SELECT FROM WHERE IN BETWEEN GROUP BY HAVING LIKE CASE What I’ll Cover.
45. Classified as Internal Performing a SQL Query is like Shopping. Analogy @nabeelt87 linkedin.com/in/nabeelt87
46. Classified as Internal Bakery Chocolate Cookies Warm & Fresh Analogy
47. Classified as Internal SELECT Analogy
48. Classified as Internal SELECT FROM Analogy
49. Classified as Internal SELECT FROM WHERE Analogy
50. Classified as Internal COLUMN NAMES TABLE NAME FILTER SELECT FROM WHERE Analogy
51. 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’
52. @nabeelt87 linkedin.com/in/nabeelt87
53. Classified as Internal Multiple Values in a WHERE clause SQL QUERY - IN Organic Paid
54. Classified as Internal Countries SQL QUERY - IN
55. Classified as Internal SELECT geoNetwork.country, DATE, channelGrouping FROM 'bigquery-public- data.google_analytics_sample.ga_sessions_20170801' WHERE geoNetwork.country IN ("Germany", "Poland", "Brazil") SQL QUERY - IN
56. Classified as Internal SQL QUERY - IN
57. 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") SQL QUERY - NOT IN
58. Classified as Internal Find values within a certain range.. SQL QUERY - BETWEEN
59. Classified as Internal Find values within a certain range.. SQL QUERY - BETWEEN Page Views 50 > but <100
60. Classified as Internal SELECT FROM WHERE BETWEEN
61. 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
62. Classified as Internal SQL QUERY - BETWEEN
63. Classified as Internal Avg. Positions Find values within a certain range.. SQL QUERY - BETWEEN
64. Classified as Internal Avg. Positions Clicks Find values within a certain range.. SQL QUERY - BETWEEN
65. Classified as Internal Avg. Positions Clicks Impress. Find values within a certain range.. SQL QUERY - BETWEEN
66. Classified as Internal COUNT SUM MIN MAX AVG What if you wish to aggregate? AGGREGATE FUNCTIONS
67. Classified as Internal Group values into a summary row. Usually used with Aggregate functions. SQL QUERY – GROUP BY
68. 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
69. Classified as Internal SQL QUERY – GROUP BY
70. Classified as Internal CHALLENGE WHERE statement can’t be used with aggregate functions.
71. Classified as Internal CHALLENGE SELECT * FROM table_name WHERE SUM(column_name) > 15
72. Classified as Internal Filter results based on aggregate conditions SUM COUNT AVG/MIN SQL QUERY – HAVING
73. 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
74. Classified as Internal SUM of Page_Views > 100 SQL QUERY – HAVING
75. Classified as Internal Search Volumes Impress./cl icks Linking Domains KW Cannibalization SQL QUERY – HAVING
76. Classified as Internal What if you want to find more specific patterns in your data?
77. Classified as Internal Typical Process GA/GSC FILTER EXCEL CLEAN SORT
78. @nabeelt87 linkedin.com/in/nabeelt87
79. Classified as Internal SQL QUERY - LIKE Find specific patterns.. URL Patterns Granular Keywords Specific Country
80. Classified as Internal SQL QUERY - LIKE percentage underscore % -
81. Classified as Internal SQL QUERY - LIKE percentage underscore % - AND OR
82. Classified as Internal SQL QUERY - LIKE Let’s try some use cases..
83. Classified as Internal SQL QUERY - LIKE URL Patterns
84. Classified as Internal URLs containing /mens/ using wildcard SQL QUERY - LIKE
85. 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/%"
86. Classified as Internal SQL QUERY - LIKE MEN
87. Classified as Internal SQL QUERY - LIKE CCTLD’s
88. Classified as Internal Referrer contains Google.fr using wildcard SQL QUERY - LIKE
89. 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/"
90. Classified as Internal SQL QUERY - LIKE France
91. Classified as Internal WE’VE ONLY USED % WILDCARD SO FAR..
92. Classified as Internal SQL QUERY – WILDCARDS % - [ ] ^ *
93. Classified as Internal n = (m - p + 1) ^ k Possible Combinations SQL QUERY – WILDCARDS
94. @nabeelt87 linkedin.com/in/nabeelt87
95. SQL QUERY - LIKE @nabeelt87 linkedin.com/in/nabeelt87
96. Classified as Internal =IF(C6<5,"True","False")
97. Classified as Internal The CASE statement is SQL's way of handling if/then logic. SQL QUERY - CASE
98. 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
99. 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
100. DATASET Keywords URLs Branded No Brand Folders Countries
101. Classified as Internal There are other use cases too, obviously! SQL QUERY - CASE
102. Classified as Internal SQL QUERY - CASE CASE Aggregate Functions LIKE
103. Classified as Internal SQL QUERY - CASE MEN KIDS
104. Classified as Internal SQL QUERY - CASE CASE LIKE
105. Classified as Internal SQL QUERY - CASE CASE LIKE IF/ ELSE URL pattern
106. 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`,
107. Classified as Internal MEN KIDS
108. Classified as Internal SQL QUERY - CASE MEN KIDS Organic $$
109. Classified as Internal SELECT FROM GROUP CASE WHERE
110. 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
111. Classified as Internal Total Revenue for Each Group
112. Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 We all have been there before..
113. 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
114. 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
115. Classified as Internal DUET AI Enable API
116. Classified as Internal 2 3 1 Enter a prompt 1. Your prompt 2. SQL Query. 3.
117. 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.
118. Classified as Internal More Possibilities.. Backlink Profile Internal Linking Server Logs Google Suggestions Keyword Database
119. Classified as Internal
120. Classified as Internal SELECT FROM WHERE RECAP @nabeelt87 linkedin.com/in/nabeelt87
121. Classified as Internal IN BETWEEN GROUP BY HAVING LIKE CASE RECAP @nabeelt87 linkedin.com/in/nabeelt87
122. Classified as Internal Not Covered DISTINCT WINDOW WITH NESTED- QUERIES @nabeelt87 linkedin.com/in/nabeelt87 IN BETWEEN GROUP BY HAVING LIKE CASE
123. Interested in slides? nabeelt87@gmail.com
124. Classified as Internal THANKS! @nabeelt87 linkedin.com/in/nabeelt87 Nabeel Tanveer