Cohort analysis in SQL

L’analisi di coorte non è una definizione da memorizzare, è un modo di leggere i dati che parte sempre da una domanda concreta e arriva a una scelta. Quando funziona bene, collega la domanda di partenza, il dato che osservi, il controllo che applichi e l’azione che ne deriva. Il lavoro tecnico resta ancorato a una decisione che qualcun altro può verificare, anche quando i numeri sono incerti.

Il problema che si vuole risolvere

Capita spesso di vedere un picco nelle nuove iscrizioni e di non sapere cosa farne. Quegli utenti restano attivi nei mesi successivi, oppure il picco è solo un’onda passeggera, magari spinta da una campagna marketing rumorosa? Il totale degli utenti attivi non aiuta, perché impasta insieme chi è arrivato ieri e chi era già qui da un anno. L’analisi di coorte serve proprio a separare i gruppi in base al momento di ingresso e a osservare come ciascun gruppo si comporta nel tempo, così la retention reale smette di essere una media e diventa una curva leggibile.

Come funziona, in concreto

Il cuore del metodo è una matrice. Sulle righe stanno le coorti, cioè i gruppi di utenti definiti dal momento in cui hanno compiuto per la prima volta una certa azione, di solito il mese di registrazione. Sulle colonne stanno i periodi di osservazione successivi: il mese 0, il mese 1, e così via. Dentro le celle finisce la metrica che interessa, tipicamente la percentuale di utenti ancora attivi.

Da questa struttura nascono le domande che contano davvero. La retention sta migliorando nel tempo? I nuovi iscritti restano più a lungo di quelli di sei mesi fa? L’ultima modifica al prodotto ha cambiato il comportamento sul lungo periodo? Sono tutte domande a cui un singolo numero aggregato non sa rispondere, mentre una matrice di coorte sì.

Per non perdere di vista la logica, conviene tenere a mente come ogni fase si collega alla successiva.

Fase	Cosa chiarire	Output
Domanda	Quale scelta reale deve migliorare?	Decisione da prendere
Misura	Quale segnale rappresenta il problema?	Metrica o dato sorgente
Controllo	Quale baseline rende il risultato interpretabile?	Confronto credibile
Azione	Che cosa cambia dopo l’analisi?	Prossimo passo operativo

Lo schema operativo

Perché l’analisi sia riproducibile e non solo suggestiva, vale la pena fissare alcuni punti prima di scrivere una riga di SQL. L’unità di analisi va dichiarata: lavoriamo per riga, per partizione, per finestra, per coorte o per metrica temporale? Va scelto il segnale principale, che sia correttezza, performance, presenza di duplicati, grain o stabilità del risultato. Serve una baseline di confronto, che può essere il periodo precedente, un gruppo comparabile, un benchmark o uno scenario controfattuale. Va detto cosa ci aspettiamo di produrre, che sia una query, un modello intermedio, un test SQL o un pattern riusabile. E va riconosciuto il rischio principale, quasi sempre lo stesso: scambiare un numero disponibile per una prova sufficiente.

Elemento	Specifica richiesta
Unità di analisi	riga, partizione, finestra, join, coorte o metrica temporale
Segnale	correttezza, performance, duplicati, grain e stabilità del risultato
Baseline	Periodo precedente, gruppo comparabile, benchmark o scenario controfattuale
Decisione	query, modello intermedio, test SQL o pattern riusabile
Rischio	Scambiare un numero disponibile per una prova sufficiente

Costruzione della matrice di coorte in SQL

Partiamo da una tabella user_activity con user_id, signup_date e activity_date, e vogliamo la retention mensile. La strada è in tre passi: assegnare ogni utente alla propria coorte definita dal mese di iscrizione, calcolare per ogni utente e mese di attività il numero di periodo rispetto al mese di iscrizione, e infine ricavare la percentuale di utenti attivi per ogni coorte e periodo.

Il primo passo definisce le coorti e le attività mensili.

WITH user_cohorts AS (
  SELECT user_id, DATE_TRUNC('month', signup_date) AS cohort_month
  FROM users
),
activity_by_month AS (
  SELECT DISTINCT uc.cohort_month, uc.user_id, DATE_TRUNC('month', a.activity_date) AS activity_month
  FROM user_cohorts uc
  JOIN user_activity a ON uc.user_id = a.user_id
)

