Esperimenti e A/B analysis in SQL

Un esperimento sembra vincente dopo due giorni, poi perde effetto quando arrivano utenti mobile e il supporto segnala più richieste di aiuto. La query non può limitarsi alla conversione media: deve proteggere randomizzazione, granularità e metriche di guardrail. Esperimenti e A/B analysis in SQL entra nel punto in cui un test diventa decisione di prodotto.

Una scena da cui partire

Leggi la lezione come una checklist prima di dichiarare una variante vincente. Devi sapere chi è stato assegnato, quando, quale evento conta come successo e quali segnali secondari impediscono una lettura troppo ottimista.

• Contesto: Quale unità sperimentale rende valido il confronto?
• Metodo: Quale guardrail bloccherebbe una vittoria apparente?
• Applicazione: Come comunicheresti incertezza e impatto prima del rollout?

La struttura dei dati di un A/B test

Un A/B test ben tracciato produce tre tabelle:

1. assignment: user_id, variant (A o B), assignment_time
2. events: user_id, event_time, event_type, value
3. users: user_id, signup_date, country, device

La sfida in SQL è: per ogni metrica, calcolare il valore per utente, aggregare per variante, e calcolare la differenza con l’intervallo di confidenza. Non è solo una query: è un workflow di validazione.

Calcolo delle metriche per variante

Metodo naive (sbagliato):

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;

Perché è sbagliato? Include solo gli utenti che hanno transato. Gli utenti con zero transazioni sono esclusi, e la media è calcolata solo sui transanti. Se il trattamento B riduce il numero di transanti ma aumenta il valore medio di quelli che transano, questa query mostrerà un aumento di avg_revenue — un risultato fuorviante.

Metodo corretto: valore per utente (inclusi 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;

La LEFT JOIN e COALESCE(..., 0) sono essenziali: garantiscono che ogni utente assegnato contribuisca al denominatore, anche se non ha transato.

Calcolo dell’intervallo di confidenza in SQL

L’intervallo di confidenza al 95% per la differenza tra due medie indipendenti è:

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

In SQL, puoi calcolarlo con una query che unisce 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;

Interpretazione: Se il ci_lower è positivo, possiamo dire con confidenza del 95% che il trattamento B produce un effetto positivo. Se il ci_lower è negativo e il ci_upper positivo, l’effetto non è statisticamente distinguibile da zero. Se entrambi sono negativi, B peggiora la metrica.

Metriche di guardrail: il secondo livello di validazione

Una metrica di guardrail è una metrica che NON deve peggiorare quando la metrica primaria migliora. Nel caso Bing, il guardrail mancante era il revenue per sessione (o il tempo sul sito). In SQL, il guardrail si calcola esattamente come la metrica primaria, ma con una soglia diversa: non serve che migliori, serve che sia statisticamente non peggiorata.

Il test per il guardrail è un test di non-inferiorità: l’intervallo di confidenza della differenza non deve scendere sotto una soglia negativa praticamente significativa (es. -1% del baseline). In SQL:

-- Guardrail: ci_lower NON deve essere inferiore a un margine negativo
SELECT
  lift,
  ci_lower,
  CASE WHEN ci_lower > -0.005  -- tolleranza dello 0.5%
    THEN 'guardrail_ok'
    ELSE 'guardrail_failed'
  END AS guardrail_status
FROM diff_stats;

Caso reale: Amazon e l’A/B testing della search

Amazon esegue oltre 10.000 A/B test all’anno (fonte: Amazon Science Blog, 2022). Ogni test coinvolge una pipeline SQL su Redshift che calcola metriche per variante usando window function per evitare duplicazioni.

Un pattern specifico di Amazon è il filtro di esposizione: contare solo gli utenti che sono stati effettivamente esposti al trattamento. Un utente assegnato a B ma che non ha mai visitato la pagina dove il test era attivo non deve essere contato. La query per il filtro:

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;

Solo gli utenti esposti vengono usati per il calcolo delle metriche. Amazon ha documentato che questo filtro aumenta la sensibilità statistica del 25-40% perché rimuove rumore (utenti non esposti che producono zero per costruzione).

🖥️ Prova in Sandbox

[SQL]Prova tu

Confronta il revenue medio degli ordini per fonte di acquisizione utente (google, facebook, direct, linkedin). Usa JOIN + GROUP BY.

SELECT u.fonte, u.nome, o.importo FROM utenti u JOIN ordini o ON u.id = o.utente_id ORDER BY u.fonte

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Calcola il numero di ordini e il revenue totale per ogni mese. Poi calcola la differenza mese-su-mese (month-over-month) usando LAG.

SELECT strftime('%Y-%m', data_ordine) AS mese, COUNT(*) AS num_ordini, SUM(importo) AS revenue FROM ordini GROUP BY mese ORDER BY mese

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

Laboratorio pratico

Dataset: 10.000 utenti assegnati a control o treatment con eventi di click, add_to_cart, purchase.

Livello 1 — Metriche base: Calcola avg_clicks_per_user, avg_revenue_per_user, e conversion_rate (almeno un acquisto) per variante. Includi gli zeri.

Livello 2 — Intervallo di confidenza: Estrai il lift con CI al 95% per la metrica revenue. L’effetto è statisticamente significativo?

Livello 3 — Analisi temporale: Calcola la metrica per variante per giorno di test. Cerca il plateau effect: la differenza tra varianti converge dopo N giorni? Usa una window function per calcolare la cumulative revenue per giorno.

Checkpoint operativi

1. Perché la query naive AVG(revenue) JOIN ... GROUP BY variant è sbagliata e come si corregge?

2. Cosa sono le metriche di guardrail e come si testano in SQL?

3. Perché Amazon filtra per utenti esposti e qual è il guadagno statistico?

---

Riferimenti accademici:

• Kohavi, R., Tang, D., & Xu, Y. (2020). Trustworthy Online Controlled Experiments. Cambridge University Press. Capitoli 2, 4, 7.
• Kohavi, R., Longbotham, R., Sommerfield, D., & Henne, R. M. (2009). “Controlled experiments on the web: survey and practical guide.” Data Mining and Knowledge Discovery, 18(1), pp. 140-181.
• Deng, A. & Shi, X. (2016). “Data-Driven Metric Development for Online Controlled Experiments.” Proceedings of the 22nd ACM SIGKDD.

Problema reale

Nel dominio di advanced SQL, Esperimenti e A/B analysis in SQL 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 Esperimenti e A/B analysis in SQL 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 A/B test sembra positivo, ma l’analisi SQL deve prima controllare esposizione, randomizzazione, finestre, unità sperimentale e metriche guardrail. La query non serve solo a calcolare uplift: serve a verificare che l’esperimento sia leggibile.

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 Esperimenti e A/B analysis in SQL: 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 Esperimenti e A/B analysis in SQL 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

Esperimenti e A/B analysis in SQL 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: Tecnico. Difficoltà: advanced. Tempo stimato: 22 min.