SEO Market Opportunity Analysis for a Travel Vertical

SELECT
    market,
    SUM(avg_monthly_searches) AS total_search_volume,
    COUNT(DISTINCT keyword_norm) AS unique_keywords
FROM keyword_clean
WHERE dup_keyword_market_flag = FALSE
GROUP BY market
ORDER BY total_search_volume DESC;

SELECT
    market,
    SUM(avg_monthly_searches) AS non_brand_search_volume,
    COUNT(DISTINCT keyword_norm) AS non_brand_keywords
FROM keyword_clean
WHERE brand_flag = FALSE
  AND dup_keyword_market_flag = FALSE
GROUP BY market
ORDER BY non_brand_search_volume DESC;

SELECT
    market,
    intent,
    SUM(avg_monthly_searches) AS search_volume,
    COUNT(DISTINCT keyword_norm) AS keyword_count
FROM keyword_clean
WHERE dup_keyword_market_flag = FALSE
GROUP BY market, intent
ORDER BY market, search_volume DESC;

SELECT
    market,
    query_segment,
    SUM(avg_monthly_searches) AS segment_search_volume,
    COUNT(DISTINCT keyword_norm) AS keyword_count
FROM keyword_clean
WHERE dup_keyword_market_flag = FALSE
GROUP BY market, query_segment
ORDER BY market, segment_search_volume DESC;

SELECT
    market,
    player_type,
    COUNT(*) AS ranking_rows,
    SUM(estimated_monthly_traffic) AS estimated_traffic,
    MIN(estimated_position) AS best_position
FROM rankings_clean
WHERE market IS NOT NULL
  AND market <> ''
GROUP BY market, player_type
ORDER BY market, estimated_traffic DESC;