Ti sei mai chiesto cosa succede ad un utente che, dopo l’ingresso nel sito, esce senza aver completato l’acquisto. Oppure come si comporta un utente che ha effettuato l’acquisto nei giorni successivi. Torna? Dopo quanti giorni o settimane? Oppure abbandona definitivamente il nostro e-commerce?

E’ possibile utilizzare le capacità di BigQuery per analizzare le cohort e la retention degli utenti, identificando come i diversi gruppi di utenti si comportano nel tempo o rispondono a specifiche campagne o eventi.

👉 Se vuoi approfondire con un caso di studio sull’argomento, ti consiglio di leggere questo articolo: I TUOI UTENTI SONO FEDELI? COME ANALIZZARE LE COHORT PER IL TUO BUSINESS.

Cosa è una cohort?

Una cohort è un gruppo di persone che condividono una caratteristica comune o hanno vissuto un evento specifico nello stesso periodo di tempo. Nel contesto dell’analisi dati, specialmente nell’e-commerce o nell’analisi del comportamento degli utenti, una cohort spesso si riferisce a gruppi di utenti raggruppati in base alla loro data di primo acquisto, iscrizione, o altra azione significativa.

Analizzando le cohort, puoi osservare come gruppi specifici di utenti interagiscono col tuo prodotto o servizio nel tempo, permettendoti di valutare la retention, l’engagement e altri comportamenti importanti in maniera più precisa e significativa.

Un approccio consiste nel calcolare la retention giornaliera, settimanale o mensile, seguendo gli utenti per vedere se e quando compiono azioni successive dopo l’evento iniziale.

Esempio di Analisi di Retention

Supponiamo di voler analizzare la retention settimanale degli utenti che hanno effettuato un acquisto:

Analizzando i dati del report possiamo osservare il comportamento di una coorte di utenti che hanno effettuato il loro primo acquisto il 12 dicembre 2023. La coorte iniziale è composta da 41 utenti. 

Ecco alcuni insight basati sui dati di ritenzione settimanali:

Ritenzione Iniziale Elevata: La ritenzione nella prima settimana è del 100%, il che significa che tutti i 41 utenti della coorte hanno avuto un’attività di ritorno in quella settimana. Questo indica un inizio molto forte, con un coinvolgimento elevato subito dopo l’azione iniziale.

Calo Significativo nella Seconda Settimana: Si nota un calo drastico della ritenzione passando dalla prima alla seconda settimana, con solo il 7.32% degli utenti (3 su 41) che tornano. Questo suggerisce che, nonostante l’inizio promettente, l’interesse o l’engagement degli utenti cala significativamente dopo la prima settimana.

Ritenzione Costante ma Bassa nelle Settimane Successive: Dalla terza settimana in poi, la ritenzione si stabilizza ma rimane bassa, con un solo utente (2.44%) che ritorna nelle settimane 3, 4, 6 e 7. Questo pattern indica che, dopo il calo iniziale, l’engagement degli utenti rimanenti diventa molto più sporadico e limitato.

Assenza di Attività in Alcune Settimane: Non ci sono dati per la ritenzione nelle settimane 2 e 5, il che potrebbe indicare che non ci sono state interazioni registrate da parte degli utenti della coorte in quei periodi. Questo può suggerire un’ulteriore diminuzione dell’interesse o delle opportunità di engagement per gli utenti.

Sfide nel Mantenere l’Engagement a Lungo Termine: I dati mostrano che, nonostante un inizio forte, mantenere l’engagement degli utenti nel tempo è una sfida. Questo potrebbe indicare la necessità di implementare strategie mirate per migliorare la ritenzione a lungo termine, come offerte personalizzate, contenuti o funzionalità che incoraggiano gli utenti a tornare.

I dati del report suggeriscono di approfondire l’analisi del comportamento degli utenti per capire perché sono altamente attivi nella prima settimana e meno nelle successive. Questo potrebbe aiutare a identificare opportunità di miglioramento. Inoltre, suggeriscono di intraprendere iniziative di ritenzione mirate: esempio lanciare campagne di marketing mirate o offerte speciali per incoraggiare gli utenti a tornare dopo la prima settimana, potrebbe aiutare a migliorare la ritenzione nelle fasi successive.

Come otteniamo questo report? Ecco la query (considerala come un grande regalo!):

