Attribution queries e path analytics

Due team rivendicano la stessa vendita: paid social perché ha generato il primo clic, email perché ha chiuso la conversione, brand perché l’utente era già noto. Senza una regola esplicita, l’attribuzione diventa una negoziazione politica. Attribution queries e path analytics mostra come trasformare percorsi, touchpoint e finestre temporali in una query discutibile ma trasparente.

Una scena da cui partire

Leggi la lezione immaginando di dover spiegare perché un canale riceve credito e un altro no. Il punto non è trovare il modello perfetto, ma costruire una logica che renda visibili ipotesi, limiti e conseguenze sul budget.

• Contesto: Quale decisione di budget userà questa attribuzione?
• Metodo: Quale finestra temporale rende il credito difendibile?
• Applicazione: Come presenteresti un modello con limiti espliciti senza indebolirlo?

I modelli di attribuzione in SQL

Esistono quattro modelli classici di attribuzione, implementabili in SQL con diversi livelli di complessità:

Modello	Come distribuisce il credito	Complessità SQL
Last-click	100% all’ultimo touchpoint	Semplice: FIRST_VALUE al contrario (LAST_VALUE con frame)
First-click	100% al primo touchpoint	Semplice: FIRST_VALUE
Linear	Equamente diviso tra N touchpoint	Media complessità: conta dei touchpoint per conversione
Time decay	Più peso ai touchpoint recenti	Complesso: pesi esponenziali con window functions

Last-click in SQL:

SELECT conversion_id, user_id, channel,
  FIRST_VALUE(channel) OVER (
    PARTITION BY conversion_id ORDER BY touch_time DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_touch_channel
FROM touchpoints;

First-click in SQL:

SELECT conversion_id, user_id, channel,
  FIRST_VALUE(channel) OVER (
    PARTITION BY conversion_id ORDER BY touch_time ASC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_touch_channel
FROM touchpoints;

Linear attribution in SQL:

WITH touchpoint_counts AS (
  SELECT *,
    COUNT(*) OVER (PARTITION BY conversion_id) AS total_touches,
    1.0 / COUNT(*) OVER (PARTITION BY conversion_id) AS weight
  FROM touchpoints
)
SELECT channel,
  SUM(conversion_value * weight) AS attributed_revenue
FROM touchpoint_counts
GROUP BY channel;

Il modello lineare divide il valore della conversione equamente tra tutti i touchpoint. Se un utente ha visto un display ad, poi un’email, poi ha cercato su Google e comprato, ogni canale riceve 1/3 del revenue.

Time decay in SQL:

WITH ordered_touches AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY conversion_id ORDER BY touch_time DESC) AS recency_rank,
    COUNT(*) OVER (PARTITION BY conversion_id) AS total_touches
  FROM touchpoints
),
time_decay_weights AS (
  SELECT *,
    POWER(0.5, recency_rank - 1) AS raw_weight,
    SUM(POWER(0.5, recency_rank - 1)) OVER (PARTITION BY conversion_id) AS total_weight
  FROM ordered_touches
)
SELECT channel,
  SUM(conversion_value * raw_weight / total_weight) AS attributed_revenue
FROM time_decay_weights
GROUP BY channel;

Qui il touchpoint più recente riceve peso 1, il precedente 0.5, quello ancora prima 0.25, ecc. La somma dei pesi è normalizzata a 1 per ogni conversione. Questo modello è il più realistico per prodotti ad alta considerazione (auto, SaaS enterprise, immobili), dove l’ultimo click è spesso il risultato di un percorso di maturazione lungo.

Caso reale: Booking.com e l’attribuzione dinamica

Booking.com spende oltre 4 miliardi di dollari all’anno in marketing digitale. Nel 2019, il team di marketing science pubblicò un paper (NIPS 2019 Workshop) descrivendo il loro modello di attribuzione interna, che va oltre i modelli a regola fissa.

Invece di scegliere a priori last-click o first-click o lineare, Booking.com usa un modello di Shapley value: ogni canale riceve credito proporzionale al suo contributo marginale medio in tutte le possibili combinazioni di canali. In pratica, è come chiedersi: “Quanto revenue in più genera l’aggiunta di Facebook Ads a una combinazione già esistente di Google Ads e Email?”

L’implementazione richiede combinatoria, ma il concetto SQL per una versione semplificata è calcolare il tasso di conversione per ogni combinazione di canali e poi attribuire i delta:

