Kuidas luua kasutajakohti graafikutena Grafanas [+docker-pilt näidiseks]

Kuidas me lahendasime kasutajakohtade visualiseerimise ülesande Promopult'is Grafana abil.

Promopult — võimas teenus, millel on palju kasutajaid. 10 aasta jooksul on süsteemis registreerimiste arv ületanud miljoni. Need, kes on selliste teenustega kokku puutunud, teavad, et see kasutajaskond ei ole kaugeltki ühesugune.

Mõned registreerusid ja on igaveseks unustanud. Mõned unustasid parooli ja registreerusid paar korda kuue kuu jooksul. Mõned toovad raha kassasse, teised aga tulid tasuta tööriistu. Ja oleks hea, kui igalt ühel neist oleks mingisugune profit.

Niisuguste suurte andmehulkade puhul, nagu meil on, on mõttetu analüüsida ühe kasutaja käitumist ja teha mikrootsuseid. Küll aga on vaja jälgida trende ja töötada suurte gruppidega — just seda me teeme.

Kokkuvõte

1. Mis on koondanalüüs ja miks see vajalik on.
2. Kuidas luua koondid kasutajate registreerimise kuu järgi SQL-is.
3. Kuidas kanda koondid üle Grafana.

Kui tead juba, mis on koondanalüüs ja kuidas seda SQL-is teha, mine kohe viimasel jaotisele.

1. Mis on kohortide analüüs ja miks see on vajalik

Kohortide analüüs on meetod, mis põhineb erinevate kasutajagruppide (kohortide) võrdlemisel. Enamasti moodustame grupid nädalate või kuude lõikes, mil kasutaja alustas teenuse kasutamist. Sellest arvutatakse kasutaja eluiga, mis on näitaja, mille alusel saab teostada üsna keerukat analüüsi. Näiteks mõista:

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

2. Kuidas luua kohorde SQL-is?

Artikli suurus ja tervet mõistust ei luba meil tuua siia meie tegelikke andmeid — testimisdump sisaldab statistikat poolteist aastat: 1200 kasutajat ja 53 000 tehingut. Et saaksite nende andmetega katsetada, oleme valmistanud docker-pildi koos MySQL ja Grafanaga, kus saab kõike ise proovida. Link GitHubi lehele artikli lõpus.

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

Oletame, et meil on teenus. Kasutajad registreeruvad sellele ja kulutavad raha teenustele. Aja jooksul lahkuvad kasutajad. Soovime teada, kui kaua kasutajad püsivad ja kui palju neist lahkub pärast teenuse esimest ja teist kuud.

Nendele küsimustele vastamiseks peame koostama kuukohordid. Tegevust mõõdame igas kuus tehtud kulutuste järgi. Kulutuste asemel võivad olla tellimused, liikmemaksud või mis tahes muu tegevus, mis on ajaga seotud.

Algandmed

Näited on koostatud MySQL-i abil, kuid teiste andmebaasisüsteemide puhul ei tohiks olla mingeid olulisi erinevusi.

Kasutajate tabel — users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Kulutuste tabel — billing:

userId
Date
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 väljaminekud ja registreerimise kuupäevad:

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

Tulemus:

userId
Date
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

Koostame kuukohordid, muutes kõik kuupäevad kuudeks:

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

Nüüd peame teadma, kui mitu kuud on kasutaja aktiivne olnud — see on erinevus väljamineku kuu ja registreerimise kuu vahel. MySQL-is on olemas funktsioon PERIOD_DIFF() — erinevus kahe kuu vahel. Lisame PERIOD_DIFF() päringusse:

VALI
    b.userId,
    DATE_FORMAT(b.Date, '%Y-%m') AS Arvekuu,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS Registreerimiskuu,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS KuudeVahe
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId

userId
Arvekuu
RegistrationDate
KuudeVahe

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

Loeme igas kuus aktiveeritud kasutajate arvu — rühmitame kirjed Arvekuul, Registreerimiskuul ja KuudeVahe järgi:

VALI
    COUNT(DISTINCT(b.userId)) AS KasutajateArv,
    DATE_FORMAT(b.Date, '%Y-%m') AS Arvekuu,
    DATE_FORMAT(u.RegistrationDate, '%Y-%m') AS Registreerimiskuu,
    PERIOD_DIFF(DATE_FORMAT(b.Date, '%Y%m'), DATE_FORMAT(u.RegistrationDate, '%Y%m')) AS KuudeVahe
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
GROUP BY Arvekuu, Registreerimiskuu, KuudeVahe

Tulemus:

KasutajateArv
Arvekuu
Registreerimiskuu
KuudeVahe

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 igaühes üks uus kasutaja — KuudeVahe = 0. Üks jaanuari kasutaja oli aktiivne ka veebruaris — Registreerimiskuu = 2019-01, Arvekuu = 2019-02, samuti oli üks veebruari kasutaja aktiivne märtsis.

Suurel andmehulgad on mustrid loomulikult paremini nähtavad.

Kuidas kandeid Grafanas edastada

Oleme õppinud koogreid koostama, kuid kui kirjeid on palju, on nende analüüsimine juba keeruline. Kirjed saab eksportida Excelisse ja luua ilusaid tabeleid, kuid see ei ole meie meetod!

Koogreid saab esitada interaktiivse graafikuna Grafana.

Selle jaoks lisame veel ühe päringu, et muuta andmed Grafana sobivasse formaati:

SELECT
  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 koorid
  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

Ja ekspordime andmed Grafanasse.

Graafiku näide: demo:

Käega katsuda:

GitHubi hoidla näitega — see on docker-pilt koos MySQL ja Grafana'ga, mille saab arvutisse käivitada. Andmebaasis on juba poolteise aasta demoandmed, alates 2018. aasta jaanuarist kuni 2019. aasta juuli lõpuni.

Soovi korral võib selle pildi sisse oma andmeid laadida.

P.S. Artiklid kooranalüüsist SQL-is:

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

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

Allikas: habr.com