Sessionization e raggruppamento comportamentale

Questa lezione appartiene al binario ml-tabellare: sessioni, episodi e viaggi si ricostruiscono tutti con query su tabelle di eventi.

L’idea in una frase

La sessionization trasforma sequenze piatte di eventi in blocchi operativi delimitati da inattività o cambi di stato, per misurare conversione e durata.

Il percorso in cinque passi

1. Misura la distribuzione dei gap tra eventi successivi dello stesso utente con LAG e fissa una soglia di inattività difendibile dai dati.
2. Marca ogni evento con flag di nuova sessione (is_new_session) quando il gap supera la soglia o quando scatta un confine logico.
3. Trasforma il flag in numero di sessione con SUM() OVER () ordinata per tempo e aggrega per utente e sessione.
4. Calcola durata, profondità e conversione per sessione con mediana e quota di sessioni mono-evento separate dalla media.
5. Congela le sessioni chiuse oltre una finestra di guardia e ricalcola solo eventi recenti più coda di trascinamento.

Perché gli eventi grezzi non bastano per decidere

I log arrivano come righe ordinate per tempo ma senza confini. Aggregare per giorno risponde a quanti eventi ci sono stati ieri, non a quanti tentativi reali di acquisto. Un utente con quaranta eventi in un giorno può essere molto coinvolto oppure uno script in loop, e la differenza si vede solo ricostruendo le sequenze. La sessionizzazione introduce un livello intermedio con durata, profondità e conversione per blocco. Solo a quel punto si confronta il comportamento tra coorti e canali. Lavorare per sessioni riduce anche di ordini di grandezza le righe delle analisi successive.

Livello di aggregazione	Riga tipica	Domanda a cui risponde
Evento	un click alle 8:02:14	cosa è successo esattamente
Sessione	5 eventi in 12 minuti	cosa voleva fare l’utente in quel blocco
Utente / giorno	3 sessioni in un giorno	quanto vale quel cliente questa settimana

La soglia di inattività come decisione di prodotto

Nessuna sessione esiste nei dati: esiste una convenzione legata al prodotto. Trenta minuti è lo standard ereditato dai sistemi di web analytics, ma per consegne rapide è troppo lungo e per configuratori complessi è troppo corto. La scelta merita un grafico della distribuzione dei gap su scala logaritmica, con valle tra gap brevi dentro la sessione e gap lunghi tra sessioni. Il secondo controllo è la sensibilità: ricalcolo del numero di sessioni e della conversione a novecento, milleottocento e tremilaseicento secondi. Se i numeri si muovono di pochi punti, la soglia è robusta e si può congelare.

-- Gap in secondi calcolato su timestamp assoluti, senza conversioni di fuso
SELECT
  user_id,
  event_time,
  -- Recupera l'istante precedente dello stesso utente
  LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS prev_event_time,
  -- Differenza in secondi rispetto all'evento precedente
  EXTRACT(EPOCH FROM (
    event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
  )) AS gap_s
FROM raw_events;

Il risultato va letto prima di fissare la soglia, con mediana, novantesimo e novantanovesimo percentile e quota di gap sopra i trenta minuti. Sessionizzare su timestamp assoluti con aritmetica in epoch evita rotture da fuso orario, con conversioni solo in presentazione.

Il pattern a due passaggi con flag e somma cumulativa

Quasi ogni sessionizzazione segue lo stesso scheletro in due passaggi. Il primo marca ogni evento con flag binario di apertura sessione. Il secondo trasforma la sequenza di zeri e uni in identificativo con somma cumulativa ordinata per tempo. Tre dettagli decidono la tenuta in produzione: ordinamento totale con tiebreaker, calcolo unico della finestra e chiave vera data dalla coppia user_id e numero.

WITH events_with_gap AS (
  SELECT
    user_id,
    event_time,
    event_type,
    -- Flag 1 quando l'evento apre una nuova sessione
    CASE
      WHEN LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time) IS NULL THEN 1
      WHEN EXTRACT(EPOCH FROM (
        event_time - LAG(event_time) OVER (PARTITION BY user_id ORDER BY event_time)
      )) > 1800 THEN 1
      ELSE 0
    END AS is_new_session
  FROM raw_events
),
events_with_session AS (
  SELECT
    *,
    -- Somma cumulativa del flag: genera il numero di sessione per utente
    SUM(is_new_session) OVER (
      PARTITION BY user_id ORDER BY event_time ROWS UNBOUNDED PRECEDING
    ) AS session_number
  FROM events_with_gap
)
SELECT
  user_id,
  session_number,
  MIN(event_time) AS session_start,
  MAX(event_time) AS session_end,
  COUNT(*) AS events_in_session,
  -- Durata in secondi tra primo e ultimo evento della sessione
  EXTRACT(EPOCH FROM (MAX(event_time) - MIN(event_time))) AS session_duration_s
