ProHoster > Blog > Ma'muriyat > Grafana-da foydalanuvchi kogortalarini grafik sifatida qanday yig'ish mumkin [+ misol bilan docker tasviri]

Grafana-da foydalanuvchi kogortalarini grafik sifatida qanday yig'ish mumkin [+ misol bilan docker tasviri]

Grafana-da foydalanuvchi kogortalarini grafik sifatida qanday yig'ish mumkin [+ misol bilan docker tasviri]

Grafana yordamida Promopult xizmatida foydalanuvchilar kogortalarini vizualizatsiya qilish muammosini qanday hal qildik.

Promopult - ko'p sonli foydalanuvchilar bilan kuchli xizmat. 10 yillik faoliyat davomida tizimda roΚ»yxatga olinganlar soni bir milliondan oshdi. Shunga o'xshash xizmatlarga duch kelganlar, bu foydalanuvchilar qatori bir hil emasligini bilishadi.

Kimdir ro'yxatdan o'tdi va abadiy "uxlab qoldi". Kimdir parolini unutdi va olti oy davomida yana bir necha marta ro'yxatdan o'tdi. Kimdir kassaga pul olib keladi, kimdir tekinga kelgan asboblar. Va hammadan bir oz foyda olish yaxshi bo'lardi.

Bizniki kabi katta ma'lumotlar to'plamlarida individual foydalanuvchining xatti-harakatlarini tahlil qilish va mikro-qarorlar qabul qilish befoyda. Ammo tendentsiyalarni ushlash va katta guruhlar bilan ishlash mumkin va zarur. Biz aynan nima qilamiz.

Xulosa

  1. Kohort tahlili nima va u nima uchun kerak?
  2. SQL-da foydalanuvchi ro'yxatdan o'tgan oy bo'yicha kogortalarni qanday qilish kerak.
  3. Kogortalarni qanday o'tkazish kerak grafana.

Agar siz kohort tahlili nima ekanligini va uni SQLda qanday qilishni allaqachon bilsangiz, to'g'ridan-to'g'ri oxirgi bo'limga o'ting.

1. Kogorta tahlili nima va u nima uchun kerak?

Kohort tahlili - foydalanuvchilarning turli guruhlarini (kogortalarini) solishtirishga asoslangan usul. Ko'pincha bizning guruhlarimiz foydalanuvchi xizmatdan foydalanishni boshlagan hafta yoki oy bo'yicha tuziladi. Bu erdan foydalanuvchining ishlash muddati hisoblab chiqiladi va bu juda murakkab tahlilni amalga oshirish mumkin bo'lgan ko'rsatkichdir. Masalan, tushuning:

  • sotib olish kanali foydalanuvchining umriga qanday ta'sir qiladi;
  • har qanday xususiyat yoki xizmatdan foydalanish hayotga qanday ta'sir qiladi;
  • X xususiyatining ishga tushirilishi o'tgan yilga nisbatan hayotga qanday ta'sir qildi.

2. SQL da kohortlar qanday qilinadi?

Maqola hajmi va aql-idrok bizning haqiqiy ma'lumotlarimizni bu erda taqdim etishga imkon bermaydi - test dumpida, bir yarim yil statistikasi: 1200 foydalanuvchi va 53 000 tranzaksiya. Ushbu ma'lumotlar bilan o'ynashingiz uchun biz MySQL va Grafana bilan docker tasvirini tayyorladik, unda siz buni o'zingiz boshdan kechirishingiz mumkin. Maqolaning oxirida GitHub-ga havola.

Va bu erda biz soddalashtirilgan misol yordamida kogortalarni yaratishni ko'rsatamiz.

Faraz qilaylik, bizda xizmat bor. Foydalanuvchilar u erda ro'yxatdan o'tadi va xizmatlarga pul sarflashadi. Vaqt o'tishi bilan foydalanuvchilar undan voz kechishadi. Biz foydalanuvchilar qancha umr ko'rishini va ulardan qanchasi xizmatdan foydalanishning 1 va 2 oylaridan keyin yiqilib tushishini bilmoqchimiz.

