🥇Ako zbierať kohorty používateľov vo forme grafov v Grafane [+obrázok dockera s príkladom]

Ako sme vyriešili problém vizualizácie kohort používateľov v službe Promopult pomocou Grafany.

Promópult - výkonná služba s veľkým počtom používateľov. Za 10 rokov fungovania presiahol počet registrácií v systéme milión. Tí, ktorí sa s podobnými službami stretli, vedia, že toto pole používateľov nie je ani zďaleka homogénne.

Niekto sa prihlásil a „zaspal“ navždy. Niekto zabudol svoje heslo a v priebehu šiestich mesiacov sa ešte niekoľkokrát zaregistroval. Niekto prinesie peniaze do pokladne a niekto si prišiel po zadarmo nástrojov. A bolo by pekné získať od každého nejaký zisk.

Na takých veľkých súboroch údajov, ako sú tie naše, je analyzovanie správania jednotlivého používateľa a prijímanie mikrorozhodnutí zbytočné. Ale zachytiť trendy a pracovať s veľkými skupinami je možné a potrebné. Čo je presne to, čo robíme.

zhrnutie

Čo je kohortová analýza a prečo je potrebná?
Ako vytvoriť kohorty podľa mesiaca registrácie používateľa v SQL.
Ako preniesť kohorty do grafana.

Ak už viete, čo je kohortová analýza a ako sa to robí v SQL, preskočte rovno na poslednú časť.

1. Čo je kohortová analýza a prečo je potrebná?

Kohortová analýza je metóda založená na porovnávaní rôznych skupín (kohort) používateľov. Najčastejšie sú naše skupiny tvorené týždňom alebo mesiacom, v ktorom používateľ začal službu využívať. Odtiaľ sa počíta životnosť používateľa a toto je ukazovateľ, na základe ktorého je možné vykonať pomerne komplexnú analýzu. Pochopte napríklad:

ako akvizičný kanál ovplyvňuje životnosť používateľa;
ako používanie akejkoľvek funkcie alebo služby ovplyvňuje životnosť;
Ako spustenie funkcie X ovplyvnilo životnosť v porovnaní s minulým rokom.

2. Ako vytvoriť kohorty v SQL?

Veľkosť článku a zdravý rozum nám nedovoľujú prezentovať tu naše reálne dáta – v testovacom výpise štatistiky za rok a pol: 1200 53 používateľov a 000 XNUMX transakcií. Aby ste sa s týmito dátami mohli hrať, pripravili sme pre vás docker image s MySQL a Grafana, v ktorom si to všetko môžete vyskúšať na vlastnej koži. Odkaz na GitHub na konci článku.

A tu si ukážeme vytváranie kohort na zjednodušenom príklade.

Predpokladajme, že máme službu. Používatelia sa tam registrujú a míňajú peniaze za služby. Postupom času používatelia odpadávajú. Chceme zistiť, ako dlho užívatelia žijú a koľko z nich odpadne po 1. a 2. mesiaci používania služby.

Na zodpovedanie týchto otázok musíme zostaviť kohorty na základe mesiaca registrácie. Aktivitu budeme merať podľa výdavkov v každom mesiaci. Namiesto výdavkov môžu existovať objednávky, poplatky za predplatné alebo akékoľvek iné časové aktivity.

Surové údaje

Príklady boli vytvorené v MySQL, ale pre ostatné DBMS by nemali existovať žiadne významné rozdiely.

Tabuľka používateľov – používatelia:

ID používateľa
Dátum registrácie

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabuľka nákladov – fakturácia:

ID používateľa
dátum
Súčet

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

Vyberte všetky odpisy používateľov a dátum registrácie:

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

Výsledok:

ID používateľa
dátum
Dátum registrácie

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

Kohorty vytvárame podľa mesiacov, aby sme to dosiahli, konvertujeme všetky dátumy na mesiace:

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

Teraz potrebujeme vedieť, koľko mesiacov bol používateľ aktívny – to je rozdiel medzi mesiacom debetu a mesiacom registrácie. MySQL má funkciu PERIOD_DIFF() - rozdiel medzi dvoma mesiacmi. Pridajte PERIOD_DIFF() do požiadavky:

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 používateľa
Fakturačný mesiac
Dátum registrácie
MesiacsDiff

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

Počítame používateľov aktivovaných v každom mesiaci – záznamy zoskupujeme podľa BillingMonth, RegistrationMonth a 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

Výsledok:

Počet používateľov
Fakturačný mesiac
Mesiac registrácie
MesiacsDiff

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

V januári, februári a marci sa objavil každý jeden nový používateľ – MonthsDiff = 0. Jeden januárový používateľ bol aktívny vo februári – RegistrationMonth = 2019-01, BillingMonth = 2019-02 a jeden februárový používateľ bol aktívny v marci.

Prirodzene, vzory sú lepšie viditeľné vo veľkom súbore údajov.

Ako preniesť kohorty do Grafany

Naučili sme sa vytvárať kohorty, ale keď existuje veľa záznamov, už nie je ľahké ich analyzovať. Záznamy je možné exportovať do Excelu a formovať z nich krásne tabuľky, ale toto nie je naša metóda!

Kohorty môžu byť zobrazené ako interaktívny graf v grafana.

Za týmto účelom pridávame ďalší dotaz na konverziu údajov do formátu vhodného pre Grafana:

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

A nahrajte údaje do Grafany.

Príklad grafu z demonštrácie:

Dotknite sa rukami:

Úložisko GitHub s príkladom je docker image s MySQL a Grafana, ktorý môžete spustiť na svojom počítači. Databáza už obsahuje demo dáta za rok a pol, od januára 2018 do júla 2019.

Ak chcete, môžete do tohto obrázka načítať svoje údaje.

PS Články o kohortovej analýze v SQL:

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

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

Zdroj: hab.com