Experiments and A/B analysis in SQL

Un esperimento può sembrare vincente dopo pochi giorni e poi cambiare segno quando entrano nuovi utenti o quando emergono segnali secondari, come un aumento delle richieste di supporto. Guardare la conversione media non basta: la query deve preservare la randomizzazione e la granularità, e tenere conto delle metriche di guardrail. Qui affrontiamo il momento in cui un test smette di essere un numero e diventa una decisione di prodotto.

Il problema da risolvere

Con SQL avanzato il nodo è scrivere query corrette anche quando granularità, finestre temporali, coorti e casi limite complicano l’analisi. Non è teoria: serve a migliorare una scelta concreta con dati, assunzioni esplicite e controlli minimi. Se non sai indicare quale decisione cambia, quale dato osservi e quale errore vuoi evitare, la lezione non si è ancora trasformata in competenza applicata.

Come ragionare su un esperimento

Uno schema essenziale tiene il focus dove serve: ogni approfondimento tecnico deve rafforzare almeno una di queste quattro fasi.

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

Per analizzare un esperimento in SQL definisci prima l’unità di analisi (riga, partizione, finestra, join, coorte o metrica temporale), poi collegala a una metrica osservabile (correttezza, performance, duplicati, granularità, stabilità) e infine 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	correttezza, performance, duplicati, granularità, stabilità
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

La formalizzazione regge quando un altro analista può riprodurre la logica, criticare le assunzioni e arrivare alla stessa decisione.

The data structure of an A/B test

Un A/B test ben tracciato produce tre tabelle chiave. La tabella assignment lega ogni user_id alla variant (A o B) e all’assignment_time. La tabella events registra user_id, event_time, event_type e value. La tabella users descrive l’utente con signup_date, country e device.

La sfida in SQL è calcolare per ogni metrica il valore per utente, aggregarlo per variante e stimare la differenza con il suo intervallo di confidenza. Non è una singola query, ma un workflow di validazione.

Metric calculation per variant

Un errore frequente è calcolare la media solo sugli utenti che hanno generato transazioni:

SELECT variant, AVG(revenue) AS avg_revenue
FROM assignments a
JOIN transactions t ON a.user_id = t.user_id
WHERE t.txn_date >= a.assignment_time
GROUP BY variant;

Questa query esclude gli utenti senza transazioni e distorce la media. Se il trattamento riduce il numero di transanti ma alza il valore medio di chi transa, la media apparirà più alta pur essendo un risultato fuorviante.

Il metodo corretto calcola il valore per utente includendo gli zeri:

WITH user_revenue AS (
  SELECT
    a.user_id,
    a.variant,
    COALESCE(SUM(t.revenue), 0) AS total_revenue
  FROM assignments a
  LEFT JOIN transactions t
    ON a.user_id = t.user_id
    AND t.txn_date >= a.assignment_time
    AND t.txn_date < a.assignment_time + INTERVAL '14 days'
  GROUP BY a.user_id, a.variant
)
SELECT
  variant,
  COUNT(*) AS users,
  ROUND(AVG(total_revenue), 4) AS avg_revenue_per_user,
  ROUND(STDDEV(total_revenue) / SQRT(COUNT(*)), 4) AS se
FROM user_revenue
GROUP BY variant;

The LEFT JOIN e COALESCE(..., 0) fanno in modo che ogni utente assegnato contribuisca, anche senza transazioni.

Confidence interval calculation in SQL

L’intervallo di confidenza al 95% per la differenza tra due medie indipendenti si calcola così:

CI = (avg_B - avg_A) ± 1.96 * sqrt(se_A² + se_B²)

In SQL lo si ottiene unendo i risultati delle due varianti:

