Cálculo de retenção por cohort em SQL

Publicado em 2025-02-25

Pergunta

Como montar uma matriz de retenção mensal por cohort de cadastro?

Explicação

A análise de cohort agrupa usuários pelo período em que entraram e mede quantos retornaram em cada mês subsequente. É a base de qualquer dashboard de produto sério.

Query SQL

WITH cohort AS (
  SELECT user_id, DATE_TRUNC('month', signup_at) AS cohort_month
  FROM users
),
activity AS (
  SELECT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
  GROUP BY 1, 2
)
SELECT
  c.cohort_month,
  EXTRACT(MONTH FROM AGE(a.active_month, c.cohort_month)) AS month_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM cohort c
JOIN activity a USING (user_id)
WHERE a.active_month >= c.cohort_month
GROUP BY 1, 2
ORDER BY 1, 2;

Resposta esperada

Tabela cohort_month × month_number com o número de usuários retidos em cada janela.

Erros comuns

• Calcular retenção sobre eventos brutos em vez de usuários distintos
• Considerar o mês 0 como retenção em vez de base 100%
• Ignorar usuários sem nenhuma atividade pós-signup (eles existem no denominador!)