Coortes simples e retenção básica com SQL orientado a produto e relacionamento

Coortes e retenção são duas ferramentas centrais para análise orientada a produto e relacionamento porque respondem a perguntas como: “usuários que começaram em um mesmo período se comportam de forma parecida?”, “quanto tempo as pessoas continuam ativas após o primeiro contato?”, “qual canal traz clientes que ficam mais tempo?”. Em vez de olhar apenas para totais (novos usuários, receita do mês), coortes ajudam a enxergar a qualidade do crescimento e a saúde do engajamento ao longo do tempo.

O que é uma coorte (na prática)

Uma coorte é um grupo de entidades (usuários, contas, clientes, assinantes) que compartilham um mesmo “evento de início” dentro de um período definido. O evento de início mais comum é a primeira atividade (primeiro login, primeira compra, primeira mensagem enviada, primeira sessão), mas pode ser também: data de cadastro, primeira assinatura paga, primeira ativação de um recurso-chave, ou início de um relacionamento (primeiro atendimento).

Na análise orientada a produto, costuma-se escolher um evento que represente entrada real no uso (ex.: primeira sessão) e um evento que represente valor (ex.: primeira compra, primeiro pedido entregue, primeira automação criada). Em relacionamento (CRM/suporte), o “início” pode ser o primeiro ticket, o primeiro contato comercial, ou a primeira resposta do time.

Coorte por período

O período define o “tamanho” do agrupamento: coorte semanal (usuários que iniciaram na mesma semana), mensal (mesmo mês) ou diária (mesmo dia). Para retenção básica, semanal e mensal costumam ser mais estáveis e fáceis de comunicar.

O que é retenção básica

Retenção mede a proporção de uma coorte que volta a ficar ativa após o início. A forma mais simples é: para cada coorte, contar quantos usuários estiveram ativos no período 0 (o período de início) e quantos estiveram ativos no período 1, 2, 3… (semanas ou meses após o início). A retenção do período N é:

Continue em nosso aplicativo

Você poderá ouvir o audiobook com a tela desligada, ganhar gratuitamente o certificado deste curso e ainda ter acesso a outros 5.000 cursos online gratuitos.

ou continue lendo abaixo...

Baixar o aplicativo

retencao_N = usuarios_ativos_no_periodo_N / usuarios_na_coorte

“Ativo” precisa ser definido com clareza. Em produto, pode ser “teve ao menos uma sessão” ou “executou um evento-chave”. Em relacionamento, pode ser “abriu ticket”, “respondeu e-mail”, “teve interação registrada”. O importante é que a definição seja consistente e alinhada ao objetivo.

Retenção de logo (product) vs retenção de relacionamento

• Produto: foco em uso recorrente e adoção de funcionalidades (ex.: retornou e criou um novo projeto).
• Relacionamento: foco em continuidade do vínculo (ex.: voltou a interagir com o time, respondeu campanhas, abriu novo chamado).

Modelos de dados típicos para coortes

Você normalmente terá pelo menos duas tabelas (ou duas fontes lógicas):

• Entidades: usuários/contas (ex.: users com user_id, created_at, channel).
• Eventos: atividades ao longo do tempo (ex.: events com user_id, event_name, event_at).

Para relacionamento, pode ser contacts e interactions (e-mails, ligações, tickets). Para produto, pode ser sessions, pageviews ou product_events.

Passo a passo prático: coorte simples por primeira atividade e retenção semanal

A seguir, um passo a passo que você pode adaptar. A ideia é construir em camadas: (1) descobrir a data de início por usuário, (2) mapear cada evento para uma “semana relativa” desde o início, (3) contar usuários por coorte e semana relativa, (4) calcular a taxa.

1) Defina o evento de início e o evento de atividade

Exemplo orientado a produto:

• Início (coorte): primeira vez que o usuário fez event_name = 'first_session' (ou primeira sessão registrada).
• Atividade (retenção): qualquer event_name = 'session' (ou qualquer evento de uso).

Exemplo orientado a relacionamento:

• Início (coorte): primeira interação registrada (interaction_type em e-mail/ligação/ticket).
• Atividade (retenção): qualquer interação posterior.

Escolha um e mantenha o mesmo raciocínio.

2) Encontre a data de início (primeira atividade) por usuário

Suponha uma tabela events com colunas: user_id, event_name, event_at. Vamos criar a “data de coorte” como a primeira data em que o usuário teve o evento de início.

WITH cohort_start AS (  SELECT     user_id,    MIN(event_at) AS cohort_at  FROM events  WHERE event_name = 'first_session'  GROUP BY user_id)

Se você não tiver um evento explícito de “primeira sessão”, pode usar a primeira ocorrência de session como proxy. O ponto é: a coorte precisa ser derivada de um critério inequívoco.

