Anomalie, Pareto e segmentazione: trovare i pochi che contano con SQL

Anche questa lezione vive nel binario ml-tabellare: tutto ciò che vedremo si costruisce con query su tabelle, senza uscire dal database.

L’idea in una frase

La segmentazione guidata da Pareto separa i pochi clienti e prodotti che spiegano la quota dominante del margine dal resto della base.

Il metodo in cinque passi

1. Calcola la distribuzione della spesa per cliente per decili con NTILE(10) e verifica che l’ultimo decile pesi in modo anomalo sul totale.
2. Costruisci una baseline mobile per prodotto con AVG e STDDEV su finestra e segnala come anomalie solo gli scarti ampi e persistenti rispetto alla storia recente.
3. Ordina i clienti per margine decrescente, accumula le quote con SUM() OVER () e leggi quanti servono per coprire l’80% del totale.
4. Segmenta su dimensioni comportamentali con soglie mediane versionate via PERCENTILE_CONT e assegna a ogni segmento un’azione con criterio di arresto.
5. Misura ogni azione contro un gruppo di controllo tenuto fuori dall’intervento con COUNT(*) FILTER (WHERE ...).

Quando pochi clienti spiegano quasi tutto il fatturato

Quasi ogni base clienti obbedisce a una concentrazione spietata: pochi elementi pesano più di tutti gli altri messi insieme. Il primo passo in SQL è guardare la distribuzione prima di qualsiasi aggregato furbo. Una query per decili di spesa dice in dieci righe quello che un indicatore medio non dirà mai. Se i decili risultano quasi piatti, non c’è concentrazione da sfruttare, e forzare una segmentazione produce gruppi arbitrari.

-- Distribuzione della spesa per cliente: dieci righe che valgono più di un KPI medio
WITH spesa_cliente AS (
    SELECT
        customer_id,
        SUM(amount) AS spesa_totale -- spesa cumulata del cliente nel periodo
    FROM orders
    WHERE order_date >= '2024-01-01' -- finestra di analisi fissa, mai dimenticarla
      AND status = 'completed'       -- solo ordini effettivi, niente rimborsati
    GROUP BY customer_id
)
SELECT
    NTILE(10) OVER (ORDER BY spesa_totale) AS decile, -- 1 = chi spende meno, 10 = top spender
    COUNT(*) AS n_clienti,
    ROUND(AVG(spesa_totale), 2) AS spesa_media,
    ROUND(SUM(spesa_totale), 2) AS spesa_complessiva
FROM spesa_cliente
GROUP BY decile
ORDER BY decile;

Su dati reali il decile più alto pesa tipicamente tra il 40% e il 70% del totale. Il controllo onesto sulla forma della distribuzione viene sempre prima della tecnica.

Trovare le anomalie senza farsi ingannare dal rumore

Un ordine molto grande su uno storico di ordini piccoli è un’anomalia, mentre un picco di vendite a dicembre è stagionalità. La distinzione passa per una baseline esplicita, con confronto tra osservazione e atteso per stesso cliente, prodotto o periodo. Il metodo più robusto in SQL puro confronta il fatturato del mese con la media degli ultimi mesi, escludendo il mese stesso dal calcolo: in questo modo l’anomalia non contamina il suo stesso termine di paragone.

-- Z-score mensile per prodotto: scarto dalla media mobile dei 6 mesi precedenti
WITH mensile AS (
    SELECT
        product_id,
        DATE_TRUNC('month', order_date) AS mese, -- granularità mensile
        SUM(amount) AS ricavo -- segnale osservato
    FROM orders
    WHERE status = 'completed'
    GROUP BY product_id, DATE_TRUNC('month', order_date)
),
con_baseline AS (
    SELECT
        product_id,
        mese,
        ricavo,
        -- media e deviazione sui 6 mesi precedenti, mese corrente escluso
        AVG(ricavo) OVER (
            PARTITION BY product_id ORDER BY mese
            ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
        ) AS media_6m,
        STDDEV(ricavo) OVER (
            PARTITION BY product_id ORDER BY mese
            ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING
        ) AS sigma_6m
    FROM mensile
)
SELECT
    product_id,
    mese,
    ricavo,
    -- z-score: quanti sigma sopra o sotto la baseline; |z| > 3 merita indagine
    (ricavo - media_6m) / NULLIF(sigma_6m, 0) AS z_score
