Tweet
Tweet

Slide 1

Slide 1 text

This is BigQuery for SEOs Sam Torres Chief Digital Officer, Gray Dot Co This is BigQuery for SEOs October 17 - 18 2024 CAM Raleigh 409 W. Martin Street Raleigh, NC 27603

Slide 2

Slide 2 text

This is BigQuery for SEOs This is BigQuery for SEOs I’m Sam Torres. Aloha! Official role Chief Digital Officer at Gray Dot Co Unofficial (but important) roles Women in Tech SEO member SEO mermaid™ Day-to-day focus Creating solutions where search, data, and development intersect.

Slide 3

Slide 3 text

This is BigQuery for SEOs GA4’s 14-month data window? Painfully slow loading Looker Studio Reports? GSC’s 16-month data window?

Slide 4

Slide 4 text

This is BigQuery for SEOs Enter… This is BigQuery for SEOs

Slide 5

Slide 5 text

This is BigQuery for SEOs Part 01 - Is BigQuery worth it? Part 02 - How BigQuery Pricing Works We’ll cover… 01 02 03 04 How BigQuery can solve many of the internal data pain points we’ve all experienced. Part 03 - Getting Started Part 04 - Navigating BigQuery This is BigQuery for SEOs 05 Part 05 - Creating Aggregate Tables

Slide 6

Slide 6 text

This is BigQuery for SEOs Part 01 Is BigQuery worth it? This is BigQuery for SEOs

Slide 7

Slide 7 text

This is BigQuery for SEOs First things first, what is it? This is BigQuery for SEOs “BigQuery is a fully managed enterprise data warehouse that helps you manage and analyze your data with built-in features like machine learning, geospatial analysis, and business intelligence. BigQuery’s serverless architecture lets you use SQL queries to answer your organization’s biggest questions with zero infrastructure management. BigQuery’s scalable, distributed analysis engine lets you query terabytes in seconds and petabytes in minutes.”

Slide 8

Slide 8 text

This is BigQuery for SEOs Just kidding, I fixed it… This is BigQuery for SEOs “BigQuery is a fully managed enterprise data warehouse that helps you manage and analyze your data with built-in features like machine learning, geospatial analysis, and business intelligence. BigQuery’s serverless architecture lets you use SQL queries to answer your organization’s biggest questions with zero infrastructure management. BigQuery’s scalable, distributed analysis engine lets you query terabytes in seconds and petabytes in minutes.”

Slide 9

Slide 9 text

This is BigQuery for SEOs With BQ 🤩 Unsampled data 🤩 Data scales as you do 🤩 1 warehouse for many sources 🤩 Lower costs & budget alerts 🤩 Big requests in seconds 🤩 Access data back to day 1 Before BQ 😭 Platforms report sampled data 😭 Data setup isn’t scalable 😭 Data is in many places 😭 High data-storage costs 😭 Big requests break or are slow 😭 Historical data is limited Benefits of Using BigQuery This is BigQuery for SEOs

Slide 10

Slide 10 text

This is BigQuery for SEOs Why doesn’t everyone use it? This is BigQuery for SEOs

Slide 11

Slide 11 text

This is BigQuery for SEOs Part 02 How BigQuery Pricing Works This is BigQuery for SEOs

Slide 12

Slide 12 text

This is BigQuery for SEOs Computation 💸 How much handling of that data is BQ doing 💸 Free tier gets you 1Tb of computation or processing Storage 💸 How much data contained within BQ 💸 Free tier gets you 10 GB of data storage BigQuery Pricing Structure This is BigQuery for SEOs

Slide 13

Slide 13 text

This is BigQuery for SEOs Sneaky build of compute costs This is BigQuery for SEOs This single branded impression chart is filtering for specific words across queries, grouping according to month and summing impressions.

Slide 14

Slide 14 text

This is BigQuery for SEOs 1. Create a budget This is BigQuery for SEOs Creating Budget Alerts It can be for one or more projects, or the whole cloud account (which is the default).

Slide 15

Slide 15 text

