Era primaveril está aberta 😎

Hora de atualizar o design para tons quentes que aquecem a alma. Montei um novo design para que cada post transmita energia primaveril 🔥

Então, vamos agora falar sobre questões de produto 👇

Te dão duas tabelas - users e events. E 20 minutos para a tarefa:
«Calcula, meu caro, o retention por coortes M0–M3»

E é aí que bate o bloqueio.
Mas por onde começar? 🤔

O mais fácil - não é com SQL, mas com uma imagem na cabeça. Imagine uma academia.
100 pessoas compraram um plano - essa é sua coorte.
Geralmente, essa coorte é considerada 100%. Isso é M0.
Mas tem um detalhe: não é uma lei da natureza, apenas um acordo tácito.
No dia seguinte, 60 pessoas vieram - Day 1 = 60%.
Depois de uma semana, vieram 40 - Day 7 = 40%.

A lógica não muda.
Só que em vez de dias, são meses.
E em vez de academia, é o produto.

1️⃣ Nível Júnior - entender quantas pessoas vieram em cada coorte
SELECT
  DATE_TRUNC('month', registration_date) AS cohort_month,
  COUNT(DISTINCT user_id) AS new_users
FROM users
GROUP BY 1
ORDER BY 1;

Se já houver erro aqui, pode parar tudo. Porque DATE_TRUNC e DISTINCT são o básico do bloco de produto.
Pelo menos para PostgreSQL e dialetos similares.

2️⃣ Próximo nível - entender quem voltou
WITH cohort_dec AS (
  SELECT user_id, registration_date
  FROM users
  WHERE registration_date >= '2023-12-01'
    AND registration_date < '2024-01-01'
)
SELECT
  COUNT(*) AS cohort_size,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 1 THEN user_id END) AS day1,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 7 THEN user_id END) AS day7
FROM cohort_dec u
LEFT JOIN events e ON u.user_id = e.user_id;

Aqui tem armadilhas 🤔

Esse código é normal se suas datas forem armazenadas como DATE, sem hora.
Mas se for TIMESTAMP, uma simples igualdade pode quebrar o cálculo.
Porque um evento pode ser em 2023-12-08 00:01, outro em 2023-12-08 19:42, e formalmente não é o mesmo valor.

Então, é preciso converter para data ou calcular por intervalos.
Ou seja, a lógica é sempre a mesma:
✅ fixou a coorte → viu quem voltou

Para M1, M2, M3 é quase a mesma coisa, mas tem um ponto crucial:
👉 não se comparam apenas datas, mas o deslocamento em meses corridos em relação ao mês de registro (parece complicado, vou explicar agora)

Ou seja, não é +30 dias. Meses têm durações diferentes, então esse cálculo desvia.
Para retention mensal, olha-se o deslocamento por meses corridos, não apenas somar 30 dias.

❌ Onde mais tem armadilhas

1️⃣ Contam eventos em vez de pessoas
Um usuário fez 5 eventos.
E de repente seu retention fica maior que 100%.
Isso significa que você está contando atividade, não retorno de pessoas.

2️⃣ Usam INNER JOIN
E na seleção ficam apenas os que voltaram.
Os que desistiram simplesmente desaparecem.
Resulta num número bonito. E um quadro completamente falso.

3️⃣ Não fixam a base de cálculo
O tamanho da coorte geralmente é considerado 100%, e a partir daí se calcula tudo.
Se a base varia, as porcentagens viram lixo.

E aí começa o maior problema... teste de pensamento e soft skills. Essa é a parte mais espinhosa se você tem pouca experiência.
Porque na entrevista não vão te perguntar:
«Como calcular retention?»

Mas vão perguntar:
«D30 caiu. Por quê?»

Pronto... E aí o SQL já não impressiona ninguém.

✅ Um raciocínio normal seria:
- decompor o retention por coortes
- olhar os canais de aquisição
- verificar o activation
- entender se o onboarding não quebrou
- comparar o comportamento antes e depois de lançamentos
Pode-se cavar ainda mais fundo, mas para começar essas bases bastam.

❌ Resposta que acaba com a conversa
«Bem… o retention caiu porque os usuários começaram a voltar menos…»

Obrigado, Capitão Óbvio. Mas isso não é resposta de analista, é um palpite.

Em palavras simples:
Retention não é sobre SQL, é sobre comportamento.
SQL é apenas a pá com que você desenterra o número.

Se você não consegue explicar por que as pessoas pararam de voltar,
então suas porcentagens não dizem nada 🤷‍♀️

Você já teve problemas no bloco de produto? E, no geral, você gosta mais da área de produto ou da parte de engenharia? 👇