ProHoster > Blog > Administrazioa > Nola bildu erabiltzaile-kohorteak grafiko gisa Grafana-n [+ docker irudia adibidearekin]

Nola bildu erabiltzaile-kohorteak grafiko gisa Grafana-n [+ docker irudia adibidearekin]

Nola bildu erabiltzaile-kohorteak grafiko gisa Grafana-n [+ docker irudia adibidearekin]

Nola konpondu genuen Grafana erabiliz Promopult zerbitzuan erabiltzaile kohorteak ikusteko arazoa.

Promopult - zerbitzu indartsua erabiltzaile kopuru handiarekin. 10 urteko funtzionamenduan zehar, sistemaren erregistroen kopurua milioi bat gainditu da. Antzeko zerbitzuak topatu dituztenek badakite erabiltzaile sorta hori homogeneotik urrun dagoela.

Norbaitek izena eman zuen eta betiko "lo hartu zuen". Norbaitek pasahitza ahaztu eta beste pare bat aldiz erregistratu zen sei hilabetean. Norbaitek dirua ekartzen du kutxazainera, eta norbait doako eske etorri zen tresnak. Eta polita litzateke guztiongandik etekin bat ateratzea.

Gurea bezalako datu multzo handietan, erabiltzaile indibidual baten portaera aztertzea eta mikroerabakiak hartzea ez da ezertarako balio. Baina joerak harrapatzea eta talde handiekin lan egitea posible eta beharrezkoa da. Hori da, hain zuzen, egiten duguna.

laburpena

  1. Zer da kohorte-analisia eta zergatik behar da?
  2. Nola egin kohorteak SQL-n erabiltzaileak erregistratutako hilabetearen arabera.
  3. Nola transferitu kohorteak Grafana.

Kohorteen analisia zer den eta nola egin SQL-n ezagutzen baduzu, joan zuzenean azken atalera.

1. Zer da kohorte-analisia eta zergatik behar da?

Kohorteen analisia erabiltzaile talde desberdinak (kohorteak) alderatzean oinarritzen den metodoa da. Gehienetan, gure taldeak erabiltzailea zerbitzua erabiltzen hasi zen astearen edo hilabetearen arabera eratzen dira. Hemendik aurrera erabiltzailearen bizitza-denbora kalkulatzen da, eta horren oinarrian analisi konplexu samarra egin daitekeen adierazlea da. Adibidez, ulertu:

  • eskuratze-kanalak nola eragiten du erabiltzailearen bizitzan;
  • Ezaugarri edo zerbitzuren erabilerak nola eragiten duen bizitzan;
  • X funtzioaren abiarazteak nola eragin zuen bizitzan iazkoarekin alderatuta.

2. Nola egin kohorteak SQLn?

Artikuluaren tamainak eta zentzu onak ez gaituzte gure benetako datuak hemen aurkezteko aukera ematen: probako zabortegian, urte eta erdiko estatistikak: 1200 erabiltzaile eta 53 transakzio. Datu hauekin jolastu ahal izateko, docker irudi bat prestatu dugu MySQL eta Grafana-rekin, eta bertan zuk zeuk esperimentatu dezakezu. Artikuluaren amaieran GitHub-era estekatu.

Eta hemen kohorteen sorrera erakutsiko dugu adibide sinplifikatu bat erabiliz.

Demagun zerbitzu bat dugula. Erabiltzaileek bertan erregistratu eta dirua gastatzen dute zerbitzuetan. Denborarekin, erabiltzaileek uzten dute. Zerbitzua erabiltzen duten 1. eta 2. hilabetearen ondoren zenbat denbora bizi diren erabiltzaileak eta haietako zenbat erortzen diren jakin nahi dugu.

Galdera horiei erantzuteko, izena emateko hilabetearen arabera kohorteak eraiki behar ditugu. Jarduera gastuen arabera neurtuko dugu hilabete bakoitzean. Gastuen ordez, eskariak, harpidetza kuotak edo denboran oinarritutako beste edozein jarduera egon daitezke.

Datu gordinak

Adibideak MySQL-n egin ziren, baina beste DBMS batzuetarako ez luke desberdintasun nabarmenik egon behar.

Erabiltzaileen taula - erabiltzaileak:

userId
Izena emateko data

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Kostuen taula - fakturazioa:

userId
data
Batura

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

Hautatu erabiltzaileen bajak eta erregistro-data guztiak:

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

Emaitza:

userId
data
Izena emateko data

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

Kohorteak hilabeteka eraikitzen ditugu; horretarako, data guztiak hilabetetan bihurtzen ditugu:

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

Orain erabiltzailea zenbat hilabete egon den aktibo jakin behar dugu - hau da zordunketa hilabetearen eta erregistroaren hilabetearen arteko aldea. MySQL-k PERIOD_DIFF() funtzio bat du - bi hilabeteren arteko aldea. Gehitu PERIOD_DIFF() eskaerari:

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
FakturazioHilabetea
Izena emateko data
HilabeteakDif

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

Hilabete bakoitzean aktibatutako erabiltzaileak zenbatzen ditugu - erregistroak BillingMonth, RegistrationMonth eta MonthsDiff arabera biltzen ditugu:

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

Emaitza:

Erabiltzaileen kopurua
FakturazioHilabetea
Izen emateaHilabetea
HilabeteakDif

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

Urtarrilean, otsailean eta martxoan, erabiltzaile berri bat agertu zen bakoitzean - MonthsDiff = 0. Urtarrileko erabiltzaile bat aktibo zegoen otsailean - RegistrationMonth = 2019-01, BillingMonth = 2019-02, eta otsaileko erabiltzaile bat aktibo zegoen martxoan.

Jakina, ereduak hobeto ikusten dira datu multzo handi batean.

Nola transferitu kohorteak Grafanara

Kohorteak osatzen ikasi dugu, baina erregistro asko daudenean, jada ez da erraza izaten horiek aztertzea. Erregistroak Excel-era esportatu eta taula eder batean eratu daitezke, baina hau ez da gure metodoa!

Kohorteak grafiko interaktibo gisa erakutsi daitezke Grafana.

Horretarako, beste kontsulta bat gehitzen dugu datuak Grafanarako egokia den formatu batean bihurtzeko:

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

Eta igo datuak Grafanara.

grafikoaren adibidea demo:

Nola bildu erabiltzaile-kohorteak grafiko gisa Grafana-n [+ docker irudia adibidearekin]

Eskuekin ukitu:

GitHub biltegia adibidearekin Zure ordenagailuan exekutatu dezakezun MySQL eta Grafana dituen docker-irudi bat da. Datu-baseak dagoeneko urte eta erdiko demo datuak ditu, 2018ko urtarriletik 2019ko uztailera bitartean.

Nahi baduzu, zure datuak karga ditzakezu irudi honetan.

PS Kohorteen analisiari buruzko artikuluak SQL-n:

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

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

Iturria: www.habr.com

Gehitu iruzkin berria