Un analisi super utile in ambito e-commerce, specialmente quando si lavora con BigQuery, è il calcolo del valore del tempo di vita del cliente (Customer Lifetime Value, CLTV). Il CLTV è una metrica cruciale che consente di comprendere il valore totale che un cliente porta al tuo business nel corso del tempo. Conoscere il CLTV ti permette di prendere decisioni su quanto investire in acquisizione clienti e in strategie di fidelizzazione.

L’obiettivo che ci poniamo in questo articolo è calcolare il CLTV per ogni cliente basandoci sui loro comportamenti di acquisto passati, identificando i clienti più preziosi per adattare le nostre strategie di marketing.

Ecco un report che mostra il Customer Lifetime Value (CLTV) in un contesto e-commerce:

Vediamo nel dettaglio cosa rappresenta ciascuna colonna del report:

user_id:

• Descrizione: È l’identificativo unico di ciascun cliente. Questo campo viene utilizzato per aggregare tutti gli acquisti fatti da un singolo utente, permettendo di calcolare metriche specifiche per cliente.
• Applicazione: Serve per tracciare il comportamento di acquisto, le preferenze e il valore generato da ogni cliente nel tempo. Utilizzato per aggregare e analizzare i dati specifici di questo cliente senza comprometterne la privacy.

total_spent:

• Descrizione: Rappresenta la somma totale di denaro speso dal cliente per gli acquisti sul tuo sito e-commerce. È calcolata sommando tutti gli importi delle transazioni effettuate dall’utente.
• Applicazione: Questa metrica è fondamentale per valutare quanto valore un cliente ha portato al tuo business in termini monetari. Un valore elevato suggerisce un cliente ad alto valore o numerosi acquisti di alto prezzo.

purchase_count:

• Descrizione: Indica il numero totale di acquisti effettuati dal cliente. Questo conteggio include ogni singola transazione registrata per l’utente.
• Applicazione: Fornisce un’indicazione della frequenza con cui il cliente ha acquistato dal tuo e-commerce. Serve per comprendere la frequenza di acquisto e l’engagement del cliente con il tuo sito. Clienti con un alto numero di acquisti possono essere considerati più fedeli o più coinvolti rispetto a quelli con pochi acquisti.

customer_lifetime_days:

• Descrizione: Calcola il numero di giorni tra il primo e l’ultimo acquisto del cliente, più uno per assicurarsi che il minimo valore sia sempre almeno 1 (per evitare divisioni per zero). Questo indica la durata del “tempo di vita” del cliente con il tuo business, a partire dal loro primo acquisto fino all’ultimo registrato nel periodo considerato.
• Applicazione: Questa metrica aiuta a capire per quanto tempo un cliente è rimasto attivo e impegnato con il tuo e-commerce, offrendo una stima della “longevità” del cliente. Un periodo relativamente breve con alto spend e molteplici acquisti indica una forte intensità di acquisto in quel lasso di tempo.

cltv (Customer Lifetime Value):

• Descrizione: Il CLTV calcolato come total_spent / customer_lifetime_days fornisce una stima giornaliera del valore che il cliente ha portato al tuo business durante il suo tempo di vita come cliente. Questo calcolo semplice divide il totale speso per il numero di giorni di attività del cliente per ottenere un valore medio giornaliero.
• Applicazione: Il CLTV è una metrica chiave per valutare il valore a lungo termine di un cliente. Aiuta a determinare quanto sia redditizio investire in certi segmenti di clienti e a personalizzare le strategie di marketing e vendita per massimizzare il valore generato dai clienti nel corso del tempo. Il CLTV giornaliero fornisce un modo per valutare il contributo medio giornaliero del cliente al tuo business. Un CLTV elevato, invece, sottolinea l’importanza del cliente per il tuo e-commerce e può giustificare investimenti mirati in marketing e fidelizzazione per clienti simili.

Queste colonne, quando analizzate insieme, offrono un quadro dettagliato del valore e del comportamento dei clienti nel tuo e-commerce. Conoscere il CLTV ti permette di allocare risorse di marketing in modo più efficace, migliorando la retention e incrementando i profitti.

Analisi dati nel report: cosa racconta la prima riga?

Analizziamo la prima riga del report, che rappresenta i dati di un singolo cliente:

user_id: qr2W3OKme9AF04fZeyW2EkTBDWs/pFA9izrJAQvYXCU=.1693478754

• Questo è l’identificativo unico dell’utente, probabilmente criptato o anonimizzato per motivi di privacy e sicurezza. Questo ID consente di tracciare in modo univoco le attività di acquisto di questo utente.

total_spent: 57174.69

• Il cliente ha speso un totale di 57,174.69 unità monetarie (ad esempio, euro, dollari, ecc.) nei suoi acquisti sul tuo sito e-commerce. Questo importo rappresenta la somma di tutti gli acquisti effettuati dall’utente.

purchase_count: 58

• Questo cliente ha effettuato 58 acquisti in totale. Questo numero riflette la quantità di transazioni completate dall’utente.

customer_lifetime_days: 88