3) Transforme a data de coorte em um “período” (semana ou mês)

Para coorte semanal, você quer um marcador como “início da semana” (ou um identificador de semana). A função exata varia por banco, mas o conceito é: truncar a data para semana. Exemplo genérico:

WITH cohort_start AS (  SELECT     user_id,    MIN(event_at) AS cohort_at  FROM events  WHERE event_name = 'first_session'  GROUP BY user_id), cohort_labeled AS (  SELECT     user_id,    DATE_TRUNC('week', cohort_at) AS cohort_week  FROM cohort_start)

Se seu banco não suportar DATE_TRUNC com 'week', adapte para a função equivalente. O importante é que cohort_week seja o mesmo para todos os usuários que começaram naquela semana.

4) Mapeie cada atividade para uma “semana relativa” desde a coorte

Agora precisamos pegar eventos de atividade (por exemplo, session) e calcular quantas semanas se passaram desde a semana da coorte. Isso cria o eixo “week_number” (0, 1, 2...).

WITH cohort_start AS (  SELECT     user_id,    MIN(event_at) AS cohort_at  FROM events  WHERE event_name = 'first_session'  GROUP BY user_id), cohort_labeled AS (  SELECT     user_id,    DATE_TRUNC('week', cohort_at) AS cohort_week  FROM cohort_start), activity AS (  SELECT     e.user_id,    DATE_TRUNC('week', e.event_at) AS activity_week  FROM events e  WHERE e.event_name = 'session'), activity_with_cohort AS (  SELECT     a.user_id,    c.cohort_week,    a.activity_week,    DATE_DIFF('week', c.cohort_week, a.activity_week) AS week_number  FROM activity a  JOIN cohort_labeled c    ON a.user_id = c.user_id  WHERE a.activity_week >= c.cohort_week)

O filtro a.activity_week >= c.cohort_week evita semanas negativas (eventos antes do início). Em bases reais, isso pode acontecer por inconsistências de tracking ou migrações.

5) Conte usuários únicos por coorte e semana relativa

Retenção básica geralmente conta “usuários ativos” como usuários com ao menos uma atividade na semana. Então você agrega por cohort_week e week_number contando usuários distintos.

WITH cohort_start AS (  SELECT user_id, MIN(event_at) AS cohort_at  FROM events  WHERE event_name = 'first_session'  GROUP BY user_id), cohort_labeled AS (  SELECT user_id, DATE_TRUNC('week', cohort_at) AS cohort_week  FROM cohort_start), activity AS (  SELECT user_id, DATE_TRUNC('week', event_at) AS activity_week  FROM events  WHERE event_name = 'session'), activity_with_cohort AS (  SELECT     a.user_id,    c.cohort_week,    DATE_DIFF('week', c.cohort_week, a.activity_week) AS week_number  FROM activity a  JOIN cohort_labeled c ON a.user_id = c.user_id  WHERE a.activity_week >= c.cohort_week), retained_counts AS (  SELECT     cohort_week,    week_number,    COUNT(DISTINCT user_id) AS active_users  FROM activity_with_cohort  GROUP BY cohort_week, week_number) SELECT * FROM retained_counts ORDER BY cohort_week, week_number;

Esse resultado já é uma “tabela de retenção” em formato longo (long format). Para relatórios, você pode pivotar depois, mas o formato longo é ótimo para validação e para gráficos.

6) Calcule o tamanho da coorte e a taxa de retenção

O tamanho da coorte é o número de usuários cujo início caiu naquela semana. Você pode obter isso a partir de cohort_labeled. Depois, junte com retained_counts e calcule a taxa.

WITH cohort_start AS (  SELECT user_id, MIN(event_at) AS cohort_at  FROM events  WHERE event_name = 'first_session'  GROUP BY user_id), cohort_labeled AS (  SELECT user_id, DATE_TRUNC('week', cohort_at) AS cohort_week  FROM cohort_start), cohort_sizes AS (  SELECT cohort_week, COUNT(*) AS cohort_users  FROM cohort_labeled  GROUP BY cohort_week), activity AS (  SELECT user_id, DATE_TRUNC('week', event_at) AS activity_week  FROM events  WHERE event_name = 'session'), activity_with_cohort AS (  SELECT     a.user_id,    c.cohort_week,    DATE_DIFF('week', c.cohort_week, a.activity_week) AS week_number  FROM activity a  JOIN cohort_labeled c ON a.user_id = c.user_id  WHERE a.activity_week >= c.cohort_week), retained_counts AS (  SELECT     cohort_week,    week_number,    COUNT(DISTINCT user_id) AS active_users  FROM activity_with_cohort  GROUP BY cohort_week, week_number) SELECT   r.cohort_week,  r.week_number,  s.cohort_users,  r.active_users,  (r.active_users * 1.0) / s.cohort_users AS retention_rate FROM retained_counts r JOIN cohort_sizes s   ON r.cohort_week = s.cohort_week ORDER BY r.cohort_week, r.week_number;

