Attribution queries and path analytics

Un totale in crescita può nascondere un’emorragia: gli utenti attivi salgono mentre la coorte più vecchia perde metà dei suoi membri in sessanta giorni. Per vedere questo serve la coorte; per capire chi ha davvero generato le conversioni serve l’attribuzione. Qui le due analisi viaggiano insieme, con query che assegnano crediti e sequenze senza farsi ingannare dalle medie.

L’idea in una frase

Attribution e path analytics separano generazioni di utenti e percorsi di contatto per assegnare il merito della conversione senza farsi ingannare dalle medie aggregate.

Il percorso in cinque passi

1. Assegna ogni utente a una coorte stabile con data_attivazione calcolata una volta sola su evento di ingresso esplicito.
2. Calcola la retention a scadenze fisse con denominatore fisso alla nascita e sole coorti con finestra completa.
3. Assegna il credito di conversione con modello dichiarato tra ultimo tocco, primo tocco, lineare e decadimento temporale.
4. Estrai le sequenze ordinate di contatto con STRING_AGG e confronta convertiti e non convertiti con soglia minima di volume.
5. Verifica che la somma dei crediti torni col ricavo vero e che ogni utente appartenga a una sola coorte.

Perché le medie aggregate mentono sulle coorti

Un prodotto in abbonamento può mostrare utenti attivi in crescita mentre la coorte più vecchia perde oltre metà degli utenti in sessanta giorni. Il totale cresce perché l’acquisizione copre l’emorragia, non perché il prodotto trattiene meglio. La coorte fissa l’origine condivisa e cambia il denominatore di ogni metrica successiva. In SQL la distinzione passa da un passaggio che calcola la data_attivazione una volta sola e poi non la tocca più.

-- Una riga per utente: l'origine della coorte non si ricalcola mai
WITH coorte AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(event_date)) AS mese_coorte, -- origine condivisa
    MIN(event_date) AS data_attivazione
  FROM eventi
  WHERE tipo_evento = 'attivazione' -- solo l'evento che definisce l'ingresso
  GROUP BY user_id
)
SELECT mese_coorte, COUNT(*) AS utenti
FROM coorte
GROUP BY mese_coorte
ORDER BY mese_coorte;

Il filtro su tipo_evento di ingresso va concordato con chi legge i numeri, perché definisce la coorte. Usare il minimo su tutti gli eventi senza filtro sposta l’origine e assegna coorti sbagliate. Centralizzare l’assegnazione in una vista condivisa evita che tre dashboard definiscano la coorte in tre modi diversi.

Come definire la coorte giusta in SQL

Le coorti comportamentali raggruppano per ciò che l’utente ha fatto e non solo per quando è arrivato. Chi completa l’onboarding contro chi si ferma, chi compra da mobile contro desktop: esperienze diverse, gruppi diversi. La struttura non cambia, con un passaggio che assegna un’etichetta stabile per user_id.

