Come calcolare il Customer Lifetime Value da un CSV (passo dopo passo)
Per calcolare il customer lifetime value da un CSV di transazioni, occorre calcolare il valore medio dell'ordine, la frequenza d'acquisto e la durata della vita del cliente. Moltiplica i tre valori tra loro, quindi applica il margine lordo. L'operazione aritmetica è semplice. Sono le decisioni soggettive — quali clienti considerare persi e se guardare al passato o al futuro — a determinare se il risultato sarà utile o meno.
Due team possono applicare la stessa formula allo stesso file e ottenere due cifre che differiscono di un fattore tre. Nessuno dei due ha torto. Hanno semplicemente risposto a domande diverse senza rendersene conto.
Questa guida illustra i dati necessari, il calcolo manuale in tre fasi e i limiti dell'approccio basato sui fogli di calcolo.
Cosa serve prima di iniziare
È necessario un export a livello di transazione: una riga per ordine, contenente un identificativo del cliente, la data dell'ordine e il valore dell'ordine. Un riepilogo mensile non funzionerà, perché il lifetime value riguarda fondamentalmente il comportamento dei singoli clienti nel tempo.
Due decisioni precedono qualsiasi formula e influenzano il risultato finale più di qualsiasi calcolo.
Storico o predittivo. Il customer lifetime value storico somma quanto i clienti hanno effettivamente speso finora. Quello predittivo stima quanto spenderanno in futuro. Il dato storico è inattaccabile ma sempre sottostimato; quello predittivo è più utile per la pianificazione del budget e dipende interamente dalle ipotesi di fidelizzazione (retention).
Cosa si intende per abbandono (churn). Per un abbonamento è semplice: la data di cancellazione. Per le attività transazionali non esiste un evento di cancellazione, quindi serve una regola. Ad esempio, un cliente che non acquista da dodici mesi viene considerato inattivo. Scegli un intervallo di tempo e mettilo per iscritto.
Un buon risultato si presenta così: una tabella per cliente con la spesa totale, il numero di ordini, la data del primo e dell'ultimo ordine e un'etichetta di segmento. Accanto, un dato principale con l'ipotesi di margine chiaramente indicata.
Come procedere manualmente
Opzione 1: Calcolare il valore medio dell'ordine e la frequenza d'acquisto
Raggruppa il file delle transazioni per identificativo cliente. Per ogni cliente, somma i valori degli ordini e conta il numero di ordini. Il valore medio dell'ordine è il ricavo totale diviso per il totale degli ordini dell'intera base clienti. La frequenza d'acquisto è il totale degli ordini diviso per il numero di clienti unici nel periodo considerato.
La trappola qui è il periodo di riferimento. La frequenza misurata su dodici mesi e quella misurata sull'intero storico sono numeri diversi, e mescolarli in un'unica formula produce un risultato privo di senso. Definisci prima l'intervallo temporale e usalo in modo coerente.
Opzione 2: Aggiungere la curva di fidelizzazione (retention)
La durata della vita del cliente è il dato che la maggior parte delle persone si limita a ipotizzare. La versione più attendibile proviene dai tuoi stessi dati: raggruppa i clienti per mese di acquisizione, quindi conta quanti erano ancora attivi in ciascun mese successivo.
In questo modo si ottiene una curva di retention. La durata media della vita del cliente corrisponde all'incirca all'area sottostante la curva — in un foglio di calcolo, la somma dei tassi di retention mensili. Se 100 clienti mantengono un tasso di retention dell'80%, 65% e 55% nell'arco di tre mesi, stai accumulando la durata mese dopo mese anziché ipotizzare una cifra tonda. La nostra guida su come creare un grafico di retention per coorte copre l'aspetto grafico, mentre l'articolo su cos'è l'analisi di coorte ne spiega il concetto.
Opzione 3: Trasformare i dati in LTV e nel rapporto LTV:CAC
Moltiplica il valore medio dell'ordine per la frequenza d'acquisto e per la durata della vita del cliente, quindi moltiplica il risultato per il margine lordo. Un lifetime value basato sui ricavi sovrastima la cifra, a volte anche di molto, perché tratta un prodotto a basso margine allo stesso modo di uno ad alto margine.
Successivamente, dividi per il costo di acquisizione clienti (CAC). Quel rapporto è il vero scopo di questo calcolo. Il lifetime value preso singolarmente è solo una curiosità; il lifetime value rapportato a quanto hai speso per acquisire il cliente diventa uno strumento decisionale.
I limiti del metodo manuale
La segmentazione è il punto in cui questo sistema mostra i suoi limiti. Un unico dato aggregato fa la media tra le coorti migliori e peggiori, restituendo un numero poco informativo. La versione davvero utile è quella per canale, per piano o per mese di acquisizione. Ogni segmento implica la ricostruzione dell'intera catena di formule su un sottoinsieme filtrato. Di solito ci sono più segmenti da verificare di quanti se ne abbia la pazienza di analizzare, quindi la maggior parte rimane esclusa.
Il secondo problema è l'aggiornamento dei dati. Il file del trimestre successivo conterrà più righe e, spesso, colonne con nomi diversi. La griglia di retention che ha richiesto un intero pomeriggio di lavoro dovrà essere estesa negli intervalli e le formule dovranno essere ricontrollate. Un intervallo mancato è invisibile nel risultato finale, ed è proprio questo a renderlo pericoloso.
Inoltre, le ipotesi di partenza rimangono invisibili. Sei mesi dopo, il file mostra un numero e nessuno ricorda se fosse basato sui ricavi o sul margine, o quale intervallo di churn fosse stato utilizzato. È così che un dato obsoleto finisce nella presentazione per il consiglio d'amministrazione.
C'è anche un problema più sottile. Poiché il modello è laborioso da ricostruire, si tende a calcolarlo una sola volta e a citarlo per un anno intero. Il lifetime value non è una costante. Cambia quando variano i prezzi, quando il mix di acquisizione si sposta verso un canale più economico e quando una modifica al prodotto altera la retention. Un dato calcolato la scorsa primavera potrebbe descrivere un'azienda che oggi non esiste più.
Come calcolare il customer lifetime value con Powerdrill Bloom
Passaggio 1: Carica i tuoi dati
Carica direttamente il CSV delle transazioni. Powerdrill Bloom analizzerà le colonne, consentendoti di verificare la presenza di un identificativo cliente valido e di una colonna data corretta prima di procedere a qualsiasi raggruppamento.
Passaggio 2: Descrivi il calcolo in linguaggio naturale
Definisci le regole come faresti con un collega. Considera inattivi i clienti che non effettuano acquisti da dodici mesi, applica questa percentuale di margine lordo e calcola il lifetime value per canale di acquisizione. Poi poni le domande di approfondimento che contano: come varia la cifra tra il primo e l'ultimo quartile, o come si è mossa tra le diverse coorti di acquisizione.
Passaggio 3: Esporta il grafico, il report o la presentazione
Esporta la tabella per segmento, la relativa curva di retention o un riepilogo scritto con le ipotesi di partenza registrate accanto al valore numerico.
Perché questo metodo è migliore rispetto a ricostruire il modello ogni trimestre
| Metodo con foglio di calcolo | Powerdrill Bloom | |
|---|---|---|
| Segmentazione per canale | Ricostruire la catena di formule per ogni segmento | Aggiungerlo alla richiesta |
| Modifica dell'intervallo di churn | Rielaborare la griglia di retention | Richiederlo nuovamente con un nuovo intervallo |
| Registrazione delle ipotesi di partenza | Nota manuale, spesso mancante | Specificate nella richiesta |
| File del trimestre successivo | Estendere gli intervalli, controllare le formule | Caricare il file e ripetere la richiesta |
La seconda riga è quella che cambia davvero la qualità delle informazioni ottenute. Un intervallo di churn di dodici mesi e uno di sei mesi possono produrre lifetime value molto diversi. Il modo più onesto di procedere è provarli entrambi e vedere se le conclusioni rimangono valide. Con un foglio di calcolo, nessuno lo fa due volte.
Errori comuni
Utilizzare i ricavi anziché il margine. Un lifetime value basato sui ricavi lusinga qualsiasi attività, ma distorce gravemente qualsiasi confronto con il costo di acquisizione.
Aggregare tutti i clienti in un unico dato. La media calcolata su una base clienti con segmenti molto diversi non descrive alcun cliente reale. Segmenta prima di trarre conclusioni.
Ipotizzare la durata della vita del cliente. "Circa due anni" è il dato inserito più di frequente, ma anche il meno giustificabile. Ricavalo invece dalla tua curva di retention, anche se la curva è breve.
Ignorare l'effetto sopravvivenza (survivor effect). Il lifetime value storico tiene conto solo dei clienti già esistenti, sbilanciando il dato verso quelli più fedeli. Il calcolo basato sulle coorti evita questo errore.
Presentare il dato senza il CAC. Un lifetime value di $900 non significa nulla finché non si sa se l'acquisizione è costata $90 o $800.
Considerare il dato come permanente. Ricalcolalo ogni volta che i prezzi, il prodotto o il mix di acquisizione cambiano in modo significativo. Un numero trascinato dall'anno scorso è solo un'ipotesi travestita da dato preciso.
Conclusione
Il calcolo del customer lifetime value da un CSV richiede tre passaggi: calcolare il valore medio dell'ordine e la frequenza, ricavare una curva di retention dalle proprie coorti, e infine applicare il margine e calcolare il rapporto con il costo di acquisizione. Farlo su un foglio di calcolo funziona, almeno finché non si ha la necessità di segmentare i dati o di testare un intervallo di churn diverso.
Se il modello viene ricostruito solo quando qualcuno lo richiede durante una riunione, prova Powerdrill Bloom sul tuo export delle transazioni. La nostra pagina dedicata all'assistente AI per CSV illustra il flusso di lavoro completo, mentre l'articolo su come trasformare un CSV in un grafico ne descrive l'output visivo.
Domande frequenti
Qual è la formula per il customer lifetime value?
Moltiplica il valore medio dell'ordine per la frequenza d'acquisto e per la durata media della vita del cliente, quindi moltiplica il risultato per il margine lordo. Utilizzare i ricavi anziché il margine gonfia il risultato e rende inaffidabile qualsiasi confronto con il costo di acquisizione.
Come si calcola il customer lifetime value in Excel?
Raggruppa il file delle transazioni per identificativo cliente per ottenere il conteggio e i totali degli ordini. Costruisci una griglia di retention per coorte per ricavare la durata della vita del cliente, quindi combina i tre dati. La griglia di retention è la parte più laboriosa e richiede di essere ricostruita ogni volta che cambia l'intervallo di riferimento.
Qual è la differenza tra LTV storico e predittivo?
L'LTV storico somma quanto i clienti hanno già speso, quindi è sempre sottostimato e facile da giustificare. Quello predittivo stima la spesa futura in base a un'ipotesi di retention, il che lo rende più utile per la pianificazione ma anche più soggetto a errori.
Qual è un buon rapporto LTV:CAC?
Il target comunemente citato è di tre a uno, ma dipende dalla struttura dei margini e dal periodo di ammortamento (payback period). La domanda più utile da porsi è quanti mesi occorrono a un cliente per ripagare il proprio costo di acquisizione.
Come gestire i clienti che non effettuano acquisti da molto tempo?
Definisci un intervallo di churn e applicalo in modo coerente — dodici mesi senza acquisti è una regola comune per le attività transazionali. Registra l'intervallo accanto al dato ottenuto, poiché modificarlo può variare notevolmente il valore del lifetime value.