🥇Come creare coorti di utenti sotto forma di grafici in Grafana [+immagine Docker con esempio]

Come abbiamo risolto la visualizzazione delle coorti di utenti nel servizio Promopult utilizzando Grafana.

Promopult è un potente servizio con un gran numero di utenti. In 10 anni di attività, il numero di registrazioni nella piattaforma ha superato il milione. Chi ha avuto a che fare con servizi simili sa che questo grande gruppo di utenti è tutt'altro che omogeneo.

Alcuni si sono registrati e sono rimasti inattivi per sempre. Altri hanno dimenticato la password e si sono registrati un paio di volte in sei mesi. Alcuni portano soldi alla cassa, mentre altri sono qui per gli strumenti gratuiti . E sarebbe utile ottenere un certo profitto da ognuno di loro.Con set di dati così ampi, analizzare il comportamento di un singolo utente e prendere micro-decisioni è senza senso. È invece possibile e necessario catturare le tendenze e lavorare con grandi gruppi — e questo è esattamente ciò che stiamo facendo.

Su set di dati di grandi dimensioni come il nostro, analizzare il comportamento di singoli utenti e prendere micro-decisioni è poco utile. È però possibile e necessario individuare le tendenze e lavorare con grandi gruppi, ed è proprio ciò che facciamo.

Sommario

Cos'è l'analisi delle coorti e a cosa serve.
Come creare coorti in base al mese di registrazione degli utenti in SQL.
Come trasferire le coorti in Grafana.

Se già sapete cos'è l'analisi delle coorti e come farla in SQL, passate direttamente all'ultima sezione.

1. Che cos'è l'analisi per coorti e a cosa serve

L'analisi per coorti è un metodo basato sul confronto di diversi gruppi (coorti) di utenti. Di solito, queste gruppi vengono creati in base alla settimana o al mese in cui un utente ha iniziato a utilizzare il servizio. Da qui si calcola il tempo di vita dell'utente, il che diventa un indicatore per condurre analisi piuttosto complesse. Ad esempio, per capire:

come il canale di acquisizione influisce sul tempo di vita dell'utente;
come l'uso di una certa funzione o servizio influisce sul tempo di vita;
come il lancio della funzione X ha impattato sul tempo di vita rispetto all'anno scorso.

2. Come creare coorti in SQL?

La dimensione dell'articolo e il buon senso non consentono di fornire qui i nostri dati reali — nel dump di test ci sono statistiche di un anno e mezzo: 1200 utenti e 53.000 transazioni. Per permettervi di esplorare questi dati, abbiamo preparato un'immagine docker con MySQL e Grafana, in cui potrete sperimentare autonomamente. Il link a GitHub è alla fine dell'articolo.

Qui mostreremo la creazione di coorti con un esempio semplificato.

Supponiamo di avere un servizio. Gli utenti si registrano e spendono soldi per i servizi. Col tempo, gli utenti abbandonano. Vogliamo sapere quanto a lungo vivono gli utenti e quanti di loro abbandonano dopo il primo e il secondo mese di utilizzo del servizio.

Per rispondere a queste domande, dobbiamo costruire delle coorti in base al mese di registrazione. Misureremo l'attività in base alle spese ogni mese. Al posto delle spese, possono esserci ordini, abbonamenti o qualsiasi altra attività legata al tempo.

Dati di origine

Esempi sono fatti in MySQL, ma per gli altri DBMS non ci dovrebbero essere differenze sostanziali.

Tabella utenti — users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabella spese — billing:

userId
Date
Sum

1
2019-01-02
11

1
2019-02-22
11

2
2019-02-12
12

3
2019-02-11
13

3
2019-03-11
13

4
2019-03-01
14

4
2019-03-02
14

Selezioniamo tutte le spese degli utenti e le date di registrazione:

SELECT 
  b.userId, 
  b.Date,
  u.RegistrationDate
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId

Risultato:

userId
Date
RegistrationDate

1
2019-01-02
2019-01-02

1
2019-02-22
2019-01-02

2
2019-02-12
2019-02-01

3
2019-02-11
2019-02-10

3
2019-03-11
2019-02-10

4
2019-03-01
2019-03-01

