Cohort analysis in SQL

Ogni mese di iscrizioni è una generazione a sé, e solo confrontando le generazioni tra loro capisci se il prodotto trattiene davvero. La matrice di coorte trasforma questa intuizione in una tabella leggibile: una riga per coorte, una colonna per periodo, e un denominatore che non cambia mai. In questa lezione la costruisci passo passo in SQL, fino ai controlli che impediscono di raccontare storie false.

L’idea in una frase

The cohort analysis misura, per ogni generazione di utenti nata nello stesso mese, quanta parte resta attiva periodo dopo periodo a parità di età dalla nascita.

Il percorso in cinque passi

1. Assegna ogni utente alla coorte del mese di iscrizione con DATE_TRUNC al mese e deduplica le attività a granularità mensile con SELECT DISTINCT.
2. Calculate period_number come distanza in mesi tra mese di attività e mese di nascita con formula robusta al cambio di anno.
3. Fissa il denominatore alla numerosità iniziale della coorte (cohort_size) e calcola retention_pct per coorte e periodo.
4. Ruota la tabella con aggregazione condizionata (MAX(CASE WHEN ...)) per ottenere una colonna per periodo e una riga per coorte.
5. Controlla che nessuna cella superi il 100% e marca come incomplete le coorti recenti con pochi periodi osservabili.

Il problema che si vuole risolvere

Un picco di iscrizioni sembra una buona notizia, finché qualcuno chiede quanti di quegli utenti sono ancora attivi tre mesi dopo. Il conteggio degli utenti attivi mensili non risponde: somma in un unico numero gli iscritti di ieri e i clienti di due anni fa. Una campagna può portare diecimila iscritti che spariscono in trenta giorni. Il totale cresce, poi si sgonfia, e non spiega nulla.

L’analisi di coorte ribalta la prospettiva: il momento di ingresso diventa la chiave di lettura. Ogni gruppo di utenti nati nello stesso periodo viene seguito separatamente nel tempo. La domanda diventa precisa e operativa, e si aggancia a due leve concrete: il canale di acquisizione e la revisione dell’onboarding.

Phase	What to clarify	Output
Question	Which choice needs to improve?	Decision to make
Measure	Quale segnale rappresenta il comportamento?	Metrica e sorgente
Control	Quale baseline rende il confronto credibile?	Confronto tra coorti
Action	Cosa cambia dopo la lettura?	Next operational step

Lo schema operativo prima di scrivere la query

Prima di toccare il codice conviene fissare cinque punti, perché quasi tutti gli errori di coorte nascono da ambiguità lasciate aperte. L’unità di analisi and the segnale vanno dichiarati con soglia esplicita. La baseline e l’output atteso devono restare stabili tra esecuzioni successive. E il rischio da presidiare è sempre lo stesso: un denominatore scelto male.

Element	Requested specification
Unit of analysis	Utente per coorte mensile e periodo di osservazione
Signal	Presenza di attività nel periodo, soglia dichiarata
Baseline	Coorti adiacenti e media delle coorti mature
Decision	Matrice con percentuali e numerosità assolute
Risk	Denominatore incoerente tra coorti e periodi

Le coorti recenti sono troncate a destra, cioè incomplete per costruzione. La coorte del mese scorso ha un solo periodo osservabile, mentre quella di sei mesi fa ne ha sei. Ogni colonna va confrontata solo tra coorti che l’hanno completata. La numerosità va sempre mostrata accanto alla percentuale, perché un 100% su quattro utenti non è un segnale.

Assegnare ogni utente alla sua coorte

Si parte da due tabelle, users e user_activity. Il primo blocco assegna la coorte con DATE_TRUNC al mese e riduce l’attività a granularità mensile con SELECT DISTINCT. Senza questa deduplicazione, un utente iperattivo pesa più di uno che accede una sola volta e il conteggio degli attivi si gonfia. La scelta del mese è una convenzione operativa: con alta frequenza ha senso la settimana, con abbonamenti annuali il trimestre. L’importante è che coorte e periodo usino la stessa unità temporale, altrimenti period_number diventa ambiguo.

