Lab avanzato: query professionali su casi reali

FashionHub ha vendite online, ordini in negozio, campagne sovrapposte e un CMO che chiede una raccomandazione prima della prossima riunione budget. I dati non arrivano già pronti: vanno puliti, riconciliati e trasformati in una decisione. Lab avanzato: query professionali su casi reali mette insieme le competenze del modulo in un flusso vicino al lavoro quotidiano.

Una scena da cui partire

Leggi questo caso come una consegna da consulenza: non basta ottenere un risultato numerico, devi mostrare da dove viene, quali esclusioni hai fatto e quale scelta suggerisci. Ogni query dovrebbe ridurre l’ambiguità tra dati grezzi e decisione del CMO.

• Contesto: Quale vincolo del business cambia la lettura del dato?
• Metodo: Quale controllo rende credibile la raccomandazione?
• Applicazione: Quale decisione prenderesti se il tempo per analizzare fosse limitato?

Il contesto: “FashionHub”

FashionHub è un retailer di moda con 85 negozi fisici in Italia e un e-commerce. L’azienda sta affrontando tre sfide:

1. Il CMO vuole sapere quali canali di marketing generano il revenue incrementale, non solo attribuito. Serve un’analisi di coorte per canale.
2. Il team di prodotto vuole capire se i clienti omnicanale (online + negozio) hanno un LTV più alto dei clienti single-channel.
3. Il CFO chiede una riconciliazione del revenue mensile tra il sistema di pagamento (Shopify Payments) e il data warehouse. Ci sono discrepanze del 1.8%.

Avete a disposizione quattro tabelle:

Tabella	Righe	Grain	Descrizione
customers	450K	customer_id	Anagrafica clienti registrati
transactions	2.8M	transaction_id	Ogni acquisto, online o in negozio
marketing_touchpoints	5.2M	touchpoint_id	Esposizioni a canali marketing (display, search, email, social)
shopify_settlements	36K	settlement_id	Pagamenti ricevuti da Shopify (per riconciliazione)

Schema delle tabelle:

customers (customer_id, email, signup_date, country, city, segment)
transactions (transaction_id, customer_id, channel, store_id, amount, cost, margin,
              txn_date, status, currency)
marketing_touchpoints (touchpoint_id, customer_id, channel, campaign_id,
                       touch_date, cost, attributed_revenue)
shopify_settlements (settlement_id, settlement_date, gross_amount, fees, net_amount,
                     currency, period_start, period_end)

Esercizio 1: Pulizia e preparazione (data quality)

Prima di ogni analisi, verifica l’integrità dei dati.

Task 1.1 — Identifica transazioni duplicate: Scrivi una query che trova transaction_id presenti più di una volta nella tabella transactions. Per i duplicati, tieni solo la riga con txn_date più recente e status = 'completed' (se esiste).

WITH duplicates AS (
  SELECT transaction_id, COUNT(*) AS cnt
  FROM transactions
  GROUP BY transaction_id
  HAVING COUNT(*) > 1
),
ranked AS (
  SELECT t.*,
    ROW_NUMBER() OVER (
      PARTITION BY t.transaction_id
      ORDER BY
        CASE WHEN t.status = 'completed' THEN 0 ELSE 1 END,
        t.txn_date DESC
    ) AS rn
  FROM transactions t
  JOIN duplicates d ON t.transaction_id = d.transaction_id
)
-- Crea una tabella pulita o una view
CREATE VIEW transactions_clean AS
SELECT * FROM transactions
WHERE transaction_id NOT IN (SELECT transaction_id FROM duplicates)
UNION ALL
SELECT * FROM ranked WHERE rn = 1;

Task 1.2 — Gap nei dati di marketing: Trova i giorni senza alcun touchpoint marketing. La data spine sarà essenziale. Il marketing spende 12.000€/giorno; un giorno senza dati significa che mancano completamente le campagne o c’è un bug nel tracking.

🖥️ Prova in Sandbox

[SQL]Prova tu

Deduplica gli ordini: trova gli utenti che hanno più ordini nella stessa categoria. Usa ROW_NUMBER() per assegnare un rank per utente+categoria, poi filtra per tenere solo il primo ordine (quello più recente).

SELECT utente_id, categoria, data_ordine, importo FROM ordini ORDER BY utente_id, categoria, data_ordine

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

[SQL]Prova tu

Riconciliazione: confronta il totale importi per utente dalla tabella ordini con gli eventi 'purchase' della tabella eventi. Usa un FULL OUTER JOIN tra le due aggregazioni per trovare discrepanze.

SELECT o.utente_id, SUM(o.importo) AS totale_ordini FROM ordini o GROUP BY o.utente_id ORDER BY o.utente_id

EseguiCtrl+Enter per eseguireSuggerimentoSoluzione

---

Esercizio 2: Analisi di coorte per canale marketing

Il CMO vuole sapere: “Se smetto di spendere su Facebook Ads, quanto revenue perdo?” Non basta l’attribuzione last-click — serve vedere il comportamento nel tempo dei clienti acquisiti da ciascun canale.

Task 2.1 — Assegna ogni cliente al canale del primo touchpoint:

