🥇Kako prikupiti skupine korisnika kao grafikone u Grafani [+ docker slika s primjerom]

Kako smo pomoću Grafana riješili problem vizualizacije korisničkih kohorti u Promopult servisu.

promovirati - moćan servis s velikim brojem korisnika. U 10 godina rada, broj registracija u sustav premašio je milijun. Oni koji su se susreli sa sličnim uslugama znaju da je ovaj niz korisnika daleko od homogenog.

Netko se prijavio i zauvijek "zaspao". Netko je zaboravio lozinku i registrirao se još nekoliko puta tijekom šest mjeseci. Netko donese novac na blagajnu, a netko je došao po gratis instrumenti. I bilo bi lijepo od svakoga dobiti nešto.

Na tako velikim skupovima podataka kao što je naš, besmisleno je analizirati ponašanje pojedinog korisnika i donositi mikro-odluke. Ali uhvatiti trendove i raditi s velikim grupama moguće je i potrebno. Što je upravo ono što mi radimo.

rezime

Što je kohortna analiza i zašto je potrebna?
Kako napraviti kohorte po mjesecu registracije korisnika u SQL-u.
Kako prenijeti kohorte na grafana.

Ako već znate što je kohortna analiza i kako je napraviti u SQL-u, prijeđite ravno na posljednji odjeljak.

1. Što je kohortna analiza i zašto je potrebna?

Kohortna analiza je metoda koja se temelji na usporedbi različitih skupina (kohorti) korisnika. Najčešće se naše grupe formiraju prema tjednu ili mjesecu u kojem je korisnik počeo koristiti uslugu. Odavde se izračunava životni vijek korisnika, a to je pokazatelj na temelju kojeg se može provesti prilično složena analiza. Na primjer, shvatite:

kako kanal akvizicije utječe na životni vijek korisnika;
kako korištenje bilo koje značajke ili usluge utječe na vijek trajanja;
Kako je lansiranje značajke X utjecalo na životni vijek u usporedbi s prošlom godinom.

2. Kako napraviti kohorte u SQL-u?

Veličina članka i zdrav razum nam ne dopuštaju da ovdje iznesemo svoje stvarne podatke - u testnom dumpu statistika za godinu i pol dana: 1200 korisnika i 53 000 transakcija. Kako biste se mogli poigrati s ovim podacima, pripremili smo docker sliku s MySQL-om i Grafanom u kojoj to sve možete sami iskusiti. Link na GitHub na kraju članka.

Ovdje ćemo prikazati stvaranje kohorti koristeći pojednostavljeni primjer.

Pretpostavimo da imamo uslugu. Korisnici se tamo registriraju i troše novac na usluge. S vremenom korisnici odustaju. Želimo saznati koliko dugo korisnici žive, te koliko ih otpada nakon 1. i 2. mjeseca korištenja usluge.

Da bismo odgovorili na ova pitanja, moramo konstruirati skupine na temelju mjeseca registracije. Aktivnost ćemo mjeriti troškovima u svakom mjesecu. Umjesto troškova, mogu postojati narudžbe, naknade za pretplatu ili bilo koje druge aktivnosti vezane uz vrijeme.

Početni podaci

Primjeri su napravljeni u MySQL-u, ali za druge DBMS-ove ne bi trebalo biti značajnih razlika.

Tablica korisnika - korisnici:

ID korisnika
Datum registracije

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tablica troškova - obračun:

ID korisnika
Datum
Suma

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

Odaberite sve korisničke otpise i datum registracije:

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

Rezultat:

ID korisnika
Datum
Datum registracije

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

Kohorte gradimo po mjesecima; da bismo to učinili, pretvaramo sve datume u mjesece:

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

Sada moramo znati koliko je mjeseci korisnik bio aktivan - to je razlika između mjeseca zaduženja i mjeseca registracije. MySQL ima funkciju PERIOD_DIFF() - razlika između dva mjeseca. Dodajte PERIOD_DIFF() zahtjevu:

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

ID korisnika
Mjesec obračuna
Datum registracije
MjeseciRazl

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

Brojimo aktivirane korisnike u svakom mjesecu - grupiramo zapise prema 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

Rezultat:

Broj korisnika
Mjesec obračuna
Mjesec registracije
MjeseciRazl

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

U siječnju, veljači i ožujku pojavio se po jedan novi korisnik - MonthsDiff = 0. Jedan siječanjski korisnik bio je aktivan u veljači - RegistrationMonth = 2019-01, BillingMonth = 2019-02, a jedan veljački korisnik bio je aktivan u ožujku.

Naravno, uzorci su bolje vidljivi u velikom skupu podataka.

Kako prebaciti kohorte na Grafanu

Naučili smo formirati kohorte, ali kada ima puno zapisa, više ih nije lako analizirati. Zapisi se mogu izvesti u Excel i oblikovati u prekrasne tablice, ali to nije naša metoda!

Kohorte se mogu prikazati kao interaktivni grafikon u grafana.

Da bismo to učinili, dodajemo još jedan upit za pretvaranje podataka u format prikladan za Grafanu:

SELECT
  DATE_ADD(CONCAT(s.RegistrationMonth, '-01'), INTERVAL s.MonthsDiff MONTH) AS time_sec,
  SUM(s.Users) AS value,
  s.RegistrationMonth AS metric
FROM (
  ## старый запрос, возвращающий когорты
  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

I uploadati podatke na Grafanu.

Primjer grafa iz demo:

Dodirni rukama:

GitHub repozitorij s primjerom je docker slika s MySQL-om i Grafanom koju možete pokrenuti na svom računalu. Baza podataka već sadrži demo podatke za godinu i pol, od siječnja 2018. do srpnja 2019.

Ako želite, možete učitati svoje podatke na ovu sliku.

PS Članci o kohortnoj analizi u SQL-u:

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

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

Izvor: www.habr.com