Atribución multicanal con orgánico como primer touch

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

-- Rutas de conversión con orgánico como primer touch
-- Reconstruye el camino de canales por usuario hasta la conversión
WITH sesiones_usuario AS (
  SELECT
    user_pseudo_id,
    PARSE_DATE('%Y%m%d', event_date) AS fecha,
    IFNULL(traffic_source.medium, '(none)') AS medio,
    event_name,
    ROW_NUMBER() OVER(
      PARTITION BY user_pseudo_id
      ORDER BY event_timestamp ASC
    ) AS orden_sesion
  FROM
    `your-project.analytics_XXXXXXXXX.events_*`
  WHERE
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 90 DAY))
      AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
    AND event_name IN ('session_start', 'generate_lead', 'purchase')
),
usuarios_primer_touch_organic AS (
  SELECT DISTINCT user_pseudo_id
  FROM sesiones_usuario
  WHERE orden_sesion = 1 AND medio = 'organic'
),
rutas AS (
  SELECT
    s.user_pseudo_id,
    STRING_AGG(
      DISTINCT s.medio
      ORDER BY s.medio
    ) AS canales_involucrados,
    COUNTIF(s.event_name IN ('generate_lead', 'purchase')) AS conversiones
  FROM
    sesiones_usuario s
    INNER JOIN usuarios_primer_touch_organic u
      ON s.user_pseudo_id = u.user_pseudo_id
  GROUP BY
    s.user_pseudo_id
)
SELECT
  canales_involucrados AS ruta_canales,
  COUNT(*) AS usuarios,
  SUM(conversiones) AS total_conversiones,
  ROUND(SUM(conversiones) / COUNT(*) * 100, 2) AS tasa_conversion
FROM
  rutas
GROUP BY
  ruta_canales
ORDER BY
  usuarios DESC
LIMIT 20