Scale up SEO with Google BigQuery

Slide 1

Slide 1 text

Classified as Internal Scaling up your SEO with Google BigQuery Nabeel Tanveer Global SEO Manager Electrolux Group @nabeelt87 linkedin.com/in/nabeelt87

Slide 2

Slide 2 text

Classified as Internal How I transitioned from Excel to BigQuery #BrightonSEO @nabeelt87 linkedin.com/in/nabeelt87

Slide 3

Slide 3 text

Mostly working with small to medium size sites. AGENCY-SIDE @nabeelt87 linkedin.com/in/nabeelt87

Slide 4

Slide 4 text

@nabeelt87 linkedin.com/in/nabeelt87

Slide 5

Slide 5 text

AGENCY CLIENT-SIDE @nabeelt87 linkedin.com/in/nabeelt87

Slide 6

Slide 6 text

6 Any guess? @nabeelt87 linkedin.com/in/nabeelt87

Slide 7

Slide 7 text

No content

Slide 8

Slide 8 text

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

Slide 9

Slide 9 text

Classified as Internal Small site Enterprise Site @nabeelt87 linkedin.com/in/nabeelt87

Slide 10

Slide 10 text

@nabeelt87 linkedin.com/in/nabeelt87

Slide 11

Slide 11 text

@nabeelt87 linkedin.com/in/nabeelt87

Slide 12

Slide 12 text

@nabeelt87 linkedin.com/in/nabeelt87

Slide 13

Slide 13 text

Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 Automation Cloud Language Python

Slide 14

Slide 14 text

@nabeelt87 linkedin.com/in/nabeelt87 True Story

Slide 15

Slide 15 text

Classified as Internal

Slide 16

Slide 16 text

Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 Automation Cloud Language Big Query

Slide 17

Slide 17 text

Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 Automation Cloud Language Big Query SQL

Slide 18

Slide 18 text

Classified as Internal The learning Curve wasn’t easy...

Slide 19

Slide 19 text

@nabeelt87 linkedin.com/in/nabeelt87

Slide 20

Slide 20 text

Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 Automation Cloud Language Big Query

Slide 21

Slide 21 text

Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 App Engine Kubernetes Engine Network & Security Data Storage (BigQuery) AI & IOT Google Cloud

Slide 22

Slide 22 text

DATA WAREHOUSE

Slide 23

Slide 23 text

Classified as Internal Peta Bytes of Data BigQuery Local Disk

Slide 24

Slide 24 text

@nabeelt87 linkedin.com/in/nabeelt87 INTEGRATION

Slide 25

Slide 25 text

Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 Automation Cloud Language SQL

Slide 26

Slide 26 text

Classified as Internal SQL (Structured Query Language) @nabeelt87 linkedin.com/in/nabeelt87

Slide 27

Slide 27 text

Classified as Internal GSC API + BigQuery

Slide 28

Slide 28 text

GSC BULK DATA EXPORT TO BIGQUERY @nabeelt87 linkedin.com/in/nabeelt87

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

1- Manual process. 2- Data limitations. (1000 records max.) Why Do We need it? @nabeelt87 linkedin.com/in/nabeelt87

Slide 31

Slide 31 text

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

Slide 32

Slide 32 text

1- You’ll need property owner rights. 2- You will also need to set up billing. How to Set Up?

Slide 33

Slide 33 text

Settings Bulk Data Export https://trevorfox.com/2023/03/google-search-console-bulk-export-for-bigquery/ How to Set Up?

Slide 34

Slide 34 text

Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

Classified as Internal Let’s get started with BigQuery Console.. @nabeelt87 linkedin.com/in/nabeelt87

Slide 37

Slide 37 text

Classified as Internal DataSets

Slide 38

Slide 38 text

Classified as Internal Query Editor DataSets

Slide 39

Slide 39 text

Classified as Internal Query Editor Results DataSets

Slide 40

Slide 40 text

@nabeelt87 linkedin.com/in/nabeelt87

Slide 41

Slide 41 text

Classified as Internal Columns & Data Types

Slide 42

Slide 42 text

@nabeelt87 linkedin.com/in/nabeelt87

Slide 43

Slide 43 text

Classified as Internal https://support.google.com/analytics/answer/7586738#zippy=%2Cin-this-article

