Comparativa periodo actual vs anterior por URL
Compara el rendimiento de cada URL entre los últimos 14 días y los 14 días anteriores. Permite generar un reporte de variación de tráfico por página para detectar mejoras o caídas.
-- Comparativa de URLs: últimos 14 días vs 14 días anteriores
WITH datos AS (
SELECT
url,
CASE
WHEN data_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY) THEN 'actual'
ELSE 'anterior'
END AS periodo,
clicks,
impressions,
sum_top_position
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'
)
SELECT
url,
SUM(IF(periodo = 'anterior', clicks, 0)) AS clics_anterior,
SUM(IF(periodo = 'actual', clicks, 0)) AS clics_actual,
SUM(IF(periodo = 'actual', clicks, 0)) - SUM(IF(periodo = 'anterior', clicks, 0)) AS dif_clics,
ROUND(SAFE_DIVIDE(
SUM(IF(periodo = 'actual', clicks, 0)) - SUM(IF(periodo = 'anterior', clicks, 0)),
SUM(IF(periodo = 'anterior', clicks, 0))
) * 100, 2) AS variacion_clics_pct,
ROUND(SAFE_DIVIDE(
SUM(IF(periodo = 'anterior', sum_top_position + impressions, 0)),
SUM(IF(periodo = 'anterior', impressions, 0))
), 2) AS pos_anterior,
ROUND(SAFE_DIVIDE(
SUM(IF(periodo = 'actual', sum_top_position + impressions, 0)),
SUM(IF(periodo = 'actual', impressions, 0))
), 2) AS pos_actual
FROM
datos
GROUP BY
url
HAVING
clics_anterior >= 10
ORDER BY
dif_clics ASC
LIMIT 50Explicación paso a paso
- 5 Clasifica cada fila en periodo 'actual' o 'anterior' según si la fecha cae en los últimos 14 días o los 14 previos.
- 20 Usa IF condicional dentro de SUM para separar métricas de cada periodo en columnas distintas.
- 23 SAFE_DIVIDE calcula la variación porcentual evitando errores cuando el periodo anterior no tiene datos.
- 27 Calcula la posición media de cada periodo por separado usando la fórmula correcta del export GSC.
- 39 Filtra URLs con al menos 10 clics en el periodo anterior para excluir páginas con tráfico marginal.
- 41 Ordena por diferencia de clics ascendente para mostrar primero las mayores caídas.
Ejemplo de resultado esperado
| url | clics_anterior | clics_actual | dif_clics | variacion_clics_pct | pos_anterior | pos_actual |
|---|---|---|---|---|---|---|
| https://ejemplo.com/guia-antigua | 234 | 87 | -147 | -62.82 | 4.20 | 8.50 |
| https://ejemplo.com/blog/post-2024 | 187 | 123 | -64 | -34.22 | 5.10 | 6.30 |
| https://ejemplo.com/herramientas | 145 | 98 | -47 | -32.41 | 3.80 | 5.20 |
Variaciones y adaptaciones
Para ver las URLs que más crecieron, cambiar ORDER BY a dif_clics DESC. Para comparar meses completos, ajustar a INTERVAL 30 DAY e INTERVAL 60 DAY. Para incluir también el cambio de impresiones, añadir columnas de impresiones por periodo con la misma lógica.