Funnel analysis in SQL

Il team prodotto vede molte iscrizioni e poche attivazioni, il marketing guarda le campagne e customer success vede ticket aperti subito dopo l’onboarding. Serve una query che trasformi eventi sparsi in passaggi verificabili. Funnel analysis in SQL lavora su questa distanza: non conta solo quanti utenti arrivano allo step finale, ma con quale definizione di ingresso, ordine e tempo massimo.

Una scena da cui partire

Leggi la lezione come se dovessi difendere il funnel in una riunione dove ogni reparto ha una metrica diversa. Le funzioni FIRST_VALUE, LAST_VALUE e NTILE non servono a decorare il report: servono a collegare il comportamento iniziale, l’esito finale e la distribuzione degli utenti.

• Contesto: Quale evento apre davvero il funnel?
• Metodo: Quale vincolo temporale impedisce conversioni gonfiate?
• Applicazione: Quale drop-off porteresti al team prodotto con una raccomandazione concreta?

Il modello concettuale del funnel

Un funnel è una sequenza ordinata di passaggi (step) che un utente deve (o può) compiere. Non tutti gli utenti completano tutti i passaggi. L’analisi del funnel risponde a tre domande, in ordine di profondità:

1. Quanti utenti raggiungono ogni step? (conteggio assoluto)
2. Quanti utenti passano da uno step al successivo? (tasso di conversione tra step)
3. Dove si perde il maggior numero assoluto di utenti? (bottleneck prioritario)

La risposta alla terza domanda non è necessariamente lo step con il tasso di conversione più basso, ma quello con la maggior perdita assoluta rispetto all’investimento. Se lo step 1→2 perde il 40% di 100.000 utenti (40.000 persi) e lo step 3→4 perde il 60% di 10.000 utenti (6.000 persi), il bottleneck è lo step 1→2, anche se il tasso è meno drammatico.

Costruzione del funnel in SQL

Il funnel richiede di aggregare eventi per utente ed estrarre il timestamp del primo raggiungimento di ogni step. La struttura base:

WITH step_events AS (
  SELECT
    user_id,
    MIN(CASE WHEN event = 'signup_started' THEN event_time END) AS step1_time,
    MIN(CASE WHEN event = 'email_verified' THEN event_time END) AS step2_time,
    MIN(CASE WHEN event = 'profile_completed' THEN event_time END) AS step3_time,
    MIN(CASE WHEN event = 'first_post' THEN event_time END) AS step4_time
  FROM events
  GROUP BY user_id
),
funnel_counts AS (
  SELECT
    COUNT(*) AS total,
    COUNT(step1_time) AS step1,
    COUNT(step2_time) AS step2,
    COUNT(step3_time) AS step3,
    COUNT(step4_time) AS step4
  FROM step_events
)
SELECT
  'Users' AS metric,
  total,
  step1,
  ROUND(step1 * 100.0 / total, 1) AS step1_pct,
  step2,
  ROUND(step2 * 100.0 / step1, 1) AS step2_pct,
  step3,
  ROUND(step3 * 100.0 / step2, 1) AS step3_pct,
  step4,
  ROUND(step4 * 100.0 / step3, 1) AS step4_pct
FROM funnel_counts;

Ma questa versione ha un problema: conta tutti gli utenti che prima o poi raggiungono uno step, anche se lo raggiungono in ordine sbagliato. Un funnel vero richiede che gli step siano completati in ordine.

Versione con vincolo temporale (funnel ordinato):

WITH ordered_steps AS (
  SELECT
    user_id,
    MIN(CASE WHEN event = 'signup_started' THEN event_time END) AS step1_time,
    MIN(CASE WHEN event = 'email_verified' AND event_time >
      MIN(CASE WHEN event = 'signup_started' THEN event_time END)
      OVER (PARTITION BY user_id) THEN event_time END) AS step2_time,
    MIN(CASE WHEN event = 'profile_completed' AND event_time >
      MIN(CASE WHEN event = 'email_verified' THEN event_time END)
      OVER (PARTITION BY user_id) THEN event_time END) AS step3_time
  FROM events
  GROUP BY user_id
)

Questa versione garantisce che ogni step avvenga dopo il precedente. Ma la finestra dentro la funzione MIN con condizione è un artificio. Una versione più semplice e leggibile usa una CTE ricorsiva o una subquery correlata per ogni step.

Caso reale: HubSpot e il funnel di conversione del CRM

HubSpot, azienda da 2.2 miliardi di revenue nel 2023, ha costruito il proprio motore di funnel analytics interamente su PostgreSQL + window functions. Il loro funnel classico è: visitatore anonimo → lead conosciuto (form compilato) → MQL (Marketing Qualified Lead) → SQL (Sales Qualified Lead) → opportunità → cliente.

Nel 2020, il team di product analytics di HubSpot scoprì un’anomalia: il tasso di conversione da lead a MQL era del 23%, ma il volume assoluto di MQL era calato del 17% anno su anno nonostante i lead fossero in crescita. La causa, emersa sezionando il funnel per sorgente lead, era un cambiamento nell’algoritmo di scoring automatico (HubSpot usa behavioral scoring per qualificare automaticamente i lead). L’algoritmo aggiornato nel Q3 2019 era più restrittivo e bollava meno lead come MQL, nonostante il volume di lead fosse cresciuto.

La query che rivelò il problema era un funnel segmentato per lead_source e score_version:

WITH funnel AS (
  SELECT
    lead_source,
    score_version,
    COUNT(DISTINCT CASE WHEN step = 'lead' THEN lead_id END) AS leads,
    COUNT(DISTINCT CASE WHEN step = 'mql' THEN lead_id END) AS mqls,
    COUNT(DISTINCT CASE WHEN step = 'sql' THEN lead_id END) AS sqls
  FROM lead_funnel_events
  WHERE event_date >= '2019-01-01'
  GROUP BY lead_source, score_version
)
SELECT lead_source, score_version,
  ROUND(mqls * 100.0 / leads, 1) AS lead_to_mql_pct,
  ROUND(sqls * 100.0 / mqls, 1) AS mql_to_sql_pct
