Quando tre domande diverse condividono le stesse tabelle sporche

Questo lab appartiene al binario ml-tabellare: ogni passaggio, dalla deduplica alla riconciliazione, si costruisce con query su tabelle reali.

L’idea in una frase

Il lab integra deduplica, coorti e riconciliazione in una sola base fidata che sostiene attribuzione e confronto tra canali.

Il percorso in cinque passi

1. Deduplica le transazioni con regola deterministica di priorità di stato su transaction_id e quarantena delle righe scartate.
2. Assegna ogni cliente al canale del primo touchpoint osservato con FIRST_VALUE e calcola spesa cumulata a uno, tre, sei e dodici mesi.
3. Etichetta il segmento omnicanale su finestra mobile di dodici mesi con conteggio distinto dei canali via COUNT(DISTINCT channel).
4. Riconcilia warehouse e fonte esterna per mese e per giorno con FULL OUTER JOIN e classifica a mano un campione di righe orfane.
5. Stima i tempi di adozione del secondo canale con curva di sopravvivenza e consegna viste numerate rieseguibili in ordine.

Perché tre domande diverse condividono le stesse tabelle

La tentazione è trattare attribuzione, coorti e riconciliazione come progetti separati. Sarebbe un errore costoso, perché tutte leggono le stesse transazioni e lo stesso grafo di touchpoint. Se ciascuno pulisce i dati a modo suo, si ottengono tre versioni del revenue che non quadrano tra loro. La prima mossa professionale è costruire un singolo strato di transazioni fidate e farci leggere sopra ogni analisi. Il modello dati è volutamente realistico: duplicati fisiologici da retry e reimport, buchi di tracking in alcuni giorni.

Phase	What to clarify	Expected output
Question	Quale scelta reale stai migliorando?	Decision to make
Measure	Quale segnale osservi?	Metrica con unità e finestra
Control	Quale baseline rende il segnale interpretabile?	Credible comparison
Action	Cosa cambia dopo l’analisi?	Next operational step

Deduplicare le transazioni senza buttare segnale

I duplicati sono il primo posto dove un’analisi muore in silenzio. La stessa transazione può comparire fino a tre volte con stati diversi e timestamp ravvicinati. Tenere tutto gonfia il revenue di alcuni punti percentuali, mentre tenere una riga a caso introduce variazioni non riproducibili. La convenzione scelta qui è esplicita: priorità a completed over refunded over pending, e a parità di stato vince la riga più recente. Il resto finisce in una tabella di quarantena ispezionabile, mai cancellata in silenzio.

-- Deduplica deterministica: una riga per transaction_id, quarantena inclusa
WITH ranked AS (
    SELECT
        t.*,
        -- priorità di stato esplicita: completed batte refunded batte pending
        CASE status WHEN 'completed' THEN 0 WHEN 'refunded' THEN 1 ELSE 2 END AS status_prio,
        ROW_NUMBER() OVER (
            PARTITION BY transaction_id
            ORDER BY
                CASE status WHEN 'completed' THEN 0 WHEN 'refunded' THEN 1 ELSE 2 END ASC,
                created_at DESC,   -- a pari stato vince la riga più recente
                source_row_id DESC -- spareggio deterministico finale
        ) AS rn
    FROM transactions t
)
-- tabella fidata: solo rn = 1
-- SELECT * FROM ranked WHERE rn = 1  ->  trusted_transactions
-- SELECT * FROM ranked WHERE rn > 1  ->  quarantine_duplicates
SELECT transaction_id, customer_id, channel, amount_eur, status, created_at
FROM ranked WHERE rn = 1;

Il controllo che chiude il passaggio è l’invariante contabile: la somma delle parti deve ridare il totale al centesimo. Una query di controllo che confronta aggregati su base mensile intercetta regressioni della pipeline prima che raggiungano le slide. I giorni senza alcun touchpoint ma con transazioni normali segnalano quasi sempre un’interruzione del tracciamento. Una spina calendario continua resa con LEFT JOIN contro i touchpoint rende visibili i giorni a zero, da annotare come tracking non affidabile.

-- Giorni con transazioni ma zero touchpoint: probabili buchi di tracking
WITH days AS (
    -- spina calendario continua sul periodo di analisi
    SELECT generate_series(DATE '2024-01-01', DATE '2024-12-31', INTERVAL '1 day')::date AS d
),
daily AS (
    SELECT d.d,
        COUNT(DISTINCT tx.transaction_id) AS n_tx,   -- transazioni fidate del giorno
        COUNT(DISTINCT tp.touch_id) AS n_touch        -- touchpoint registrati
    FROM days d
    LEFT JOIN trusted_transactions tx ON tx.created_at::date = d.d
    LEFT JOIN marketing_touchpoints tp ON tp.touched_at::date = d.d
    GROUP BY d.d
)
SELECT d, n_tx, n_touch
FROM daily
WHERE n_touch = 0 AND n_tx > 20  -- soglia anti-rumore: pochi ordini possono non avere touch
ORDER BY d;