This is BigQuery for SEOs 2. Specify a budget This is BigQuery for SEOs Creating Budget Alerts Note that you can set this for specific organization, projects or even services (BigQuery is a service).

Slide 16

Slide 16 text

This is BigQuery for SEOs 3. Create alert thresholds This is BigQuery for SEOs Creating Budget Alerts Need to send alerts to different emails than the project owner? You can do that here.

Slide 17

Slide 17 text

This is BigQuery for SEOs Sample Budget Ideas This is BigQuery for SEOs Set cost thresholds: ● Last month’s spend ● ANY spending (my personal favorite!) Limit to specific services: ● BigQuery Storage ● BigQuery Data Transfer Service: Responsible for the scheduled queries

Slide 18

Slide 18 text

This is BigQuery for SEOs Part 03 Getting Started (& Connecting to GA4 & GSC) This is BigQuery for SEOs

Slide 19

Slide 19 text

This is BigQuery for SEOs Create a billing account in Google Cloud This is BigQuery for SEOs Go to console.cloud.google.com and select Billing

Slide 20

Slide 20 text

This is BigQuery for SEOs 1. Create a project This is BigQuery for SEOs Connecting to GSC

Slide 21

Slide 21 text

This is BigQuery for SEOs 2. Enable the BigQuery API This is BigQuery for SEOs Connecting to GSC Can’t find this page? Go to > APIs & Services and search for “BigQuery”

Slide 22

Slide 22 text

This is BigQuery for SEOs 3. Set Up Permissions This is BigQuery for SEOs Connecting to GSC

Slide 23

Slide 23 text

This is BigQuery for SEOs 3. Set Up Permissions This is BigQuery for SEOs Connecting to GSC

Slide 24

Slide 24 text

This is BigQuery for SEOs 3. Set Up Permissions This is BigQuery for SEOs Connecting to GSC

Slide 25

Slide 25 text

This is BigQuery for SEOs 4. GSC Bulk Export This is BigQuery for SEOs Connecting to GSC

Slide 26

Slide 26 text

This is BigQuery for SEOs 5. Add your project ID This is BigQuery for SEOs Connecting to GSC Find your ID in the Project Overview page: > Cloud Overview

Slide 27

Slide 27 text

This is BigQuery for SEOs 6. Set the name & location This is BigQuery for SEOs Connecting to GSC Useful if you’re connecting multiple properties into a single project Set the country that your data governance is based in

Slide 28

Slide 28 text

This is BigQuery for SEOs 7. Wait for it… This is BigQuery for SEOs Connecting to GSC 24-48 hours

Slide 29

Slide 29 text

This is BigQuery for SEOs 8. Ta da! This is BigQuery for SEOs Connecting to GSC

Slide 30

Slide 30 text

This is BigQuery for SEOs 1. Create a project This is BigQuery for SEOs Connecting to GA4 Or you can use the same project. Consider data storage size and separate budget alerts/ ceiling needs.

Slide 31

Slide 31 text

This is BigQuery for SEOs 2. Link GA4 Property This is BigQuery for SEOs Connecting to GA4

Slide 32

Slide 32 text

This is BigQuery for SEOs 3. Choose location This is BigQuery for SEOs Connecting to GA4 Selecting the right location is extremely important!

Slide 33

Slide 33 text

This is BigQuery for SEOs 4. Configure streams & events This is BigQuery for SEOs Connecting to GA4 Limit is 1M events per day. Take what you need.

Slide 34

Slide 34 text

This is BigQuery for SEOs 4. Configure streams & events This is BigQuery for SEOs Connecting to GA4

Slide 35

Slide 35 text

This is BigQuery for SEOs 5. Wait for it… This is BigQuery for SEOs Connecting to GA4 24-48 hours

Slide 36

Slide 36 text

This is BigQuery for SEOs 6. Ta da! This is BigQuery for SEOs Connecting to GA4

Slide 37

Slide 37 text

This is BigQuery for SEOs So I’m ready for Looker Studio reporting and all the fun, right?! This is BigQuery for SEOs

Slide 38

Slide 38 text

This is BigQuery for SEOs Part 04 Navigating BigQuery This is BigQuery for SEOs