FROM events_with_session
GROUP BY user_id, session_number;

La durata merita attenzione: una sessione con un solo evento ha durata zero per costruzione. Meglio mediana, percentili e quota di sessioni mono-evento separati, invece di una media che mescola comportamenti diversi.

Quando il confine non è il tempo ma un cambio di stato

Non tutti i raggruppamenti nascono da un gap temporale. Una pagina di ringraziamento chiude la sessione anche dopo pochi secondi, un logout chiude sempre, e un cambio di stato del sensore apre un nuovo episodio. La struttura della query non cambia: cambia solo la condizione dentro il CASE. Con sensori che campionano ogni minuto l’obiettivo può essere isolare sequenze sopra i trenta gradi lunghe almeno cinque letture. Il confronto con valore mancante richiede ramo esplicito con IS NULL, perché altrimenti il primo record perde l’episodio iniziale.

WITH temp_flagged AS (
  SELECT
    sensor_id,
    measured_at,
    temperature,
    -- Stato 0 = surriscaldamento, 1 = normale
    CASE WHEN temperature > 30 THEN 0 ELSE 1 END AS state,
    -- Stato precedente dello stesso sensore
    LAG(CASE WHEN temperature > 30 THEN 0 ELSE 1 END)
      OVER (PARTITION BY sensor_id ORDER BY measured_at) AS prev_state
  FROM sensor_readings
),
state_changes AS (
  SELECT
    *,
    -- Nuovo episodio a ogni cambio di stato o al primo record
    CASE WHEN state != prev_state OR prev_state IS NULL THEN 1 ELSE 0 END AS is_new_episode
  FROM temp_flagged
)
SELECT
  sensor_id,
  -- Identificativo progressivo di episodio per sensore
  SUM(is_new_episode) OVER (
    PARTITION BY sensor_id ORDER BY measured_at ROWS UNBOUNDED PRECEDING
  ) AS episode_id,
  MIN(measured_at) AS episode_start,
  MAX(measured_at) AS episode_end,
  MAX(temperature) AS peak_temp,
  COUNT(*) AS readings_in_episode
FROM state_changes
WHERE state = 0  -- tiene solo gli episodi caldi, scarta i periodi normali
GROUP BY sensor_id, episode_id
HAVING COUNT(*) >= 5;  -- almeno 5 letture consecutive sopra soglia

Lo stesso schema gestisce confini misti di tempo ed evento con condizione composta. Conviene registrare anche il motivo di apertura in colonna dedicata, perché in debug la prima domanda riguarda sempre il perché della separazione.

Sessioni di guida e telemetria su scala reale

Su flotte connesse la sessionizzazione diventa rilevazione di viaggi, con trigger su cambio, velocità e posizione. Una sosta breve al semaforo non chiude il viaggio, mentre una sosta lunga con cambio in sosta e spostamento reale spesso sì. La logica va eseguita in batch lato server, con soglia di spostamento che filtra il rumore del segnale da fermo. Partizionare per vin e data evita smistamenti ingestibili, perché la finestra resta confinata a un veicolo per volta.

WITH vehicle_events AS (
  SELECT
    vin,
    event_time,
    gear,   -- 'P', 'D', 'R', 'N': P indica sosta con cambio in parking
    speed,
    lat, lon,
    -- Stato precedente dello stesso veicolo
    LAG(gear) OVER (PARTITION BY vin ORDER BY event_time) AS prev_gear,
    LAG(lat) OVER (PARTITION BY vin ORDER BY event_time) AS prev_lat,
    LAG(lon) OVER (PARTITION BY vin ORDER BY event_time) AS prev_lon
  FROM telemetry
),
trip_starts AS (
  SELECT
    *,
    -- Nuovo viaggio al primo evento o dopo sosta con spostamento reale
    CASE
      WHEN prev_gear IS NULL THEN 1
      WHEN prev_gear = 'P' AND gear = 'D'
        AND haversine(prev_lat, prev_lon, lat, lon) > 0.1 THEN 1
      ELSE 0
    END AS is_new_trip
  FROM vehicle_events
)
SELECT
  vin,
  -- Identificativo progressivo di viaggio per veicolo
  SUM(is_new_trip) OVER (
    PARTITION BY vin ORDER BY event_time ROWS UNBOUNDED PRECEDING
  ) AS trip_id,
  COUNT(*) AS events,
  MAX(speed) AS max_speed,
  MAX(event_time) - MIN(event_time) AS duration
FROM trip_starts
GROUP BY vin, trip_id;

Attribuzione e metriche dentro la sessione