4
2019-03-02
2019-03-01

Costruiamo le coorti per mese, per questo convertiamo tutte le date in mesi:

DATE_FORMAT(Date, '%Y-%m')

Ora abbiamo bisogno di sapere per quanti mesi l'utente è stato attivo: è la differenza tra il mese della spesa e il mese di registrazione. In MySQL c'è la funzione PERIOD_DIFF() — la differenza tra due mesi. Aggiungiamo PERIOD_DIFF() nella query:

SELEZIONA
    b.userId,
    DATE_FORMAT(b.Date, '%Y-%m') AS MeseDiFatturazione,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS MeseDiRegistrazione,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS DifferenzaMesi
DA fatturazione AS b LEFT JOIN utenti AS u ON b.userId = u.userId

userId
MeseDiFatturazione
RegistrationDate
DifferenzaMesi

1
2019-01
2019-01
0

1
2019-02
2019-01
1

2
2019-02
2019-02
0

3
2019-02
2019-02
0

3
2019-03
2019-02
1

4
2019-03
2019-03
0

Calcoliamo gli utenti attivati in ogni mese: raggruppiamo le registrazioni per MeseDiFatturazione, MeseDiRegistrazione e DifferenzaMesi:

SELEZIONA
    COUNT(DISTINCT(b.userId)) AS NumeroUtenti,
    DATE_FORMAT(b.Date, '%Y-%m') AS MeseDiFatturazione,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS MeseDiRegistrazione,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS DifferenzaMesi
DA fatturazione AS b LEFT JOIN utenti AS u ON b.userId = u.userId
RAGGRUPPA PER MeseDiFatturazione, MeseDiRegistrazione, DifferenzaMesi

Risultato:

NumeroUtenti
MeseDiFatturazione
MeseDiRegistrazione
DifferenzaMesi

1
2019-01
2019-01
0

1
2019-02
2019-01
1

2
2019-02
2019-02
0

1
2019-03
2019-02
1

1
2019-03
2019-03
0

A gennaio, febbraio e marzo è comparso un nuovo utente ciascun mese — DifferenzaMesi = 0. Un utente di gennaio è stato attivo anche a febbraio — MeseDiRegistrazione = 2019-01, MeseDiFatturazione = 2019-02, e anche un utente di febbraio è stato attivo a marzo.

Su un grande insieme di dati, le tendenze sono chiaramente visibili.

Come trasferire le coorti in Grafana

Abbiamo imparato a formare le coorti, ma quando le registrazioni diventano numerose, analizzarle diventa difficile. Si possono esportare in Excel e creare belle tabelle, ma non è il nostro metodo!

Le coorti possono essere visualizzate come un grafico interattivo in Grafana.

Aggiungiamo un'altra query per trasformare i dati in un formato adatto a Grafana:

SELECT
  DATE_ADD(CONCAT(s.RegistrationMonth, '-01'), INTERVAL s.MonthsDiff MONTH) AS time_sec,
  SUM(s.Users) AS value,
  s.RegistrationMonth AS metric
FROM (
  ## vecchia query che restituisce le coorti
  SELECT 
    COUNT(DISTINCT(b.userId)) AS Users, 
    DATE_FORMAT(b.Date, '%Y-%m') AS BillingMonth,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS RegistrationMonth,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS MonthsDiff
  FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
  WHERE
    u.RegistrationDate BETWEEN '2018-01-01' AND CURRENT_DATE
  GROUP BY 
    BillingMonth, RegistrationMonth, MonthsDiff 
) AS s
GROUP BY 
  time_sec, metric

E scarichiamo i dati in Grafana.

Esempio di grafico da demo:

Provare con mano:

Repository GitHub con un esempio — è un'immagine docker con MySQL e Grafana, che può essere eseguita sul proprio computer. Nel database ci sono già dati demo per un anno e mezzo, da gennaio 2018 a luglio 2019.

Se desiderato, è possibile caricare i propri dati in questa immagine.

P.S. Articoli sull'analisi delle coorti in SQL:

https://chartio.com/resources/tutorials/performing-cohort-analysis-using-mysql/

https://www.holistics.io/blog/calculate-cohort-retention-analysis-with-sql/

Fonte: habr.com