Jak sestavit uživatelské skupiny ve formě grafů v Grafana [+docker obraz s příkladem]

Jak jsme vyřešili vizualizaci uživatelských kohort v službě Promopult pomocí Grafana.

Promopult — silná služba s velkým počtem uživatelů. Za 10 let fungování překročil počet registrací v systému milion. Ti, kteří se setkali s podobnými službami, vědí, že tento obrovský počet uživatelů rozhodně není homogenní.

Někdo se zaregistroval a navždy "usnul". Někdo zapomněl heslo a zaregistroval se ještě několikrát za půl roku. Někdo přináší peníze do pokladny, zatímco někdo přišel pro bezplatné nástroje. A bylo by dobré z každého získat nějaký profit.

Na tak velkých objemech dat, jaké máme, je analýza chování jednotlivého uživatele a přijímání mikrorozhodnutí nesmyslné. Místo toho je možné a nutné zachycovat trendy a pracovat s velkými skupinami. Což vlastně také děláme.

Krátké shrnutí

1. Co je to kohortní analýza a k čemu je dobrá.
2. Jak vytvořit kohorty podle měsíce registrace uživatelů v SQL.
3. Jak přenést kohorty do Grafana.

Pokud již víte, co je kohortní analýza a jak ji udělat v SQL, můžete přejít rovnou k poslední části.

1. Co je to kohortní analýza a k čemu je dobrá

Kohortní analýza je metoda založená na porovnávání různých skupin (kohort) uživatelů. Nejčastěji tvoříme skupiny podle týdne nebo měsíce, ve kterém uživatel začal službu používat. Odtud se počítá doba života uživatele, což je už ukazatel, na základě kterého lze provádět poměrně složité analýzy. Například pochopit:

• jak ovlivňuje kanál získávání dobu života uživatele;
• jak používání nějaké funkce nebo služby ovlivňuje dobu života;
• jak spuštění funkce X ovlivnilo dobu života ve srovnání s minulým rokem.

2. Jak vytvořit kohorty v SQL?

Velikost článku a zdravý rozum neumožňují uvádět zde naše reálná data — v testovním dumpu jsou statistiky za půl roku: 1200 uživatelů a 53 000 transakcí. Aby jste si mohli s těmito daty pohrát, připravili jsme docker obraz s MySQL a Grafana, kde si to můžete vyzkoušet sami. Odkaz na GitHub je na konci článku.

A zde ukážeme vytvoření kohort na zjednodušeném příkladu.

Představme si, že máme službu. Uživatelé se v ní registrují a utrácejí peníze za služby. V průběhu času uživatelé odcházejí. Chceme vědět, jak dlouho uživatelé vydrží, a kolik z nich odchází po 1. a 2. měsíci používání služby.

Abychom odpověděli na tyto otázky, musíme sestavit kohorty podle měsíce registrace. Aktivitu budeme měřit podle výdajů v každém měsíci. Místo výdajů mohou být objednávky, předplatné nebo jakákoli jiná aktivita spojená s časem.

Vstupní data

Příklady jsou vytvořeny v MySQL, ale pro ostatní DBMS by neměly být zásadní rozdíly.

Tabulka uživatelů — users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabulka výdajů — 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

Vybereme všechny výdaje uživatelů a datum registrace:

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

Result:

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

Kohorty sestavujeme podle měsíců, k tomu převedeme všechna data na měsíce:

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

Teď potřebujeme vědět, kolik měsíců byl uživatel aktivní — to je rozdíl mezi měsícem výdaje a měsícem registrace. V MySQL existuje funkce PERIOD_DIFF() — rozdíl mezi dvěma měsíci. Přidáváme PERIOD_DIFF() do dotazu:

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

4
2019-03
2019-03
0

Počítáme aktivované uživatele v každém měsíci — seskupujeme záznamy podle 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

Result:

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

V lednu, únoru a březnu přibyl jeden nový uživatel — MonthsDiff = 0. Jeden uživatel z ledna byl aktivní i v únoru — RegistrationMonth = 2019-01, BillingMonth = 2019-02, stejně jako jeden uživatel z února byl aktivní v březnu.

Na velkém množství dat jsou vzorce samozřejmě lépe viditelné.

Jak přenést kohorty do Grafana

Kohorty jsme se naučili vytvářet, ale když se záznamů stane hodně, analyzovat je už není snadné. Záznamy lze exportovat do Excelu a vytvořit krásné tabulky, ale to není naše metoda!

Kohorty lze zobrazit jako interaktivní graf v Grafana.

Přidáme ještě jeden dotaz, abychom převedli data do formátu vhodného pro 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 (
  ## starý dotaz vracející kohorty
  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 exportujeme data do Grafany.

Příklad grafu z demo:

Dotknout se rukama:

GitHub repozitář s příkladem – je to docker obraz s MySQL a Grafanou, který můžete spustit na svém počítači. V databázi už jsou demo data za rok a půl, od ledna 2018 do července 2019.

Pokud chcete, můžete do tohoto obrazu nahrát svá data.

P.S. Články o kohortní analýze na SQL:

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

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

Zdroj: habr.com