🥇Kuidas koguda kasutajaid graafikute kujul Grafanas [+docker-pilt näitega]

Kuidas me lahendasime kasutajate cohortide visualiseerimise ülesande Promopult teenuses Grafana abil.

Promopult on võimas teenus, millel on suur kasutajaskond. Kümne aasta jooksul on süsteemi registreerimiste arv ületanud miljoni. Need, kes on selliste teenustega kokku puutunud, teavad, et see kasutajaskond pole kaugeltki ühtlane.

Mõned registreerusid ja jäid igaveseks unustusse. Mõned unustasid oma parooli ja registreerusid paar korda kuue kuu jooksul taas. Mõni toob raha kassasse, samas kui teised tulid tasuta tööriistu. Ja oleks hea, kui igaüks sellest mingit kasu tooks.

Nii suurte andmehulkade puhul nagu meil on, pole mõtet analüüsida üksikute kasutajate käitumist ja teha mikrootsuseid. Kuid trende kinni püüda ja töötada suurte gruppidega on võimalik ja vajalik. Just seda me teeme.

Kokkuvõte

Mis on cohortide analüüs ja milleks see vajalik on.
Kuidas luua kogruppe registreerimiskuu põhjal SQL-is.
Kuidas viia kogrupid üle Grafana.

Kui tead juba, mis on cohortide analüüs ja kuidas seda SQL-is teha, mine kohe viimasesse jaotisse.

1. Mis on cohortide analüüs ja milleks see vajalik on

Cohortide analüüs on meetod, mis põhineb erinevate kasutajate rühmade (cohortide) võrdlemisel. Enamasti moodustame me rühmad registreerimisnädala või -kuu järgi, mil kasutaja hakkas teenust kasutama. Selle alusel arvutatakse kasutaja eluiga, mis on juba näitaja, mille põhjal saab teha üsna keerukat analüüsi. Näiteks, et mõista:

kuidas mõjutab kaasamise kanal kasutaja eluiga;
kuidas mõjutab mõne funktsiooni või teenuse kasutamine eluiga;
kuidas mõjutas funktsiooni X käivitamine eluiga võrreldes eelmise aastaga.

2. Kuidas luua kogruppe SQL-is?

Artikli mahud ja mõistlikud argumendid ei võimalda meil tuua siia meie reaalseid andmeid — testdumpis on statistika pooleteise aasta jooksul: 1200 kasutajat ja 53 000 tehingut. Et saaksite nende andetega mängida, oleme ette valmistanud docker-pildi MySQL ja Grafana jaoks, milles saate kõike ise katsetada. Link GitHubi lehele leiab artikli lõpust.

Siin näitame kogruppe loomist lihtsustatud näite abil.

Oletame, et meil on teenus. Selles registreeruvad kasutajad ja kulutavad raha teenustele. Aja jooksul kaotavad kasutajad huvi. Me tahame teada, kui kaua kasutajad püsivad ja kui palju neist loobub pärast 1. ja 2. kuu kasutamist teenusest.

Nendele küsimustele vastamiseks peame koostama kooti registreerimise kuu järgi. Aktiviteeti mõõdame igakuistelt kuludelt. Kulude asemel võivad olla tellimused, liikmemaksud või muu ajaga seotud tegevus.

Algandmed

Näited on tehtud MySQL-is, kuid teiste andmebaasisüsteemide vahel ei tohiks olla olulisi erinevusi.

Kasutajate tabel — users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Kulu tabel — billing:

userId
, samuti veel mustandina olev API
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

Valime kõik kasutajate kanded ja registreerimise kuupäeva:

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

Tulemus:

userId
, samuti veel mustandina olev API
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

Kogume koote kuude kaupa, selleks muundame kõik kuupäevad kuudeks:

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

Nüüd peame teadma, kui palju kuude jooksul kasutaja aktiivne oli — see on vahe kulutamiskuu ja registreerimise kuu vahel. MySQL-is on funktsioon PERIOD_DIFF() — kahe kuu vahe. Lisame PERIOD_DIFF() päringusse:

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

Loeme igas kuus aktiveerunud kasutajad — grupeerime kirjed BillingMonth, RegistrationMonth ja MonthsDiff põhjal:

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

Tulemus:

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

Jaanuaris, veebruaris ja märtsis ilmus igas kuus üks uus kasutaja — MonthsDiff = 0. Üks jaanuari kasutaja oli aktiivne ka veebruaris — RegistrationMonth = 2019-01, BillingMonth = 2019-02, samuti oli üks veebruari kasutaja aktiivne märtsis.

Suurte andmehulkade puhul on mustrid loomulikult paremini nähtavad.

Kuidas kooted Grafanas kuvada

Kooted oskasime koostada, kuid kui kirjeid on palju, on neid raske analüüsida. Kirjed saab eksportida Excelisse ja luua ilusaid tabeleid, kuid see ei ole meie meetod!

Kooted saab esitada interaktiivse graafikuna. Grafana.

Selleks lisame veel ühe päringu, et muuta andmed Grafana jaoks sobivaks:

VALI
  DATE_ADD(CONCAT(s.RegistrationMonth, '-01'), INTERVAL s.MonthsDiff MONTH) AS time_sec,
  SUM(s.Users) AS value,
  s.RegistrationMonth AS metric
FROM (
  ## vana päring, mis tagastab kogumid
  VALI 
    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

Ja eksportime andmed Grafanasse.

Näide graafikust demo:

Käeotsaga katsuda:

GitHubi hoidla näitega — see on docker-pilt MySQL ja Grafana, mida saab oma arvutis käivitada. Andmebaasis on juba poolteise aasta demonäidised, jaanuar 2018 kuni juuli 2019.

Soovi korral saab sellesse pildisse laadida oma andmed.

P.S. Artiklid SQL-iga kogumite analüüsi kohta:

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

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

Allikas: habr.com