Una volta delimitate le sessioni serve sapere cosa è successo dentro: conversione, canale di ingresso e durata reale. L’attribuzione al canale del primo evento mostra il FIRST_VALUE usato bene, senza sottoquery lente. La durata mediana resta più robusta della media contro outlier e rimbalzi. Attribuire tutto al primo tocco sovrastima i canali di scoperta, mentre la conversione per sessione e per utente rispondono a domande diverse: mescolare i denominatori crea litigi sui numeri.

WITH session_events AS (
  SELECT
    user_id,
    session_number,
    event_time,
    event_type,
    channel,
    -- Canale del primo evento della sessione, propagato a ogni riga
    FIRST_VALUE(channel) OVER (
      PARTITION BY user_id, session_number ORDER BY event_time
    ) AS session_entry_channel,
    -- Istante del primo evento: serve per durate e ordinamenti
    FIRST_VALUE(event_time) OVER (
      PARTITION BY user_id, session_number ORDER BY event_time
    ) AS session_start_time
  FROM events_with_session
)
SELECT
  session_entry_channel,
  COUNT(DISTINCT (user_id, session_number)) AS sessions,
  -- Quota di sessioni con almeno un acquisto: conversione per canale di ingresso
  COUNT(DISTINCT CASE WHEN event_type = 'purchase'
    THEN (user_id, session_number) END) * 1.0
    / COUNT(DISTINCT (user_id, session_number)) AS conv_rate,
  -- Durata mediana: più robusta della media contro outlier e bounce
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (event_time - session_start_time))
  ) AS median_duration_s
FROM session_events
GROUP BY session_entry_channel;

Per sequenze più fini le funzioni di scorrimento (LAG e LEAD) ricostruiscono pagina precedente e successiva e tempi tra carrello e acquisto. La metrica di attrito più utile resta la probabilità di passaggio condizionata, calcolata per sessione e non per evento.

I modi più costosi per sbagliare la sessionizzazione

Il primo errore tratta l’identità utente come affidabile, mentre cookie resettati, login multipli e traffico da più dispositivi creano identità parallele. Il sintomo è una marea di sessioni mono-evento, che invita a controllare la copertura dell’identificativo prima di abbassare la soglia. Il secondo errore ignora i duplicati da retry e ricariche, che azzerano i gap e fondono sessioni da separare: serve deduplica prima della finestra. Il terzo errore impone una soglia unica a segmenti diversi con ritmi diversi e appiattisce le differenze da trovare. La soluzione pragmatica parametrizza la soglia per segmento in tabella di configurazione.

Come rendere la logica stabile in produzione

La sessionizzazione notturna richiede incrementalità, con congelamento delle sessioni chiuse oltre una finestra di guardia di ventiquattro o quarantotto ore. Il test automatico impone quadratura delle righe, coerenza temporale con fine mai precedente all’inizio e banda storica sul numero di sessioni a parità di traffico. Le assunzioni su soglia, identità, deduplica e fuso vanno documentate in tabella di configurazione versionata. Il controllo più trascurato confronta sempre la metrica sessionizzata con una baseline semplice per utente-giorno e tratta il disaccordo come informazione preziosa sulla definizione stessa.

Riferimenti: Kaushik 2010 sul framework di sessionizzazione web, documentazione Google Analytics 2024 per la definizione operativa di sessione a trenta minuti e Suthar e Patel 2023 sui pattern clickstream con window function.

In sintesi: usa primo tocco per l’ingresso e ultimo tocco per la chiusura, dichiara sempre quale vista guida la decisione, con finestra per righe e ordinamento totale con tiebreaker.

Verdetto: primo tocco per l’ingresso e ultimo tocco per la chiusura, con finestra per righe e ordinamento totale con tiebreaker; dichiara sempre quale vista guida la decisione.

L’esempio di Google Analytics: una soglia che è convenzione, non legge

Google Analytics definisce dal 2024 come standard una sessione con timeout di trenta minuti, pari a milleottocento secondi di inattività. La documentazione operativa fissa anche la chiusura a mezzanotte e all’arrivo di nuove informazioni di campagna. Il framework di Kaushik del 2010 aveva già distinto visite e visitatori con metriche per sessione come rimbalzo e durata. Suthar e Patel, nel 2023, mostrarono gli stessi pattern con window function su dati clickstream reali. La soglia resta una convenzione da validare sui propri gap, non una legge fisica.

Domande per chiudere la lezione

1. Perché l’ordinamento deve essere totale con tiebreaker prima di calcolare flag e somma cumulativa?
2. Quando una soglia unica di milleottocento secondi deforma le metriche tra segmenti diversi?
3. Quale deduplica applicaresti prima della finestra per evitare gap azzerati da eventi gemelli?
4. Perché la conversione per sessione e quella per utente rispondono a domande diverse?