Learn SQL for SEO: GSC Analytics in BigQuery

Are you an SEO, digital marketer, or analyst that wants to take your technical skills (and your career) to the next level? This course is for you — and it's completely free. Every lesson, video, code sample, and the custom GPT that used to be premium content is here.

Get a jump on the newest trend in SEO analytics! When Google launched the BigQuery integration for Google Search Console, it became possible to analyze your website's search data with unprecedented scale and sophistication. Take this course, and you will learn how to use BigQuery to infinitely scale your analytical impact.

What you'll learn

BigQuery for data analysis

You'll learn how to connect your Google Search Console data to BigQuery even if you've never used BigQuery before.

Analytical patterns for SEO

You'll identify search trends, diagnose issues, and find opportunities to increase search traffic in ways you never thought possible.

SQL for web analytics

You'll develop a tool kit of SQL patterns that you can use on any web data set that includes keywords and URLs.

BigQuery cost control

You'll learn the secrets to minimizing your BigQuery data storage and processing costs and stay below the Free tier.

Google Search Console data and reports

You'll become familiar with the intricacies of how Google Search Console defines and reports search metrics.

Visualizing search data

You'll learn how to connect Google Search Console data in BigQuery to Looker Data Studio to create dynamic reports.

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 GSC data in BigQuery yet, set up the integration by following this setup tutorial.

Optional but recommended: a basic understanding of SQL. While this course starts very basic, it is designed for practitioners with intermediate SQL skills.

The course

Part 1: Intro to GSC Data in BigQuery

We'll dive right into BigQuery and explore the three Google Search Console tables from the native integration — plus SQL to bulk-add column descriptions to the GSC tables so the documentation lives right in your console.

Part 2: 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.

Part 3: GSC Performance Metrics Deep Dive

A framework for understanding SEO metrics and levers, a deep dive into impressions, positions, and clicks on real search pages, and the two GSC reporting tables rebuilt in SQL to understand their differences.

Part 4: SEO Analytics SQL

The most significant part of the course: SQL syntax, BigQuery cost control, SQL patterns for SEO data — and the GSC BigQuery SQL Writer, a custom GPT that generates and explains queries against your GSC tables.

About the instructor

I've spent over a decade in digital analytics and I've run thousands of SQL queries and spent the last segment of my career in the data warehouse industry. There's no person better to teach a course on SQL for SEO.

Questions about the course? Email me or connect on LinkedIn.