Analisi dei coorti

Analisi coorte

L'analisi di coorte raggruppa gli oggetti (in genere gli utenti) in base a un unico evento di avvio e confronta per quanto tempo e per quanto tempo rimangono attivi e preziosi. Questo approccio separa l'effetto tempo del sistema (stagioni, promozioni) dall'effetto età coorte (giorni dalla partenza).

1) Definizioni di base

Cohort: molti giocatori uniti da un evento di nascita - registrazione, primo deposito, primo gioco, primo acquisto.
Asse calendario (calendar time) - Date effettive (2025-10-01,...).
Asse di età Cohort age: giorni/settimane dalla nascita (D0, D1,...).
Metriche di contenimento: D1/D7/D30 (Exact e Rolling), WAU/MAU, Stickover( DAU/MAU).
Monetizzazione: ARPU/ARPU, LTV cumulativa (su D7/D30/D90).
Unità di contabilità utente (user/master _ id) - Fissa nel passaporto.

2) Viste degli artigli e quando selezionarli

Coorti Acquisition: per data di registrazione/prima visita - valutazione dei canali di attrazione e onboarding.
Attivation/Monetization-coorti: per primo deposito/acquisto - valutazione early-monetizzazione e promo.
Feature - Per il primo uso della categoria fici/gioco, effetto release.
Coorti Behavior: RFM/pattern di partenza (ad esempio, mobile notturna).

3) Assi e griglie: come guardare la matrice

Matrice degli artigli: righe - coorti (calendario), colonne - età (D0... D90).
Stagionalità: confrontare le diagonali (stesso giorno di calendario) per separare gli effetti stagionali.
Normalizzazione: metriche relative (CR, quote) + cumulative (LTV), visualizzate entrambe.

4) Passaporto coorte e metriche (template)

5) Pseudonimo-SQL - Matrice di ritenzione (Exact Dn)

sql
WITH regs AS (
SELECT user_id, DATE_TRUNC('day', MIN(ts)) AS cohort_day
FROM event_register
GROUP BY 1
),
act AS (
SELECT user_id, DATE_TRUNC('day', ts) AS act_day
FROM event_activity
),
ages AS (
SELECT r. user_id, r. cohort_day, a. act_day,
(a. act_day - r. cohort_day) AS age_days
FROM regs r
JOIN act a ON a. user_id = r. user_id
),
exact AS (
SELECT cohort_day,
age_days,
COUNT(DISTINCT user_id) AS users_active
FROM ages
GROUP BY 1,2
),
coh_size AS (
SELECT cohort_day, COUNT(DISTINCT user_id) AS cohort_size
FROM regs GROUP BY 1
)
SELECT e. cohort_day,
e. age_days,
e. users_active::decimal / NULLIF(c. cohort_size,0) AS exact_retention
FROM exact e
JOIN coh_size c USING (cohort_day)
WHERE age_days IN (1,7,30,90)
ORDER BY cohort_day, age_days;

Rolling Dn (attività 1... n giorno)

sql
WITH days AS (... as above...),
roll AS (
SELECT cohort_day,
CASE WHEN age_days BETWEEN 1 AND 7 THEN 7
WHEN age_days BETWEEN 1 AND 30 THEN 30 END AS bucket,
COUNT(DISTINCT user_id) AS any_active
FROM days
WHERE age_days BETWEEN 1 AND 30
GROUP BY 1,2
)
SELECT r. cohort_day, r. bucket AS Dn,
r. any_active::decimal / s. cohort_size AS rolling_retention
FROM roll r
JOIN (SELECT cohort_day, COUNT(DISTINCT user_id) cohort_size FROM regs GROUP BY 1) s USING (cohort_day)
ORDER BY cohort_day, Dn;

6) Coorte LTV e monetizzazione

LTV cumulativo (Dn) - Importo del reddito per utente coorte a Dn.
ARPU/ARPPU: reddito per utente/pagatore Dn.
Il% dei paganti è una quota con un pagamento a Dn.

