Come raccogliere coorti di utenti in forma di grafici in Grafana [+immagine docker con esempio]

Come abbiamo risolto il problema della visualizzazione delle coorti degli 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 nel sistema ha superato il milione. Coloro che hanno avuto a che fare con servizi simili sanno che questo ampio insieme di utenti non è per nulla omogeneo.

C'è chi si è registrato e si è "addormentato" per sempre. Qualcuno ha dimenticato la password e si è registrato altre due volte in sei mesi. Alcuni portano soldi in cassa, mentre altri sono venuti per avere qualcosa di gratuito strumenti. E sarebbe bello ottenere un certo profitto da ciascuno di loro.

Con insiemi di dati così grandi come il nostro, analizzare il comportamento di un singolo utente e prendere decisioni micro è poco sensato. D'altra parte, individuare tendenze e lavorare con grandi gruppi è possibile e necessario. Questo è esattamente ciò che facciamo.

Riassunto

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

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

1. Cos'è l'analisi delle coorti e a cosa serve

L'analisi delle coorti è un metodo basato sul confronto di diversi gruppi (coorti) di utenti. Di solito, formiamo gruppi in base alla settimana o al mese in cui l'utente ha iniziato a utilizzare il servizio. Da qui si calcola la vita media dell'utente, un indicatore su cui è possibile condurre analisi piuttosto complesse. Ad esempio, comprendere:

• come il canale di acquisizione influisce sulla vita media dell'utente;
• come l'uso di una determinata funzione o servizio influisce sulla vita media;
• come il lancio della funzione X ha influenzato la vita media 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 abbiamo statistiche per un anno e mezzo: 1200 utenti e 53.000 transazioni. Per permettervi di giocare con questi dati, abbiamo preparato un'immagine docker con MySQL e Grafana, dove potete esplorare il tutto da soli. Il link a GitHub si trova alla fine dell'articolo.

Qui mostreremo come creare coorti con un esempio semplificato.

Supponiamo di avere un servizio. Gli utenti si registrano e spendono soldi per i servizi. Col passare del tempo, alcuni utenti smettono di utilizzare il servizio. Vogliamo sapere quanto a lungo restano gli utenti attivi e quanti di loro smettono 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 di ogni mese. Al posto delle spese, possiamo considerare ordini, abbonamenti o qualsiasi altra attività legata al tempo.

Dati di origine

Gli esempi sono fatti in MySQL, ma non dovrebbero esserci differenze sostanziali per altri DBMS.

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 transazioni degli utenti e la data 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 fare ciò trasformiamo tutte le date nei mesi:

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

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

SELECT
    b.userId,
    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

userId
BillingMonth
RegistrationDate
MonthsDiff

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

4
2019-03
2019-03
0

Contiamo gli utenti attivabili in ogni mese — raggruppiamo i record per BillingMonth, RegistrationMonth e MonthsDiff:

SELECT
    COUNT(DISTINCT(b.userId)) AS UsersCount,
    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
GROUP BY BillingMonth, RegistrationMonth, MonthsDiff

Risultato:

UsersCount
BillingMonth
RegistrationMonth
MonthsDiff

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 ciascuno — MonthsDiff = 0. Un utente di gennaio è stato attivo anche a febbraio — RegistrationMonth = 2019-01, BillingMonth = 2019-02, così come un utente di febbraio è stato attivo a marzo.

Su un grande insieme di dati, le tendenze sono ovviamente più evidenti.

Come trasferire le coorti in Grafana

Abbiamo imparato a formare le coorti, ma quando ci sono molti record diventa difficile analizzarli. I record possono essere esportati in Excel per creare bellissime tabelle, ma questo non è il nostro metodo!

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

Per questo aggiungiamo un'altra query per trasformare i dati in un formato idoneo 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 (
  ## query precedente 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 una demo:

Da toccare con mano:

Repository GitHub con 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 lo si desidera, è 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