At the beginning of every month, Google Search Console sends out its Monthly Search Performance email to site owners. Each month I receive this email, I’m reminded of two things: 1) My site’s performance doesn’t really change much, and 2) These statistics are a great way to demonstrate how to write SQL for the Google Search Console BigQuery integration.
The nice thing about these reports is that the SQL is pretty simple and easy for beginners to grasp. Every one of the statistics is a simple aggregation over the past month’s data. If you’re new to SQL, offer a template that you can modify to start analyzing your data in different ways.
Here is a preview video of the of that course. It’s just the tip of the iceberg.
In this post, I’ll provide SQL queries to generate all the statistics in the monthly emails and explain each. I’ll go into detail for the first query to explain the SQL fundamentals that will apply to the rest of the queries.
Performance overview
This section provides a high-level view of your site’s search performance, especially for content that has just started gaining traction.
There are three statistics that are easily derived from two different queries:
- Total clicks and impressions for last month
- The URLs with the first impressions
Total clicks and impressions for last month
The email starts with high-level performance statistics: total impressions and clicks for the month and the number of new pages that received their first impressions in search. Summing impressions and clicks is quite simple. The first impressions part is a bit more complex, so we’ll get to that later.
Query: Summing monthly impressions and clicks
-- This line is a comment. They are always start with two dashes (--)
SELECT
SUM(clicks) AS total_clicks,
SUM(impressions) AS total_impressions
FROM `searchconsole.searchdata_site_impression`
WHERE
site_url = 'sc-domain:trevorfox.com' -- Replace this with your GSC domain
AND search_type = 'WEB'
AND EXTRACT(MONTH FROM data_date) = 8
AND EXTRACT(YEAR FROM data_date) = 2024
How to read this query
Every analytical query must have two parts: the FROM clause, which specifies the table from which the data is coming, and the SELECT clause, which selects the columns from that table.
The WHERE clause is optional and acts as a filter.
Those three clauses must always happen in that order: SELECT, FROM, WHERE.
But it can be helpful to read a SQL query in this order: FROM, WHERE, SELECT.
In this case, we’d read the query like this:
FROM searchdata_site_impression, I want to filter in all rows WHERE the data matches the following conditions: the site_url exactly matches ‘sc-domain:trevorfox.com’, the search_type exactly matches ‘WEB’, the month is 8 (August), and the year is 2024. Then, from that data, I want to SELECT the sum of clicks and impressions.
The URLs with the first impressions
The URLs with the first impressions query is a little more complex, so I’ll put it here. But if you are new to SQL, I encourage you to skip ahead to the next query explanation.
SELECT
url,
SUM(CASE WHEN data_date < '2024-08-01' THEN impressions END) AS before_august_impressions,
SUM(CASE WHEN DATE_TRUNC(data_date, month) = '2024-08-01' THEN impressions END) AS august_impressions
FROM
`searchconsole.searchdata_url_impression`
WHERE
site_url = 'sc-domain:trevorfox.com'
AND search_type = 'WEB'
AND EXTRACT(MONTH FROM data_date) = 8
AND EXTRACT(YEAR FROM data_date) = 2024
GROUP BY url
HAVING before_august_impressions is null
AND august_impressions > 0
Your content achievements
This section highlights your content’s performance, showing your top three pages by clicks and growth in clicks. You can recreate these stats with two queries:
- Top performing pages last month
- Top growing pages last month
Top performing pages last month
The next section is similar to the last query, but it introduces the GROUP BY clause. Grouping in SQL is a lot like a spreadsheet pivot table. It allows you to aggregate statistics within groups based on the values of a different column, in this case, the URL column.
You could read this query like this.
FROM searchdata_site_impression, I want to filter in all rows WHERE the data matches the following conditions: the site_url exactly matches ‘sc-domain:trevorfox.com’, the search_type exactly matches ‘WEB’, and the month is 8 (August) and the year is 2024. Then from that data I want to SELECT the sum of clicks and the sum of impressions GROUPED BY URL.
Finally, I want just the top three URLs, so ORDER (the resulting rows) BY total clicks and LIMIT the results to just three rows.
SELECT
url,
SUM(clicks) AS total_clicks
FROM `mvp-data-321618.searchconsole.searchdata_url_impression`
WHERE
site_url = 'sc-domain:trevorfox.com'
AND search_type = 'WEB'
AND EXTRACT(MONTH FROM data_date) = 8
AND EXTRACT(YEAR FROM data_date) = 2024
GROUP BY 1
ORDER BY total_clicks DESC
LIMIT 3;
See!? You can learn SQL!
If this makes sense so far, you understand 80% of the foundation you’ll need to analyze data with SQL. The rest is just variations of this and lots of little details.
Ready to get started with SQL for SEO? Get hours of course videos and code samples for just $50 when you sign up with code “MONTHLY” at checkout.
Want to learn how to read and adapt the rest of the queries? I’m recording a video to explain how that is available for all FREE course participants.
You can sign up here: https://trevorfox.com/learn-sql-for-seo/
If you’re already a SQL expert, read on to check out the rest of the queries.
Top growing pages last month
This query is similar to the last one, except it introduces a CASE statement to do a month-over-month comparison. Sign up to check out the explainer video. ;p
SELECT
url,
SUM(CASE WHEN DATE_TRUNC(data_date, month) = '2024-08-01' THEN clicks END)
- SUM(CASE WHEN DATE_TRUNC(data_date, month) = '2024-07-01' THEN clicks END) mom_click_growth
FROM `searchconsole.searchdata_url_impression`
WHERE
site_url = 'sc-domain:trevorfox.com'
AND search_type = 'WEB'
AND EXTRACT(MONTH FROM data_date) = 8
AND EXTRACT(YEAR FROM data_date) = 2024
GROUP BY url
ORDER BY mom_click_growth desc
LIMIT 3
How did people find you
This section of the email report shifts the focus to the queries that searchers use to find your site. Similar to the last section, it shows the top queries by total clicks and click growth.
- Top performing queries last month
- Top 3 performing queries growth
Top performing queries last month
This query is the same as the top-performing page’s query, except it groups by query instead of clicks and queries the site impressions table. In section two of the course, I explain why this query uses the site impressions table instead of the URL impressions table in vivid detail.
SELECT
query,
sum(clicks) AS total_clicks
FROM `mvp-data-321618.searchconsole.searchdata_site_impression`
WHERE
site_url = 'sc-domain:trevorfox.com'
AND search_type = 'WEB'
AND query is not null
AND EXTRACT(MONTH FROM data_date) = 8
AND EXTRACT(YEAR FROM data_date) = 2024
GROUP BY 1
ORDER BY
total_clicks DESC
LIMIT 3;
Top 3 performing queries growth
This query is the same as the top-growing page’s query, except it groups by query instead of clicks and queries the site impressions table. Again, check out section two of the course if you want to know why this query uses the site impressions table instead of the URL impressions table.
SELECT
query,
SUM(CASE WHEN DATE_TRUNC(data_date, month) = '2024-08-01' THEN clicks END)
- SUM(CASE WHEN DATE_TRUNC(data_date, month) = '2024-07-01' THEN clicks END) mom_click_growth
FROM `searchconsole.searchdata_site_impression`
WHERE
site_url = 'sc-domain:trevorfox.com'
AND search_type = 'WEB'
AND query is not null
AND EXTRACT(MONTH FROM data_date) = 8
AND EXTRACT(YEAR FROM data_date) = 2024
GROUP BY query
ORDER BY mom_click_growth desc
LIMIT 3
Learn about your audience
This section provides a high-level overview of the three most significant searcher attributes when it comes to SEO. While these are important, these attributes don’t even begin to display the fine-grain detail you can access in the URL impressions table.
- Device breakdown
- Country breakdown
- Search type breakdown
Device breakdown
This is a simple aggregation of the sum of clicks by device type.
SELECT
device,
sum(clicks) AS total_clicks
FROM `mvp-data-321618.searchconsole.searchdata_site_impression`
WHERE
site_url = 'sc-domain:trevorfox.com'
AND search_type = 'WEB'
AND EXTRACT(MONTH FROM data_date) = 8
AND EXTRACT(YEAR FROM data_date) = 2024
GROUP BY 1
ORDER BY total_clicks DESC
LIMIT 3;
Country breakdown
This is the same as the device type, except the searcher’s country instead.
SELECT
country,
sum(clicks) AS total_clicks
FROM `mvp-data-321618.searchconsole.searchdata_site_impression`
WHERE
site_url = 'sc-domain:trevorfox.com'
AND search_type = 'WEB'
AND EXTRACT(MONTH FROM data_date) = 8
AND EXTRACT(YEAR FROM data_date) = 2024
GROUP BY 1
ORDER BY
total_clicks DESC
LIMIT 3;
Search type breakdown
Last but not least is the search type breakdown. It’s the same as the last two, but it does not filter on the “web” search type. Instead, it aggregates clicks across the top three search types.
SELECT
search_type,
sum(clicks) AS total_clicks
FROM `mvp-data-321618.searchconsole.searchdata_site_impression`
WHERE
site_url = 'sc-domain:trevorfox.com'
AND search_type = 'WEB'
AND EXTRACT(MONTH FROM data_date) = 8
AND EXTRACT(YEAR FROM data_date) = 2024
GROUP BY 1
ORDER BY
total_clicks DESC
LIMIT 3;
Ready to go deeper?
Learn everything you need to know to analyze SEO data like a pro. Gain access to hours of course video, sample code, and expert instruction from me 🙂 You can sign up here: https://trevorfox.com/learn-sql-for-seo/