ProHoster > блог > адміністраванне > Як сабраць кагорты карыстальнікаў у выглядзе графікаў у Grafana [+docker-вобраз з прыкладам]

Як сабраць кагорты карыстальнікаў у выглядзе графікаў у Grafana [+docker-вобраз з прыкладам]

Як сабраць кагорты карыстальнікаў у выглядзе графікаў у Grafana [+docker-вобраз з прыкладам]

Як мы вырашылі задачу візуалізацыі кагорты карыстальнікаў у сэрвісе Promopult з дапамогай Grafana.

Promopult - Магутны сэрвіс з вялікай колькасцю карыстальнікаў. За 10 гадоў працы колькасць рэгістрацый у сістэме пераваліла за мільён. Тыя, хто сутыкаўся з падобнымі сэрвісамі, ведаюць, што гэты масіў юзэраў далёка не аднастайны.

Нехта зарэгаўся і «заснуў» навекі. Хтосьці забыўся пароль і зарэгаўся яшчэ пару разоў за паўгода. Хтосьці нясе грошы ў касу, а хтосьці прыйшоў па халяўныя інструментамі. І добра было б з кожнага атрымаць нейкі профіт.

На такіх буйных масівах дадзеных, як у нас, аналізаваць паводзіны асобнага карыстача і прымаць мікра-рашэнні бессэнсоўна. А вось адлоўліваць трэнды і працаваць з вялікімі групамі - можна і трэба. Што мы, уласна, і які робіцца.

Кароткі змест

  1. Што такое кагортны аналіз і навошта ён патрэбен.
  2. Як зрабіць кагорты па месяцы рэгістрацыі карыстальнікаў на SQL.
  3. Як перанесці кагорты ў Графана.

Калі вы ўжо ведаеце, што такое кагортны аналіз, і як яго зрабіць на SQL, адразу пераходзіце да апошняй часткі.

1. Што такое кагортны аналіз і навошта ён патрэбен

Кагортны аналіз - гэта метад, заснаваны на параўнанні розных груп (кагорта) карыстальнікаў. Часцей за ўсё ў нас групы фармуюцца па тыдні ці месяцу, у якім карыстач пачаў выкарыстоўваць сэрвіс. Адгэтуль вылічаецца час жыцця карыстача, а гэта ўжо паказчык, на аснове якога можна праводзіць даволі складаны аналіз. Напрыклад, зразумець:

  • як уплывае канал прыцягнення на час жыцця карыстальніка;
  • як выкарыстанне якой-небудзь функцыі ці паслугі ўплывае на час жыцця;
  • як запуск фічы X паўплываў на час жыцця ў параўнанні з мінулым годам.

2. Як зрабіць кагорты на SQL?

Памер артыкула і здаровы сэнс не дазваляюць прыводзіць тут нашы рэальныя дадзеныя - у тэставым дампе статыстыка за паўтара года: 1200 карыстальнікаў і 53 транзакцый. Каб вы маглі пагуляць з гэтымі дадзенымі, мы падрыхтавалі docker-выява з MySQL і Grafana, у якім можна памацаць усё гэта самому. Спасылка на GitHub у канцы артыкула.

А тут мы пакажам стварэнне кагорты на спрошчаным прыкладзе.

Дапусцім, што ў нас ёсць сэрвіс. У ім рэгіструюцца карыстачы і марнуюць грошы на паслугі. З часам карыстачы адвальваюцца. Мы жадаем пазнаць, як доўга жывуць карыстачы, і колькі з іх адвальваецца пасля 1-го і 2-го месяца выкарыстання сэрвісу.

Для адказу на гэтыя пытанні нам трэба пабудаваць кагорты па месяцы рэгістрацыі. Актыўнасць будзем мераць па выдатках у кожным месяцы. Замест выдаткаў могуць быць замовы, абаненцкая плата ці любая іншая актыўнасць, прывязаная да часу.

Зыходныя дадзеныя

Прыклады зроблены ў MySQL, але для астатніх СКБД істотных адрозненняў быць не павінна.

Табліца карыстальнікаў - users:

Ідэнтыфікатар карыстальніка
RegistrationDate

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Табліца выдаткаў - billing:

Ідэнтыфікатар карыстальніка
дата
Сума

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

Вынік:

Ідэнтыфікатар карыстальніка
дата
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

Кагорты будуем па месяцах, для гэтага пераўтворым усе даты ў месяцы:

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

Ідэнтыфікатар карыстальніка
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

Лічым актываваных у кожным месяцы карыстальнікаў - групуем запісы па 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
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, гэтак жа і адзін карыстач лютага быў актыўны ў сакавіку.

На вялікім масіве дадзеных заканамернасці, вядома, бачныя лепш.

Як перанесці кагорты ў 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.

Прыклад графіка з дэма:

Як сабраць кагорты карыстальнікаў у выглядзе графікаў у Grafana [+docker-вобраз з прыкладам]

Пакратаць рукамі:

GitHub-рэпазітар з прыкладам - гэта docker-выява з 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/

Крыніца: habr.com

Дадаць каментар