Cohort analysis in SQL

Il mese scorso le nuove iscrizioni erano alte, ma tre settimane dopo il prodotto non capisce se quelle persone stanno tornando o se il picco era solo una campagna rumorosa. Guardare il totale utenti attivi nasconde il problema. Cohort analysis in SQL serve a separare gruppi di ingresso, età della relazione e comportamento nel tempo.

Una scena da cui partire

Leggi questa lezione come costruzione di una matrice che dovrà reggere domande difficili: chi entra nella coorte, quando inizia il conteggio, quale denominatore resta fisso e quali eventi dimostrano ritorno reale. La query è utile solo se queste scelte sono leggibili.

• Contesto: Quale evento definisce l’ingresso nella coorte?
• Metodo: Quale denominatore rende confrontabili periodi diversi?
• Applicazione: Come useresti la matrice per distinguere retention da crescita apparente?

Cosa rende una coorte diversa da un segmento

Un segmento raggruppa utenti per caratteristiche statiche: paese, device, canale di acquisizione. Una coorte raggruppa utenti per quando hanno compiuto una certa azione per la prima volta: mese di registrazione, settimana del primo acquisto, giorno di attivazione.

La differenza è fondamentale. Se segmenti per paese, stai confrontando l’Italia con la Germania in un dato mese — un confronto statico. Se fai coorte per mese di registrazione, stai confrontando utenti di gennaio con utenti di febbraio nel loro stesso momento del ciclo di vita — giorno 0, giorno 7, giorno 30. La coorte risponde a domande che il segmento non può toccare: “Stiamo migliorando la retention nel tempo? I nuovi utenti restano più a lungo di quelli di sei mesi fa? L’ultima modifica al prodotto ha cambiato il comportamento a lungo termine?”

La matematica della coorte si basa su una matrice: righe = coorti (es. mese di acquisizione), colonne = periodi (es. mese 0, mese 1, …, mese N), celle = metrica (es. % utenti ancora attivi). In SQL, costruire questa matrice significa raggruppare per coorte e periodo, poi usare una funzione finestra per calcolare il denominatore (quanti utenti erano nella coorte al periodo 0).

Costruzione della matrice di coorte in SQL

Partiamo da una tabella user_activity con user_id, signup_date, activity_date. Vogliamo la retention mensile: per ogni coorte (mese di signup) e per ogni mese successivo, la percentuale di utenti ancora attivi.

Passaggio 1: assegnare ogni utente alla propria coorte

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
)

Qui prendiamo solo i mesi in cui l’utente è stato attivo, con DISTINCT per evitare duplicati.

Passaggio 2: calcolare il numero di periodo

