Detección de URLs con caída brusca de tráfico orgánico
Compara el tráfico orgánico de cada URL en las últimas 2 semanas contra las 2 semanas anteriores. Detecta páginas con caídas significativas que pueden indicar pérdida de posiciones, errores técnicos o penalizaciones.
-- Detección de URLs con caída brusca de tráfico orgánico
-- Compara últimas 2 semanas vs 2 semanas anteriores
WITH trafico AS (
SELECT
(SELECT value.string_value FROM UNNEST(event_params)
WHERE key = 'page_location') AS pagina,
CASE
WHEN PARSE_DATE('%Y%m%d', event_date) >= DATE_SUB(CURRENT_DATE(), INTERVAL 14 DAY)
THEN 'reciente'
ELSE 'anterior'
END AS periodo,
CONCAT(
user_pseudo_id, '.',
CAST(
(SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id')
AS STRING)
) AS session_id
FROM
`your-project.analytics_XXXXXXXXX.events_*`
WHERE
_TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 28 DAY))
AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
AND traffic_source.medium = 'organic'
AND event_name = 'session_start'
)
SELECT
pagina,
COUNT(DISTINCT IF(periodo = 'anterior', session_id, NULL)) AS sesiones_anterior,
COUNT(DISTINCT IF(periodo = 'reciente', session_id, NULL)) AS sesiones_reciente,
COUNT(DISTINCT IF(periodo = 'reciente', session_id, NULL))
- COUNT(DISTINCT IF(periodo = 'anterior', session_id, NULL)) AS diferencia,
ROUND(SAFE_DIVIDE(
COUNT(DISTINCT IF(periodo = 'reciente', session_id, NULL))
- COUNT(DISTINCT IF(periodo = 'anterior', session_id, NULL)),
COUNT(DISTINCT IF(periodo = 'anterior', session_id, NULL))
) * 100, 2) AS variacion_pct
FROM
trafico
GROUP BY
pagina
HAVING
-- Solo páginas con tráfico relevante en el periodo anterior
COUNT(DISTINCT IF(periodo = 'anterior', session_id, NULL)) >= 20
-- Solo caídas mayores al 30%
AND SAFE_DIVIDE(
COUNT(DISTINCT IF(periodo = 'reciente', session_id, NULL))
- COUNT(DISTINCT IF(periodo = 'anterior', session_id, NULL)),
COUNT(DISTINCT IF(periodo = 'anterior', session_id, NULL))
) <= -0.30
ORDER BY
diferencia ASC
LIMIT 30Explicación paso a paso
- 7 Clasifica cada sesión en 'reciente' (últimas 2 semanas) o 'anterior' (2 semanas previas) para la comparación.
- 21 Filtra los últimos 28 días para cubrir ambos periodos de 2 semanas.
- 28 Cuenta sesiones del periodo anterior y del reciente por separado usando IF condicional.
- 30 Calcula la diferencia absoluta en sesiones entre periodos.
- 32 SAFE_DIVIDE calcula la variación porcentual sin riesgo de error por división entre cero.
- 43 HAVING filtra solo páginas con al menos 20 sesiones previas y caída mayor al 30%.
- 50 Ordena por diferencia ascendente para mostrar primero las caídas más pronunciadas.
Ejemplo de resultado esperado
| pagina | sesiones_anterior | sesiones_reciente | diferencia | variacion_pct |
|---|---|---|---|---|
| https://ejemplo.com/guia-antigua | 456 | 123 | -333 | -73.03 |
| https://ejemplo.com/blog/post-2024 | 234 | 89 | -145 | -61.97 |
| https://ejemplo.com/herramientas | 187 | 98 | -89 | -47.59 |
Variaciones y adaptaciones
Para detectar subidas en lugar de caídas, cambiar la condición HAVING a >= 0.30 y ORDER BY diferencia DESC. Para comparar meses completos, ajustar los intervalos a INTERVAL 30 DAY e INTERVAL 60 DAY. Para añadir la landing page más frecuente de cada URL, agregar un campo con la página donde más se entra.