WITH cohort_data AS (
  SELECT
    user_pseudo_id,
    MIN(PARSE_DATE('%Y%m%d', event_date)) AS cohort_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 60 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
  GROUP BY
    user_pseudo_id
),
total_cohort_users AS (
  SELECT
    cohort_date,
    COUNT(DISTINCT user_pseudo_id) AS total_cohort_users
  FROM
    cohort_data
  GROUP BY
    cohort_date
),
activity_data AS (
  SELECT
    user_pseudo_id,
    PARSE_DATE('%Y%m%d', event_date) AS event_date
  FROM
    `your_project.your_dataset.your_table`
  WHERE 
    _TABLE_SUFFIX BETWEEN FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE(), INTERVAL 60 DAY)) AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())
),
returning_users AS (
  SELECT
    cohort_date,
    DATE_DIFF(activity_data.event_date, cohort_data.cohort_date, WEEK) AS weeks_after_first_event,
    COUNT(DISTINCT activity_data.user_pseudo_id) AS returning_users
  FROM
    cohort_data
  JOIN
    activity_data ON cohort_data.user_pseudo_id = activity_data.user_pseudo_id
  WHERE
    DATE_DIFF(activity_data.event_date, cohort_data.cohort_date, WEEK) >= 0
  GROUP BY
    cohort_date, weeks_after_first_event
)
SELECT
  ru.cohort_date,
  ru.weeks_after_first_event,
  tcu.total_cohort_users,
  ru.returning_users,
  ROUND(ru.returning_users / tcu.total_cohort_users * 100, 2) AS retention_percentage
FROM
  returning_users ru
JOIN
  total_cohort_users tcu ON ru.cohort_date = tcu.cohort_date
ORDER BY
  ru.cohort_date, ru.weeks_after_first_event

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.

La query fa riferimento all’evento “purchase”, ma nulla vieta di utilizzarla per analizzare la retention degli utenti “first_visit”.

Essa si scompone in diverse parti, ognuna delle quali contribuisce a un’analisi della coorte focalizzata sull’identificare e tracciare la ritenzione degli utenti basata sugli acquisti. Ecco il processo dettagliato:

1. cohort_data CTE (Common Table Expression)

• Scopo: Identificare la data della prima transazione (acquisto) per ciascun utente, definendo così la loro “data di coorte”.
• Metodologia:
  • Filtra gli eventi per event_name = 'purchase' per considerare solo gli acquisti.
  • Utilizza MIN(PARSE_DATE('%Y%m%d', event_date)) per trovare la data della prima transazione di ogni utente.
  • Applica un filtro sul suffisso della tabella per considerare dati nell’intervallo degli ultimi 60 giorni.

2. total_cohort_users CTE

• Scopo: Calcolare il numero totale di utenti unici per ciascuna data di coorte.
• Metodologia:
  • Raggruppa i dati per cohort_date ottenuta dal CTE cohort_data.
  • Conta gli user_pseudo_id distinti per ogni cohort_date.

3. activity_data CTE

• Scopo: Raccogliere i dati di attività degli utenti, inclusa la data di ciascun evento, per l’intervallo temporale considerato.
• Metodologia:
  • Seleziona user_pseudo_id e la data dell’evento (trasformata in data).
  • Applica un filtro sul suffisso della tabella per includere eventi nell’ultimo intervallo di 60 giorni.

4. returning_users CTE

• Scopo: Determinare il numero di utenti che sono tornati e hanno effettuato un’azione (qualsiasi evento, non necessariamente un acquisto) dopo la loro prima transazione, su base settimanale.
• Metodologia:
  • Unisce cohort_data con activity_data sull’user_pseudo_id.
  • Calcola la differenza in settimane tra la data dell’evento e la data della coorte.
  • Filtra per includere solo gli eventi che accadono dopo la data della prima transazione (week >= 0).
  • Conta gli utenti distinti per ogni combinazione di cohort_date e settimane dopo il primo evento.

5. Query Finale

• Scopo: Calcolare la percentuale di ritenzione degli utenti per ciascuna coorte settimanale.
• Metodologia:
  • Unisce returning_users con total_cohort_users sulla cohort_date.
  • Seleziona la cohort_date, le settimane dopo il primo evento, il numero totale di utenti della coorte, il numero di utenti che sono tornati, e calcola la percentuale di ritenzione.
  • Ordina i risultati per cohort_date e per settimane dopo il primo evento.

In sintesi, la query consente di analizzare come gli utenti che hanno effettuato un acquisto continuano a interagire con il servizio o prodotto nel tempo, offrendo insight preziosi sulla fedeltà e l’engagement degli utenti basati sul loro comportamento di acquisto iniziale.

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.