ProHoster > Блог > Захиргаа > Графана дахь график хэлбэрээр хэрэглэгчийн когортуудыг хэрхэн цуглуулах вэ [+ жишээ бүхий докерын зураг]

Графана дахь график хэлбэрээр хэрэглэгчийн когортуудыг хэрхэн цуглуулах вэ [+ жишээ бүхий докерын зураг]

Графана дахь график хэлбэрээр хэрэглэгчийн когортуудыг хэрхэн цуглуулах вэ [+ жишээ бүхий докерын зураг]

Бид Grafana ашиглан Promopult үйлчилгээний хэрэглэгчдийн бүлгийг дүрслэн харуулах асуудлыг хэрхэн шийдсэн бэ.

сурталчлах - олон тооны хэрэглэгчидтэй хүчирхэг үйлчилгээ. Үйл ажиллагаа явуулж байгаа 10 жилийн хугацаанд уг систем дэх бүртгэлийн тоо нэг сая давжээ. Ижил төрлийн үйлчилгээтэй тулгарсан хүмүүс энэ хэрэглэгчдийн масс нь нэг төрлийн биш гэдгийг мэддэг.

Хэн нэгэн бүртгүүлээд үүрд "унтсан". Хэн нэгэн нууц үгээ мартсан бөгөөд зургаан сарын хугацаанд хэд хэдэн удаа бүртгүүлсэн байна. Хэн нэгэн кассанд мөнгө авчирдаг, хэн нэгэн үнэгүй мөнгө авахаар ирсэн хэрэгсэл. Тэгээд хүн бүрээс жаахан ашиг хүртвэл сайхан байх болно.

Манайх шиг том өгөгдлийн багц дээр хувь хүний ​​зан төлөвт дүн шинжилгээ хийж, бичил шийдвэр гаргах нь утгагүй юм. Гэхдээ чиг хандлагыг олж авах, том бүлгүүдтэй ажиллах боломжтой бөгөөд шаардлагатай. Энэ нь бидний яг хийдэг зүйл юм.

Хураангуй

  1. Когорт шинжилгээ гэж юу вэ, яагаад хэрэгтэй вэ?
  2. SQL дээр хэрэглэгчийн бүртгэлийн сараар хэрхэн когорт хийх вэ.
  3. Когортыг хэрхэн шилжүүлэх вэ Графана.

Хэрэв та когорт шинжилгээ гэж юу болох, үүнийг SQL дээр хэрхэн хийхийг аль хэдийн мэддэг бол сүүлчийн хэсэг рүү шууд алгасах хэрэгтэй.

1. Когортын шинжилгээ гэж юу вэ, яагаад хэрэгтэй вэ?

Когортын шинжилгээ нь хэрэглэгчдийн янз бүрийн бүлгийг (когорт) харьцуулах арга юм. Ихэнхдээ манай бүлгүүдийг тухайн хэрэглэгч тухайн үйлчилгээг ашиглаж эхэлсэн долоо хоног, сараар бүрдүүлдэг. Эндээс хэрэглэгчийн амьдралын хугацааг тооцдог бөгөөд энэ нь нэлээд төвөгтэй дүн шинжилгээ хийх боломжтой үзүүлэлт юм. Жишээ нь, ойлгох:

  • худалдан авах суваг нь хэрэглэгчийн амьдралд хэрхэн нөлөөлдөг вэ;
  • аливаа онцлог, үйлчилгээг ашиглах нь амьдралын туршид хэрхэн нөлөөлдөг;
  • X функцийг эхлүүлсэн нь өнгөрсөн жилтэй харьцуулахад ашиглалтын хугацаанд хэрхэн нөлөөлсөн бэ.

2. SQL дээр хэрхэн когорт хийх вэ?

Нийтлэлийн хэмжээ, эрүүл саруул ухаан нь бидний бодит мэдээллийг энд танилцуулахыг зөвшөөрдөггүй - туршилтын овоолго, нэг жил хагасын статистик: 1200 хэрэглэгч, 53 гүйлгээ. Таныг энэ өгөгдөлтэй тоглохын тулд бид MySQL болон Grafana-ийн тусламжтайгаар та бүгдийг өөрөө мэдрэх боломжтой docker дүрсийг бэлтгэсэн. Өгүүллийн төгсгөлд GitHub руу холбоно уу.

Энд бид хялбаршуулсан жишээ ашиглан когорт үүсгэхийг харуулах болно.