Slide 44

Slide 44 text

Classified as Internal SELECT FROM WHERE IN BETWEEN GROUP BY HAVING LIKE CASE What I’ll Cover.

Slide 45

Slide 45 text

Classified as Internal Performing a SQL Query is like Shopping. Analogy @nabeelt87 linkedin.com/in/nabeelt87

Slide 46

Slide 46 text

Classified as Internal Bakery Chocolate Cookies Warm & Fresh Analogy

Slide 47

Slide 47 text

Classified as Internal SELECT Analogy

Slide 48

Slide 48 text

Classified as Internal SELECT FROM Analogy

Slide 49

Slide 49 text

Classified as Internal SELECT FROM WHERE Analogy

Slide 50

Slide 50 text

Classified as Internal COLUMN NAMES TABLE NAME FILTER SELECT FROM WHERE Analogy

Slide 51

Slide 51 text

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’

Slide 52

Slide 52 text

@nabeelt87 linkedin.com/in/nabeelt87

Slide 53

Slide 53 text

Classified as Internal Multiple Values in a WHERE clause SQL QUERY - IN Organic Paid

Slide 54

Slide 54 text

Classified as Internal Countries SQL QUERY - IN

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

Classified as Internal SQL QUERY - IN

Slide 57

Slide 57 text

Slide 58

Slide 58 text

Classified as Internal Find values within a certain range.. SQL QUERY - BETWEEN

Slide 59

Slide 59 text

Classified as Internal Find values within a certain range.. SQL QUERY - BETWEEN Page Views 50 > but <100

Slide 60

Slide 60 text

Classified as Internal SELECT FROM WHERE BETWEEN

Slide 61

Slide 61 text

Slide 62

Slide 62 text

Classified as Internal SQL QUERY - BETWEEN

Slide 63

Slide 63 text

Classified as Internal Avg. Positions Find values within a certain range.. SQL QUERY - BETWEEN

Slide 64

Slide 64 text

Classified as Internal Avg. Positions Clicks Find values within a certain range.. SQL QUERY - BETWEEN

Slide 65

Slide 65 text

Classified as Internal Avg. Positions Clicks Impress. Find values within a certain range.. SQL QUERY - BETWEEN

Slide 66

Slide 66 text

Classified as Internal COUNT SUM MIN MAX AVG What if you wish to aggregate? AGGREGATE FUNCTIONS

Slide 67

Slide 67 text

Classified as Internal Group values into a summary row. Usually used with Aggregate functions. SQL QUERY – GROUP BY

Slide 68

Slide 68 text

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

Slide 69

Slide 69 text

Classified as Internal SQL QUERY – GROUP BY

Slide 70

Slide 70 text

Classified as Internal CHALLENGE WHERE statement can’t be used with aggregate functions.

Slide 71

Slide 71 text

Classified as Internal CHALLENGE SELECT * FROM table_name WHERE SUM(column_name) > 15

Slide 72

Slide 72 text

Classified as Internal Filter results based on aggregate conditions SUM COUNT AVG/MIN SQL QUERY – HAVING

Slide 73

Slide 73 text

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

Slide 74

Slide 74 text

Classified as Internal SUM of Page_Views > 100 SQL QUERY – HAVING

Slide 75

Slide 75 text

Classified as Internal Search Volumes Impress./cl icks Linking Domains KW Cannibalization SQL QUERY – HAVING

Slide 76

Slide 76 text

Classified as Internal What if you want to find more specific patterns in your data?

Slide 77

Slide 77 text

Classified as Internal Typical Process GA/GSC FILTER EXCEL CLEAN SORT

Slide 78

Slide 78 text

@nabeelt87 linkedin.com/in/nabeelt87

Slide 79

Slide 79 text

Classified as Internal SQL QUERY - LIKE Find specific patterns.. URL Patterns Granular Keywords Specific Country

Slide 80

Slide 80 text

Classified as Internal SQL QUERY - LIKE percentage underscore % -

Slide 81

Slide 81 text

Classified as Internal SQL QUERY - LIKE percentage underscore % - AND OR

Slide 82

Slide 82 text

Classified as Internal SQL QUERY - LIKE Let’s try some use cases..

Slide 83

Slide 83 text