Coorti per canale dal primo tocco al revenue cumulato

L’errore classico è attribuire tutto il revenue all’ultimo click e dichiarare vincente il canale che intercetta la domanda già formata. Il confronto onesto sposta l’unità di analisi dal singolo ordine alla coorte: tutti i clienti il cui primo touchpoint arriva da un canale, con spesa cumulata a uno, tre, sei e dodici mesi. L’assegnazione usa FIRST_VALUE sulla sequenza dei touch di ciascun cliente. È una scelta con limiti noti sui percorsi multitocco, ma stabile nel tempo e senza premi al canale che chiude.

-- Canale di acquisizione = canale del primo touchpoint osservato
WITH first_touch AS (
    SELECT DISTINCT
        customer_id,
        FIRST_VALUE(channel) OVER (
            PARTITION BY customer_id ORDER BY touched_at ASC, touch_id ASC
        ) AS acquisition_channel,
        MIN(touched_at) OVER (PARTITION BY customer_id) AS cohort_month
    FROM marketing_touchpoints
),
cohort_revenue AS (
    SELECT
        date_trunc('month', f.cohort_month)::date AS coorte,
        f.acquisition_channel AS canale,
        tx.customer_id,
        -- mesi trascorsi tra acquisizione e transazione (0 = stesso mese)
        (EXTRACT(YEAR FROM tx.created_at) - EXTRACT(YEAR FROM f.cohort_month)) * 12
          + (EXTRACT(MONTH FROM tx.created_at) - EXTRACT(MONTH FROM f.cohort_month)) AS mese_idx,
        SUM(tx.amount_eur) AS speso
    FROM first_touch f
    JOIN trusted_transactions tx USING (customer_id)
    WHERE tx.status = 'completed' AND tx.created_at >= f.cohort_month
    GROUP BY 1, 2, 3, 4
)
-- LTV cumulato medio per coorte a 1/3/6/12 mesi: pivot sugli orizzonti
SELECT coorte, canale,
    AVG(SUM(CASE WHEN mese_idx <= 0 THEN speso ELSE 0 END)) OVER w AS ltv_m1,
    AVG(SUM(CASE WHEN mese_idx <= 2 THEN speso ELSE 0 END)) OVER w AS ltv_m3,
    AVG(SUM(CASE WHEN mese_idx <= 5 THEN speso ELSE 0 END)) OVER w AS ltv_m6,
    AVG(SUM(CASE WHEN mese_idx <= 11 THEN speso ELSE 0 END)) OVER w AS ltv_m12
FROM cohort_revenue
WINDOW w AS (PARTITION BY coorte, canale);

Presentare le differenze senza intervalli di confidenza sarebbe disonesto. Con coorti da poche centinaia di clienti, due canali con intervalli ampiamente sovrapposti non sono vincitore e perdente ma un pareggio statistico. Meglio una tabella con stima, intervallo e numerosità che un grafico a barre senza barre d’errore.

Quanto vale davvero un cliente omnicanale

La definizione operativa conta più del confronto. Qui omnicanale significa transazioni completate su almeno due canali distinti negli ultimi dodici mesi mobili. La finestra mobile evita di gonfiare il gruppo con clienti vecchi e di trasformare il confronto in una proxy dell’anzianità. Il confronto tra segmenti va fatto a parità di anzianità: clienti acquisiti nello stesso trimestre, su ordini per cliente, scontrino medio e valore a dodici mesi.

-- Etichetta omnicanale su finestra mobile di 12 mesi
WITH cust_month AS (
    SELECT DISTINCT
        customer_id,
        date_trunc('month', created_at)::date AS mese,
        channel
    FROM trusted_transactions
    WHERE status = 'completed'
),
multicanale AS (
    SELECT customer_id, mese,
        COUNT(DISTINCT channel) OVER (
            PARTITION BY customer_id
            ORDER BY mese
            RANGE BETWEEN INTERVAL '11 months' PRECEDING AND CURRENT ROW
        ) AS n_canali_12m
    FROM cust_month
)
SELECT customer_id, mese,
    CASE WHEN n_canali_12m >= 2 THEN 'omnicanale' ELSE 'single-channel' END AS segmento
FROM multicanale;

Metrica (coorte Q1, 12 mesi)	Single-channel	Omnicanale	Nota
Ordini per cliente	2,1	4,3	Guida la differenza
Scontrino medio	54 €	61 €	Effetto minore
LTV 12 mesi	113 €	262 €	Stratificato per anzianità
Retention mese 12	31%	57%	Definizione sopra