Slide 39

Slide 39 text

This is BigQuery for SEOs Individual URL Data Includes page-level performance Where SERP Feature data lives Site Data Best for seeing total site performance Ideal for testing GSC data includes… This is BigQuery for SEOs

Slide 40

Slide 40 text

This is BigQuery for SEOs In BigQuery, that looks like… This is BigQuery for SEOs

Slide 41

Slide 41 text

This is BigQuery for SEOs GA4 data includes… This is BigQuery for SEOs Table PER day Nested columns (the details that apply to specific types of data)

Slide 42

Slide 42 text

This is BigQuery for SEOs In BigQuery, that looks like… This is BigQuery for SEOs

Slide 43

Slide 43 text

This is BigQuery for SEOs Power of Table Schema This is BigQuery for SEOs

Slide 44

Slide 44 text

This is BigQuery for SEOs Ok but now I’m swimming in tables. This is BigQuery for SEOs

Slide 45

Slide 45 text

This is BigQuery for SEOs Enter… This is BigQuery for SEOs

Slide 46

Slide 46 text

This is BigQuery for SEOs Use SQL queries to get your data and do things with it This is BigQuery for SEOs

Slide 47

Slide 47 text

This is BigQuery for SEOs Basics of Query Structure This is BigQuery for SEOs SELECT , FROM WHERE =’Value’ ORDER BY Retrieve data from tables or expressions. Which table(s) should the data be retrieved from? Which rows should be retrieved? Which columns should dictate sorting, in which direction?

Slide 48

Slide 48 text

This is BigQuery for SEOs Queries can be…simple This is BigQuery for SEOs SELECT * FROM `table.searchconsole.searchdata_ site_impression` WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AND country = 'usa' AND query IS NOT NULL

Slide 49

Slide 49 text

This is BigQuery for SEOs Queries can be…complex This is BigQuery for SEOs SELECT CASE WHEN session_source IS NULL THEN 'Direct' WHEN REGEXP_CONTAINS(session_campaign_name, 'cross-network') THEN 'Cross-network' WHEN ( REGEXP_CONTAINS( session_source, 'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart' ) OR REGEXP_CONTAINS( session_campaign_name, '^(.*(([^a-df-z]|^)shop|shopping).*)$' ) ) AND REGEXP_CONTAINS(session_medium, '^(.*cp.*|ppc|paid.*)$') THEN 'Paid Shopping' WHEN REGEXP_CONTAINS( session_source, 'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex' ) AND REGEXP_CONTAINS(session_medium, '^(.*cp.*|ppc|paid.*)$') THEN 'Paid Search' WHEN REGEXP_CONTAINS( session_source, 'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp' ) AND REGEXP_CONTAINS(session_medium, '^(.*cp.*|ppc|paid.*)$') THEN 'Paid Social' WHEN REGEXP_CONTAINS( session_source, 'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube' ) AND REGEXP_CONTAINS(session_medium, '^(.*cp.*|ppc|paid.*)$') THEN 'Paid Video' WHEN session_medium IN ( 'display', 'banner', 'expandable', 'interstitial', 'cpm' ) THEN 'Display' WHEN REGEXP_CONTAINS( session_source, 'alibaba|amazon|google shopping|shopify|etsy|ebay|stripe|walmart' ) OR REGEXP_CONTAINS( session_campaign_name, '^(.*(([^a-df-z]|^)shop|shopping).*)$' ) THEN 'Organic Shopping' WHEN REGEXP_CONTAINS( session_source, 'badoo|facebook|fb|instagram|linkedin|pinterest|tiktok|twitter|whatsapp' ) OR session_medium IN ( 'social', 'social-network', 'social-media', 'sm', 'social network', 'social media' ) THEN 'Organic Social' WHEN REGEXP_CONTAINS( session_source, 'dailymotion|disneyplus|netflix|youtube|vimeo|twitch|vimeo|youtube' ) OR REGEXP_CONTAINS(session_medium, '^(.*video.*)$') THEN 'Organic Video' WHEN REGEXP_CONTAINS( session_source, 'baidu|bing|duckduckgo|ecosia|google|yahoo|yandex' ) OR session_medium = 'organic' THEN 'Organic Search' WHEN REGEXP_CONTAINS(session_source, 'email|e-mail|e_mail|e mail') OR REGEXP_CONTAINS(session_medium, 'email|e-mail|e_mail|e mail') THEN 'Email' WHEN session_medium = 'affiliate' THEN 'Affiliates' WHEN session_medium = 'referral' THEN 'Referral' WHEN session_medium = 'audio' THEN 'Audio' WHEN session_medium = 'sms' THEN 'SMS' WHEN session_medium LIKE '%push' OR REGEXP_CONTAINS(session_medium, 'mobile|notification') THEN 'Mobile Push Notifications' ELSE 'Unassigned' END AS session_channel, DATE, device_category, COUNT(DISTINCT session_id) AS sessions, COUNT(DISTINCT new_users) AS new_users, COUNT(DISTINCT user_pseudo_id) AS total_users, SUM(purchases) AS purchases, SUM(revenue) AS revenue, COUNT( DISTINCT CASE WHEN new_users IS NOT NULL AND total_purchasers IS NOT NULL THEN user_pseudo_id ELSE NULL END ) AS first_time_purchasers, COUNT(DISTINCT total_purchasers) AS total_purchasers