WITH first_touch AS (
  SELECT DISTINCT ON (customer_id)
    customer_id, channel AS acquisition_channel, touch_date
  FROM marketing_touchpoints
  ORDER BY customer_id, touch_date ASC
)
SELECT ft.acquisition_channel,
  DATE_TRUNC('month', ft.touch_date) AS cohort_month,
  COUNT(DISTINCT ft.customer_id) AS acquired_customers
FROM first_touch ft
GROUP BY ft.acquisition_channel, cohort_month;

Task 2.2 — Matrice di retention per canale: Per ogni coorte (mese × canale) calcola la retention e il revenue cumulativo a 1, 3, 6 e 12 mesi. Crea una vista tabellare che il CMO possa esplorare in una dashboard.

Task 2.3 — Confronto statistico: I clienti acquisiti via Search hanno un LTV a 12 mesi diverso da quelli via Social? Calcola la differenza media e l’intervallo di confidenza al 95%.

---

Esercizio 3: Segmentazione omnicanale vs single-channel

Il team di prodotto ipotizza che i clienti omnicanale abbiano un LTV più alto. Verifica l’ipotesi con un’analisi statistica.

Task 3.1 — Etichetta ogni cliente come omnicanale o single-channel:

Un cliente è omnicanale se ha transazioni su più di un canale (es. online e store) negli ultimi 12 mesi.

Task 3.2 — Confronta le metriche: Calcola e confronta:

• Numero medio di transazioni all’anno
• Valore medio per transazione (AOV)
• Revenue totale per cliente (LTV a 12 mesi)
• Tasso di churn (nessuna transazione da 6 mesi)

Per ogni metrica, estrai la significatività statistica della differenza.

Task 3.3 — Funnel di migrazione: Tra i clienti che iniziano online, quale percentuale effettua un acquisto in negozio entro 12 mesi? Costruisci una curva di Kaplan-Meier semplificata: la percentuale cumulativa di clienti che hanno effettuato il primo acquisto in negozio entro ciascun mese.

---

Esercizio 4: Riconciliazione del revenue

Il CFO segnala una discrepanza dell’1.8% tra il revenue nel data warehouse e i settlement Shopify.

Task 4.1 — Confronto mensile:

WITH wh_revenue AS (
  SELECT DATE_TRUNC('month', txn_date) AS month,
    SUM(amount) AS wh_amount, COUNT(*) AS wh_txns
  FROM transactions_clean
  WHERE channel = 'online' AND status = 'completed'
    AND currency = 'EUR'
  GROUP BY month
),
shopify_revenue AS (
  SELECT DATE_TRUNC('month', settlement_date) AS month,
    SUM(gross_amount) AS shopify_amount
  FROM shopify_settlements
  WHERE currency = 'EUR'
  GROUP BY month
)
SELECT COALESCE(w.month, s.month) AS month,
  COALESCE(w.wh_amount, 0) AS warehouse_revenue,
  COALESCE(s.shopify_amount, 0) AS shopify_revenue,
  COALESCE(s.shopify_amount, 0) - COALESCE(w.wh_amount, 0) AS gap
FROM wh_revenue w
FULL OUTER JOIN shopify_revenue s ON w.month = s.month
ORDER BY month;

Task 4.2 — Diagnostica: La discrepanza è concentrata in giorni specifici del mese (es. fine mese, weekend)? Segmenta per giorno del mese e giorno della settimana. Usa le window function per calcolare la media mobile del gap e identificare outlier.

Task 4.3 — Correzione: Scrivi una query che riconcilia automaticamente identificando le transazioni presenti in Shopify ma non nel warehouse (e viceversa). Usa un anti-join.

---

Esercizio 5: Raccomandazione finale per il CMO

Sintetizza i risultati in una query che produce un report esecutivo:

• Top 3 insight emersi dall’analisi
• 3 raccomandazioni operative con impatto stimato in Euro
• Principali rischi e assunzioni da validare

Il formato: una tabella con insight_id, insight_desc, evidence_query (una sottoquery che produce il numero chiave), recommendation, estimated_impact_eur, confidence_level.

---

Consegna

Il lavoro va consegnato come una serie di view SQL (non query una tantum) in uno schema analytics_fashionhub, con nomi parlanti e commenti. Le view devono essere eseguibili nell’ordine: le view downstream dipendono dalle upstream, e la pipeline è ricostruibile con un singolo script.

---

Riferimenti:

• Kimball, R. & Ross, M. (2013). The Data Warehouse Toolkit, 3rd ed. Wiley. Capitolo 6: “Order Management.”
• Amazon Retail Analytics Documentation. (2023). “Omnichannel Metrics and Cohort Analysis.”

Problema reale

Nel dominio di advanced SQL, ‘Lab avanzato: query professionali su casi reali’ 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 ‘Lab avanzato: query professionali su casi reali’ 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 lab mette insieme retention, funnel, deduplica, attribution e performance su uno stesso dataset. L’obiettivo non è scrivere la query più breve, ma produrre SQL che un collega possa leggere, testare, ottimizzare e difendere davanti a una decisione reale.

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 ‘Lab avanzato: query professionali su casi reali’: 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 ‘Lab avanzato: query professionali su casi reali’ 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

‘Lab avanzato: query professionali su casi reali’ 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: Lab. Difficoltà: advanced. Tempo stimato: 28 min.