FROM con_baseline
WHERE media_6m IS NOT NULL -- servono almeno mesi di storia prima di giudicare
ORDER BY ABS((ricavo - media_6m) / NULLIF(sigma_6m, 0)) DESC NULLS LAST;

Con pochi dati storici la deviazione standard è instabile e un singolo mese folle può nascondere sé stesso. Per serie corte o rumorose conviene la mediana con scarto mediano, molto meno sensibile agli estremi e calcolabile con PERCENTILE_CONT.

La curva di Pareto in SQL con cumulata e soglia dell’ottanta per cento

Dire che vale la regola ottanta-venti senza misurarla è folklore. La verifica richiede la curva cumulata: clienti ordinati per contributo decrescente e accumulo delle quote fino alla soglia. Due window function bastano, e il risultato è un numero difendibile in riunione. Il dettaglio che cambia tutto è usare il margine invece del fatturato, perché i top spender per fatturato includono spesso clienti con molti resi e molta assistenza.

-- Curva di Pareto: quanti clienti servono per l'80% del margine?
WITH margine_cliente AS (
    SELECT
        customer_id,
        SUM(amount - cost) AS margine -- il margine, non il fatturato: i resi contano
    FROM orders
    WHERE status = 'completed'
      AND order_date >= '2024-01-01'
    GROUP BY customer_id
),
ordinati AS (
    SELECT
        customer_id,
        margine,
        -- quota di ciascuno sul totale generale
        margine / SUM(margine) OVER () AS quota,
        ROW_NUMBER() OVER (ORDER BY margine DESC) AS posizione
    FROM margine_cliente
),
cumulata AS (
    SELECT
        customer_id,
        margine,
        quota,
        posizione,
        -- cumulata: somma delle quote dal primo fino a questo cliente
        SUM(quota) OVER (ORDER BY margine DESC) AS quota_cumulata
    FROM ordinati
)
SELECT
    -- percentuale di clienti che copre l'80% del margine: il vero numero di Pareto
    MIN(posizione) * 100.0 / (SELECT COUNT(*) FROM margine_cliente) AS pct_clienti_per_80
FROM cumulata
WHERE quota_cumulata >= 0.8;

Con molti clienti piccoli e identici l’ordinamento tra pari balla a ogni esecuzione. Per report stabili conviene raggruppare per fasce di margine oppure fissare un ordinamento secondario deterministico su customer_id.

Segmentare i clienti in modo che regga alla prova dei dati

Una segmentazione utile assegna ogni cliente a un solo segmento, con azioni diverse per gruppo e regole ripetibili da un collega sugli stessi dati. Le segmentazioni a regole, con soglie esplicite su poche dimensioni, sono meno eleganti dei cluster automatici e molto più governabili. La regola d’oro è segmentare su dimensioni indipendenti dalla metrica che si vuole muovere, per evitare tautologie. Dimensioni buone sono frequenza d’ordine e ampiezza del catalogo, con leve operative distinte per quadrante.

-- Segmentazione comportamentale: frequenza x ampiezza catalogo, 4 quadranti azionabili
WITH comportamento AS (
    SELECT
        customer_id,
        COUNT(DISTINCT order_id) AS n_ordini, -- frequenza: quante volte torna
        COUNT(DISTINCT product_id) AS ampiezza, -- ampiezza: quanto esplora il catalogo
        SUM(amount) AS spesa
    FROM orders
    WHERE status = 'completed'
      AND order_date >= '2024-01-01'
    GROUP BY customer_id
),
soglie AS (
    -- mediane come soglie: robuste agli estremi, ricalcolabili ogni periodo
    SELECT
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY n_ordini) AS med_ordini,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ampiezza) AS med_ampiezza
    FROM comportamento
)
SELECT
    c.customer_id,
    CASE
        WHEN c.n_ordini >= s.med_ordini AND c.ampiezza >= s.med_ampiezza
            THEN 'esploratori_fedeli' -- comprano spesso e variano: candidati a upsell
        WHEN c.n_ordini >= s.med_ordini AND c.ampiezza < s.med_ampiezza
            THEN 'abitudinari' -- stesso prodotto ogni volta: candidati ad abbonamento
        WHEN c.n_ordini < s.med_ordini AND c.ampiezza >= s.med_ampiezza
            THEN 'occasionali_curiosi' -- raro ma vario: da riattivare con promo mirate
        ELSE 'marginali' -- raro e monotono: non investire oltre il marketing di base
    END AS segmento,
    c.spesa
