Cohort analysis and behavioral cohorts

Questa lezione si muove sul binario ml-tabellare: coorti, segmenti e matrici di transizione vivono tutti in tabelle che incrociano comportamento e tempo. L’idea di fondo è semplice da enunciare e impegnativa da applicare: smettere di chiedersi chi è l’utente e cominciare a chiedersi cosa fa l’utente.

Il concetto in due frasi

Le behavioral cohorts segmentano utenti per azioni compiute nei primi giorni per prevedere retention, espansione e abbandono.

La procedura dall’inizio alla fine

1. Definisci coorte temporale di attivazione e finestra di osservazione a 30 giorni.
2. Calcola per utente giorni attivi, ampiezza feature, profondità e segnali chiave.
3. Assegna ogni utente a un segmento comportamentale con soglie dichiarate.
4. Measure retention e transizioni per segmento mese su mese.
5. Incrocia coorte temporale e segmento per separare effetto mix da effetto prodotto.
6. Traduci il movimento peggiore in intervento con owner e monitoraggio.

Demografica contro comportamentale

Tre utenti fitness mostrano il limite dell’anagrafica. Marco e Giulia sono identici per età e device ma opposti per uso. Ahmed è diverso per età e paese ma vicino a Marco per comportamento.

Approach	Grouping variable	Example	Risposta tipica
Demographic	Chi è l’utente	Età, paese, device	Gli utenti iOS spendono più di Android
Behavioral	Cosa fa l’utente	Frequenza, feature usate	I power user hanno un LTV 3x
Time cohort	Quando ha iniziato	Signup month	La retention migliora nel tempo

In sintesi: la comportamentale spiega il valore e la demografica descrive solo il contorno.

Verdetto: la segmentazione comportamentale vince su quella demografica: raggruppa per azioni compiute, non per chi è l’utente, perché solo il comportamento spiega il valore.

Coorti temporali e behavioral cohorts in SQL

La coorte classica raggruppa per settimana di acquisizione. Poi misura la retention nel tempo sullo stesso gruppo iniziale.

SELECT
  DATE_TRUNC('week', signup_date) AS cohort_week,
  COUNT(DISTINCT user_id) AS cohort_size,
  ROUND(COUNT(DISTINCT CASE WHEN week_1_active THEN user_id END) * 100.0 / COUNT(*), 1) AS week1_retention,
  ROUND(COUNT(DISTINCT CASE WHEN week_2_active THEN user_id END) * 100.0 / COUNT(*), 1) AS week2_retention,
  ROUND(COUNT(DISTINCT CASE WHEN week_4_active THEN user_id END) * 100.0 / COUNT(*), 1) AS week4_retention,
  ROUND(COUNT(DISTINCT CASE WHEN week_12_active THEN user_id END) * 100.0 / COUNT(*), 1) AS week12_retention
FROM user_cohorts
GROUP BY cohort_week
ORDER BY cohort_week;

The behavioral cohort raggruppa invece per azioni compiute. Le dimensioni sono frequenza, ampiezza e profondità. I segnali chiave tipici sono acquisto, invito e creazione contenuto.

WITH user_behavior_30d AS (
  SELECT
    user_id,
    COUNT(DISTINCT DATE(event_time)) AS active_days,
    COUNT(*) AS total_events,
    COUNT(DISTINCT event_type) AS unique_event_types,
    MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS has_purchased,
    MAX(CASE WHEN event_type = 'invite' THEN 1 ELSE 0 END) AS has_invited,
    MAX(CASE WHEN event_type = 'create_project' THEN 1 ELSE 0 END) AS has_created,
    AVG(session_duration_seconds) AS avg_session_secs
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
    AND user_id IS NOT NULL
  GROUP BY user_id
)
SELECT
  user_id,
  CASE
    WHEN active_days >= 20 AND has_purchased = 1 AND has_invited = 1 THEN 'champion'
    WHEN active_days >= 20 THEN 'power_user'
    WHEN active_days >= 10 THEN 'regular'
    WHEN active_days >= 3 THEN 'casual'
    WHEN active_days >= 1 THEN 'dormant'
    ELSE 'dead'
  END AS behavior_segment,
  active_days,
  total_events,
  unique_event_types,
  has_purchased,
  has_invited,
  ROUND(avg_session_secs, 0) AS avg_session_secs
FROM user_behavior_30d;

Segment	Feature	Product action
Champion	Use, pay, invite	Nurturing, community
Power User	Daily, all features	Retention, upsell
Regular	Multiple times per week	Deepening e scoperta feature
Casual	3-9 giorni al mese	Activation e abitudine
Dormant	1-2 giorni al mese	Re-engagement mirato
Dead	Zero activity	Win-back o disinvestimento

Il punto chiave: le soglie dichiarate battono i segmenti impliciti perché si possono replicare e contestare.

Matrice di transizione e ciclo di vita

Gli utenti si muovono tra segmenti. La matrice mostra dove vanno mese su mese. Un decadimento oltre il 15 percento è un allarme. Una resurrezione sotto il 5 percento segnala un re-engagement inefficace.

