SQL Queries for SEO Analysis

Practical SQL Queries for SEO Analysis

Using SQL in BigQuery enables SEO professionals to conduct in-depth analysis of their data, uncovering trends and insights that are essential for decision-making. This section provides SQL queries for SEO that are both practical and powerful, designed to help you analyze Google Search Console data efficiently. These SEO SQL examples will give you a foundational toolkit to perform key analyses, from understanding keyword performance to monitoring trends and detecting anomalies.

1. Analyzing Top Keywords by Clicks and Impressions

Understanding which keywords drive the most traffic is essential for prioritizing SEO efforts.

• Query:

  SELECT
    query,
    SUM(clicks) AS total_clicks,
    SUM(impressions) AS total_impressions,
    (SUM(clicks) / SUM(impressions)) * 100 AS ctr_percentage
  FROM
    `your_project.your_dataset.search_data`
  GROUP BY
    query
  ORDER BY
    total_clicks DESC
  LIMIT 10;

• Explanation: This query summarizes clicks and impressions for each keyword, sorts them by total clicks, and displays the top 10. It also calculates the click-through rate (CTR) to understand which keywords are performing well in terms of user engagement.

2. Tracking Keyword Performance Over Time

Monitoring keyword trends over time allows you to identify growth opportunities and seasonal patterns.

• Query:

  SELECT
    query,
    EXTRACT(YEAR FROM date) AS year,
    EXTRACT(MONTH FROM date) AS month,
    SUM(clicks) AS monthly_clicks,
    SUM(impressions) AS monthly_impressions
  FROM
    `your_project.your_dataset.search_data`
  WHERE
    query = 'target_keyword'
  GROUP BY
    query, year, month
  ORDER BY
    year, month;

• Explanation: This query aggregates clicks and impressions for a specific keyword by month and year. By tracking monthly performance, you can see if there are any seasonal spikes or consistent growth patterns for the target keyword.

3. Identifying High-Impression, Low-CTR Keywords

Low-CTR keywords with high impressions might need content updates or metadata optimization to improve click performance.

• Query:

  SELECT
    query,
    SUM(impressions) AS total_impressions,
    (SUM(clicks) / NULLIF(SUM(impressions), 0)) * 100 AS ctr_percentage
  FROM
    `your_project.your_dataset.search_data`
  GROUP BY
    query
  HAVING
    total_impressions > 1000
    AND ctr_percentage < 2
  ORDER BY
    ctr_percentage ASC;

• Explanation: This query identifies keywords with high impressions (over 1000) but low CTR (under 2%). It highlights terms that may benefit from optimizations like updated titles, descriptions, or better alignment with user intent.

4. Comparing Mobile vs. Desktop Performance

Analyzing data by device type helps you understand if users on different devices engage with your content differently.

• Query:

  SELECT
    device,
    SUM(clicks) AS total_clicks,
    SUM(impressions) AS total_impressions,
    (SUM(clicks) / SUM(impressions)) * 100 AS ctr_percentage,
    AVG(position) AS avg_position
  FROM
    `your_project.your_dataset.search_data`
  GROUP BY
    device
  ORDER BY
    total_clicks DESC;

• Explanation: This query summarizes SEO performance by device (mobile, desktop, tablet), calculating clicks, impressions, CTR, and average position for each. By comparing these metrics, you can see which device types perform best and identify potential areas for mobile or desktop optimization.

5. Identifying Underperforming Pages

Analyzing page-level data helps pinpoint specific URLs that could benefit from SEO improvements.

• Query:

  SELECT
    page,
    SUM(clicks) AS total_clicks,
    SUM(impressions) AS total_impressions,
    AVG(position) AS avg_position
  FROM
    `your_project.your_dataset.search_data`
  GROUP BY
    page
  HAVING
    total_impressions > 500
    AND total_clicks < 50
  ORDER BY
    avg_position DESC;

• Explanation: This query identifies pages with high impressions (over 500) but low clicks (under 50), meaning they appear in search results frequently but fail to attract clicks. Pages like these may need content updates, better metadata, or improved targeting.

6. Detecting Anomalies in Keyword Performance

Sudden changes in keyword performance can indicate ranking changes, algorithm updates, or other impactful factors.

• Query:

  SELECT
    query,
    date,
    clicks,
    LAG(clicks, 1) OVER (PARTITION BY query ORDER BY date) AS previous_day_clicks,
    (clicks - LAG(clicks, 1) OVER (PARTITION BY query ORDER BY date)) AS click_difference
  FROM
    `your_project.your_dataset.search_data`
  WHERE
    query = 'target_keyword'
    AND date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
  ORDER BY
    date;

• Explanation: This query compares daily clicks for a target keyword to the previous day, calculating the difference in clicks. A significant drop or spike in clicks could indicate an anomaly, prompting further investigation into rankings or search interest changes.

7. Calculating Share of Voice for Key Queries

Share of voice helps measure your visibility compared to total impressions for a keyword, especially when integrated with competitor data.

• Query:

  SELECT
    query,
    (SUM(clicks) / (SUM(clicks) + competitor_clicks)) * 100 AS share_of_voice
  FROM
    `your_project.your_dataset.search_data`
  JOIN
    `your_project.competitor_data.competitor_clicks`
  USING (query)
  WHERE
    query IN ('target_keyword1', 'target_keyword2')
  GROUP BY
    query;

• Explanation: This query calculates your share of voice by comparing your clicks to competitor clicks on shared keywords. It reveals your relative visibility in search results for important keywords, showing where you lead or lag behind competitors.

8. Analyzing CTR Changes Over Time for a Specific Page

Monitoring CTR over time for a page allows you to gauge the effectiveness of changes made to titles, descriptions, or content.

• Query:

  SELECT
    date,
    (SUM(clicks) / SUM(impressions)) * 100 AS ctr_percentage
  FROM
    `your_project.your_dataset.search_data`
  WHERE
    page = 'https://yourwebsite.com/specific-page'
  GROUP BY
    date
  ORDER BY
    date;

• Explanation: This query calculates daily CTR for a specific page. By analyzing CTR trends, you can assess whether content or metadata updates positively impact engagement over time.

Summary

These SEO SQL examples provide essential insights into keyword performance, page engagement, device trends, and more. By using these queries in BigQuery, you can uncover targeted opportunities for SEO improvement and track the impact of your strategies with greater accuracy. As you become comfortable with these foundational queries, you can modify and combine them to conduct deeper analyses, empowering data-driven decision-making for SEO.