sql
WITH reg AS (
SELECT user_id, DATE_TRUNC('day', MIN(ts)) AS cohort_day
FROM event_register GROUP BY 1
),
pay AS (
SELECT user_id, amount, DATE_TRUNC('day', ts) AS pay_day
FROM fact_payments
),
ltv AS (
SELECT r. cohort_day,
(pay_day - r. cohort_day) AS age_days,
SUM(amount) AS rev
FROM reg r JOIN pay p USING (user_id)
WHERE pay_day >= r. cohort_day
GROUP BY 1,2
),
cum AS (
SELECT cohort_day, age_days,
SUM(rev) OVER (PARTITION BY cohort_day ORDER BY age_days ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rev_cum
FROM ltv
)
SELECT c. cohort_day, c. age_days,
c. rev_cum::decimal / NULLIF(sz. cohort_size,0) AS ltv_per_user
FROM cum c
JOIN (SELECT cohort_day, COUNT(DISTINCT user_id) cohort_size FROM reg GROUP BY 1) sz USING (cohort_day)
WHERE age_days IN (7,30,90)
ORDER BY cohort_day, age_days;

7) Survival/hazard per contenimento

Kaplan-Meier - La curva di sopravvivenza non singola (S (t)) è una percentuale di non distesi.
I modelli Hazard (Soh/Logit-to-Day) sono gli effetti dei segni (canale, paese, piattaforma, bonus, contenuti) sul rischio di deflusso.
Pratica: costruiamo KM per segmenti, poi spieghiamo la differenza con il modello hazard.

8) Stagionalità, TZ e calendario

TZ: memorizzare gli eventi in UTC, analizzarli in locale TZ di mercato; siate consistenti.
Calendario: vacanze/stipendi/partite/comunicati come bandiere; paragonate gli artigli a settimane simili.
La finestra scorrevole è per le riunioni settimanali/mensili, il multiplo di feste e periodi di rendicontazione.

9) Segmentazione e attribuzione

Segmenti: canale di attrazione, piattaforma/OS, geo, primo contenuto, prezzo/limiti, metodo di pagamento.
Assegnazione coorte: «chi ha portato» l'utente - Fissa l'algoritmo (last non-direct, data-driven).
Pesatura LTV: confrontare non solo CR, ma anche LTV (D30/D90) attraverso i canali/segmenti.

10) Visualizzazione

Colore termico della matrice coorte (CR/LTV).
Linee di tendenza D1/D7/D30 in calendario.
Grafica Survival/Hazard.
Bridge «ciò che ha cambiato LTV a D30» è il contributo dei paganti, la frequenza, l'assegno medio.

11) Esperimenti e causalità

A/B: onboording, tutoriali, paywall, offerenti. La metrica principale è D7/D30 Retention e LTV (D30).
Quasi-espatrio: DiD/controllo sintetico per l'estrazione sui mercati.
Modelli Uplift: target per aumentare il ritorno a r-attivazione (Qini/AUUC, uplift @ k).

12) Utilizzo e fornitura

Versioning: «RET_D7_vN», «LTV_D30_vN»; changelog quando cambia la definizione di attività/valuta.
SLO freschezza: coorti giornalieri - pronto fino alle 6:00; Il numero di dati è di 1 ora.
Qualità: eventi coverage, quota di duplicati, quota di bot/frode fuori dagli artigli.
Accesso: RLS/CLS, maschera PII; esportazione: solo aggregazioni.
Runbooks: caduta D1 (onboording), D7 (contenuto), livello di eventi/identità.

13) Errori frequenti (anti-pattern)

Miscelazione degli assi: confrontano le diverse età degli artigli in diverse stagioni senza modifiche.
Rolling vs Exact, interpretato come la stessa cosa.
Miscelazione delle unità: sessioni nel denominatore, utenti nel numeratore.
Aggregazione «media»: invece di sommare numeratori/denominatori.
Ignora calendario TZ - Sposta D1 ai confini giorni/festività.
Nessun filtro bot/frode/QA.
Gli account non conteggiati sono split/merge senza ponti di identità.

14) Foglio di assegno prima della pubblicazione del rapporto di coorte

• Definito evento nascita, unità, TZ, finestre attività
• Esclusi bot/frod/QA; identità riunite (golden record)
• Costruite matrici CR (Exact/Rolling) e LTV per D7/D30/D90
• Tenere conto del calendario/festività; segmenti per canale/piattaforma/geo
• Aggiunte le grafiche survival/hazard e bridge LTV
• Sono documentate le versioni delle metriche e l'algoritmo di attribuzione
• Configurazione SLO freschezza, monitoraggio coverage/duplicati/errori
• Pronti runbooks per le cadute D1/D7 e per gli eventi

Totale

Le analisi di coorte sono due assi e una disciplina: il momento di nascita fisso, le finestre corrette e TZ, le matrici di contenimento e LTV, la segmentazione e la verifica causale delle modifiche. Questo approccio aiuta non solo ad osservare le curve, ma anche a decidere dove aggiustare il onboard, quali canali scalare, quali contenuti e offerenti tengono i giocatori più a lungo e aumentano la LTV.