Learn SQL for SEO

Course Outline

Course Access

Sign up for Free

Sign up for email updates when free and premium content is released.

Register
Get Premium Content

Purchase the course on Stripe to access all the premium content.

Get Access
Access Premium Content

Once you’ve checked out on Stripe, sign in to access premium content.

Sign In

Course Contents

Welcome to the course!

This 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. But first, you should check out the prerequisites.

Prerequisites

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
  • Request access to demo data that you can load into your BigQuery instance.

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.

Intro to GSC Data 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

Intro to Data Warehousing

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.

Content

GSC Performance Metrics Deep Dive

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

You’ll learn: 

  • 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

SEO Analytics SQL (in progress!)

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: 

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

Content

Advanced Applications for GSC 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