Slide 50

Slide 50 text

This is BigQuery for SEOs So make a tool do it for you This is BigQuery for SEOs I generally use GA4SQL but that’s just one example.

Slide 51

Slide 51 text

This is BigQuery for SEOs And you get… This is BigQuery for SEOs

Slide 52

Slide 52 text

This is BigQuery for SEOs Part 05 Aggregated Tables This is BigQuery for SEOs

Slide 53

Slide 53 text

This is BigQuery for SEOs The Process Create tables and a job to update daily Use your measurement plan Connect these new tables to Looker Studio This is BigQuery for SEOs

Slide 54

Slide 54 text

This is BigQuery for SEOs 1. Decide what data you want This is BigQuery for SEOs Creating Aggregate Tables GSC - Queries & metrics aggregated by: ● Page ● Device Type ● Country GA4 - Event data aggregated by: ● Entry page ● Page path ● Channel grouping Start with only a few. You can always add more later - and the query will go through your entire set of data (meaning you can aggregate retroactively!)

Slide 55

Slide 55 text

This is BigQuery for SEOs 2. Run the SQL Query in Explorer This is BigQuery for SEOs Creating Aggregate Tables Here, you can see the compute cost. If you’re testing, try adding a limit to your query until you’re happy with the data you’re getting. Then, open the query (and number of results). LIMIT 1000

Slide 56

Slide 56 text

This is BigQuery for SEOs 2. Run the SQL Query in Explorer This is BigQuery for SEOs Creating Aggregate Tables

Slide 57

Slide 57 text

This is BigQuery for SEOs 2. Save the results to a new table This is BigQuery for SEOs Creating Aggregate Tables

Slide 58

Slide 58 text

This is BigQuery for SEOs 3. Save the results to a new table This is BigQuery for SEOs Creating Aggregate Tables

Slide 59

Slide 59 text

This is BigQuery for SEOs 4. Duplicate your query and adjust to yesterday This is BigQuery for SEOs Creating Aggregate Tables

Slide 60

Slide 60 text

This is BigQuery for SEOs 5. Schedule this new query This is BigQuery for SEOs Creating Aggregate Tables

Slide 61

Slide 61 text

This is BigQuery for SEOs 5. Schedule this new query This is BigQuery for SEOs Creating Aggregate Tables

Slide 62

Slide 62 text

This is BigQuery for SEOs 6. Use your new tables for Looker Studio This is BigQuery for SEOs Creating Aggregate Tables ● Don’t forget that you can blend tables in Looker Studio too! ● Why does this save on processing & time? ○ Calculations happen in BQ ○ Larger grouping calculations only happen once a day

Slide 63

Slide 63 text

This is BigQuery for SEOs Thank you! Have more questions? /in/samantha-torres-seo @SamTorresATL