ProHoster > Blog > Adminisztráció > Felhasználói csoportok grafikonként történő gyűjtése a Grafana alkalmazásban [+ docker kép példával]

Felhasználói csoportok grafikonként történő gyűjtése a Grafana alkalmazásban [+ docker kép példával]

Felhasználói csoportok grafikonként történő gyűjtése a Grafana alkalmazásban [+ docker kép példával]

Hogyan oldottuk meg a felhasználói csoportok megjelenítésének problémáját a Promopult szolgáltatásban a Grafana segítségével.

népszerűsít - hatékony szolgáltatás nagyszámú felhasználóval. A 10 éves működés alatt a regisztrációk száma a rendszerben meghaladta az egymilliót. Azok, akik találkoztak hasonló szolgáltatásokkal, tudják, hogy ez a felhasználói kör korántsem homogén.

Valaki jelentkezett és örökre „elaludt”. Valaki elfelejtette a jelszavát, és hat hónap alatt még néhányszor regisztrált. Valaki pénzt hoz a pénztárba, valaki pedig ingyen jött eszközök. És jó lenne mindenkitől némi haszonra szert tenni.

Ilyen nagy adathalmazokon, mint amilyen a miénk, értelmetlen az egyes felhasználók viselkedésének elemzése és mikrodöntések meghozatala. De a trendek megragadása és a nagy csoportokkal való munka lehetséges és szükséges. Pontosan ezt csináljuk.

összefoglalás

  1. Mi az a kohorszelemzés, és miért van rá szükség?
  2. Hogyan készítsünk kohorszokat a felhasználói regisztráció hónapja szerint az SQL-ben.
  3. A kohorszok átvitele ide grafana.

Ha már tudja, mi az a kohorszelemzés, és hogyan kell ezt megtenni SQL-ben, ugorjon közvetlenül az utolsó részre.

1. Mi az a kohorszelemzés, és miért van rá szükség?

A kohorszelemzés a felhasználók különböző csoportjainak (kohorszainak) összehasonlításán alapuló módszer. Csoportjaink leggyakrabban azon hét vagy hónap alapján jönnek létre, amikor a felhasználó elkezdte használni a szolgáltatást. Innen számítják ki a felhasználó élettartamát, és ez egy olyan mutató, amely alapján meglehetősen összetett elemzés végezhető el. Például értsd meg:

  • hogyan befolyásolja az akvizíciós csatorna a felhasználó élettartamát;
  • hogyan befolyásolja bármely funkció vagy szolgáltatás használata az élettartamot;
  • Hogyan befolyásolta az X funkció elindítása az élettartamot a tavalyi évhez képest.

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

A cikk mérete és a józan ész nem teszi lehetővé, hogy valós adatainkat itt közöljük - a teszt dumpban, másfél év statisztikája: 1200 felhasználó és 53 ezer tranzakció. Hogy játszhasson ezekkel az adatokkal, elkészítettünk egy docker image-et MySQL-lel és Grafana-val, amelyben mindezt Ön is megtapasztalhatja. Link a GitHubhoz a cikk végén.

És itt bemutatjuk a kohorszok létrehozását egy egyszerűsített példán keresztül.

Tegyük fel, hogy van szolgáltatásunk. A felhasználók ott regisztrálnak, és pénzt költenek a szolgáltatásokra. Idővel a felhasználók kiesnek. Szeretnénk utánajárni, hogy a felhasználók mennyi ideig élnek, és hányan esnek ki a szolgáltatás használatának 1. és 2. hónapja után.

E kérdések megválaszolásához kohorszokat kell felépíteni a regisztráció hónapja alapján. Az aktivitást minden hónapban a kiadásokkal mérjük. A költségek helyett megrendelések, előfizetési díjak, vagy bármilyen más időalapú tevékenység jöhet szóba.

Nyers adatok

A példák MySQL-ben készültek, de más DBMS-ek esetében nem lehetnek jelentős eltérések.

Felhasználói táblázat – felhasználók:

Felhasználói azonosító
Regisztráció dátuma

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Költségtábla - számlázás:

Felhasználói azonosító
találka
Ö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

Válassza ki az összes felhasználói leírá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:

Felhasználói azonosító
találka
Regisztráció dátuma

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 kohorszokat hónapok szerint építjük fel; ehhez az összes dátumot hónapra konvertáljuk:

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

Most tudnunk kell, hány hónapig volt aktív a felhasználó - ez a különbség a terhelés és a regisztráció hónapja között. A MySQL-nek van egy PERIOD_DIFF() függvénye – ez a különbség két hónap között. PERIOD_DIFF() hozzáadása a kéré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

Felhasználói azonosító
Számlázási hónap
Regisztráció dátuma
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

Minden hónapban számoljuk az aktivált felhasználókat - a rekordokat BillingMonth, RegistrationMonth és MonthsDiff szerint csoportosítjuk:

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
Számlázási hónap
Regisztráció hónapja
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ó volt aktív februárban - Regisztrációs hónap = 2019-01, BillingMonth = 2019-02, és egy februári felhasználó volt aktív márciusban.

Természetesen a minták jobban láthatóak egy nagy adathalmazban.

A kohorszok átvitele a Grafanába

Megtanultuk, hogyan kell kohorszokat alkotni, de ha sok a rekord, már nem könnyű elemezni őket. A rekordokat Excelbe exportálhatjuk és gyönyörű táblázatokká formázhatjuk, de ez nem a mi módszerünk!

A kohorszok interaktív grafikonként jeleníthetők meg grafana.

Ehhez hozzáadunk egy másik lekérdezést, hogy az adatokat a Grafana számára megfelelő formátumba konvertáljuk:

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

És töltse fel az adatokat a Grafanába.

Példa grafikon innen demó:

Felhasználói csoportok grafikonként történő gyűjtése a Grafana alkalmazásban [+ docker kép példával]

Érintse meg a kezét:

GitHub adattár példával egy MySQL-t és Grafana-t tartalmazó docker-képfájl, amelyet a számítógépén futtathat. Az adatbázis már másfél év demó adatait tartalmazza, 2018 januárjától 2019 júliusáig.

Ha szeretné, adatait betöltheti ebbe a képbe.

PS cikkek a kohorszelemzésről SQL-ben:

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

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

Forrás: will.com

Hozzászólás