Calcular variación porcentual entre dos periodos

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

-- Variación porcentual entre dos periodos
-- Patrón reutilizable: cambiar las fechas y métricas según necesidad
WITH periodo_anterior AS (
  SELECT
    url,
    SUM(clicks) AS clics,
    SUM(impressions) AS impresiones
  FROM
    `your-project.your_dataset.search_data`
  WHERE
    data_date BETWEEN '2026-03-01' AND '2026-03-31'
  GROUP BY
    url
),
periodo_actual AS (
  SELECT
    url,
    SUM(clicks) AS clics,
    SUM(impressions) AS impresiones
  FROM
    `your-project.your_dataset.search_data`
  WHERE
    data_date BETWEEN '2026-04-01' AND '2026-04-30'
  GROUP BY
    url
)
SELECT
  COALESCE(a.url, p.url) AS url,
  IFNULL(p.clics, 0) AS clics_anterior,
  IFNULL(a.clics, 0) AS clics_actual,
  IFNULL(a.clics, 0) - IFNULL(p.clics, 0) AS diferencia_clics,
  ROUND(SAFE_DIVIDE(
    IFNULL(a.clics, 0) - IFNULL(p.clics, 0),
    IFNULL(p.clics, 0)
  ) * 100, 2) AS variacion_clics_pct,
  IFNULL(p.impresiones, 0) AS impresiones_anterior,
  IFNULL(a.impresiones, 0) AS impresiones_actual,
  ROUND(SAFE_DIVIDE(
    IFNULL(a.impresiones, 0) - IFNULL(p.impresiones, 0),
    IFNULL(p.impresiones, 0)
  ) * 100, 2) AS variacion_impresiones_pct
FROM
  periodo_actual a
  FULL OUTER JOIN periodo_anterior p ON a.url = p.url
ORDER BY
  diferencia_clics DESC
LIMIT 50