FROM funnel
ORDER BY lead_source, score_version;

Il risultato mostrò che il score_version = 'v3' aveva un tasso lead→MQL del 19%, contro il 27% di v2. Ma i lead di v3 avevano anche una conversione SQL del 48% (contro 41% di v2) — quindi l’algoritmo era più selettivo ma produceva MQL di qualità più alta. La decisione fu di non revertire l’algoritmo ma adattare le quote di marketing al nuovo tasso di conversione.

FIRST_VALUE, LAST_VALUE e NTILE: arricchire il funnel

Le window function speciali permettono di arricchire l’analisi del funnel con contesto.

FIRST_VALUE e LAST_VALUE rispondono a: “Qual era la prima (o ultima) X per ogni utente?” Sono utili per attribuzione:

SELECT user_id,
  FIRST_VALUE(channel) OVER (
    PARTITION BY user_id ORDER BY visit_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS first_channel,
  LAST_VALUE(channel) OVER (
    PARTITION BY user_id ORDER BY visit_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS last_channel
FROM visits;

Attenzione: senza il frame esplicito ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, LAST_VALUE restituisce il valore della riga corrente (default frame: CURRENT ROW), non l’ultimo valore dell’intera partizione. Questo è l’errore più comune con LAST_VALUE.

NTILE segmenta gli utenti in bucket di pari dimensione. Nel contesto del funnel, può creare una “classifica di completamento”: il 20% più veloce a completare il funnel vs il 20% più lento.

Laboratorio pratico: funnel con soglie temporali

Dataset simulato di un funnel di checkout e-commerce: page_view → add_to_cart → begin_checkout → purchase.

Obiettivo: calcolare il funnel con un vincolo temporale — ogni step deve essere completato entro 24 ore dallo step precedente.

WITH ordered_actions AS (
  SELECT
    session_id,
    action,
    action_time,
    ROW_NUMBER() OVER (PARTITION BY session_id, action ORDER BY action_time) AS rn
  FROM checkout_events
),
first_actions AS (
  SELECT * FROM ordered_actions WHERE rn = 1
),
funnel_steps AS (
  SELECT
    pv.session_id,
    pv.action_time AS view_time,
    atc.action_time AS cart_time,
    bc.action_time AS checkout_time,
    pu.action_time AS purchase_time
  FROM (SELECT * FROM first_actions WHERE action = 'page_view') pv
  LEFT JOIN (SELECT * FROM first_actions WHERE action = 'add_to_cart') atc
    ON pv.session_id = atc.session_id
    AND atc.action_time > pv.action_time
    AND atc.action_time <= pv.action_time + INTERVAL '24 hours'
  LEFT JOIN (SELECT * FROM first_actions WHERE action = 'begin_checkout') bc
    ON atc.session_id = bc.session_id
    AND bc.action_time > atc.action_time
    AND bc.action_time <= atc.action_time + INTERVAL '24 hours'
  LEFT JOIN (SELECT * FROM first_actions WHERE action = 'purchase') pu
    ON bc.session_id = pu.session_id
    AND pu.action_time > bc.action_time
    AND pu.action_time <= bc.action_time + INTERVAL '24 hours'
)
SELECT
  COUNT(*) AS sessions,
  COUNT(view_time) AS viewed,
  COUNT(cart_time) AS carted,
  COUNT(checkout_time) AS checked_out,
  COUNT(purchase_time) AS purchased
FROM funnel_steps;

🖥️ Prova in Sandbox

[SQL]Prova tu

Costruisci un funnel di conversione base: conta gli utenti per ogni step del processo di acquisto (page_view, purchase). Usa la tabella eventi con tipo = 'page_view' e 'purchase'.

SELECT tipo, COUNT(DISTINCT utente_id) AS utenti FROM eventi WHERE tipo IN ('page_view', 'purchase') GROUP BY tipo

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Usa FIRST_VALUE per identificare, per ogni utente, il primo canale di acquisizione (fonte nella tabella utenti) e calcola il tasso di conversione per canale.

SELECT u.nome, u.fonte, e.tipo FROM utenti u LEFT JOIN eventi e ON u.id = e.utente_id WHERE e.tipo = 'purchase' OR e.id IS NULL ORDER BY u.nome

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

Checkpoint operativi

1. Qual è il bottleneck di un funnel e come differisce dal tasso di conversione più basso?

2. Perché LAST_VALUE richiede un frame esplicito e cosa succede se ometti il frame?

3. Come si implementa un funnel ordinato temporalmente in SQL? Quali sono i passaggi chiave?

---

Riferimenti accademici:

• McClure, D. (2007). “Startup Metrics for Pirates: AARRR!” 500 Startups.
• Crooks, M. (2020). “Building a Real-time Funnel Analytics Pipeline with Kafka Streams and ClickHouse.” Confluent Blog.
• Ehsan, N. & Patterson, D. (2020). “Accurate Conversion Rate Measurement.” arXiv:2012.04257.

Controllo di qualità

Prima di usare funnel analysis in sql 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.

Problema reale

Nel dominio di advanced SQL, Funnel 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 Funnel 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

Il funnel mostra un drop improvviso tra checkout e pagamento, ma prima di intervenire serve capire se l’ordine degli eventi, le finestre temporali e i criteri di esclusione sono corretti. La query diventa uno strumento di diagnosi, non solo una tabella di conversion rate.

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 Funnel 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 Funnel 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

Funnel 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.