Comparativa orgánico vs otros canales (mes actual vs anterior)

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

-- Comparativa de canales: mes actual vs mes anterior
WITH datos AS (
  SELECT
    CASE
      WHEN PARSE_DATE('%Y%m%d', event_date) >= DATE_TRUNC(CURRENT_DATE(), MONTH)
        THEN 'mes_actual'
      ELSE 'mes_anterior'
    END AS periodo,
    IFNULL(traffic_source.medium, '(none)') AS medio,
    user_pseudo_id,
    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_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH))
      AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    AND event_name = 'session_start'
)
SELECT
  medio,
  COUNTIF(periodo = 'mes_anterior') AS sesiones_mes_anterior,
  COUNTIF(periodo = 'mes_actual') AS sesiones_mes_actual,
  ROUND(
    SAFE_DIVIDE(
      COUNTIF(periodo = 'mes_actual') - COUNTIF(periodo = 'mes_anterior'),
      COUNTIF(periodo = 'mes_anterior')
    ) * 100, 2
  ) AS variacion_pct
FROM
  datos
GROUP BY
  medio
ORDER BY
  sesiones_mes_actual DESC
LIMIT 15