Análisis de retención de cohortes con SQL

La retención pregunta si un actor definido regresa después de un hito inicial. Una consulta de retención útil indica el actor, el evento de cohorte, la actividad de retorno calificada, el intervalo y la ventana de observación. La “retención semanal” es ambigua hasta que esas cinco opciones sean explícitas.

Cree tablas de cohortes y de actividades por separado

WITH cohorts AS (
  SELECT
    account_id,
    date_trunc('week', MIN(timestamp_utc)) AS cohort_week
  FROM product_events
  WHERE event_name = 'activation_completed'
  GROUP BY account_id
),
activity AS (
  SELECT DISTINCT
    account_id,
    date_trunc('week', timestamp_utc) AS activity_week
  FROM product_events
  WHERE event_name IN ('query_completed', 'dashboard_viewed')
)
SELECT
  cohort_week,
  activity_week,
  COUNT(DISTINCT cohorts.account_id) AS retained_accounts
FROM cohorts
JOIN activity
  ON cohorts.account_id = activity.account_id
 AND activity.activity_week >= cohorts.cohort_week
GROUP BY cohort_week, activity_week
ORDER BY cohort_week, activity_week;

Este resultado preserva las semanas de cohorte y de actividad. Una capa de informes puede convertir la brecha en la semana cero, la semana uno y períodos posteriores después de confirmar la aritmética de fechas respaldada por el motor SQL seleccionado. Mantener visibles las fechas intermedias hace que los errores de límites sean más fáciles de detectar.

Definir el denominador una vez

El denominador es el número de actores elegibles en la cohorte, no el número que produjo un evento de actividad posterior. Calcule el tamaño de la cohorte por separado y únalo al recuento retenido. Proteja el porcentaje con NULLIF y conserve ambos recuentos en la salida.

Las cohortes recientes no han tenido tiempo de alcanzar intervalos posteriores. Marque esas celdas como incompletas en lugar de tratarlas como retención cero. Decida también si la semana cero significa la activación en sí o un retorno separado después de la activación.

Utilice un evento que represente un valor duradero en lugar de cualquier latido de fondo. Excluya cuentas de prueba y actividades automatizadas con campos controlados. Valide fusiones de identidades, eventos duplicados, llegadas tardías y límites de zona horaria con un pequeño dispositivo.

Lea Análisis de embudo para conocer la progresión de los hitos, División del tiempo para conocer las reglas de intervalo y DataFusion SQL referencia antes de adaptar los cálculos de fechas de otro dialecto SQL.