ProHoster > Блог > басқарма > Графанадағы графиктер ретінде пайдаланушы когорталарын қалай жинауға болады [+ мысалмен докер суреті]

Графанадағы графиктер ретінде пайдаланушы когорталарын қалай жинауға болады [+ мысалмен докер суреті]

Графанадағы графиктер ретінде пайдаланушы когорталарын қалай жинауға болады [+ мысалмен докер суреті]

Біз Grafana көмегімен Promopult қызметіндегі пайдаланушылар когорталарын визуализациялау мәселесін қалай шештік.

Промопулт - пайдаланушылардың көп саны бар қуатты қызмет. Жұмыс істеген 10 жыл ішінде жүйеде тіркелгендер саны миллионнан асты. Ұқсас қызметтерге тап болғандар пайдаланушылардың бұл массивінің біртекті емес екенін біледі.

Біреу тіркеліп, мәңгілікке «ұйықтап қалды». Біреу өзінің құпия сөзін ұмытып, алты ай ішінде тағы бірнеше рет тіркелді. Біреу кассаға ақша әкеледі, ал біреу тегін алуға келді аспаптар. Және әркімнен біраз пайда тапса жақсы болар еді.

Біздікі сияқты үлкен деректер жиынтығында жеке пайдаланушының мінез-құлқын талдау және микро шешімдер қабылдау мағынасыз. Бірақ трендтерді ұстану және үлкен топтармен жұмыс істеу мүмкін және қажет. Бұл дәл біз жасайтын нәрсе.

Қысқаша мазмұны

  1. Когортты талдау дегеніміз не және ол не үшін қажет?
  2. SQL жүйесінде пайдаланушыны тіркеу айы бойынша когорттарды қалай жасауға болады.
  3. Когорталарды қалай тасымалдауға болады Графана.

Егер сіз когортты талдаудың не екенін және оны SQL тілінде қалай жасау керектігін білсеңіз, тікелей соңғы бөлімге өтіңіз.

1. Когорттық талдау дегеніміз не және ол не үшін қажет?

Когортты талдау – пайдаланушылардың әртүрлі топтарын (когорталарын) салыстыруға негізделген әдіс. Көбінесе біздің топтар пайдаланушы қызметті пайдалана бастаған апта немесе ай бойынша құрылады. Осы жерден пайдаланушының қызмет ету мерзімі есептеледі және бұл өте күрделі талдауды жүргізуге болатын көрсеткіш. Мысалы, түсініңіз:

  • сатып алу арнасы пайдаланушының өмір сүру ұзақтығына қалай әсер етеді;
  • кез келген мүмкіндікті немесе қызметті пайдалану өмір сүру ұзақтығына қалай әсер етеді;
  • Өткен жылмен салыстырғанда X мүмкіндігін іске қосу өмір сүру ұзақтығына қалай әсер етті.

2. SQL тілінде когорттар қалай жасалады?

Мақаланың көлемі мен парасаттылық біздің нақты деректерімізді мұнда - сынақ қоқысында, бір жарым жылдағы статистикада көрсетуге мүмкіндік бермейді: 1200 пайдаланушы және 53 000 транзакция. Осы деректермен ойнау үшін біз MySQL және Grafana көмегімен докер кескінін дайындадық, онда сіз мұның бәрін өзіңіз көре аласыз. Мақаланың соңында GitHub сілтемесі.

Мұнда біз жеңілдетілген мысал арқылы когорталарды құруды көрсетеміз.

Бізде қызмет бар делік. Пайдаланушылар сонда тіркеліп, қызметтерге ақша жұмсайды. Уақыт өте келе пайдаланушылар шығып қалады. Біз пайдаланушылардың қанша өмір сүретінін және олардың қаншасы қызметті пайдаланудың 1-ші және 2-ші айынан кейін жоғалатынын білгіміз келеді.

Бұл сұрақтарға жауап беру үшін біз тіркелген айға негізделген когорталарды құруымыз керек. Біз әр айдағы белсенділікті шығындар бойынша өлшейтін боламыз. Шығындардың орнына тапсырыстар, жазылу төлемдері немесе басқа уақытқа негізделген әрекет болуы мүмкін.

Бастапқы деректер

Мысалдар MySQL-де жасалған, бірақ басқа ДҚБЖ үшін айтарлықтай айырмашылықтар болмауы керек.

Пайдаланушылар кестесі - пайдаланушылар:

Қолданушының 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

нәтижесі:

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 және ақпан айында бір пайдаланушы наурызда белсенді болды.

Әрине, үлгілер үлкен деректер жинағында жақсырақ көрінеді.

Когорттарды Графанаға қалай тасымалдауға болады

Біз когорталарды құруды үйрендік, бірақ жазбалар көп болған кезде, оларды талдау енді оңай болмайды. Жазбаларды 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 жылдың шілдесіне дейін бір жарым жыл ішінде демонстрациялық деректер бар.

Қаласаңыз, деректеріңізді осы суретке жүктей аласыз.

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

пікір қалдыру