O multiplicador * 1.0 (ou cast) garante divisão com decimal em muitos bancos. Ajuste conforme necessário.

Como interpretar a tabela de retenção

Com week_number = 0, você deve ver a maioria (ou todos) os usuários da coorte, dependendo da definição de atividade. Se o evento de início for diferente do evento de atividade, é possível que nem todos apareçam no período 0. Exemplo: coorte baseada em “cadastro” e atividade baseada em “sessão”; alguns cadastraram mas não fizeram sessão na semana 0.

Em produto, padrões comuns:

• Queda forte do 0 para o 1: onboarding fraco, valor não percebido, ativação incompleta.
• Retenção estabiliza após algumas semanas: existe um núcleo de usuários que encontrou valor recorrente.
• Coortes mais recentes melhores: melhorias de produto, canais melhores, mudanças de pricing/posicionamento.

Em relacionamento, padrões comuns:

• Retenção baixa: contatos pontuais (suporte reativo) ou baixa cadência de relacionamento.
• Picos em semanas específicas: campanhas, renovações, ciclos de cobrança, eventos sazonais.

Variações úteis (sem complicar demais)

Retenção por canal de aquisição ou segmento

Para produto e relacionamento, é muito comum querer saber se “coortes vindas do canal X” retêm melhor. Para isso, você precisa trazer um atributo do usuário/conta (ex.: channel, plan, region) para a tabela de coorte e incluir no agrupamento.

WITH cohort_start AS (  SELECT user_id, MIN(event_at) AS cohort_at  FROM events  WHERE event_name = 'first_session'  GROUP BY user_id), cohort_labeled AS (  SELECT     u.user_id,    u.channel,    DATE_TRUNC('week', cs.cohort_at) AS cohort_week  FROM cohort_start cs  JOIN users u ON u.user_id = cs.user_id), cohort_sizes AS (  SELECT cohort_week, channel, COUNT(*) AS cohort_users  FROM cohort_labeled  GROUP BY cohort_week, channel), activity AS (  SELECT user_id, DATE_TRUNC('week', event_at) AS activity_week  FROM events  WHERE event_name = 'session'), activity_with_cohort AS (  SELECT     a.user_id,    c.channel,    c.cohort_week,    DATE_DIFF('week', c.cohort_week, a.activity_week) AS week_number  FROM activity a  JOIN cohort_labeled c ON a.user_id = c.user_id  WHERE a.activity_week >= c.cohort_week), retained_counts AS (  SELECT     cohort_week, channel, week_number, COUNT(DISTINCT user_id) AS active_users  FROM activity_with_cohort  GROUP BY cohort_week, channel, week_number) SELECT   r.cohort_week,  r.channel,  r.week_number,  s.cohort_users,  r.active_users,  (r.active_users * 1.0) / s.cohort_users AS retention_rate FROM retained_counts r JOIN cohort_sizes s   ON r.cohort_week = s.cohort_week  AND r.channel = s.channel ORDER BY r.cohort_week, r.channel, r.week_number;

Esse recorte é poderoso para decisões práticas: priorizar canais que trazem usuários com maior retenção, ou ajustar expectativa de LTV por canal.

Retenção baseada em evento-chave (ativação/valor)

Em produto, “sessão” pode ser um sinal fraco. Muitas vezes, você quer retenção de um evento de valor, como 'created_project', 'sent_message' ou 'completed_checkout'. Basta trocar o filtro do CTE activity para o evento desejado. O cuidado é que a retenção ficará naturalmente menor, mas mais alinhada ao que importa.

Retenção por conta (B2B) em vez de usuário

Em B2B, a unidade de valor costuma ser a conta. Você pode construir coortes por account_id e considerar a conta “ativa” se qualquer usuário da conta gerou atividade. O ajuste principal é trocar user_id por account_id na derivação de coorte e nas contagens distintas.

Armadilhas comuns e como evitar

1) Coorte por cadastro vs coorte por primeira atividade

Coorte por cadastro é útil para medir conversão do onboarding, mas pode distorcer retenção de uso se muitos cadastros não ativam. Coorte por primeira atividade tende a ser mais “justa” para retenção de uso porque todos começaram de fato. Uma prática comum é manter as duas visões: uma para funil de ativação e outra para retenção pós-ativação.

2) Definição de “ativo” muito permissiva

Se “ativo” for qualquer evento (incluindo eventos automáticos de sistema), a retenção pode parecer artificialmente alta. Prefira eventos que representem ação do usuário/conta.