• Il cliente è stato attivo per 88 giorni, dal primo all’ultimo acquisto registrato. Questo periodo rappresenta la durata della relazione del cliente con il tuo e-commerce.

cltv: 649.71

• Il valore medio giornaliero generato da questo cliente durante il suo ciclo di vita è di 649.71 unità monetarie. Questo valore è ottenuto dividendo il total_spent per i customer_lifetime_days.

In sintesi, questa riga descrive un cliente ad alto valore per l’azienda, con numerosi acquisti e un significativo spend totale in un periodo relativamente breve.

Realizziamo la query e spieghiamola step by step

Vediamo ora la parte più interessante, ovvero la query per realizzare il report precedente (ovviamente adeguala al tuo progetto):

WITH customer_purchases AS (
  SELECT
    user_pseudo_id AS user_id,
    ROUND(SUM((SELECT value.double_value FROM UNNEST(event_params) WHERE key = 'value')), 2) AS total_spent,
    COUNT(*) AS purchase_count,
    MIN(event_date) AS first_purchase_date,
    MAX(event_date) AS last_purchase_date
  FROM
  `your_project.your_dataset.your_table`
  WHERE
    event_name = 'purchase'
     AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
     -- AND _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 120 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  GROUP BY
    user_id
)

, customer_lifetime AS (
  SELECT
    user_id,
    total_spent,
    purchase_count,
    DATE_DIFF(PARSE_DATE('%Y%m%d', last_purchase_date), PARSE_DATE('%Y%m%d', first_purchase_date), DAY) + 1 AS customer_lifetime_days
  FROM
    customer_purchases
)

SELECT
  user_id,
  total_spent,
  purchase_count,
  customer_lifetime_days,
  ROUND(total_spent / customer_lifetime_days, 2) AS cltv -- Questo è un semplice calcolo; potresti volerlo rendere più complesso
FROM
  customer_lifetime
ORDER BY
  cltv DESC

PS. Ricorda di sostituire your_project, your_dataset e your_table con i tuoi identificativi di progetto, dataset e tabella effettivi. Queste query sono solo punti di partenza e potrebbero necessitare di personalizzazioni per adattarsi alle tue esigenze specifiche e alla struttura dei tuoi dati.

Spieghiamola procedendo per passaggi, descrivendo ogni sezione della query.

Fase 1: Aggregazione dei Dati di Acquisto (customer_purchases)

• Obiettivo: Raccogliere e aggregare i dati di acquisto per ogni cliente.
• Operazioni:
  • Selezione e Aggregazione: Per ogni utente, identificato dall’user_pseudo_id rinominato in user_id, la query somma il valore degli acquisti (value.double_value estratto da event_params dove la chiave è ‘value’) e conta il numero totale di acquisti. Questo produce il totale speso (total_spent) e il conteggio degli acquisti (purchase_count) per cliente.
  • Determinazione del Periodo di Acquisto: Calcola la data del primo e dell’ultimo acquisto (first_purchase_date e last_purchase_date) per ciascun cliente.
  • Filtro Temporale: Considera solo gli eventi di acquisto (purchase) nell’ultimo anno, assicurando che l’analisi sia attuale.

Fase 2: Calcolo della Durata della Vita del Cliente (customer_lifetime)

• Obiettivo: Determinare il periodo di attività di acquisto per ogni cliente.
• Operazioni:
  • Calcolo dei Giorni: Per ogni cliente, calcola la differenza in giorni tra la data del primo acquisto e quella dell’ultimo acquisto, aggiungendo 1 per assicurare che ogni cliente abbia almeno un giorno di vita. Questo numero rappresenta i customer_lifetime_days, ovvero la durata in giorni della vita del cliente con il tuo e-commerce.

Fase 3: Calcolo e Ordinamento del CLTV

• Obiettivo: Calcolare il CLTV per ogni cliente e ordinare i risultati per mostrare prima i clienti più preziosi.
• Operazioni:
  • Calcolo del CLTV: Divide il total_spent per i customer_lifetime_days per ottenere il CLTV, che rappresenta il valore medio generato dal cliente per ogni giorno di attività. Il risultato è arrotondato a due cifre decimali per facilitare la lettura.
  • Ordinamento: Ordina i clienti in base al loro CLTV in ordine decrescente, mettendo in evidenza i clienti che hanno generato il maggior valore medio giornaliero nel periodo considerato.

Conclusione

La query svolge un’analisi approfondita del valore che ciascun cliente porta al tuo business nel corso della sua “vita” come cliente, utilizzando dati di acquisto storici. Offre insight critici su come ottimizzare la spesa pubblicitaria, migliorare l’engagement dei clienti e personalizzare le esperienze di acquisto per massimizzare la fedeltà e il valore del cliente nel tempo.

Giuseppe Cristofaro

Data Scientist
Sono un Data Scientist con esperienza nell’applicazione di tecniche avanzate di Machine Learning per l’analisi di complessi set di dati nel campo del marketing digitale. Specializzato nell’uso di Python, sviluppo modelli predittivi e di analisi dei dati che permettono di identificare e risolvere criticità nei progetti.