Comparativa orgánico vs otros canales (mes actual vs anterior)
Compara el rendimiento de orgánico frente a otros canales de adquisición entre el mes actual y el anterior. Genera una vista tipo reporte mensual para identificar cambios de tendencia por canal.
-- 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 15Explicación paso a paso
- 4 Clasifica cada sesión en 'mes_actual' o 'mes_anterior' según la fecha del evento comparada con el inicio del mes en curso.
- 9 Agrupa por medio de adquisición, reemplazando valores nulos por '(none)'.
- 20 Filtra desde el inicio del mes anterior hasta hoy para tener ambos periodos completos.
- 26 Usa COUNTIF para contar sesiones de cada periodo por separado en la misma fila.
- 29 SAFE_DIVIDE evita errores de división por cero cuando un canal no tenía tráfico el mes anterior.
- 33 Calcula la variación porcentual mes sobre mes para cada canal.
Ejemplo de resultado esperado
| medio | sesiones_mes_anterior | sesiones_mes_actual | variacion_pct |
|---|---|---|---|
| organic | 12450 | 13820 | 11.00 |
| (none) | 4320 | 3980 | -7.87 |
| cpc | 2340 | 2560 | 9.40 |
| referral | 1230 | 1180 | -4.07 |
Variaciones y adaptaciones
Para comparar trimestres en lugar de meses, ajustar DATE_TRUNC a QUARTER. Para añadir usuarios únicos a la comparativa, incluir COUNT(DISTINCT IF(periodo = 'mes_actual', user_pseudo_id, NULL)). Para ver solo orgánico desglosado por fuente (google, bing, etc.), filtrar por medio = 'organic' y añadir traffic_source.source al GROUP BY.