3) Duplicidade de eventos e contagem inflada

Mesmo contando DISTINCT user_id, duplicidades podem afetar outras métricas (ex.: número de sessões). Para retenção básica, foque em “usuário ativo” (distinct) e, se precisar de intensidade, calcule separadamente (ex.: sessões por usuário ativo).

4) Janelas de tempo incompletas

Coortes recentes ainda não tiveram tempo de chegar em semanas mais altas. Em relatórios, é comum limitar week_number a um máximo (ex.: 0 a 12) e/ou filtrar coortes que tenham maturidade suficiente para comparar (ex.: só coortes com pelo menos 8 semanas desde o início).

5) Timezone e datas truncadas

Se eventos estão em UTC e o negócio opera em horário local, a fronteira de dia/semana pode “quebrar” sessões em períodos errados. Padronize o timezone antes de truncar para semana/mês.

Retenção básica para relacionamento: exemplo com interações

Suponha tabelas contacts (contatos) e interactions (interações), onde interactions tem: contact_id, interaction_at, interaction_type. Você quer coorte por primeira interação e retenção mensal por qualquer interação.

WITH cohort_start AS (  SELECT     contact_id,    MIN(interaction_at) AS cohort_at  FROM interactions  GROUP BY contact_id), cohort_labeled AS (  SELECT     contact_id,    DATE_TRUNC('month', cohort_at) AS cohort_month  FROM cohort_start), activity AS (  SELECT     contact_id,    DATE_TRUNC('month', interaction_at) AS activity_month  FROM interactions), activity_with_cohort AS (  SELECT     a.contact_id,    c.cohort_month,    DATE_DIFF('month', c.cohort_month, a.activity_month) AS month_number  FROM activity a  JOIN cohort_labeled c ON a.contact_id = c.contact_id  WHERE a.activity_month >= c.cohort_month), cohort_sizes AS (  SELECT cohort_month, COUNT(*) AS cohort_contacts  FROM cohort_labeled  GROUP BY cohort_month), retained_counts AS (  SELECT     cohort_month,    month_number,    COUNT(DISTINCT contact_id) AS active_contacts  FROM activity_with_cohort  GROUP BY cohort_month, month_number) SELECT   r.cohort_month,  r.month_number,  s.cohort_contacts,  r.active_contacts,  (r.active_contacts * 1.0) / s.cohort_contacts AS retention_rate FROM retained_counts r JOIN cohort_sizes s ON r.cohort_month = s.cohort_month ORDER BY r.cohort_month, r.month_number;

Esse modelo responde: “de todos os contatos que tiveram a primeira interação em um mês, quantos voltaram a interagir nos meses seguintes?”. Para CRM, você pode segmentar por origem do lead, responsável, etapa do funil no primeiro contato, ou tipo de interação inicial.

Checklist de validação antes de publicar o relatório

• Coorte faz sentido? Verifique alguns usuários/contatos manualmente: a data de coorte é realmente a primeira ocorrência do evento de início?
• Week/month 0 está coerente? Se a atividade é “sessão” e a coorte é “primeira sessão”, a retenção no período 0 tende a ser alta.
• Semanas negativas foram eliminadas? Garanta que não há week_number ou month_number negativos.
• Taxas não passam de 100%? Se passar, há duplicidade no denominador (tamanho da coorte) ou erro de junção.
• Comparação justa entre coortes? Coortes antigas têm mais semanas observadas; coortes novas não devem ser comparadas em horizontes que ainda não existem para elas.

Extensão prática: tabela pronta para heatmap (formato pivotado)

Muitos times gostam de visualizar retenção como uma matriz (coorte nas linhas, semana/mês nas colunas). O pivot depende do banco, mas a ideia é transformar week_number em colunas. Se seu banco não tiver pivot nativo, você pode usar agregações condicionais:

WITH base AS (  -- aqui entra a query que gera: cohort_week, week_number, retention_rate  SELECT     cohort_week,    week_number,    retention_rate  FROM retention_result) SELECT   cohort_week,  MAX(CASE WHEN week_number = 0 THEN retention_rate END) AS w0,  MAX(CASE WHEN week_number = 1 THEN retention_rate END) AS w1,  MAX(CASE WHEN week_number = 2 THEN retention_rate END) AS w2,  MAX(CASE WHEN week_number = 3 THEN retention_rate END) AS w3,  MAX(CASE WHEN week_number = 4 THEN retention_rate END) AS w4 FROM base GROUP BY cohort_week ORDER BY cohort_week;

Esse formato é útil para dashboards e para leitura rápida. Para manter a query sustentável, limite o horizonte (por exemplo, até 12 semanas) e gere as colunas necessárias.