FROM comportamento c CROSS JOIN soglie s;

Le mediane come soglie rendono i quadranti bilanciati, ma vanno ricalcolate e versionate a ogni periodo. Salvare soglie e regole in una tabella di configurazione con data di validità trasforma la segmentazione da script usa e getta a sistema.

Il modello RFM con le window function

The model RFM resta la segmentazione più collaudata del commercio: recency, frequency e monetary. Tre dimensioni e cinque punteggi ciascuna si compattano in gruppi operativi come campioni, fedeli, a rischio, persi e nuovi. Ogni dimensione punta a un’azione: riattivazione per recency bassa, premi per frequency alta, gestione dedicata per monetary alto. L’implementazione pulita usa quintili separati per dimensione con NTILE(5), con l’accortezza di invertire la recency.

-- Punteggi RFM 1-5 per cliente: NTILE separato su ciascuna dimensione
WITH rfm_base AS (
    SELECT
        customer_id,
        CURRENT_DATE - MAX(order_date) AS recency_gg, -- giorni dall'ultimo ordine
        COUNT(DISTINCT order_id) AS frequency,        -- quante volte ha comprato
        SUM(amount) AS monetary                        -- quanto ha speso in totale
    FROM orders
    WHERE status = 'completed'
      AND order_date >= CURRENT_DATE - INTERVAL '12 months' -- finestra mobile di 12 mesi
    GROUP BY customer_id
),
rfm_score AS (
    SELECT
        customer_id,
        recency_gg, frequency, monetary,
        -- recency invertita: ordine DESC sui giorni, chi ha comprato ieri prende 5
        NTILE(5) OVER (ORDER BY recency_gg DESC) AS r,
        NTILE(5) OVER (ORDER BY frequency) AS f,
        NTILE(5) OVER (ORDER BY monetary) AS m
    FROM rfm_base
)
SELECT
    customer_id, recency_gg, frequency, monetary,
    r, f, m,
    CASE
        WHEN r >= 4 AND f >= 4 AND m >= 4 THEN 'campioni' -- tutto alto: coccolare e trattenere
        WHEN r <= 2 AND f >= 4 THEN 'a_rischio' -- compravano spesso, spariti: riattivare subito
        WHEN r <= 2 AND f <= 2 THEN 'persi' -- andati da tempo e mai fedeli: ultimo tentativo
        WHEN r >= 4 AND f <= 2 THEN 'nuovi' -- arrivati da poco: onboarding e secondo acquisto
        ELSE 'regolari' -- il resto: comunicazione standard, niente sprechi
    END AS segmento_rfm
FROM rfm_score;

Con molti pareggi sulla frequency i quintili assegnano gruppi in modo arbitrario tra pari; con distribuzioni molto discrete sono più oneste soglie fisse documentate. Il modello fotografa il passato, quindi va sempre incrociato con i dati di canale prima di agire con sconti.

Dalla segmentazione alla decisione operativa

Un segmento senza azione assegnata resta un esercizio di tassonomia. Il formato che funziona lega ogni gruppo a leva, responsabile e metrica di successo misurabile, usando la stessa query che ha creato i segmenti. Il criterio di arresto va dichiarato in anticipo, con soglia di riattivazione e numero massimo di campagne. La misurazione richiede un gruppo di controllo tenuto fuori dall’azione: altrimenti ogni miglioramento viene attribuito alla campagna anche se sarebbe avvenuto comunque.

-- Lettura dell'effetto campagna: trattati contro controllo sullo stesso segmento
SELECT
    gruppo, -- 'trattati' oppure 'controllo', assegnato a caso prima dell'invio
    COUNT(*) AS n_clienti,
    -- tasso di riacquisto nel mese successivo alla campagna
    COUNT(*) FILTER (WHERE ha_riacquistato) * 1.0 / COUNT(*) AS tasso_riacquisto
FROM campagna_riattivazione
WHERE segmento = 'a_rischio'
GROUP BY gruppo;

Se il controllo converte quasi quanto i trattati, il buono sta premiando chi avrebbe ricomprato comunque. La differenza tra i due tassi moltiplicata per il margine medio è il vero rendimento dell’iniziativa.

I casi in cui Pareto e segmenti raccontano bugie

