Part 4: SEO Analytics SQL
« Learn SQL for SEO · Part 4 of 4
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 grouping for long tail analysis, and opportunity discovery
- Best practices for keeping BigQuery costs under control when analyzing data
Blog posts
- Basic analyses: Recreating Google's Monthly Performance Reports in SQL
- Advanced analysis: Long-tail query Analysis with a Levenshtein Distance Custom Function
SQL patterns
- Pattern #1: Aggregating and analyzing GSC position metrics
- Pattern #2: Exploring performance over time
- Pattern #3: Grouping and filtering URLs for content analysis
Tools
GSC BigQuery SQL Writer (custom GPT)
This GSC BigQuery SQL Writer generates and explains SQL queries for analyzing Google Search Console data stored in BigQuery.
- It knows the schema and metadata about searchdata_site_impression, searchdata_url_impression, and ExportLog tables.
- It optimizes queries for performance and only provides SELECT statements based on recent data.
- It's a great way to explain the sample SQL queries in this course!
When you're diving into your Google Search Console data in BigQuery, you've probably wondered what all those columns actually mean. It's much easier to have that info handy right there, instead of constantly flipping between your analysis and the documentation.
I ran into a similar issue recently, pitting Google's new Gemini AI in BigQuery against my own custom GPT model to see how they'd handle a SQL query challenge.
What was interesting was that both AI churned out working SQL, but they came up with different answers. It really shows how even tiny changes in how you ask a question can send these things off in different directions.
And the kicker? They were both wrong at first! Turns out, I was the one who misunderstood the data.
Gemini seemed to guess what I meant to ask, even if it wasn't what I actually typed. My custom GPT, on the other hand, stuck closer to my exact wording, and it was also better at avoiding some of the data's quirks.
« Part 3: GSC Performance Metrics Deep Dive · Course index »