Ushbu savollarga javob berish uchun biz ro'yxatdan o'tgan oyga asoslangan kogortalarni tuzishimiz kerak. Biz har oyda faoliyatni xarajatlar bo'yicha o'lchaymiz. Xarajatlar o'rniga buyurtmalar, obuna to'lovlari yoki boshqa vaqtga asoslangan faoliyat bo'lishi mumkin.

Xom ma'lumotlar

Misollar MySQL-da yaratilgan, ammo boshqa DBMSlar uchun sezilarli farqlar bo'lmasligi kerak.

Foydalanuvchilar jadvali - foydalanuvchilar:

foydalanuvchi IDsi
Ro'yxatdan o'tish sanasi

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Xarajatlar jadvali - hisob-kitob:

foydalanuvchi IDsi
sana
Summa

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

Barcha foydalanuvchi hisobdan chiqarish va ro'yxatdan o'tish sanasini tanlang:

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

natija:

foydalanuvchi IDsi
sana
Ro'yxatdan o'tish sanasi

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

Biz kogortalarni oylar bo'yicha quramiz; Buning uchun biz barcha sanalarni oylarga aylantiramiz:

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

Endi biz foydalanuvchi necha oy faol bo'lganini bilishimiz kerak - bu debet oyi va ro'yxatdan o'tgan oy o'rtasidagi farq. MySQL-da PERIOD_DIFF() funksiyasi mavjud - bu ikki oy orasidagi farq. So'rovga PERIOD_DIFF() qo'shing:

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

foydalanuvchi IDsi
BillingMonth
Ro'yxatdan o'tish sanasi
Oylar Farqi

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

Biz har oyda faollashtirilgan foydalanuvchilarni hisoblaymiz - biz yozuvlarni BillingMonth, RegistrationMonth va MonthsDiff bo'yicha guruhlaymiz:

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

natija:

Foydalanuvchilar soni
BillingMonth
Ro'yxatdan o'tish oyi
Oylar Farqi

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

Yanvar, fevral va mart oylarida har birida bittadan yangi foydalanuvchi paydo bo'ldi - MonthsDiff = 0. Bir yanvar foydalanuvchisi fevralda faol bo'lgan - RegistrationMonth = 2019-01, BillingMonth = 2019-02 va fevral oyida bir foydalanuvchi mart oyida faol bo'lgan.

Tabiiyki, naqshlar katta ma'lumotlar to'plamida yaxshiroq ko'rinadi.

Kogortalarni Grafana-ga qanday o'tkazish kerak

Biz kogortalarni qanday shakllantirishni o'rgandik, ammo yozuvlar ko'p bo'lsa, ularni tahlil qilish endi oson emas. Yozuvlarni Excelga eksport qilish va chiroyli jadvallarga aylantirish mumkin, ammo bu bizning usulimiz emas!

Kohortlar interaktiv grafik sifatida ko'rsatilishi mumkin grafana.

Buning uchun biz ma'lumotlarni Grafana uchun mos formatga aylantirish uchun yana bir so'rovni qo'shamiz:

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

Va ma'lumotlarni Grafana-ga yuklang.

dan misol grafik demo:

Grafana-da foydalanuvchi kogortalarini grafik sifatida qanday yig'ish mumkin [+ misol bilan docker tasviri]

Qo'llaringiz bilan teginish:

Misol bilan GitHub ombori MySQL va Grafana bilan docker tasviri bo'lib, uni kompyuteringizda ishlatishingiz mumkin. Ma'lumotlar bazasi allaqachon 2018 yil yanvaridan 2019 yil iyuligacha bir yarim yil davomida demo ma'lumotlarini o'z ichiga oladi.

Agar xohlasangiz, ma'lumotlaringizni ushbu rasmga yuklashingiz mumkin.

PS SQL-da kohort tahlili haqida maqolalar:

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

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

Manba: www.habr.com

a Izoh qo'shish