-- Coorte comportamentale: l'etichetta dipende dal primo canale osservato
WITH primo_contatto AS (
  SELECT
    user_id,
    -- il primo canale visto definisce il gruppo di appartenenza
    FIRST_VALUE(canale) OVER (
      PARTITION BY user_id ORDER BY event_date ASC
      ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS canale_ingresso,
    MIN(event_date) AS data_attivazione
  FROM eventi
  WHERE tipo_evento = 'attivazione'
  GROUP BY user_id, canale, event_date
)
SELECT canale_ingresso, COUNT(*) AS utenti
FROM (SELECT DISTINCT user_id, canale_ingresso FROM primo_contatto) s
GROUP BY canale_ingresso;

La granularità temporale è un compromesso esplicito tra dettaglio e stabilità. Coorti giornaliere con poche decine di attivazioni producono tassi ballerini, mentre coorti mensili nascondono eventi dentro il mese. La regola pratica parte dal mese e scende alla settimana solo sopra qualche migliaio di utenti. La coorte mobile, che ricalcola l’appartenenza a ogni periodo, serve per segmentare il comportamento corrente e non per misurare la retention.

La retention a scadenze fisse senza barare sul denominatore

The retention conta i membri della coorte attivi nel periodo diviso la dimensione originaria, sempre fissa e mai ricalcolata. L’errore più diffuso include utenti che non hanno ancora avuto il tempo di tornare e deprime il tasso per costruzione. La correzione filtra le sole coorti mature con finestra completa. La LEFT JOIN mantiene nel denominatore anche chi non è mai tornato, mentre la INNER JOIN gonfia il tasso anche di dieci o venti punti.

-- Retention a 30 giorni: solo coorti con finestra di osservazione completa
WITH coorte AS (
  SELECT user_id, MIN(event_date) AS data_attivazione
  FROM eventi
  WHERE tipo_evento = 'attivazione'
  GROUP BY user_id
),
retention_30 AS (
  SELECT
    c.user_id,
    c.data_attivazione,
    -- 1 se esiste almeno un evento nella finestra giorno 1-30
    MAX(CASE
      WHEN e.event_date > c.data_attivazione
       AND e.event_date <= c.data_attivazione + INTERVAL '30 days'
      THEN 1 ELSE 0
    END) AS ritenuto
  FROM coorte c
  LEFT JOIN eventi e ON e.user_id = c.user_id
  GROUP BY c.user_id, c.data_attivazione
)
SELECT
  DATE_TRUNC('month', data_attivazione) AS mese_coorte,
  COUNT(*) AS utenti,
  -- solo coorti la cui finestra di 30 giorni è già chiusa
  SUM(CASE WHEN data_attivazione <= CURRENT_DATE - INTERVAL '30 days'
    THEN ritenuto ELSE 0 END)::float
  / NULLIF(SUM(CASE WHEN data_attivazione <= CURRENT_DATE - INTERVAL '30 days'
    THEN 1 ELSE 0 END), 0) AS retention_30
FROM retention_30
GROUP BY 1
ORDER BY 1;

The retention puntuale su un giorno esatto è severa e adatta a prodotti quotidiani, mentre quella per finestra su almeno un’attività è stabile e adatta a prodotti settimanali. A parità di dati i due numeri differiscono anche di trenta punti, quindi la scelta va sempre dichiarata.

Le curve di retention che si leggono davvero

Una singola percentuale a trenta giorni dice poco, mentre la curva a uno, sette, quattordici, trenta, sessanta e novanta giorni dice quasi tutto. Un crollo precoce poi piatto indica onboarding debole, mentre un declino lento senza stabilizzazione indica valore non continuato. La query tipica unisce la coorte a una spina di scadenze e aggrega per coorte e scadenza, con sole coorti mature per ciascuna scadenza.

-- Tabella di coorte: una riga per coorte, una colonna per scadenza
WITH coorte AS (
  SELECT user_id, MIN(event_date) AS data_attivazione
  FROM eventi
  WHERE tipo_evento = 'attivazione'
  GROUP BY user_id
),
scadenze AS (
  -- spine di offset in giorni: il calendario dei controlli
  SELECT * FROM (VALUES (1), (7), (14), (30), (60), (90)) AS t(giorno)
),
base AS (
  SELECT
    DATE_TRUNC('month', c.data_attivazione) AS mese_coorte,
    s.giorno,
    COUNT(DISTINCT c.user_id) AS denominatore,
    -- membri della coorte con almeno un evento entro la scadenza
    COUNT(DISTINCT CASE
      WHEN e.event_date > c.data_attivazione
       AND e.event_date <= c.data_attivazione + (s.giorno || ' days')::interval
      THEN e.user_id
    END) AS ritenuti
  FROM coorte c
  CROSS JOIN scadenze s
  LEFT JOIN eventi e ON e.user_id = c.user_id
  -- solo coorti mature per ciascuna scadenza
  WHERE c.data_attivazione <= CURRENT_DATE - (s.giorno || ' days')::interval
  GROUP BY 1, 2
)
SELECT mese_coorte, giorno, ritenuti::float / NULLIF(denominatore, 0) AS retention
FROM base
ORDER BY mese_coorte, giorno;

La lettura per riga mostra l’invecchiamento di una coorte, quella per colonna il miglioramento tra generazioni. La diagonale temporale rivela shock esterni quando tutte le coorti perdono utenti nella stessa settimana di calendario.

Forma della curva	Diagnosi probabile	Dove intervenire
Crollo giorno 1-7, poi piatta	Onboarding debole	Primi passi, attivazione, email di benvenuto
Declino lento e continuo	Valore non continuato	Feature di ritorno, abitudini, notifiche
Tutte le coorti cadono nello stesso mese di calendario	Shock esterno	Outage, prezzi, stagionalità, concorrenza
Coorti recenti sistematicamente peggiori	Acquisizione diluita	Qualità del traffico, targeting, sconti aggressivi

I modelli di attribuzione scritti come query difendibili

Davanti a una conversione preceduta da più contatti serve una regola di credito dichiarata. Non esiste il modello giusto in assoluto, ma solo quello le cui ipotesi reggono alla domanda sul budget. L’ultimo tocco assegna tutto al contatto finale con ordinamento discendente, mentre il primo tocco premia chi apre i percorsi. Il lineare divide in parti uguali tra tutti i tocchi, mentre il decadimento temporale pesa di più i contatti recenti, con pesi che si dimezzano a ritroso e normalizzazione che conserva il valore reale.

-- Last-click: tutto il merito all'ultimo punto di contatto
SELECT
  conversion_id,
  FIRST_VALUE(canale) OVER (
    PARTITION BY conversion_id ORDER BY istante_contatto DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS canale_accredito
FROM punti_contatto;

-- Modello lineare: il valore si divide in parti uguali
WITH pesi AS (
  SELECT *,
    -- ogni tocco vale uno fratto il numero di tocchi
    1.0 / COUNT(*) OVER (PARTITION BY conversion_id) AS peso
  FROM punti_contatto
)
SELECT canale, SUM(valore_conversione * peso) AS ricavo_attribuito
FROM pesi
GROUP BY canale;

-- Time decay: i contatti recenti pesano il doppio dei precedenti
WITH ordinati AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY conversion_id ORDER BY istante_contatto DESC
    ) AS rango_recenza -- 1 = ultimo tocco prima dell'acquisto
  FROM punti_contatto
),
con_pesi AS (
  SELECT *,
    POWER(0.5, rango_recenza - 1) AS peso_grezzo,
    SUM(POWER(0.5, rango_recenza - 1))
      OVER (PARTITION BY conversion_id) AS peso_totale
  FROM ordinati
)
SELECT canale,
  SUM(valore_conversione * peso_grezzo / peso_totale) AS ricavo_attribuito