WITH current_month AS (
  SELECT user_id, behavior_segment AS current_segment
  FROM user_behavior_monthly
  WHERE month_key = '2025-01'
),
previous_month AS (
  SELECT user_id, behavior_segment AS previous_segment
  FROM user_behavior_monthly
  WHERE month_key = '2024-12'
)
SELECT
  COALESCE(p.previous_segment, 'new') AS from_segment,
  c.current_segment AS to_segment,
  COUNT(*) AS users,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (PARTITION BY COALESCE(p.previous_segment, 'new')), 1) AS pct
FROM current_month c
LEFT JOIN previous_month p ON c.user_id = p.user_id
GROUP BY COALESCE(p.previous_segment, 'new'), c.current_segment
ORDER BY from_segment, to_segment;

Transition	Metric	Owner	B2C benchmark
New verso Activated	Activation rate	Growth	20-40 percento
Activated verso Engaged	Deepening rate	Feature team	30-50 percento
Engaged verso Power	Power conversion	Core product	10-25 percento
Qualunque verso Dormant	Decay rate	Retention	5-15 percento mensile
Dormant verso Active	Resurrection rate	CRM	3-8 percento mensile

Il passaggio da casual a regular è il motore di crescita di lungo termine e va alimentato di proposito.

Retention per coorte comportamentale e LTV

L’engagement iniziale moltiplica la retention da 3 a 5 volte. Questa misura si costruisce a monte, in onboarding.

WITH user_first_month_behavior AS (
  SELECT u.user_id,
    COUNT(DISTINCT DATE(e.event_time)) AS first_month_active_days,
    COUNT(DISTINCT e.event_type) AS first_month_event_types
  FROM users u
  JOIN events e ON u.user_id = e.user_id
    AND e.event_time BETWEEN u.signup_date AND u.signup_date + INTERVAL '30 days'
  GROUP BY u.user_id
),
user_monthly_activity AS (
  SELECT u.user_id,
    DATE_TRUNC('month', u.signup_date) AS signup_cohort,
    DATE_TRUNC('month', e.event_time) AS activity_month,
    (DATE_TRUNC('month', e.event_time) - DATE_TRUNC('month', u.signup_date)) / INTERVAL '1 month' AS month_number
  FROM users u
  JOIN events e ON u.user_id = e.user_id
)
SELECT
  CASE
    WHEN f.first_month_active_days >= 20 THEN 'high_engagement'
    WHEN f.first_month_active_days >= 10 THEN 'medium'
    WHEN f.first_month_active_days >= 3 THEN 'low'
    ELSE 'minimal'
  END AS initial_behavior_segment,
  a.month_number,
  COUNT(DISTINCT a.user_id) AS retained_users
FROM user_monthly_activity a
JOIN user_first_month_behavior f ON a.user_id = f.user_id
WHERE a.signup_cohort >= '2024-01-01'
GROUP BY initial_behavior_segment, a.month_number
ORDER BY initial_behavior_segment, a.month_number;

WITH user_segment AS (
  SELECT user_id, behavior_segment
  FROM user_behavior_monthly
  WHERE month_key = DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
),
user_revenue AS (
  SELECT user_id, SUM(amount) AS revenue_90d
  FROM transactions
  WHERE transaction_date >= CURRENT_DATE - INTERVAL '90 days'
  GROUP BY user_id
)
SELECT
  us.behavior_segment,
  COUNT(DISTINCT us.user_id) AS users,
  COALESCE(SUM(ur.revenue_90d), 0) AS total_revenue,
  ROUND(COALESCE(SUM(ur.revenue_90d), 0) / COUNT(DISTINCT us.user_id), 2) AS ltv_90d
FROM user_segment us
LEFT JOIN user_revenue ur ON us.user_id = ur.user_id
GROUP BY us.behavior_segment
ORDER BY ltv_90d DESC;

I limiti restano tre: volumi minimi di 5-10 eventi per utente, classificazione retrospettiva e intento non osservato da validare con interviste e ticket.

Riferimenti operativi: Croll e Yoskovitz Lean Analytics capitolo 7, McClure Pirate Metrics AARRR, Chen The Cold Start Problem, Amplitude Behavioral Cohorts Playbook, Kohavi Trustworthy Online Controlled Experiments capitolo 5.

Netflix: tracciare le transizioni per fermare il decadimento

Nel 2012 Netflix scopre che il 22 percento dei power user diventa regular in due mesi per esaurimento dei contenuti preferiti. La risposta è la personalizzazione predittiva che riduce il decadimento al 9 percento in sei mesi. Retention e ricavi risalgono perché l’intervento colpisce la causa misurata e non la media. La lezione è diretta: traccia transizioni, intervieni sul decadimento e misura prima e dopo sullo stesso segmento.

Quattro domande per verificare l’apprendimento

1. Quale comportamento iniziale definisce il segmento ad alto valore?
2. Quale transizione segnala allarme di prodotto questo mese?
3. Quale baseline separa effetto mix da effetto prodotto?
4. Quale segmento merita budget di acquisizione dedicato?