Hogyan lehet felépíteni a felhasználói kohortokat grafikonok formájában a Grafanában [+docker-kép példával]

Hogyan oldottuk meg a felhasználói kohortok vizualizálásának feladatát a Promopult szolgáltatásában a Grafana segítségével.

Promopult — egy erőteljes szolgáltatás nagy számú felhasználóval. Tíz évnyi működés után a rendszerben regisztráltak száma meghaladta az egymilliót. Aki már találkozott hasonló szolgáltatásokkal, tudja, hogy ez a felhasználói tömeg messze nem homogén.

Valaki regisztrált és „örökre elaludt”. Valaki elfelejtette a jelszavát és még párszor regisztrált fél év alatt. Valaki pénzt hoz a kasszába, míg más miatt ingyenes eszközöket. És jó lenne minél több profitot szerezni tőlük.

Ilyen nagy adatállományok esetén, mint amilyenek nekünk is vannak, értelmetlen egyes felhasználók viselkedését elemezni és mikro-döntéseket hozni. Az viszont lehetséges és szükséges, hogy a trendeket figyeljük és nagy csoportokkal foglalkozzunk. Amit mi, lényegében, csinálunk.

Összefoglaló

1. Mi az a kohorszanalízis és miért van szükség rá.
2. Hogyan lehet kohortokat készíteni a felhasználók regisztrációs hónapja szerint SQL-ben.
3. Hogyan lehet áthelyezni a kohortokat a Grafanába.

Ha már tudja, mi az a kohorszanalízis és hogyan kell csinálni SQL-ben, akkor azonnal ugrás a legutolsó részre.

1. Mi az a kohorszanalízis és miért van szükség rá

A kohorszanalízis egy olyan módszer, amely különböző felhasználói csoportok (kohortok) összehasonlításán alapul. Leggyakrabban a csoportok azt a hetet vagy hónapot figyelembe véve alakulnak, amelyben a felhasználó elkezdte használni a szolgáltatást. Ebből számolható ki a felhasználó élettartama, ami már egy olyan mutató, amelyre alapozva meglehetősen összetett elemzéseket lehet végezni. Például megérteni:

• hogyan befolyásolja a megszerzési csatorna a felhasználó élettartamát;
• hogyan befolyásolja valamely funkció vagy szolgáltatás használata a felhasználó élettartamát;
• hogyan befolyásolta az X funkció bevezetése a felhasználó élettartamát az előző évhez képest.

2. Hogyan készítsünk kohordokat SQL-ben?

A cikk mérete és a józan ész nem teszi lehetővé, hogy itt valós adatokat hozzunk fel — a tesztadatbázis másfél év statisztikáját tartalmazza: 1200 felhasználó és 53 000 tranzakció. Hogy Ön is tudjon játszani ezekkel az adatokkal, készítettünk egy docker-képet MySQL-lel és Grafanával, ahol mindezt kipróbálhatja. A GitHub linkjét a cikk végén találja.

Itt egy egyszerű példán keresztül megmutatjuk a kohortok létrehozását.

Tegyük fel, hogy van egy szolgáltatásunk. Ebben a felhasználók regisztrálnak és pénzt költenek a szolgáltatásokra. Idővel a felhasználók eltűnnek. Szeretnénk megtudni, hogy a felhasználók mennyi ideig élnek, és hányan válnak meg a szolgáltatás használata után az 1. és 2. hónapban.

E kérdések megválaszolásához csoportokat kell létrehoznunk a regisztráció hónapja szerint. Az aktivitást a havi költések alapján mérjük. A költések helyett lehetnek rendelések, előfizetési díjak vagy bármilyen más, időhöz kötődő aktivitás.

Forrásadatok

A példák MySQL-ben készültek, de a többi DBMS esetében lényeges eltérések nem várhatóak.

Felhasználói táblázat — users:

userId
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Kiadások táblázat — billing:

userId
Dátum
Összeg

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

Kiválasztjuk az összes felhasználótól érkező költést és a regisztráció dátumát:

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

Eredmény:

userId
Dátum
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

A csoportokat hónaponként építjük fel, ehhez az összes dátumot hónapokká alakítjuk:

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

Most meg kell tudnunk, hány hónapig volt aktív a felhasználó — ez a költési hónap és a regisztrálási hónap közötti különbség. A MySQL-ben van egy PERIOD_DIFF() függvény — két hónap közötti különbség. Hozzáadjuk a PERIOD_DIFF()-et a lekérdezéshez:

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

Számoljuk a hónaponként aktivált felhasználókat — csoportosítjuk a bejegyzéseket BillingMonth, RegistrationMonth és MonthsDiff szerint:

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

Eredmény:

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

Januárban, februárban és márciusban egy-egy új felhasználó jelent meg — MonthsDiff = 0. Egy januári felhasználó februárban is aktív volt — RegistrationMonth = 2019-01, BillingMonth = 2019-02, és egy februári felhasználó is aktív volt márciusban.

Nagy adathalmazon természetesen jobban látszanak a mintázatok.

Hogyan vigyük a csoportokat a Grafanába

Már megtanultuk a csoportok kialakítását, de amikor sok bejegyzés van, már nehéz őket elemezni. A bejegyzéseket exportálhatjuk Excelbe, és szép táblázatokat készíthetünk, de ez nem a mi módszerünk!

A csoportokat interaktív grafikonként is meg lehet jeleníteni a Grafanába.

Ehhez hozzáadunk még egy lekérdezést, hogy az adatokat a Grafana számára megfelelő formátumba alakítsuk.

SELECT
  DATE_ADD(CONCAT(s.RegistrationMonth, '-01'), INTERVAL s.MonthsDiff MONTH) AS time_sec,
  SUM(s.Users) AS value,
  s.RegistrationMonth AS metric
FROM (
  ## régi lekérdezés, amely a kohortákat adja vissza
  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

És exportáljuk az adatokat a Grafanába.

Példa grafikon a demóból:

Megérinteni:

GitHub-repo a példa kedvéért — ez egy docker-kép MySQL-lal és Grafanával, amelyet a saját számítógépén futtathat. Az adatbázisban már másfél év demóadat található, 2018 januárjától 2019 júliusáig.

Igény szerint saját adatait is feltöltheti erre a képre.

P.S. Cikkek a SQL alapú kohortanalízisról:

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

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

Forrás: habr.com