ProHoster > Блог > Administracija > Kako prikupiti korisničke kohorte kao grafikone u Grafani [+ docker slika s primjerom]

Kako prikupiti korisničke kohorte kao grafikone u Grafani [+ docker slika s primjerom]

Kako prikupiti korisničke kohorte kao grafikone u Grafani [+ docker slika s primjerom]

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

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

Neko se prijavio i "zaspao" zauvijek. Neko je zaboravio lozinku i registrovao se još par puta tokom šest meseci. Neko donosi novac na kasu, a neko je došao po gratis instrumenti. I bilo bi lijepo dobiti neki profit od svih.

Na ovako velikim skupovima podataka kao što je naš, analiza ponašanja pojedinog korisnika i donošenje mikro odluka je besmisleno. Ali uočavanje trendova i rad sa velikim grupama je moguće i neophodno. Što je upravo ono što mi radimo.

Rezime

  1. Šta je kohortna analiza i zašto je potrebna?
  2. Kako napraviti kohorte po mjesecima registracije korisnika u SQL-u.
  3. Kako prenijeti kohorte na grafana.

Ako već znate šta je kohortna analiza i kako to učiniti u SQL-u, pređite direktno na posljednji odjeljak.

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

Kohortna analiza je metoda zasnovana na poređenju različitih grupa (kohorti) korisnika. Najčešće se naše grupe formiraju po sedmici ili mjesecu u kojem je korisnik počeo koristiti uslugu. Odavde se računa životni vijek korisnika, a to je pokazatelj na osnovu kojeg se može izvršiti prilično složena analiza. Na primjer, razumjeti:

  • kako kanal akvizicije utječe na životni vijek korisnika;
  • kako upotreba bilo koje funkcije ili usluge utiče na životni vijek;
  • Kako je lansiranje funkcije X utjecalo na vijek trajanja u odnosu na prošlu godinu.

2. Kako napraviti kohorte u SQL-u?

Veličina članka i zdrav razum nam ne dozvoljavaju da ovdje iznesemo naše stvarne podatke - u test dumpu, statistika za godinu i po dana: 1200 korisnika i 53 transakcija. Kako biste se mogli poigrati ovim podacima, pripremili smo docker image sa MySQL-om i Grafanom u kojem sve to možete iskusiti i sami. Link na GitHub na kraju članka.

I ovdje ćemo pokazati stvaranje kohorti na pojednostavljenom primjeru.

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

Da bismo odgovorili na ova pitanja, moramo konstruirati kohorte na osnovu mjeseca registracije. Aktivnost ćemo mjeriti po troškovima u svakom mjesecu. Umjesto troškova, mogu postojati narudžbe, pretplate ili bilo koja druga aktivnost zasnovana na vremenu.

Sirovi podaci

Primeri su rađeni u MySQL-u, ali za druge DBMS ne bi trebalo biti značajnih razlika.

Tabela korisnika - korisnici:

Korisnički broj
Datum registracije

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabela troškova - naplata:

Korisnički broj
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:

Korisnički broj
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

Gradimo kohorte po mjesecima; da bismo to učinili, pretvaramo sve datume u mjesece:

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

Sada moramo znati koliko mjeseci je 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

Korisnički broj
BillingMonth
Datum registracije
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

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

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

U januaru, februaru i martu pojavio se po jedan novi korisnik - MonthsDiff = 0. Jedan januarski korisnik je bio aktivan u februaru - RegistrationMonth = 2019-01, BillingMonth = 2019-02, a jedan februarski korisnik je bio aktivan u martu.

Naravno, obrasci su bolje vidljivi u velikom skupu podataka.

Kako prenijeti kohorte u Grafanu

Naučili smo kako da formiramo kohorte, ali kada ima puno zapisa, više ih nije lako analizirati. Zapisi se mogu izvesti u Excel i formirati u prekrasne tabele, ali to nije naš metod!

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 prenesite podatke u Grafanu.

Primjer grafikona iz demo:

Kako prikupiti korisničke kohorte kao grafikone u Grafani [+ docker slika s primjerom]

Dodirni rukama:

GitHub spremište sa primjerom je docker slika sa MySQL i Grafanom koju možete pokrenuti na svom računaru. Baza podataka već sadrži demo podatke za godinu i po dana, od januara 2018. do jula 2019. godine.

Ako želite, možete učitati svoje podatke u 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

Dodajte komentar