5 SQL Queries Every SEO Should Know

When I first started using SQL for SEO, it was like putting on glasses for the first time. Suddenly, data that used to look blurry in generic dashboards became sharp, specific, and actionable.

You don’t need to be a developer to use SQL. If you can write a formula in Google Sheets, you can write a SQL query. It’s logic, not magic. And the power it gives you for SEO analysis is enormous.

1. Top keywords with CTR opportunity

This query finds keywords where you have strong positions but low CTR — opportunities to improve titles and metas:

SELECT
  query,
  SUM(impressions) AS total_impressions,
  SUM(clicks) AS total_clicks,
  ROUND(SUM(clicks) / SUM(impressions) * 100, 2) AS ctr,
  ROUND(SUM(position * impressions) / SUM(impressions), 1) AS avg_position
FROM `project.dataset.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY query
HAVING total_impressions > 100 AND avg_position <= 10 AND ctr < 3
ORDER BY total_impressions DESC
LIMIT 50;

What it does: finds queries in the top 10 with over 100 impressions but CTR below 3%. These are URLs where you’re visible but not convincing users to click.

2. Pages that lost traffic month over month

WITH current_month AS (
  SELECT url, SUM(clicks) AS clicks_now
  FROM `project.dataset.searchdata_site_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
  GROUP BY url
),
previous_month AS (
  SELECT url, SUM(clicks) AS clicks_before
  FROM `project.dataset.searchdata_site_impression`
  WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 56 DAY) AND DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY)
  GROUP BY url
)
SELECT
  c.url,
  p.clicks_before,
  c.clicks_now,
  c.clicks_now - p.clicks_before AS diff
FROM current_month c
JOIN previous_month p ON c.url = p.url
WHERE p.clicks_before > 10
ORDER BY diff ASC
LIMIT 30;

This query compares clicks per URL over the last 28 days versus the previous 28. The biggest losers show up first. It’s your early warning system for content that needs attention.

3. Keyword cannibalization

SELECT
  query,
  COUNT(DISTINCT url) AS urls_ranking,
  ARRAY_AGG(DISTINCT url LIMIT 5) AS sample_urls,
  SUM(impressions) AS total_impressions
FROM `project.dataset.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY query
HAVING urls_ranking > 1 AND total_impressions > 50
ORDER BY total_impressions DESC
LIMIT 50;

When multiple URLs on your site compete for the same keyword, Google doesn’t know which to prioritize. This query detects keywords with multiple ranking URLs. You decide which to consolidate.

4. Position distribution by directory

SELECT
  REGEXP_EXTRACT(url, r'https?://[^/]+(/[^/]+/)') AS directory,
  COUNT(DISTINCT url) AS pages,
  ROUND(AVG(position), 1) AS avg_position,
  SUM(clicks) AS total_clicks
FROM `project.dataset.searchdata_site_impression`
WHERE data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
GROUP BY directory
HAVING pages > 3
ORDER BY total_clicks DESC;

Perfect for large sites. It shows which sections have the best organic performance. If /blog/ has an average position of 15 but /guides/ sits at 5, you know where to focus your effort.

5. Content freshness — last indexation dates

SELECT
  url,
  MAX(data_date) AS last_seen,
  SUM(impressions) AS total_impressions,
  DATE_DIFF(CURRENT_DATE(), MAX(data_date), DAY) AS days_since_seen
FROM `project.dataset.searchdata_site_impression`
WHERE impressions > 0
GROUP BY url
HAVING days_since_seen > 30
ORDER BY total_impressions DESC
LIMIT 50;

This query finds URLs that stopped appearing in Search Console over 30 days ago. If they used to have impressions and now don’t, it could be an indexation issue, a penalty, or expired content.

Where to run these queries

The most direct way is with BigQuery. If you have Search Console connected to BigQuery (free up to a certain volume), you can run these queries directly. They also work, adapted, in any database where you have exported data.

If you don’t have BigQuery set up yet, start there. The GSC → BigQuery connection takes 10 minutes and opens up a world of possibilities for SEO analysis with SQL.