La concentrazione misurata su un anno può dissolversi su tre anni per rotazione dei top clienti, scadenze di contratti e ordini straordinari isolati. Prima di investire conviene verificare che sia più o meno lo stesso 20% di sei mesi fa, con una misura di sovrapposizione tra liste top. Segmentare descrive e non spiega: la direzione causale resta indimostrata senza test. Sotto i cento o duecento individui per gruppo i tassi oscillano per puro caso e vanno letti con intervalli di confidenza oppure accorpando i segmenti piccoli. E se il gestionale archivia solo ordini sopra soglia, la coda bassa è mutilata e la concentrazione appare più estrema del reale.

Mettere il controllo in produzione

Anomalie e segmenti valgono di più quando girano ogni mattina invece di vivere in un notebook eseguito a mano una volta al trimestre. L’architettura minima è una vista o tabella materializzata che ricalcola punteggi e segmenti, più una query di sorveglianza che scrive le eccezioni in una tabella di alert. La soglia di allerta deve richiedere persistenza su due periodi consecutivi e pesare per impatto economico, con soglia di materialità in euro. Ogni riga di alert riceve un esito tra vero positivo e falso allarme e causa nota, e la distribuzione degli esiti guida la taratura trimestrale.

-- Sorveglianza giornaliera: anomalie persistenti e materialmente rilevanti
WITH giornaliero AS (
    SELECT
        product_id,
        CURRENT_DATE - 1 AS giorno, -- ieri: ultimo giorno completo disponibile
        SUM(amount) AS ricavo
    FROM orders
    WHERE order_date = CURRENT_DATE - 1
      AND status = 'completed'
    GROUP BY product_id
),
baseline AS (
    SELECT
        product_id,
        AVG(ricavo_g) AS media_30g, -- media mobile 30 giorni come atteso
        STDDEV(ricavo_g) AS sigma_30g
    FROM (
        SELECT product_id, DATE_TRUNC('day', order_date) AS d, SUM(amount) AS ricavo_g
        FROM orders
        WHERE order_date BETWEEN CURRENT_DATE - 31 AND CURRENT_DATE - 1
          AND status = 'completed'
        GROUP BY product_id, DATE_TRUNC('day', order_date)
    ) s
    GROUP BY product_id
)
-- scrive solo i casi che meritano occhi umani: scarto grande E soldi veri
INSERT INTO alert_anomalie (giorno, product_id, ricavo, z_score, scarto_euro)
SELECT
    g.giorno,
    g.product_id,
    g.ricavo,
    (g.ricavo - b.media_30g) / NULLIF(b.sigma_30g, 0) AS z_score,
    g.ricavo - b.media_30g AS scarto_euro
FROM giornaliero g
JOIN baseline b USING (product_id)
WHERE ABS((g.ricavo - b.media_30g) / NULLIF(b.sigma_30g, 0)) > 3 -- scarto statisticamente raro
  AND ABS(g.ricavo - b.media_30g) > 500; -- ...e superiore a 500 euro: materialità

In sintesi: per le anomalie usa lo scarto dalla media mobile con mese corrente escluso e passa alla mediana con scarto mediano su serie corte; per Pareto ordina sempre per margine netto e non per fatturato.

Verdetto: per le anomalie vince lo scarto dalla media mobile con mese corrente escluso, con la mediana su serie corte; per Pareto ordina sempre per margine netto, mai per fatturato.

L’esempio storico di Vilfredo Pareto

Vilfredo Pareto osservò nel 1896 che circa l’ottanta per cento delle terre italiane apparteneva a circa il venti per cento della popolazione. La stessa asimmetria ricomparve nei decenni successivi in redditi, ricavi per cliente e resi per prodotto. La lezione operativa è identica a quella della query per decili: pochi elementi spiegano la quota dominante del totale. Chi misura la cumulata sul margine netto trova i veri pochi che contano, invece di inseguire medie che nascondono la distribuzione.

Domande per chiudere la lezione

1. Perché la cumulata di Pareto va calcolata sul margine netto e non sul fatturato lordo?
2. Quando lo scarto dalla media mobile segnala un’anomalia e quando invece è solo stagionalità?
3. Quale denominatore rende confrontabili i decili di spesa tra periodi diversi?
4. Perché la segmentazione deve usare dimensioni indipendenti dalla metrica che vuoi muovere?