Poi si calcola il numero di periodo, dove lo zero corrisponde al mese di iscrizione.

cohort_activity AS (
  SELECT cohort_month, user_id, activity_month,
    (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
)

Infine si calcola la retention e si fa il pivot della matrice.

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,
    ROUND(COUNT(DISTINCT ca.user_id) * 100.0 / cs.num_users, 1) AS retention_pct
  FROM cohort_activity ca
  JOIN cohort_size cs ON ca.cohort_month = cs.cohort_month
  GROUP BY ca.cohort_month, ca.period_number, cs.num_users
)
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;

Un caso che chiarisce il punto

Durante la pandemia Peloton vide un boom di iscrizioni. Guardando il totale, sembrava una crescita solida. L’analisi di coorte raccontò un’altra storia: le coorti nate nel picco pandemico mostravano una retention a 12 mesi molto più bassa di quelle precedenti. Quel segnale permise di riorientare il marketing verso gli utenti con maggiore propensione a restare, una scelta che il numero aggregato avrebbe nascosto. È esattamente la differenza tra contare gli iscritti e capire chi resta.

Dove ci si sbaglia di solito

L’errore più frequente è confondere coorte e periodo, mescolando utenti di età diverse e cancellando i trend che si volevano osservare. Subito dopo viene il denominatore sbagliato per la retention: usare gli utenti attivi nel periodo precedente invece degli iscritti gonfia o sgonfia la curva senza che ce ne accorgiamo. Vanno gestiti con cura anche gli utenti iscritti a fine mese, che altrimenti mostrano una retention falsamente bassa nel primo periodo. E va tenuta d’occhio la stagionalità, perché confrontare coorti con pattern naturali diversi senza correzioni porta a conclusioni che dicono più sul calendario che sul prodotto.

C’è poi un errore più sottile, che riguarda l’uso del risultato più che il calcolo. Trattare la matrice come una verità generale, invece che come evidenza condizionata da un certo periodo e da certe assunzioni, è il modo più rapido per prendere decisioni fragili. Prima di agire conviene sempre ricontrollare la baseline, le assunzioni e il costo dell’errore se la lettura fosse sbagliata.

Esercizio

Si può lavorare su tre livelli di difficoltà crescente. Per cominciare, scrivi una query che calcoli la retention settimanale per coorti definite settimanalmente: stesso impianto della versione mensile, granularità diversa. Salendo di un gradino, prendi la coorte più recente, calcola la curva di retention completa e individua il punto di flesso usando una funzione finestra come LAG. Per la versione più ambiziosa, segmenta ogni coorte in quintili in base all’attività nel mese 0 e confronta la retention a 3 mesi quintile per quintile, così da vedere se chi parte più attivo resta anche più a lungo.

Come materiale di partenza vanno bene dati di ordini, eventi, sessioni, coorti e revenue. In assenza di dati reali, costruisci un dataset sintetico che abbia almeno una dimensione temporale, dei segmenti e delle metriche di outcome, altrimenti l’esercizio non mostra nulla di interessante.

Domande di controllo

Per verificare di aver assorbito il metodo, prova a rispondere a queste domande. Qual è la differenza tra una coorte e un segmento? Quali sono i tre passaggi essenziali per costruire una query di coorte? Perché le coorti di fine mese mostrano una retention bassa nel primo periodo? Quale errore comune può invalidare un’intera analisi di coorte? E, la più difficile, come spiegheresti i risultati di una coorte a uno stakeholder che non conosce SQL?

Riepilogo operativo

L’analisi di coorte in SQL aiuta a decidere sotto incertezza perché separa gli utenti per momento di ingresso e ne osserva il comportamento nel tempo, invece di affidarsi a un totale che impasta tutto. La sua utilità dipende da tre cose: assunzioni dichiarate, coorti definite con cura e un legame esplicito tra il risultato e la decisione che dovrebbe orientare. Senza quel legame, resta un grafico interessante e nient’altro.