SEO Analytics in BigQuery Course

The online course has four sections: an introduction, a brief overview of data warehousing and data integration, the heart of the course—SEO analytics in SQL—and finally, some advanced topics like data visualization and data integration.

Course Navigation

Register for
the Free Course

Sign up for email updates every time course content is released.

Get access to
Premium Content

Checkout on Stripe to get access to all the premium course content.

Sign in to access
Premium Content

Once you’ve checked out on Stripe, sign in to view all the premium content.

Prerequisites

  1. You’ll need access to a BigQuery account with at least a week’s worth of Google Search Console data loaded in.
    • If you don’t have access to GSC data in BigQuery, you have two options:
      • Setup the integration by following this setup tutorial
      • Follow these instructions to upload some demo data into your BigQuery instance.
  2. Optional but recommended: a basic understanding of SQL. While this course will start very basic, the course is designed for practitioners with intermediate SQL skills. 

Course Outline 

Part 1: Introduction to Google Search Console Analytics in BigQuery

We’ll dive right into BigQuery and explore the three Google Search Console tables from the native integration. 

You’ll learn: 

  • An overview of BigQuery, its applications, and the BigQuery Console
  • An introduction to the three tables created and populated by the Google Search Console BigQuery integration
  • A brief demonstration of SQL analysis for SEO

Content

Part 2: Intro to Data Warehousing & Performance Metrics Deep Dive

We’ll zoom out and take a high-level look at data warehousing. These concepts will be demonstrated through a fictional scenario: how Google could collect, store, and model its search data.

You’ll learn: 

  • Data Collection: Understand how online searches generate massive amounts of data, from search queries to metadata about users, sessions, and devices, and how this data is captured by systems like Google Search Console and stored in BigQuery.
  • Data Modeling: Dive into data modeling concepts, including the basics of Star Schemas, which optimize data storage and retrieval for analytical purposes.
  • ETL vs. ELT: Explore the differences between traditional ETL (Extract, Transform, Load) and modern ELT (Extract, Load, Transform) processes and how cloud data warehouses like BigQuery revolutionize data handling.
  • Flattened Tables and Views: Learn about creating flattened tables and views to simplify analysis, making accessing and aggregating data without complex joins easier.
  • Data Governance and OLAP Cubes: Discover the importance of data governance and the role of OLAP cubes in multidimensional analysis, allowing for filtering and aggregation across multiple dimensions.
  • Practical Applications: Apply these concepts in real-world scenarios, whether in e-commerce, SaaS, or other industries, and learn how to set up efficient data pipelines for better business insights.
Premium topics
  • Overview of Search Metrics: A framework for understanding SEO metrics and levers
  • Defining Impressions and Position: Deep dive into impressions, positions, and clicks on real search pages
  • Site Impressions table vs URL impressions table: Let’s build them in SQL to understand the differences

Content

Part 3: SEO Analytics SQL (coming soon!)

This is the most significant part of the course, with a light overview of SQL syntax, some BigQuery-specific considerations for controlling costs, and several SQL patterns for SEO data.

You’ll learn: 

  • Best practices for keeping BigQuery costs under control when analyzing data
  • Common statistics and aggregation for SEO data
  • Advanced SQL patterns for specific use cases like performance trends, CTR curves, diagnostics, and opportunity discovery.

Content

Part 4: Advanced Applications for Google Search Console Data (coming soon!)

This section is all about applying the insights you’ve gained from analyzing your search data. We’ll discuss how to communicate this information to have more impact and increase your SEO efforts’ visibility.

You’ll learn about: 

  • Sharing your SEO performance by visualizing it in dashboards 
  • Communicating about your learnings to create a broader impact
  • Integrating the data with other systems to automate alerts and optimizations
CONTACT
CONTACT