Detección de URLs con caída brusca de tráfico orgánico

GA4 en BigQuery Auditoría técnica Avanzado

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.

caida-trafico-urls-organicas.sql

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 46 47 48 49 50 51 52

-- 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 30

Explicació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.

Tráfico orgánico segmentado por tipo de contenido

Páginas de salida más frecuentes en sesiones orgánicas

Eventos personalizados disparados en sesiones orgánicas

Sesiones orgánicas por día en los últimos 30 días