Paano mangolekta ng mga cohorts ng user bilang mga graph sa Grafana [+ docker image na may halimbawa]

Paano namin nalutas ang problema sa pagpapakita ng mga cohort ng mga user sa serbisyo ng Promopult gamit ang Grafana.

isulong - isang malakas na serbisyo na may malaking bilang ng mga gumagamit. Sa loob ng 10 taon ng operasyon, ang bilang ng mga pagpaparehistro sa system ay lumampas sa isang milyon. Alam ng mga nakaranas ng mga katulad na serbisyo na ang hanay ng mga user na ito ay malayo sa homogenous.

May nag-sign up at "nakatulog" nang tuluyan. May nakalimutan ang kanilang password at nagparehistro ng ilang beses pa sa loob ng anim na buwan. May nagdadala ng pera sa cash register, at may dumating para sa mga freebies mga instrumento. At ito ay magiging maganda upang makakuha ng ilang kita mula sa lahat.

Sa malalaking set ng data tulad ng sa amin, ang pagsusuri sa gawi ng isang indibidwal na user at paggawa ng mga micro-decision ay walang kabuluhan. Ngunit ang pagkuha ng mga uso at pakikipagtulungan sa malalaking grupo ay posible at kinakailangan. Na kung ano mismo ang ginagawa namin.

Buod

1. Ano ang pagsusuri ng cohort at bakit ito kailangan?
2. Paano gumawa ng mga cohort ayon sa buwan ng pagpaparehistro ng user sa SQL.
3. Paano maglipat ng mga cohort sa grafana.

Kung alam mo na kung ano ang pagsusuri ng cohort at kung paano ito gagawin sa SQL, dumiretso sa huling seksyon.

1. Ano ang pagsusuri ng cohort at bakit ito kailangan?

Ang pagsusuri ng cohort ay isang paraan batay sa paghahambing ng iba't ibang pangkat (cohorts) ng mga user. Kadalasan, ang aming mga grupo ay nabuo sa pamamagitan ng linggo o buwan kung saan nagsimulang gamitin ng user ang serbisyo. Mula dito ang buhay ng gumagamit ay kinakalkula, at ito ay isang tagapagpahiwatig sa batayan kung saan ang isang medyo kumplikadong pagsusuri ay maaaring isagawa. Halimbawa, unawain:

• paano nakakaapekto ang acquisition channel sa buhay ng user;
• kung paano nakakaapekto ang paggamit ng anumang feature o serbisyo sa buhay;
• Paano naapektuhan ng paglulunsad ng feature X ang buhay kumpara noong nakaraang taon.

2. Paano gumawa ng mga cohorts sa SQL?

Ang laki ng artikulo at sentido komun ay hindi nagpapahintulot sa amin na ipakita ang aming totoong data dito - sa test dump, mga istatistika sa loob ng isang taon at kalahati: 1200 user at 53 na transaksyon. Upang maglaro ka sa data na ito, naghanda kami ng isang docker na imahe na may MySQL at Grafana kung saan maaari mong maranasan ang lahat ng ito nang mag-isa. Mag-link sa GitHub sa dulo ng artikulo.

At dito ipapakita namin ang paglikha ng mga cohorts gamit ang isang pinasimple na halimbawa.

Ipagpalagay natin na mayroon tayong serbisyo. Ang mga gumagamit ay nagparehistro doon at gumagastos ng pera sa mga serbisyo. Sa paglipas ng panahon, huminto ang mga user. Gusto naming malaman kung gaano katagal nabubuhay ang mga user, at kung ilan sa kanila ang bumabagsak pagkatapos ng ika-1 at ika-2 buwan ng paggamit ng serbisyo.

Upang masagot ang mga tanong na ito, kailangan nating bumuo ng mga cohort batay sa buwan ng pagpaparehistro. Susukatin namin ang aktibidad ayon sa mga gastos sa bawat buwan. Sa halip na mga gastos, maaaring mayroong mga order, bayad sa subscription, o anumang iba pang aktibidad na nakabatay sa oras.

Raw data

Ang mga halimbawa ay ginawa sa MySQL, ngunit para sa iba pang mga DBMS ay hindi dapat magkaroon ng anumang makabuluhang pagkakaiba.

Talahanayan ng gumagamit - mga gumagamit:

userId
Petsa ng Pagpaparehistro

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Talahanayan ng gastos - pagsingil:

userId
petsa
Kabuuan

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

Piliin ang lahat ng user write-off at petsa ng pagpaparehistro:

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

Resulta:

userId
petsa
Petsa ng Pagpaparehistro

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

Bumubuo kami ng mga cohort ayon sa buwan;

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

Ngayon kailangan nating malaman kung ilang buwan naging aktibo ang user - ito ang pagkakaiba sa pagitan ng buwan ng debit at buwan ng pagpaparehistro. Ang MySQL ay may function na PERIOD_DIFF() - ang pagkakaiba sa pagitan ng dalawang buwan. Magdagdag ng PERIOD_DIFF() sa kahilingan:

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

userId
BillingMonth
Petsa ng Pagpaparehistro
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

Binibilang namin ang mga user na na-activate sa bawat buwan - pinapangkat namin ang mga tala ayon sa BillingMonth, RegistrationMonth at 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

Resulta:

UsersCount
BillingMonth
Buwan ng Pagpaparehistro
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

Noong Enero, Pebrero at Marso, isang bagong user ang lumitaw bawat isa - MonthsDiff = 0. Isang user ng Enero ang aktibo noong Pebrero - RegistrationMonth = 2019-01, BillingMonth = 2019-02, at isang user ng Pebrero ang aktibo noong Marso.

Naturally, ang mga pattern ay mas nakikita sa isang malaking set ng data.

Paano maglipat ng mga cohort sa Grafana

Natutunan namin kung paano bumuo ng mga cohorts, ngunit kapag maraming mga tala, hindi na madaling pag-aralan ang mga ito. Maaaring i-export ang mga tala sa Excel at mabuo sa magagandang talahanayan, ngunit hindi ito ang aming paraan!

Maaaring ipakita ang mga cohort bilang isang interactive na graph sa grafana.

Upang gawin ito, nagdaragdag kami ng isa pang query upang i-convert ang data sa isang format na angkop para sa 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

At i-upload ang data sa Grafana.

Halimbawa ng graph mula sa demo:

Hawakan gamit ang iyong mga kamay:

GitHub repository na may halimbawa ay isang docker na imahe na may MySQL at Grafana na maaari mong patakbuhin sa iyong computer. Ang database ay mayroon nang demo data para sa isang taon at kalahati, mula Enero 2018 hanggang Hulyo 2019.

Kung nais mo, maaari mong i-load ang iyong data sa larawang ito.

Mga artikulo sa PS tungkol sa pagsusuri ng cohort sa SQL:

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

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

Pinagmulan: www.habr.com