Attribution queries and path analytics

Attribuire una vendita al canale giusto è una delle decisioni più contese del marketing digitale, e quando manca una regola esplicita la discussione scivola quasi sempre sul piano politico. Paid social, email e brand possono rivendicare lo stesso acquisto, e senza una logica trasparente e condivisa il budget finisce per essere allocato in modo arbitrario. Questa lezione mostra come scrivere in SQL i modelli che rendono difendibile quella scelta.

Il problema da risolvere

La domanda non è quale canale meriti il credito, ma come giustificare che un canale lo riceva e un altro no. Non serve trovare il modello perfetto: serve una logica che renda visibili le ipotesi, i limiti e le conseguenze sul budget. Prima di scrivere una query conviene fissare tre punti. Quale decisione di budget vuoi supportare. Quale finestra temporale rende il credito difendibile. Come presentare un modello con limiti espliciti senza per questo indebolirlo agli occhi di chi deve decidere.

Come ragionare sul credito

Uno schema essenziale tiene il lavoro ancorato alle decisioni concrete invece che ai tecnicismi. Ogni analisi parte da una domanda, individua la misura che la rappresenta, sceglie un controllo che la renda interpretabile e si chiude con un’azione.

Phase	What to clarify	Output
Question	Which real choice needs improvement?	Decision to make
Measure	Which observable signal represents the problem?	Metric or source data
Control	Which baseline makes the result interpretable?	Credible comparison
Action	What changes after the analysis?	Next operational step

Rendere l’analisi riproducibile

Perché un’altra persona possa riprodurre e criticare l’analisi, definisci l’unità di lavoro (riga, partizione, finestra, join, coorte o metrica temporale), collegala a una metrica osservabile (correttezza, performance, duplicati, grain e stabilità del risultato) e dichiara la decisione attesa, che sia una query, un modello intermedio, un test SQL o un pattern riusabile.

Element	Requested specification
Unit of analysis	row, partition, window, join, cohort, or temporal metric
Primary signal	correctness, performance, duplicates, grain, and result stability
Baseline	Previous period, comparable group, benchmark, or counterfactual scenario
Decision	query, intermediate model, SQL test, or reusable pattern
Risk	Mistaking an available number for sufficient proof

Attribution models in SQL

Quattro modelli classici si implementano in SQL con complessità crescente.

Model	Distribuzione del credito	SQL complexity
Last-click	100% to the last touchpoint	Simple: FIRST_VALUE ordinato in ordine decrescente
First-click	100% to the first touchpoint	Simple: FIRST_VALUE ordinato in ordine crescente
Linear	Equally divided among all touchpoints	Media complessità: conta touchpoint per conversione
Time decay	Peso maggiore ai touchpoint più recenti	Complex: exponential weights with window functions

Il last-click assegna tutto il credito all’ultimo contatto:

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;

Il first-click fa l’opposto, premiando il contatto che ha aperto il percorso:

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;

Il modello lineare divide il credito in parti uguali tra tutti i touchpoint della conversione:

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 time decay pesa di più i contatti recenti tramite pesi esponenziali:

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;

Il time decay si adatta bene ai prodotti con un lungo percorso di maturazione, dove l’ultimo click riflette una decisione ormai consolidata.

Un caso applicato: Booking.com

Booking.com, con un budget di marketing digitale di oltre 4 miliardi di dollari, ha abbandonato i modelli a regola fissa per uno basato sul valore di Shapley. Il metodo assegna il credito a ciascun canale in base al suo contributo marginale medio su tutte le combinazioni possibili, e in questo modo distribuisce il merito in modo più equo.

Una versione semplificata in SQL calcola il tasso di conversione per ogni combinazione di canali e ne attribuisce 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;

Il passaggio a questo modello ha ridistribuito il budget e migliorato il ritorno sull’investimento.

Dalle conversioni ai percorsi

L’attribuzione risponde a quale canale abbia contribuito, mentre il path analytics indaga quale percorso abbia portato alla conversione, tenendo conto dell’ordine e della sequenza dei touchpoint. Per estrarre i percorsi più frequenti:

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;

In genere emergono percorsi come “Google Search → Direct” o “Facebook Ad → Google Search”, e sapere quali sono i più battuti aiuta a capire dove conviene intervenire sulla strategia.

Esercitarsi sui dati

Per mettere alla prova questi modelli serve un dataset realistico: 5.000 conversioni con 15.000 touchpoint su cinque canali (google_ads, facebook_ads, email, organic_search, direct), dove ogni conversione porta un conversion_value. Va bene anche una dashboard reale, un export CSV o un piccolo dataset sintetico, purché contenga almeno una domanda, una metrica osservabile e una decisione da prendere.

Un primo esercizio consiste nel calcolare il revenue attribuito a ciascun canale con last-click, first-click e modello lineare, osservando come cambia la distribuzione del credito. Da lì si passa a estrarre i tre percorsi più comuni verso la conversione, per capire se ne esiste uno dominante. L’esercizio più impegnativo è modificare il time decay perché consideri solo i touchpoint negli ultimi 7 giorni prima della conversione, assegnando peso zero a quelli più vecchi.

L’errore tipico da evitare

L’errore più comune è usare attribution queries e path analytics come etichette vuote, senza collegarle a decisioni concrete. Presentare grafici senza una decisione chiara, metriche senza baseline o conclusioni senza dichiarare le assunzioni produce analisi inutili o fuorvianti. La domanda di controllo è semplice: se il risultato fosse instabile, quale scelta sbaglierei? Se non sai rispondere in concreto, manca il legame tra analisi e azione.

Per verificare di avere capito i punti chiave, prova a rispondere a queste domande. Quali sono i quattro modelli classici di attribuzione e in quali scenari si preferiscono. In cosa differisce il path analytics dall’attribuzione e perché servono entrambi. Perché Booking.com ha scelto il valore di Shapley. Quale unità di analisi è fondamentale per misurare correttamente il problema. Quale errore tipico può compromettere la validità delle conclusioni.

Operational Summary

Attribution queries e path analytics diventano strumenti decisionali solo quando producono scelte più chiare e non restano termini tecnici. Il percorso resta lo stesso: identificare il problema reale, costruire uno schema di ragionamento, rendere l’analisi riproducibile, studiare un caso concreto, esercitarsi su dati realistici e controllare la comprensione. È questa disciplina a trasformare dati complessi in decisioni difendibili in condizioni di incertezza.