Il rapporto grezzo sovrastima l’effetto per selezione dei migliori clienti. Stratificando per coorte di acquisizione il divario si riduce ma resta ampio e significativo su grandi numeri. Sapere che gli omnicanale spendono di più non dimostra che spingere un cliente sul secondo canale ne raddoppi la spesa. L’uso onesto è priorizzare l’esperienza cross-canale per chi mostra segnali di adozione e disegnare un test controllato prima di promettere incrementi diffusi.

Riconciliare Shopify e warehouse riga per riga

La riconciliazione seria lavora a tre livelli: il mese quantifica, il giorno localizza, la singola transazione spiega. Il primo passo aggrega entrambe le fonti per mese con FULL OUTER JOIN, così emergono anche i mesi presenti da un solo lato. Il secondo passo segmenta per giorno della settimana per intercettare disallineamenti di cut-off nei weekend. Il terzo passo usa l’anti-JOIN per elencare le transazioni presenti da un solo lato e classificarne a mano un campione prima di automatizzare.

-- Riconciliazione mensile: FULL OUTER JOIN per non perdere mesi orfani
WITH wh AS (
    SELECT date_trunc('month', created_at)::date AS mese,
           SUM(amount_eur) AS revenue_wh
    FROM trusted_transactions
    WHERE channel = 'web' AND status = 'completed'
    GROUP BY 1
),
sh AS (
    SELECT date_trunc('month', settled_at)::date AS mese,
           SUM(gross_eur) AS revenue_shopify
    FROM shopify_settlements
    GROUP BY 1
)
SELECT
    COALESCE(wh.mese, sh.mese) AS mese,
    wh.revenue_wh, sh.revenue_shopify,
    -- discrepanza assoluta e relativa rispetto a Shopify (verità esterna)
    COALESCE(wh.revenue_wh, 0) - COALESCE(sh.revenue_shopify, 0) AS delta_eur,
    CASE WHEN sh.revenue_shopify <> 0
         THEN (COALESCE(wh.revenue_wh, 0) - sh.revenue_shopify) / sh.revenue_shopify END AS delta_rel
FROM wh FULL OUTER JOIN sh USING (mese)
ORDER BY mese;

-- Transazioni web senza settlement corrispondente (anti-join)
SELECT tx.transaction_id, tx.amount_eur, tx.created_at, tx.status
FROM trusted_transactions tx
LEFT JOIN shopify_settlements sh USING (transaction_id)
WHERE tx.channel = 'web' AND sh.transaction_id IS NULL
ORDER BY tx.created_at DESC
LIMIT 50; -- campione da classificare a mano prima di automatizzare

La classificazione manuale del campione rivela pattern operativi come metodi di pagamento specifici, fasce orarie o procedure difformi. Nel caso tipico oltre metà della discrepanza si spiega con timing diverso e un quarto con fee trattate in modo difforme. Al responsabile finanziario va presentata la scomposizione tra quota spiegata, quota fisiologica e residuo aperto.

Il funnel di migrazione e le curve di sopravvivenza

Resta la domanda su quanto tempo passa prima che un cliente adotti il secondo canale. La risposta onesta richiede l’analisi di sopravvivenza, perché gran parte dei clienti a fine periodo non ha ancora migrato: sono osservazioni censurate, non zeri, e trattarle come zeri sottostima i tempi. Lo stimatore standard gestisce la censura con prodotto cumulato dei fattori di sopravvivenza, calcolabile in SQL con somma dei logaritmi e riesponenziazione via EXP(SUM(LN(...))).