Classified as Internal SQL QUERY - LIKE URL Patterns

Slide 84

Slide 84 text

Classified as Internal URLs containing /mens/ using wildcard SQL QUERY - LIKE

Slide 85

Slide 85 text

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/%"

Slide 86

Slide 86 text

Classified as Internal SQL QUERY - LIKE MEN

Slide 87

Slide 87 text

Classified as Internal SQL QUERY - LIKE CCTLD’s

Slide 88

Slide 88 text

Classified as Internal Referrer contains Google.fr using wildcard SQL QUERY - LIKE

Slide 89

Slide 89 text

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/"

Slide 90

Slide 90 text

Classified as Internal SQL QUERY - LIKE France

Slide 91

Slide 91 text

Classified as Internal WE’VE ONLY USED % WILDCARD SO FAR..

Slide 92

Slide 92 text

Classified as Internal SQL QUERY – WILDCARDS % - [ ] ^ *

Slide 93

Slide 93 text

Classified as Internal n = (m - p + 1) ^ k Possible Combinations SQL QUERY – WILDCARDS

Slide 94

Slide 94 text

@nabeelt87 linkedin.com/in/nabeelt87

Slide 95

Slide 95 text

SQL QUERY - LIKE @nabeelt87 linkedin.com/in/nabeelt87

Slide 96

Slide 96 text

Classified as Internal =IF(C6<5,"True","False")

Slide 97

Slide 97 text

Classified as Internal The CASE statement is SQL's way of handling if/then logic. SQL QUERY - CASE

Slide 98

Slide 98 text

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

Slide 99

Slide 99 text

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

Slide 100

Slide 100 text

DATASET Keywords URLs Branded No Brand Folders Countries

Slide 101

Slide 101 text

Classified as Internal There are other use cases too, obviously! SQL QUERY - CASE

Slide 102

Slide 102 text

Classified as Internal SQL QUERY - CASE CASE Aggregate Functions LIKE

Slide 103

Slide 103 text

Classified as Internal SQL QUERY - CASE MEN KIDS

Slide 104

Slide 104 text

Classified as Internal SQL QUERY - CASE CASE LIKE

Slide 105

Slide 105 text

Classified as Internal SQL QUERY - CASE CASE LIKE IF/ ELSE URL pattern

Slide 106

Slide 106 text

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`,

Slide 107

Slide 107 text

Classified as Internal MEN KIDS

Slide 108

Slide 108 text

Classified as Internal SQL QUERY - CASE MEN KIDS Organic $$

Slide 109

Slide 109 text

Classified as Internal SELECT FROM GROUP CASE WHERE

Slide 110

Slide 110 text

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

Slide 111

Slide 111 text

Classified as Internal Total Revenue for Each Group

Slide 112

Slide 112 text

Classified as Internal @nabeelt87 linkedin.com/in/nabeelt87 We all have been there before..

Slide 113

Slide 113 text

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

Slide 114

Slide 114 text

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

Slide 115

Slide 115 text

Classified as Internal DUET AI Enable API

Slide 116

Slide 116 text

Classified as Internal 2 3 1 Enter a prompt 1. Your prompt 2. SQL Query. 3.

Slide 117

Slide 117 text

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.

Slide 118

Slide 118 text

Classified as Internal More Possibilities.. Backlink Profile Internal Linking Server Logs Google Suggestions Keyword Database

Slide 119

Slide 119 text

Classified as Internal

Slide 120

Slide 120 text

Classified as Internal SELECT FROM WHERE RECAP @nabeelt87 linkedin.com/in/nabeelt87

Slide 121

Slide 121 text

Classified as Internal IN BETWEEN GROUP BY HAVING LIKE CASE RECAP @nabeelt87 linkedin.com/in/nabeelt87

Slide 122

Slide 122 text

Classified as Internal Not Covered DISTINCT WINDOW WITH NESTED- QUERIES @nabeelt87 linkedin.com/in/nabeelt87 IN BETWEEN GROUP BY HAVING LIKE CASE

Slide 123

Slide 123 text

Interested in slides? [email protected]

Slide 124

Slide 124 text

Classified as Internal THANKS! @nabeelt87 linkedin.com/in/nabeelt87 Nabeel Tanveer