Cum să construiți cohorte de utilizatori sub formă de grafice în Grafana [+imagine docker cu exemplu]

Cum am rezolvat problema vizualizării cohortelor de utilizatori în serviciul Promopult cu ajutorul Grafana.

Promopult — un serviciu puternic cu un număr mare de utilizatori. În 10 ani de activitate, numărul înregistrărilor în sistem a depășit un milion. Cei care s-au confruntat cu servicii similare știu că acest volum de utilizatori nu este deloc omogen.

Unii s-au înregistrat și au „adormit” pentru totdeauna. Unii au uitat parola și s-au înregistrat din nou de câteva ori în șase luni. Unii aduc bani la casă, iar alții vin pentru resursele gratuite instrumente. Și ar fi bine să obținem un profit de la fiecare.

Pe astfel de volume mari de date, cum avem noi, analizarea comportamentului unui utilizator individual și luarea de decizii micro este inutilă. Dar identificarea tendințelor și lucrul cu grupuri mari — se poate și trebuie. Asta facem noi, de fapt.

Rezumat

1. Ce este analiza cohortelor și de ce este nevoie de ea.
2. Cum să faci cohorta pe baza lunii de înregistrare a utilizatorilor în SQL.
3. Cum să transferi cohorta în Grafana.

Dacă deja știi ce este analiza cohortelor și cum să o faci în SQL, poți să sari direct la ultima secțiune.

1. Ce este analiza cohortelor și de ce este nevoie de ea

Analiza cohortelor este o metodă bazată pe compararea diferitelor grupuri (cohortelor) de utilizatori. Cel mai adesea, grupurile noastre se formează pe săptămână sau lună, în care utilizatorul a început să folosească serviciul. De aici se calculează durata de viață a utilizatorului, iar acest indicator este deja baza pe care se poate realiza o analiză destul de complexă. De exemplu, să înțelegi:

• cum influențează canalul de atragere durata de viață a utilizatorului;
• cum utilizarea unei anumite funcții sau servicii influențează durata de viață;
• cum lansarea funcției X a influențat durata de viață comparativ cu anul trecut.

2. Cum să faci cohortele în SQL?

Dimensiunea articolului și bunul simț nu permit prezentarea aici datelor noastre reale — în dump-ul de test statisticile acoperă un an și jumătate: 1200 de utilizatori și 53 000 de tranzacții. Pentru a putea experimenta cu aceste date, am pregătit o imagine docker cu MySQL și Grafana, în care poți explora totul tu însuți. Linkul către GitHub este la sfârșitul articolului.

Aici vom arăta crearea cohortelor printr-un exemplu simplificat.

Să presupunem că avem un serviciu. Utilizatorii se înregistrează și cheltuiesc bani pe servicii. În timp, utilizatorii abandonează. Vrem să știm cât de mult trăiesc utilizatorii și câți dintre ei renunță după prima și a doua lună de utilizare a serviciului.

Pentru a răspunde la aceste întrebări, trebuie să construim cohorte în funcție de luna în care s-au înregistrat. Activitatea va fi măsurată prin cheltuieli în fiecare lună. În loc de cheltuieli, pot fi comenzi, abonamente sau orice altă activitate legată de timp.

Datele originale

Exemplele sunt realizate în MySQL, dar nu ar trebui să existe diferențe semnificative pentru celelalte SGBD-uri.

Tabelul utilizatorilor — users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabelul cheltuielilor — billing:

userId
, precum și API-ul în stadiu de proiect
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

Selectăm toate deducerile utilizatorilor și data înregistrării:

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

Rezultatul:

userId
, precum și API-ul în stadiu de proiect
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

Construim cohorte pe luni, pentru aceasta transformăm toate datele în luni:

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

Acum trebuie să știm câte luni a fost utilizatorul activ — aceasta este diferența dintre luna deducerii și luna înregistrării. În MySQL există funcția PERIOD_DIFF() — diferența dintre două luni. Adăugăm PERIOD_DIFF() în interogare:

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

Numărăm utilizatorii activați în fiecare lună — grupăm înregistrările după BillingMonth, RegistrationMonth și 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

Rezultatul:

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

În ianuarie, februarie și martie a apărut câte un nou utilizator — MonthsDiff = 0. Un utilizator din ianuarie a fost activ și în februarie — RegistrationMonth = 2019-01, BillingMonth = 2019-02, la fel și un utilizator din februarie a fost activ în martie.

Pe un volum mare de date, tiparele devin, desigur, mai evidente.

Cum să transferăm cohorte în Grafana

Am învățat să formăm cohorte, dar când înregistrările devin multe, analiza lor nu mai este ușoară. Înregistrările pot fi exportate în Excel și pot fi create grafice frumoase, dar aceasta nu este metoda noastră!

Cohortele pot fi prezentate sub formă de grafice interactive în Grafana.

Pentru aceasta, adăugăm o altă interogare pentru a transforma datele într-un format potrivit pentru 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 (
  ## interogarea veche, returnând cohorte
  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

Șiexportăm datele în Grafana.

Exemplu de grafic din demo:

Atingeți cu mâinile:

Repository-ul GitHub cu exemplul — este o imagine docker cu MySQL și Grafana, care poate fi rulată pe computerul dvs. În bază există deja date demo pentru un an și jumătate, din ianuarie 2018 până în iulie 2019.

Dacă doriți, puteți încărca propriile date în această imagine.

P.S. Articole despre analiza cohortelor în SQL:

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

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

Sursa: habr.com