WITH stats AS (
  SELECT
    variant,
    COUNT(*) AS n,
    AVG(total_revenue) AS mean,
    STDDEV(total_revenue) / SQRT(COUNT(*)) AS se
  FROM user_revenue
  GROUP BY variant
)
SELECT
  (SELECT mean FROM stats WHERE variant = 'B') - (SELECT mean FROM stats WHERE variant = 'A') AS lift,
  (SELECT mean FROM stats WHERE variant = 'B') - (SELECT mean FROM stats WHERE variant = 'A') - 1.96 * SQRT(POWER((SELECT se FROM stats WHERE variant = 'A'), 2) + POWER((SELECT se FROM stats WHERE variant = 'B'), 2)) AS ci_lower,
  (SELECT mean FROM stats WHERE variant = 'B') - (SELECT mean FROM stats WHERE variant = 'A') + 1.96 * SQRT(POWER((SELECT se FROM stats WHERE variant = 'A'), 2) + POWER((SELECT se FROM stats WHERE variant = 'B'), 2)) AS ci_upper;

When ci_lower è positivo, con il 95% di confidenza il trattamento B ha un effetto positivo. Se l’intervallo include zero, l’effetto non è distinguibile da zero. Se entrambi gli estremi sono negativi, B peggiora la metrica.

Le metriche di guardrail

Le metriche di guardrail sono indicatori che non devono peggiorare quando la metrica primaria migliora, per esempio il revenue per sessione o il tempo sul sito. In SQL si calcolano come la metrica primaria, ma il test è di non-inferiorità: l’intervallo di confidenza della differenza non deve scendere sotto una soglia negativa significativa, per esempio -0.5%.

SELECT
  lift,
  ci_lower,
  CASE WHEN ci_lower > -0.005 THEN 'guardrail_ok' ELSE 'guardrail_failed' END AS guardrail_status
FROM diff_stats;

Un caso applicato: Amazon

Amazon esegue oltre 10.000 A/B test all’anno, e uno dei suoi pattern chiave è il filtro di esposizione: contare solo gli utenti effettivamente esposti al trattamento. Un utente assegnato a B ma mai arrivato sulla pagina testata non va incluso.

WITH exposed_users AS (
  SELECT DISTINCT a.user_id, a.variant
  FROM assignments a
  JOIN page_views pv ON a.user_id = pv.user_id
    AND pv.page = 'search_results'
    AND pv.view_time >= a.assignment_time
    AND pv.view_time < a.assignment_time + INTERVAL '14 days'
)
SELECT variant, COUNT(*) AS exposed
FROM exposed_users
GROUP BY variant;

Questo filtro aumenta la sensibilità statistica del 25-40% perché elimina il rumore degli utenti mai esposti.

Esercitarsi sui dati

Per allenarsi serve un dataset di esperimento, e va bene anche una dashboard reale, un export CSV o un piccolo dataset sintetico, purché contenga una domanda, una metrica osservabile e una decisione. Un primo esercizio consiste nel calcolare per variante avg_clicks_per_user, avg_revenue_per_user e conversion_rate (almeno un acquisto), includendo gli utenti con zero eventi. Da lì si passa a estrarre il lift con intervallo di confidenza al 95% sulla revenue, verificando se l’effetto è statisticamente significativo. L’esercizio più impegnativo è calcolare la metrica per variante giorno per giorno e cercare il plateau effect, cioè capire se la differenza tra varianti converge dopo un certo numero di giorni, usando una window function per la revenue cumulativa.

L’errore tipico da evitare

Il rischio più comune è trattare l’A/B analysis come un’etichetta invece che come un processo. Succede quando si mostra un grafico senza decisione, una metrica senza baseline o una conclusione che non dichiara quali assunzioni potrebbero invalidarla. La domanda di controllo è: se questo risultato fosse instabile, quale scelta sbaglierei? Se non sai rispondere, manca il collegamento tra analisi e azione.

Per verificare di avere capito, prova a spiegare perché la query naive che calcola AVG(revenue) solo sui transanti è sbagliata e come correggerla, cosa sono le metriche di guardrail e come si testano in SQL, perché Amazon filtra per utenti esposti e che vantaggio ne ricava, quale unità di analisi rende il problema misurabile e quale output consegneresti a uno stakeholder non tecnico.

Operational Summary

Esperimenti e A/B analysis in SQL servono quando producono decisioni più chiare, non quando aggiungono terminologia. Usa lo stesso percorso ogni volta: problema, schema di ragionamento, formalizzazione, esempio, esercizio e controllo della comprensione, così la lezione diventa pratica verificabile.