FROM con_pesi
GROUP BY canale;

Prima di adottare un modello conviene calcolarne tre in parallelo sulla stessa tabella. Se ultimo tocco e lineare assegnano allo stesso canale quote molto diverse, la decisione dipende più dal modello che dai dati e va discussa apertamente. Oltre pochi canali il calcolo equo dei contributi marginali esce da SQL e passa a strumenti esterni, ma anche la tabella grezza delle combinazioni mostra quali accoppiate convertono davvero.

-- Base per Shapley semplificato: tasso di conversione per combinazione
WITH combo AS (
  SELECT user_id, conversion_id,
    STRING_AGG(DISTINCT canale, ',' ORDER BY canale) AS insieme_canali,
    MAX(valore_conversione) AS valore
  FROM punti_contatto
  GROUP BY user_id, conversion_id
)
SELECT insieme_canali,
  COUNT(*) AS utenti,
  SUM(CASE WHEN valore > 0 THEN 1 ELSE 0 END)::float / COUNT(*) AS tasso_conv,
  SUM(valore) AS valore_totale
FROM combo
GROUP BY insieme_canali
ORDER BY valore_totale DESC;

Dai crediti ai percorsi con le sequenze che convertono

L’attribuzione dice quanto vale ciascun canale, mentre l’analisi dei percorsi chiede quali sequenze portano alla conversione tenendo conto dell’ordine. L’estrazione dei percorsi frequenti aggrega stringhe ordinate per istante_contatto. Il risultato tipico è concentrato: pochi percorsi coprono metà delle conversioni. La coda di sequenze rare sotto qualche decina di casi va trattata con sospetto, perché quasi sempre riflette fortuna e non segnale.

-- I dieci percorsi di conversione più frequenti
WITH percorsi AS (
  SELECT user_id, conversion_id,
    -- la sequenza ordinata per istante è il percorso
    STRING_AGG(canale, ' → ' ORDER BY istante_contatto) AS percorso,
    COUNT(*) AS lunghezza
  FROM punti_contatto
  GROUP BY user_id, conversion_id
)
SELECT percorso,
  COUNT(*) AS conversioni,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_totale
FROM percorsi
GROUP BY percorso
ORDER BY conversioni DESC
LIMIT 10;