-- Passo 1: coorte di nascita e attività normalizzata al mese
WITH user_cohorts AS (
  SELECT
    user_id,
    -- mese di iscrizione: definisce la coorte di appartenenza
    DATE_TRUNC('month', signup_date) AS cohort_month
  FROM users
),
activity_by_month AS (
  SELECT DISTINCT
    uc.cohort_month,
    uc.user_id,
    -- mese di attività: granularità di osservazione
    DATE_TRUNC('month', a.activity_date) AS activity_month
  FROM user_cohorts AS uc
  JOIN user_activity AS a ON uc.user_id = a.user_id
)
SELECT * FROM activity_by_month LIMIT 10;

Calcolare periodo e percentuale di retention

Il secondo passo trasforma due date in un period_number intero: lo zero è il mese di nascita della coorte, l’uno è il mese successivo. Il calcolo con (anno * 12 + mese) evita gli errori nel passaggio da dicembre a gennaio, dove una semplice differenza tra mesi darebbe un numero negativo. Il terzo passo fissa il denominatore (cohort_size) una volta sola alla nascita e lo riusa per ogni periodo. Il pivot finale rende la matrice leggibile, con una colonna per periodo. Si legge per righe seguendo il decadimento e per colonne confrontando generazioni diverse allo stesso stadio di vita.

-- Passo 2: distanza in mesi tra attività e nascita della coorte
cohort_activity AS (
  SELECT
    cohort_month,
    user_id,
    activity_month,
    -- differenza robusta al cambio d'anno
    (EXTRACT(YEAR FROM activity_month) * 12 + EXTRACT(MONTH FROM activity_month))
    - (EXTRACT(YEAR FROM cohort_month) * 12 + EXTRACT(MONTH FROM cohort_month))
      AS period_number
  FROM activity_by_month
)
SELECT cohort_month, period_number, COUNT(DISTINCT user_id) AS utenti
FROM cohort_activity
GROUP BY cohort_month, period_number
ORDER BY cohort_month, period_number;

-- Passo 3: numerosità fissa e percentuale per coorte e periodo
cohort_size AS (
  SELECT cohort_month, COUNT(DISTINCT user_id) AS num_users
  FROM user_cohorts
  GROUP BY cohort_month
),
cohort_retention AS (
  SELECT
    ca.cohort_month,
    ca.period_number,
    COUNT(DISTINCT ca.user_id) AS active_users,
    cs.num_users AS cohort_size,
    -- denominatore fisso alla nascita: mai ricalcolato sul periodo
    ROUND(COUNT(DISTINCT ca.user_id) * 100.0 / cs.num_users, 1) AS retention_pct
  FROM cohort_activity AS ca
  JOIN cohort_size AS cs ON ca.cohort_month = cs.cohort_month
  GROUP BY ca.cohort_month, ca.period_number, cs.num_users
)
SELECT * FROM cohort_retention ORDER BY cohort_month, period_number;

-- Pivot: una colonna per periodo, una riga per coorte
SELECT
  cohort_month,
  MAX(CASE WHEN period_number = 0 THEN retention_pct END) AS month_0,
  MAX(CASE WHEN period_number = 1 THEN retention_pct END) AS month_1,
  MAX(CASE WHEN period_number = 2 THEN retention_pct END) AS month_2,
  MAX(CASE WHEN period_number = 3 THEN retention_pct END) AS month_3
FROM cohort_retention
GROUP BY cohort_month
ORDER BY cohort_month;

Leggere la matrice senza farsi ingannare dai totali

Una matrice ben costruita si legge in due direzioni. In orizzontale si segue il decadimento naturale: calo rapido nei primi periodi, poi appiattimento sullo zoccolo di utenti fedeli. In verticale si confrontano coorti diverse allo stesso stadio. Se la colonna del mese tre sale, il prodotto sta davvero migliorando; se scende mentre il totale cresce, la crescita è fatta di utenti che non restano. La regola pratica è tenere retention_pct e numerosità sempre insieme: una coorte piccola produce percentuali ballerine, e le coorti recenti vanno marcate come incomplete.