Бидэнд үйлчилгээ байгаа гэж бодъё. Хэрэглэгчид тэнд бүртгүүлж, үйлчилгээнд мөнгө зарцуулдаг. Цаг хугацаа өнгөрөхөд хэрэглэгчид орхигддог. Хэрэглэгчид хэр удаан амьдардаг, тэдний хэд нь үйлчилгээг ашигласнаас хойш 1, 2 дахь сарын дараа унадаг болохыг олж мэдэхийг хүсч байна.

Эдгээр асуултад хариулахын тулд бид бүртгүүлсэн сар дээр үндэслэн бүлэг байгуулах хэрэгтэй. Бид үйл ажиллагааг сар бүр зардлаар хэмжинэ. Зардлын оронд захиалга, захиалгын төлбөр эсвэл бусад цаг хугацааны үйл ажиллагаа байж болно.

Эхний өгөгдөл

Жишээнүүдийг MySQL дээр хийсэн боловч бусад DBMS-ийн хувьд мэдэгдэхүйц ялгаа байх ёсгүй.

Хэрэглэгчийн хүснэгт - хэрэглэгчид:

хэрэглэгчийн ID
Бүртгэлийн огноо

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Зардлын хүснэгт - тооцоо:

хэрэглэгчийн ID
огноо
Сум

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

Хэрэглэгчийн бүх хасалт болон бүртгэлийн огноог сонгоно уу:

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

Үр дүн:

хэрэглэгчийн ID
огноо
Бүртгэлийн огноо

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

Бид сараар бүлэг байгуулдаг; үүний тулд бид бүх огноог сар болгон хөрвүүлдэг.

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

Одоо бид хэрэглэгч хэдэн сар идэвхтэй байсныг мэдэх хэрэгтэй - энэ нь дебит болон бүртгэлийн сарын хоорондох ялгаа юм. MySQL нь PERIOD_DIFF() функцтэй - хоёр сарын зөрүү. Хүсэлтэд PERIOD_DIFF() нэмэх:

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
Тооцооны сар
Бүртгэлийн огноо
Сарын зөрүү

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

Бид сар бүр идэвхжсэн хэрэглэгчдийг тоолдог - бид бүртгэлийг BillingMonth, RegistrationMonth болон 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

Үр дүн:

Хэрэглэгчийн тоо
Тооцооны сар
Бүртгэлийн сар
Сарын зөрүү

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

Нэг, хоёрдугаар сар, гуравдугаар сард тус бүр нэг шинэ хэрэглэгч гарч ирэв - MonthsDiff = 0. Нэгдүгээр сард нэг хэрэглэгч хоёрдугаар сард идэвхтэй байсан - RegistrationMonth = 2019-01, BillingMonth = 2019-02, хоёрдугаар сард нэг хэрэглэгч гуравдугаар сард идэвхтэй байсан.

Мэдээжийн хэрэг, том өгөгдлийн багцад хэв маяг илүү сайн харагддаг.

Когортуудыг Графана руу хэрхэн шилжүүлэх вэ

Бид бүлэглэлийг хэрхэн бүрдүүлэх талаар сурсан боловч олон тооны бичлэг байгаа үед тэдгээрийг шинжлэх нь амаргүй болсон. Бичлэгийг Excel-д экспортлож, үзэсгэлэнтэй хүснэгтүүдийг үүсгэж болох боловч энэ нь бидний арга биш юм!

Когортуудыг интерактив график хэлбэрээр харуулж болно Графана.

Үүнийг хийхийн тулд бид өгөгдлийг 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

Мөн өгөгдлийг Grafana руу байршуулна уу.

Жишээ графикаас демо:

Графана дахь график хэлбэрээр хэрэглэгчийн когортуудыг хэрхэн цуглуулах вэ [+ жишээ бүхий докерын зураг]

Гараараа хүр:

Жишээ бүхий GitHub агуулах Энэ бол MySQL болон Grafana программыг ашиглан компьютер дээрээ ажиллуулах боломжтой докерын зураг юм. Мэдээллийн сан нь 2018 оны 2019-р сараас XNUMX оны XNUMX-р сар хүртэл жил хагасын турш демо өгөгдлийг аль хэдийн агуулдаг.

Хэрэв та хүсвэл энэ зураг руу өгөгдлөө ачаалж болно.

SQL дэх когорт шинжилгээний талаархи PS нийтлэлүүд:

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

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

Эх сурвалж: www.habr.com

сэтгэгдэл нэмэх