WITH channel_combos AS (
  SELECT user_id, conversion_id,
    STRING_AGG(DISTINCT channel, ', ' ORDER BY channel) AS channel_set,
    COUNT(DISTINCT channel) AS num_channels,
    MAX(conversion_value) AS value
  FROM touchpoints
  GROUP BY user_id, conversion_id
),
conversion_rates AS (
  SELECT channel_set, num_channels,
    COUNT(*) AS total_users,
    SUM(CASE WHEN value > 0 THEN 1 ELSE 0 END) AS converters,
    SUM(value) AS total_value
  FROM channel_combos
  GROUP BY channel_set, num_channels
)
SELECT * FROM conversion_rates
ORDER BY num_channels, total_value DESC;

Booking.com ha riportato che il passaggio da un modello rule-based a Shapley ha ridistribuito il 18% del budget marketing tra canali, aumentando il ROAS (Return on Ad Spend) del 7%.

Path analytics: dalle conversioni ai percorsi

L’attribuzione risponde a “quale canale ha contribuito?” Il path analytics risponde a “quale percorso porta alla conversione?” La differenza è che il path considera l’ordine e la sequenza dei canali.

Pattern: sequenze di canali

WITH user_paths AS (
  SELECT
    user_id,
    conversion_id,
    STRING_AGG(channel, ' → ' ORDER BY touch_time) AS channel_path,
    COUNT(*) AS path_length
  FROM touchpoints
  GROUP BY user_id, conversion_id
)
SELECT
  channel_path,
  COUNT(*) AS conversions,
  ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) AS pct_of_total
FROM user_paths
GROUP BY channel_path
ORDER BY conversions DESC
LIMIT 10;

Questa query produce la top 10 dei percorsi più frequenti verso la conversione. Il risultato tipico per un e-commerce: “Google Search → Direct” è spesso il percorso più comune (l’utente cerca, poi torna direttamente), seguito da “Facebook Ad → Google Search” (scoperta social, verifica via ricerca).

Laboratorio pratico

Dataset: 5.000 conversioni con 15.000 touchpoint su 5 canali (google_ads, facebook_ads, email, organic_search, direct). Ogni conversione ha un conversion_value.

Livello 1 — Confronto tra modelli: Calcola il revenue attribuito a ciascun canale con last-click, first-click, e lineare. Quanto cambia la distribuzione del credito?

Livello 2 — Top 3 percorsi: Estrai i 3 percorsi più comuni verso la conversione. C’è un percorso dominante?

Livello 3 — Time decay con finestra: Modifica il modello time decay per considerare solo i touchpoint negli ultimi 7 giorni prima della conversione (i touchpoint più vecchi di 7 giorni ricevono peso 0).

🖥️ Prova in Sandbox

[SQL]Prova tu

Analizza la distribuzione delle fonti di acquisizione: conta quanti utenti provengono da ogni fonte e calcola la percentuale sul totale usando una window function.

SELECT fonte, COUNT(*) AS num_utenti FROM utenti GROUP BY fonte ORDER BY num_utenti DESC

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Trova la categoria di prodotto con il maggior revenue totale. Mostra anche il contributo percentuale di ogni categoria.

SELECT categoria, SUM(importo) AS revenue FROM ordini GROUP BY categoria ORDER BY revenue DESC

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

Checkpoint operativi

1. Quali sono i quattro modelli di attribuzione classici e in quali scenari è preferibile ciascuno?

2. Come differisce il path analytics dall’attribuzione? Perché entrambi sono necessari?

3. Perché Booking.com usa Shapley values invece di last-click?

---

Riferimenti accademici:

• Shapley, L. S. (1953). “A Value for n-Person Games.” Contributions to the Theory of Games, 2(28), pp. 307-317.
• Anderl, E., Becker, I., von Wangenheim, F., & Schumann, J. H. (2016). “Mapping the Customer Journey: Lessons Learned from Graph-Based Online Attribution Modeling.” International Journal of Research in Marketing, 33(3).
• Booking.com. (2019). “A Shapley Value Approach to Marketing Channel Attribution.” NIPS 2019 Workshop on Machine Learning for E-Commerce.

Controllo di qualità

Prima di usare attribution queries e path analytics 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.

Interpretazione per segmenti

La media aggregata è solo il punto di partenza. Segmenta per canale, coorte, piano, paese, device e maturità dell’utente. Se due segmenti si muovono in direzioni opposte, la media non rappresenta nessuno dei due e può portare a una decisione sbagliata.

Problema reale

Nel dominio di advanced SQL, Attribution queries e path analytics 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 Attribution queries e path analytics 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

Un acquisto è preceduto da paid search, newsletter e visita diretta: attribuire tutto all’ultimo touch è semplice ma spesso fuorviante. Le query di path analytics rendono esplicite finestre, ordine degli eventi e regole di credito prima di parlare di ROI.

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 Attribution queries e path analytics: 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 Attribution queries e path analytics 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

Attribution queries e path analytics 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: Decisione. Difficoltà: advanced. Tempo stimato: 18 min.