Dove l’analisi deraglia più spesso

Il primo errore è confondere coorte e periodo, mescolando utenti di età diversa nella stessa media. Il secondo è il denominatore mobile: dividere gli attivi del mese tre per gli attivi del mese due, invece che per gli iscritti iniziali. Poi vengono i dettagli di bordo. Gli iscritti di fine mese hanno pochi giorni per agire. La stagionalità rende non confrontabili coorti nate in mesi diversi. La matrice resta un’evidenza condizionata da periodo e definizione di attività, quindi prima di agire conviene ricontrollare baseline e soglie.

Verificare i numeri prima di fidarsi

Tre controlli rapidi separano un’analisi solida da una suggestiva. La quadratura impone che la somma degli attivi per periodo non superi mai la numerosità della coorte. La stabilità alla ridefinizione richiede che, cambiando la soglia di attività, la forma delle curve resti simile. Il confronto con una misura indipendente, come rinnovi o login, chiede coerenza tra fonti diverse. Il punto di flesso, con scarto tra periodi consecutivi calcolato via LAG, dice dove l’onboarding smette di contare e inizia la fedeltà vera.

-- Sanity check: nessuna cella sopra il 100%, coorti quadrate col totale
SELECT
  cohort_month,
  -- ogni periodo deve restare entro la numerosità iniziale
  MIN(retention_pct) AS min_pct,
  MAX(retention_pct) AS max_pct,
  SUM(CASE WHEN retention_pct > 100 THEN 1 ELSE 0 END) AS celle_anomale
FROM cohort_retention
GROUP BY cohort_month
ORDER BY cohort_month;

-- Punto di flesso: dove il calo tra periodi consecutivi si attenua
SELECT
  cohort_month,
  period_number,
  retention_pct,
  -- differenza rispetto al periodo precedente della stessa coorte
  retention_pct - LAG(retention_pct) OVER (
    PARTITION BY cohort_month ORDER BY period_number
  ) AS variazione_punti
FROM cohort_retention
ORDER BY cohort_month, period_number;

Portare la coorte dentro le decisioni di prodotto

L’analisi vale solo se cambia una scelta. Il primo uso è segmentare per canale o piano tariffario, aggiungendo la dimensione alla chiave di coorte. Il secondo è valutare gli esperimenti, confrontando la curva delle coorti trattate con quella delle precedenti. Per la segmentazione per intensità d’uso nel mese zero si dividono gli utenti in quintili con NTILE e si confronta la retention a tre mesi. Per rendere l’analisi ripetibile conviene versionare la query, con soglia di attività e fuso orario dichiarati, e ricalcolare la matrice con la stessa definizione.

In sintesi: usa la granularità mensile come base, scendi alla settimana solo con coorti di migliaia di utenti e tieni fisso il denominatore alla nascita.

Verdetto: la granularità mensile vince come default: scendi alla settimana solo con coorti di migliaia di utenti e tieni sempre il denominatore fisso alla nascita.

Il caso Peloton: quando il totale inganna

Peloton, durante il picco pandemico del 2020 e 2021, mostrò un boom di iscrizioni trainato dalle palestre chiuse. La matrice di coorte raccontò una storia diversa dal totale: le coorti nate nel picco ebbero una retention a dodici mesi molto più bassa delle coorti precedenti. Chi guardava solo il totale pianificò scorte e assunzioni come se la crescita fosse strutturale. Chi lesse le coorti capì che la domanda era transitoria, e nel febbraio 2022 l’azienda annunciò un taglio di circa 2800 posti con revisione delle previsioni.

Domande per chiudere la lezione

1. Quale denominatore rende confrontabile la retention tra coorti e perché deve restare fisso alla nascita?
2. Come distingui una coorte matura da una troncata a destra nella lettura della matrice?
3. Perché la deduplicazione mensile con SELECT DISTINCT evita retention sopra il cento per cento?
4. Quando una JOIN tra coorte e attività gonfia il tasso e quale JOIN sceglieresti al suo posto?