-- Curva di Kaplan-Meier: probabilità di restare single-channel oltre t
WITH eventi AS (
    -- per ciascun cliente: mesi all'adozione del 2° canale o alla censura
    SELECT customer_id, mesi_al_secondo_canale AS t,
           CASE WHEN migrato THEN 1 ELSE 0 END AS evento
    FROM single_channel_followup
),
rischio AS (
    SELECT t,
        SUM(evento) AS d,          -- migrazioni osservate a t
        COUNT(*) AS n_uscenti,     -- escono dal rischio a t (evento o censura)
        SUM(COUNT(*)) OVER (ORDER BY t DESC
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS n_rischio
    FROM eventi GROUP BY t
)
SELECT t,
    -- prodotto cumulato dei fattori (1 - d/n) via somma dei logaritmi
    EXP(SUM(LN(1 - d::double precision / NULLIF(n_rischio, 0)))
        OVER (ORDER BY t)) AS prob_single_channel,
    n_rischio AS ancora_osservati
FROM rischio ORDER BY t;

La lettura tipica mostra adozione più rapida per i clienti web-first e più lenta per i negozio-first, con appiattimento dopo il primo anno. Il confronto tra curve per segmento richiede test dedicati, e l’effetto di covariate come scontrino iniziale richiede modelli appositi. Se l’adozione è guidata da fattori non osservati, la curva descrive bene i tempi ma non isola la leva causale.

Leggere l’incertezza prima di scrivere la raccomandazione

A questo punto i risultati vanno tradotti in slide solo dopo un inventario esplicito di ciò che potrebbe ribaltarli. Il formato che funziona con stakeholder non tecnici lega ogni affermazione a fragilità, controllo già fatto e rischio residuo.

Affermazione	Cosa la ribalterebbe	Controllo applicato	Rischio residuo
L’influencer batte il paid social su LTV 12 mesi	Coorti piccole, errore standard alto	Intervalli al 95% e numerosità riportate	Sovrapposizione parziale: pareggio possibile
Gli omnicanale valgono 1,8x	Autoselezione dei migliori clienti	Stratificazione per coorte di acquisizione	Causalità non dimostrata, serve test
Lo 0,3% residuo è fisiologico	Errori sistematici su un metodo di pagamento	Classificazione manuale di 50 righe	Ricontrollare dopo il fix del cut-off
La mediana di adozione è 9 mesi web-first	Censura informativa (i migliori migrano prima)	Kaplan-Meier con censura a destra	Stima valida solo sotto censura non informativa

Due controlli trasversali meritano di diventare abitudine. La somma degli LTV per canale pesata per le numerosità deve ridare il revenue fidato entro una tolleranza esplicita di 1% di scarto relativo. La riesecuzione delle metriche chiave con una regola di deduplica alternativa deve confermare l’ordinamento dei canali, altrimenti la conclusione è fragile.

Mettere la pipeline in uno schema che altri possono rieseguire

L’ultimo miglio è ingegneristico: query esplorative trasformate in viste ordinate dentro uno schema dedicato. La convenzione di nomi rende l’ordine evidente e i commenti documentano ogni assunzione. La vista finale delle raccomandazioni è il vero output, con impatto stimato, evidenza, costo e rischio per ogni punto.

-- Schema di consegna: view numerate, rieseguibili in ordine
CREATE SCHEMA IF NOT EXISTS analytics_fashionhub;

-- 01: transazioni fidate (regola: vince completed, poi riga più recente)
CREATE OR REPLACE VIEW analytics_fashionhub.v01_trusted_transactions AS
SELECT transaction_id, customer_id, channel, amount_eur, status, created_at
FROM ranked_dedup WHERE rn = 1; -- ranked_dedup = CTE di deduplica sopra

-- 02: coorti di acquisizione dal primo touchpoint osservato
CREATE OR REPLACE VIEW analytics_fashionhub.v02_acquisition_cohorts AS
SELECT customer_id, acquisition_channel, cohort_month FROM first_touch;

-- 03: LTV cumulato per coorte e canale a 1/3/6/12 mesi
-- 04: segmenti omnicanale vs single-channel su finestra mobile 12 mesi
-- 05: riconciliazione mensile warehouse vs Shopify con delta relativo
-- 06: curva di Kaplan-Meier per l'adozione del secondo canale
-- 07: tabella raccomandazioni con query di evidenza per ciascun punto

In sintesi: usa il primo touchpoint per coorti stabili e la finestra mobile di dodici mesi per l’etichetta omnicanale, poi riconcilia sempre contro la fonte esterna prima di spostare budget.

Verdetto: il primo touchpoint vince per coorti stabili e la finestra mobile di dodici mesi per l’etichetta omnicanale; riconcilia sempre contro la fonte esterna prima di spostare budget.

Il caso Knight Capital: il prezzo di non avere controlli

Knight Capital, il primo agosto 2012, perse circa 440 milioni di dollari in circa 45 minuti per un aggiornamento software difettoso sui sistemi di negoziazione. Il sistema difettoso inviò milioni di ordini errati prima che il team riuscisse a fermarlo. La società dovette ricorrere a un salvataggio esterno e perse gran parte del valore in pochi giorni. Il caso mostra perché deduplica deterministica, riconciliazione continua e interruttori di arresto non sono optional quando i soldi veri passano dalle tabelle.

Domande per chiudere la lezione

1. Perché la deduplica deve fissare una priorità di stato esplicita invece di tenere una riga a caso?
2. Quando il primo touchpoint è preferibile all’ultimo click per confrontare i canali?
3. Quale JOIN useresti per trovare transazioni senza settlement e perché la INNER JOIN non basta?
4. Perché la finestra mobile di dodici mesi evita di confondere anzianità e comportamento omnicanale?