🥇Чӣ тавр ҷамъ кардани гурӯҳи корбарон дар шакли графикҳо дар Grafana [+тасвири докер бо мисол]

Чӣ гуна мо мушкилоти визуалии гурӯҳҳои корбаронро дар хидмати Promopult бо истифода аз Grafana ҳал кардем.

пешбарй кунанд - хидмати пурқувват бо шумораи зиёди корбарон. Дар тӯли 10 соли фаъолият шумораи сабти номҳо дар система аз як миллион нафар гузашт. Онҳое, ки бо хидматҳои шабеҳ дучор шудаанд, медонанд, ки ин маҷмӯи корбарон аз якхела дур нест.

Касе ба қайд гирифта, то абад «хоб шуд». Касе гузарвожаи худро фаромӯш кард ва дар тӯли шаш моҳ боз чанд маротиба сабти ном кард. Касе ба касса пул меораду касе барои муфт омад асбобҳо. Ва хуб мебуд, ки аз ҳама фоида ба даст оранд.

Дар чунин маҷмӯаҳои бузурги додаҳо, ба мисли мо, таҳлили рафтори корбари инфиродӣ ва қабули қарорҳои хурд бефоида аст. Аммо пайгирии тамоюлҳо ва кор бо гурӯҳҳои калон имконпазир ва зарур аст. Ки маҳз ҳамон чизест, ки мо мекунем.

Натиҷа

Таҳлили когорт чист ва чаро он лозим аст?
Чӣ тавр гурӯҳҳоро аз рӯи моҳи бақайдгирии корбар дар SQL сохтан мумкин аст.
Чӣ тавр интиқол додани гурӯҳҳо ба Графана.

Агар шумо аллакай донед, ки таҳлили когорт чист ва чӣ тавр онро дар SQL иҷро кунед, рост ба қисми охирин гузаред.

1. Таҳлили когорт чист ва чаро он зарур аст?

Таҳлили когорт усулест, ки ба муқоисаи гурӯҳҳои (гурӯҳҳои) гуногуни корбарон асос ёфтааст. Аксар вақт, гурӯҳҳои мо аз рӯи ҳафта ё моҳе ташкил карда мешаванд, ки дар он корбар истифода аз хидматро оғоз кардааст. Аз ин ҷо мӯҳлати корбар ҳисоб карда мешавад ва ин нишондиҳандаест, ки дар асоси он таҳлили хеле мураккабро анҷом додан мумкин аст. Масалан, бифаҳмед:

канали харидорӣ ба ҳаёти корбар чӣ гуна таъсир мерасонад;
чӣ гуна истифодаи ягон хусусият ё хидмат ба умри худ таъсир мерасонад;
Чӣ тавр ба кор андохтани хусусияти X ба умр дар муқоиса бо соли гузашта таъсир расонд.

2. Чӣ тавр когортҳоро дар SQL сохтан мумкин аст?

Андозаи мақола ва ақли солим ба мо имкон намедиҳад, ки маълумоти воқеии худро дар ин ҷо пешниҳод кунем - дар партовгоҳи санҷишӣ, омори якуним сол: 1200 корбар ва 53 транзаксия. Барои он ки шумо метавонед бо ин маълумот бозӣ кунед, мо як тасвири докерро бо MySQL ва Grafana омода кардем, ки дар он шумо ҳама чизро худатон эҳсос карда метавонед. Истинод ба GitHub дар охири мақола.

Ва дар ин ҷо мо бо истифода аз мисоли соддакардашуда эҷоди гурӯҳҳоро нишон медиҳем.

Фарз мекунем, ки мо хидмат дорем. Истифодабарандагон дар он ҷо сабти ном мешаванд ва барои хидматҳо пул сарф мекунанд. Бо гузашти вақт, корбарон тарк мекунанд. Мо мехоҳем бифаҳмем, ки корбарон чӣ қадар умр доранд ва чанд нафари онҳо пас аз моҳи 1 ва 2-юми истифода аз хидмат меафтанд.

Барои ҷавоб додан ба ин саволҳо, мо бояд гурӯҳҳоро дар асоси моҳи бақайдгирӣ созем. Мо фаъолиятро аз рӯи хароҷот дар ҳар моҳ чен мекунем. Ба ҷои хароҷот, метавонад фармоишҳо, пардохти абонентӣ ё ягон фаъолияти дигар дар асоси вақт бошад.

Маълумоти ибтидоӣ

Намунаҳо дар MySQL сохта шудаанд, аммо барои дигар DBMS набояд фарқияти назаррас вуҷуд дошта бошад.

Ҷадвали корбар - корбарон:

Шаҳодатномаи Корбар
Санаи бақайдгирӣ

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Ҷадвали хароҷот - ҳисобдорӣ:

Шаҳодатномаи Корбар
таърих
Ҳамагӣ

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

Натиҷа:

Шаҳодатномаи Корбар
таърих
Санаи бақайдгирӣ

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

Шаҳодатномаи Корбар
БиллингМоҳи
Санаи бақайдгирӣ
Моҳҳо Тафовут

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

Мо корбарони дар ҳар моҳ фаъолшударо ҳисоб мекунем - мо сабтҳоро аз рӯи 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

Натиҷа:

UsersCount
БиллингМоҳи
Моҳи бақайдгирӣ
Моҳҳо Тафовут

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 ва як корбари феврал дар моҳи март фаъол буд.

Табиист, ки намунаҳо дар маҷмӯи додаҳои калон беҳтар намоёнанд.

Чӣ тавр когортҳоро ба Grafana интиқол додан мумкин аст

Мо тарзи ташкили когортҳоро ёд гирифтем, аммо вақте ки сабтҳо зиёданд, таҳлили онҳо дигар осон нест. Сабтҳоро метавон ба 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 мавҷуд аст.

Агар хоҳед, шумо метавонед маълумоти худро ба ин тасвир бор кунед.

PS Мақолаҳо дар бораи таҳлили когорт дар SQL:

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

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

Манбаъ: will.com

Чӣ тавр ҷамъ кардани когортҳои корбарон ҳамчун графикҳо дар Grafana [+ тасвири докер бо мисол]

Натиҷа

1. Таҳлили когорт чист ва чаро он зарур аст?

2. Чӣ тавр когортҳоро дар SQL сохтан мумкин аст?

Маълумоти ибтидоӣ

Чӣ тавр когортҳоро ба Grafana интиқол додан мумкин аст

Илова Эзоҳ Cancel ҷавоб