Detección de canibalización de keywords entre URLs
Detecta keywords para las que compiten dos o más URLs del mismo sitio. La canibalización puede diluir la autoridad y reducir el rendimiento en los resultados de búsqueda.
-- 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 100Explicación paso a paso
- 2 El primer CTE agrega métricas por combinación de keyword + URL.
- 17 Filtra combinaciones con al menos 10 impresiones para excluir apariciones marginales.
- 20 El segundo CTE identifica keywords que aparecen con 2 o más URLs distintas.
- 31 La query final cruza ambos CTEs para mostrar el detalle de cada URL canibalizada.
- 41 Ordena primero por cantidad de URLs compitiendo (mayor canibalización) y luego por clics dentro de cada keyword.
Ejemplo de resultado esperado
| keyword | urls_compitiendo | url | clics | impresiones | posicion_media |
|---|---|---|---|---|---|
| tutorial bigquery | 3 | https://ejemplo.com/tutorial-bigquery | 234 | 4567 | 4.20 |
| tutorial bigquery | 3 | https://ejemplo.com/blog/bigquery-guia | 87 | 3456 | 8.50 |
| tutorial bigquery | 3 | https://ejemplo.com/cursos/bigquery | 12 | 1234 | 15.30 |
Variaciones y adaptaciones
Para ver solo keywords con alta canibalización (3+ URLs), cambiar HAVING urls_compitiendo >= 2 por >= 3. Para calcular la dispersión de posiciones entre URLs, añadir una columna con MAX(posicion_media) - MIN(posicion_media) por keyword. Para enfocarse en keywords con clics significativos, añadir un filtro de clics totales por keyword.