Análisis de cohortes de usuarios orgánicos por semana de adquisición

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

-- Análisis de cohortes semanales de usuarios orgánicos
-- Semana de adquisición vs semana de actividad
WITH primera_visita AS (
  SELECT
    user_pseudo_id,
    DATE_TRUNC(
      MIN(PARSE_DATE('%Y%m%d', event_date)), WEEK
    ) AS semana_adquisicion
  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 traffic_source.medium = 'organic'
    AND event_name = 'first_visit'
  GROUP BY
    user_pseudo_id
),
actividad AS (
  SELECT
    user_pseudo_id,
    DATE_TRUNC(
      PARSE_DATE('%Y%m%d', event_date), WEEK
    ) AS semana_actividad
  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 traffic_source.medium = 'organic'
  GROUP BY
    user_pseudo_id, semana_actividad
)
SELECT
  pv.semana_adquisicion,
  DATE_DIFF(a.semana_actividad, pv.semana_adquisicion, WEEK) AS semana_n,
  COUNT(DISTINCT a.user_pseudo_id) AS usuarios_activos,
  ROUND(
    COUNT(DISTINCT a.user_pseudo_id) * 100.0 /
    MAX(COUNT(DISTINCT a.user_pseudo_id)) OVER(PARTITION BY pv.semana_adquisicion),
  2) AS tasa_retencion
FROM
  primera_visita pv
  JOIN actividad a ON pv.user_pseudo_id = a.user_pseudo_id
WHERE
  a.semana_actividad >= pv.semana_adquisicion
GROUP BY
  pv.semana_adquisicion, semana_n
ORDER BY
  pv.semana_adquisicion, semana_n