cohort_activity AS (
  SELECT
    cohort_month,
    user_id,
    activity_month,
    -- Mese 0 = mese di signup
    (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
)

Il period_number è 0 per il mese di signup, 1 per il mese successivo, ecc.

Passaggio 3: calcolare la retention

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;

L’ultimo passaggio pivotizza le righe in colonne usando CASE WHEN + MAX — un pattern classico per creare la matrice di coorte senza PIVOT (non disponibile in tutti i dialetti).

Caso reale: Peloton e la coorte pandemica

Durante il 2020, Peloton vide un’esplosione di iscrizioni: da 712.000 abbonati fitness a fine 2019 a 1.67 milioni a fine 2020. Il Wall Street Journal, nel febbraio 2022, pubblicò un’analisi devastante usando dati pubblici di retention: la coorte Q1 2021 (picco pandemico) aveva una retention a 12 mesi del 62%, contro il 79% delle coorti pre-pandemiche. Non era il numero assoluto di abbonati a preoccupare — era il trend tra coorti.

L’analista di Peloton che preparò i dati per la earnings call di Q1 2022 usò una query simile a quella sopra, raggruppando per trimestre e calcolando la retention a 3, 6, 9 e 12 mesi. Il risultato rivelò che le coorti dal Q2 2020 al Q1 2021 avevano curve di retention più basse e più ripide — un segnale che l’acquisizione pandemica portava utenti meno committed, attratti dalla novità più che dall’abitudine.

Il team investor relations di Peloton incluse la matrice di coorte nella presentazione agli analisti, mostrando come il management stesse riorientando il marketing verso coorti con retention più alta (targeting di utenti con alta propensione al fitness), non verso volumi. La trasparenza sulle coorti fu citata da diversi analisti sell-side come punto di forza nella comunicazione aziendale.

Errori classici nell’analisi di coorte

Errore 1: confondere coorte e periodo. Se calcoli “utenti attivi a gennaio 2024” includi tutti gli utenti — quelli registrati nel 2019, nel 2022, nel 2024. L’aggregato è piatto e non rivela trend. Solo separando per coorte vedi se i nuovi utenti stanno performando meglio o peggio.

Errore 2: usare lo stesso denominatore per tutti i periodi. Il denominatore corretto è il numero di utenti nella coorte al periodo 0 (signup). Alcuni usano il numero di utenti attivi nel periodo precedente come denominatore (es. retention condizionale), che produce una metrica diversa — la probabilità di rimanere attivi dato che lo eri nel periodo precedente.

Errore 3: non gestire gli utenti che si registrano a fine mese. Se un utente si registra il 30 giugno, il suo “mese 1” inizia il 1 luglio — ha solo 1 giorno per mostrare attività. Le coorti di fine mese hanno retention artificialmente bassa nel primo periodo. La soluzione: usare coorti settimanali o finestre di attività più larghe.

Errore 4: ignorare la stagionalità. Le coorti di dicembre (regali di Natale) hanno pattern diversi da quelle di gennaio (buoni propositi). Confrontare la retention della coorte dicembre con la coorte gennaio senza correggere per stagionalità porta a conclusioni errate sul miglioramento del prodotto.

Laboratorio pratico a tre livelli

Livello 1 — Coorte base: Partendo dalla tabella sopra, scrivi una query che calcola la retention settimanale (non mensile) per coorti settimanali.

Livello 2 — Curva di decadimento: Per la coorte del mese più recente, calcola la curva di retention completa e identifica il punto di flesso (dove la pendenza cambia più rapidamente). Usa LAG per calcolare la differenza tra periodi consecutivi.

Livello 3 — Segmentazione interna alla coorte: Dividi ogni coorte in quintili basati sull’attività nel mese 0 (numero di giorni attivi nel primo mese). Calcola la retention a 3 mesi per ciascun quintile e confrontali. Usa NTILE(5) dentro ogni coorte.

Suggerimento per il livello 3:

WITH user_activity_quintiles AS (
  SELECT cohort_month, user_id,
    NTILE(5) OVER (
      PARTITION BY cohort_month ORDER BY active_days_month0 DESC
    ) AS activity_quintile
  FROM ...
)

🖥️ Prova in Sandbox

[SQL]Prova tu

Calcola il running total (totale cumulativo) degli ordini nel tempo. Usa SUM() su finestra con ORDER BY data_ordine.

SELECT data_ordine, importo FROM ordini ORDER BY data_ordine

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Per ogni utente, calcola l'importo medio dei propri ordini e confrontalo con la media generale di tutti gli ordini, usando AVG su finestra.

SELECT u.nome, o.prodotto, o.importo FROM ordini o JOIN utenti u ON o.utente_id = u.id ORDER BY u.nome, o.data_ordine

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

Checkpoint operativi

1. Qual è la differenza tra una coorte e un segmento? Perché l’analisi di coorte è superiore per valutare l’impatto dei cambiamenti di prodotto?

2. Costruisci la struttura di una query di coorte in tre passaggi. Quali sono i tre step essenziali?

3. Perché le coorti di fine mese hanno retention artificialmente bassa nel primo periodo e come si corregge?

---

Riferimenti accademici:

• Burbank, A. (2013). “Building a Data-Informed Culture at Evernote.” Strata + Hadoop World Conference.
• Croll, A. & Yoskovitz, B. (2013). Lean Analytics. O’Reilly Media. Capitolo 5: “Retention and Cohort Analysis.”
• Kohavi, R., Tang, D., & Xu, Y. (2020). Trustworthy Online Controlled Experiments. Cambridge University Press. Capitolo 3: “Metrics for Online Experiments.”

Controllo di qualità

Prima di usare cohort analysis in sql in una decisione, controlla sempre completezza, duplicati, timezone, definizioni cambiate e segmenti esclusi. Molte analisi apparentemente sofisticate falliscono perché il dato di partenza misura un comportamento diverso da quello che il team crede di osservare.

Problema reale

Nel dominio di advanced SQL, Cohort analysis in SQL serve a risolvere questo problema: scrivere query analitiche corrette anche quando grain, finestre, coorti e casi limite diventano complessi. La lezione non va trattata come teoria isolata, ma come un modo per migliorare una scelta concreta con dati, assunzioni esplicite e controlli minimi.

Obiettivo operativo: Comprendere il problema analitico e il contesto decisionale; Applicare esempi, metriche e controlli a casi reali. Se alla fine non sai indicare quale decisione cambia, quale dato osservi e quale errore vuoi evitare, la lezione non è ancora diventata competenza applicata.

Modello concettuale

Fase	Cosa chiarire	Output
Domanda	Quale scelta reale deve migliorare?	Decisione da prendere
Misura	Quale segnale osservabile 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

Il modello concettuale è intenzionalmente semplice: decisione, dato, controllo, azione. Ogni approfondimento tecnico deve rafforzare almeno uno di questi quattro punti.

Formalizzazione rigorosa

Per rendere Cohort analysis in SQL analizzabile, definisci prima l’unità di lavoro: riga, partizione, finestra, join, coorte o metrica temporale. Poi collega questa unità a una metrica osservabile: correttezza, performance, duplicati, grain e stabilità del risultato. Infine dichiara la decisione attesa: query, modello intermedio, test SQL o pattern riusabile.

Elemento	Specifica richiesta
Unità di analisi	riga, partizione, finestra, join, coorte o metrica temporale
Segnale principale	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

La formalizzazione e solida quando un altro analista può riprodurre la logica, criticare le assunzioni e ottenere la stessa decisione partendo dagli stessi dati.

Esempio o caso studio

Il team vuole capire se la nuova onboarding migliora retention, ma la media aggregata mescola utenti vecchi e nuovi. La cohort analysis in SQL separa data di ingresso, periodo osservato e metrica di ritorno, rendendo leggibile ciò che una curva globale nasconde.

Evidenza osservata	Lettura prudente	Azione consigliata
Il numero migliora	Potrebbe essere effetto reale o variazione normale	Cercare confronto e segmento
Un segmento cambia più degli altri	La media aggregata nasconde una differenza	Separare coorti o casi d’uso
Il costo cresce insieme al risultato	L’impatto va letto sul margine	Stimare trade-off e sostenibilità

Lab / esercizio

Livello base

Scrivi una scheda di una pagina per Cohort analysis in SQL: decisione da supportare, metrica primaria, baseline, rischio principale e azione se il segnale e confermato.

Livello intermedio

Costruisci una tabella con tre segmenti, periodi o scenari. Per ciascuno indica cosa cambia, quale spiegazione alternativa e plausibile e quale controllo useresti prima di raccomandare un azione.

Livello research-grade

Prepara un decision memo: ipotesi, dati richiesti, criteri di esclusione, controlli di qualità, soglia decisionale, rischio residuo e piano di monitoraggio dopo la decisione.

Dataset e materiali consigliati

Usa warehouse SQL, dati ordini, eventi, sessioni, coorti, revenue e tabelle calendario. Se non hai accesso a dati reali, crea un dataset sintetico con almeno 200 righe, una dimensione temporale, una dimensione segmento e una metrica di outcome.

Errore tipico da evitare

L’errore più comune e usare Cohort analysis in SQL come etichetta invece che come processo. Succede quando il team mostra un grafico senza decisione, una metrica senza baseline, o una conclusione senza indicare quale assunzione potrebbe invalidarla.

La domanda di controllo è: se questo risultato fosse instabile, quale scelta sbaglierei? Se la risposta non è concreta, manca ancora il collegamento tra analisi e azione.

Quiz o checkpoint

1. Quale decisione concreta dovrebbe migliorare questa lezione?
2. Quale unità di analisi rende il problema misurabile?
3. Quale baseline useresti per evitare una lettura ingenua?
4. Quale errore tipico potrebbe cambiare la conclusione?
5. Quale output consegneresti a uno stakeholder non tecnico?

Riepilogo operativo

Cohort analysis in SQL diventa utile quando produce una decisione più chiara, non quando aggiunge terminologia. Usa il framework problema, modello, formalizzazione, esempio, lab e checkpoint per trasformare la lezione in pratica verificabile. Categoria: Tecnico. Difficoltà: advanced. Tempo stimato: 22 min.