Detección de canibalización de keywords entre URLs

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45

-- Detección de canibalización: keywords con múltiples URLs compitiendo
WITH keyword_urls AS (
  SELECT
    query AS keyword,
    url,
    SUM(clicks) AS clics,
    SUM(impressions) AS impresiones,
    ROUND(SUM(sum_top_position + impressions) / SUM(impressions), 2) AS posicion_media
  FROM
    `your-project.searchconsole.searchdata_url_impression`
  WHERE
    data_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY) AND CURRENT_DATE()
    AND search_type = 'WEB'
    AND is_anonymized_query = FALSE
  GROUP BY
    keyword, url
  HAVING
    impresiones >= 10
),
keywords_canibalizadas AS (
  SELECT
    keyword,
    COUNT(DISTINCT url) AS urls_compitiendo
  FROM
    keyword_urls
  GROUP BY
    keyword
  HAVING
    urls_compitiendo >= 2
)
SELECT
  ku.keyword,
  kc.urls_compitiendo,
  ku.url,
  ku.clics,
  ku.impresiones,
  ku.posicion_media
FROM
  keyword_urls ku
  INNER JOIN keywords_canibalizadas kc ON ku.keyword = kc.keyword
ORDER BY
  kc.urls_compitiendo DESC,
  ku.keyword,
  ku.clics DESC
LIMIT 100