Il passo successivo confronta i percorsi dei convertiti con quelli dei non convertiti. Una sequenza frequentissima in entrambi i gruppi non distingue nulla, mentre una sequenza rara con tasso di conversione multiplo della media merita indagine, con soglia minima di volume prima di spostare budget.

Quando coorti, attribuzione e percorsi si rompono

Il bias di sopravvivenza nasce quando la coorte usa informazioni future e seleziona retrospettivamente i migliori: la definizione può usare solo informazioni disponibili all’istante di origine. La finestra di attribuzione va calibrata sul ciclo di acquisto reale, con giorni per consegne rapide, settimane per commercio elettronico e mesi per vendite complesse. La cannibalizzazione tra canali resta invisibile ai modelli a regole fisse, perché intercettano domanda esistente invece di crearne di nuova e richiedono esperimenti con spegnimento controllato. La granularità eccessiva dei percorsi moltiplica le sequenze fino a rendere ogni percorso unico: per questo conviene restare su cinque o sei etichette robuste.

-- Finestra di attribuzione parametrizzata: solo tocchi recenti contano
WITH finestra AS (
  SELECT *,
    -- azzera il peso dei tocchi fuori finestra prima di normalizzare
    CASE WHEN istante_contatto >= istante_conversione - INTERVAL '7 days'
      THEN POWER(0.5, rango_recenza - 1) ELSE 0 END AS peso_finestra
  FROM ordinati
)
SELECT canale, SUM(valore_conversione * peso_finestra
  / NULLIF(SUM(peso_finestra) OVER (PARTITION BY conversion_id), 0))
FROM finestra
GROUP BY canale;

Mettere tutto in produzione senza perdere fiducia nei numeri

Il passaggio in produzione stratifica i dati in viste per eventi grezzi, coorti stabili, tabelle di retention e crediti per canale. Ogni strato dipende solo da quello sotto, e ridefinire la coorte in un solo punto propaga la correzione ovunque. I controlli non negoziabili impongono conservazione dei crediti sul ricavo vero, retention mai sopra uno, appartenenza a una sola coorte e nessuna conversione senza percorso.

-- Test di conservazione: il credito totale deve tornare col ricavo vero
SELECT
  SUM(ricavo_attribuito) AS totale_attribuito,
  (SELECT SUM(valore_conversione) FROM conversioni) AS ricavo_vero,
  ABS(SUM(ricavo_attribuito)
    - (SELECT SUM(valore_conversione) FROM conversioni))
    / (SELECT SUM(valore_conversione) FROM conversioni) AS scarto_relativo
FROM mart_attribuzione;
-- scarto_relativo sopra 0.001 significa pesi non normalizzati o join duplicate

Sul piano operativo conviene ricalcolare in modo incrementale solo utenti nuovi e finestre appena chiuse, invece di ricostruire anni di storia ogni notte. I parametri che cambiano le decisioni, come finestra, definizione di attivo e granularità, vanno esposti come variabili documentate. Una buona prassi fissa le soglie prima di guardare i dati, con doppio modello concorde per spostare budget e lift con volume minimo per intervenire sui percorsi.

In sintesi: usa ultimo tocco per le chiusure, primo tocco per la scoperta e decadimento temporale come compromesso operativo, ma sposta budget solo quando due modelli concordano.

Verdetto: ultimo tocco per le chiusure, primo tocco per la scoperta e decadimento temporale come compromesso; sposta budget solo quando due modelli concordano.

Il caso Procter & Gamble: quando i crediti non creano vendite

Procter & Gamble, nel 2017, tagliò circa 200 milioni di dollari di spesa pubblicitaria digitale senza registrare cali di vendite. Il caso mostrò che gran parte dei contatti attribuiti dai report intercettava domanda esistente invece di crearne di nuova. La lezione per l’attribuzione è diretta: ultimo tocco e modelli a regole fisse sovrastimano i canali di chiusura. Solo esperimenti con spegnimento controllato e confronto contro gruppi non esposti misurano l’incremento reale.

Domande per chiudere la lezione

1. Perché la INNER JOIN tra coorte ed eventi gonfia la retention rispetto alla LEFT JOIN?
2. Quando una coorte va esclusa dal calcolo per finestra di osservazione incompleta?
3. Quale modello di attribuzione premia la chiusura e quale premia la scoperta?
4. Perché la somma dei crediti deve tornare col ricavo vero entro una tolleranza stretta?