Како прикупити корисничке кохорте као графиконе у Графани [+ доцкер слика са примером]

Како смо решили проблем визуелизације кохорти корисника у Промопулт сервису користећи Графану.

промовисати - моћан сервис са великим бројем корисника. За 10 година рада, број регистрација у систему је премашио милион. Они који су се сусрели са сличним услугама знају да је овај низ корисника далеко од хомогеног.

Неко се пријавио и „заспао“ заувек. Неко је заборавио лозинку и регистровао се још неколико пута током шест месеци. Неко доноси новац у касу, а неко је дошао по гратис инструменти. И било би лепо да се од свих добије неки профит.

На овако великим скуповима података као што је наш, анализа понашања појединачног корисника и доношење микро одлука је бесмислено. Али уочавање трендова и рад са великим групама је могуће и неопходно. Што је управо оно што ми радимо.

Сажетак

1. Шта је кохортна анализа и зашто је потребна?
2. Како направити кохорте по месецима регистрације корисника у СКЛ-у.
3. Како пренети кохорте на Графана.

Ако већ знате шта је кохортна анализа и како то да урадите у СКЛ-у, пређите право на последњи одељак.

1. Шта је кохортна анализа и зашто је потребна?

Кохортна анализа је метода заснована на поређењу различитих група (кохорти) корисника. Најчешће се наше групе формирају по седмици или месецу у којем је корисник почео да користи услугу. Одавде се израчунава животни век корисника, а то је показатељ на основу којег се може извршити прилично сложена анализа. На пример, схватите:

• како канал аквизиције утиче на животни век корисника;
• како употреба било које функције или услуге утиче на животни век;
• Како је лансирање функције Кс утицало на животни век у поређењу са прошлом годином.

2. Како направити кохорте у СКЛ-у?

Величина чланка и здрав разум нам не дозвољавају да овде изнесемо наше стварне податке – у тестном депонију, статистика за годину и по дана: 1200 корисника и 53 трансакција. Да бисте могли да се играте са овим подацима, припремили смо доцкер слику са МиСКЛ и Графаном у којој све то можете и сами да доживите. Веза до ГитХуб-а на крају чланка.

А овде ћемо показати стварање кохорти на поједностављеном примеру.

Претпоставимо да имамо услугу. Корисници се тамо региструју и троше новац на услуге. Временом корисници одустају. Желимо да сазнамо колико дуго корисници живе, а колико их опада након 1. и 2. месеца коришћења услуге.

Да бисмо одговорили на ова питања, морамо да конструишемо кохорте на основу месеца регистрације. Мерићемо активност по трошковима сваког месеца. Уместо трошкова, могу бити наруџбине, претплате или било која друга активност заснована на времену.

Необрађени подаци

Примери су направљени у МиСКЛ-у, али за друге ДБМС не би требало да постоје значајне разлике.

Табела корисника - корисници:

ИД корисник
Датум регистрације

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')

Сада треба да знамо колико месеци је корисник био активан - ово је разлика између месеца задужења и месеца регистрације. МиСКЛ има функцију ПЕРИОД_ДИФФ() - разлика између два месеца. Додајте ПЕРИОД_ДИФФ() захтеву:

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

4
2019-03
2019-03
0

Бројимо кориснике активиране у сваком месецу - групишемо записе према БиллингМонтх, РегистратионМонтх и МонтхсДифф:

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

У јануару, фебруару и марту појавио се по један нови корисник - МонтхсДифф = 0. Један јануарски корисник је био активан у фебруару - РегистратионМонтх = 2019-01, БиллингМонтх = 2019-02, а један фебруарски корисник је био активан у марту.

Наравно, обрасци су боље видљиви у великом скупу података.

Како пренети кохорте у Графану

Научили смо како да формирамо кохорте, али када има много записа, више их није лако анализирати. Записи се могу извести у Екцел и формирати у прелепе табеле, али то није наш метод!

Кохорте се могу приказати као интерактивни графикон у Графана.

Да бисмо то урадили, додајемо још један упит за претварање података у формат погодан за Графану:

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

И отпремите податке у Графану.

Пример графикона из демо:

Додирните рукама:

ГитХуб спремиште са примером је доцкер слика са МиСКЛ и Графаном коју можете покренути на свом рачунару. База података већ садржи демо податке за годину и по дана, од јануара 2018. до јула 2019. године.

Ако желите, можете учитати своје податке у ову слику.

ПС Чланци о кохортној анализи у СКЛ-у:

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

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

Извор: ввв.хабр.цом