🥇Pehea e hōʻiliʻili ai i nā cohorts o nā mea hoʻohana ma ke ʻano o nā kiʻi ma Grafana [+docker kiʻi me ka laʻana]

Pehea mākou i hoʻoponopono ai i ka pilikia o ka nānā ʻana i nā cohorts o nā mea hoʻohana i ka lawelawe Promopult me ka hoʻohana ʻana iā Grafana.

paipai - kahi lawelawe ikaika me ka nui o nā mea hoʻohana. Ma luna o nā makahiki 10 o ka hana, ua ʻoi aku ka nui o nā hoʻopaʻa inoa ma ka ʻōnaehana ma mua o hoʻokahi miliona. Ua ʻike ka poʻe i hālāwai me nā lawelawe like ʻole kēia ʻano o nā mea hoʻohana i mamao loa mai ka homogeneous.

Ua kākau inoa kekahi a "ua hiamoe" mau loa. Ua poina kekahi i kā lākou ʻōlelo huna a hoʻopaʻa inoa hou i ʻelua mau manawa i loko o ʻeono mahina. Lawe kekahi i ke kālā i ka waihona kālā, a hele mai kekahi no ka uku manuahi mea kani. A he mea maikaʻi nō ka loaʻa ʻana o kekahi waiwai mai kēlā me kēia kanaka.

Ma nā pūʻulu ʻikepili nui e like me kā mākou, ʻaʻole pono ka nānā ʻana i ka ʻano o ka mea hoʻohana a me ka hana ʻana i nā hoʻoholo micro. Akā ʻo ka hopu ʻana i nā ʻano a me ka hana pū ʻana me nā hui nui hiki a pono. ʻO ia ka mea a mākou e hana ai.

Hoʻāla manaʻo

He aha ka ʻikepili cohort a no ke aha e pono ai?
Pehea e hana ai i nā cohorts ma ka mahina o ka hoʻopaʻa inoa mea hoʻohana ma SQL.
Pehea e hoʻoili ai i nā cohorts i grafana.

Inā ʻike mua ʻoe i ke ʻano o ka cohort analysis a pehea e hana ai ma SQL, lele pololei i ka pauku hope.

1. He aha ka hoʻopaʻa ʻana o ka cohort a no ke aha e pono ai?

ʻO ka loiloi cohort kahi ala e pili ana i ka hoʻohālikelike ʻana i nā hui like ʻole (cohorts) o nā mea hoʻohana. ʻO ka pinepine, hoʻokumu ʻia kā mākou hui e ka pule a i ʻole ka mahina i hoʻomaka ai ka mea hoʻohana i ka lawelawe. Mai ʻaneʻi ua helu ʻia ke ola o ka mea hoʻohana, a he hōʻailona kēia ma ke kumu e hiki ai ke hoʻokō i kahi loiloi paʻakikī. No ka laʻana, hoʻomaopopo:

pehea ka hopena o ke ala kūʻai i ke ola o ka mea hoʻohana;
pehea ka hoʻohana ʻana o kekahi hiʻohiʻona a lawelawe paha i ke ola a pau;
Pehea ka hoʻomaka ʻana o ka hiʻohiʻona X i pili i ke ola i hoʻohālikelike ʻia i ka makahiki i hala.

2. Pehea e hana ai i nā cohorts ma SQL?

ʻAʻole ʻae ka nui o ka ʻatikala a me ka noʻonoʻo maʻamau iā mākou e hōʻike i kā mākou ʻikepili maoli ma aneʻi - i ka hoʻāʻo ʻana, ʻikepili no hoʻokahi makahiki a me ka hapa: 1200 mea hoʻohana a me 53 mau hana. I hiki iā ʻoe ke pāʻani me kēia ʻikepili, ua hoʻomākaukau mākou i kahi kiʻi docker me MySQL a me Grafana i hiki ai iā ʻoe ke ʻike iā ʻoe iho. Link i GitHub ma ka hope o ka ʻatikala.

A ma aneʻi e hōʻike mākou i ka hana ʻana o nā cohorts me ka hoʻohana ʻana i kahi hiʻohiʻona maʻalahi.

E manaʻo mākou he lawelawe kā mākou. Hoʻopaʻa inoa nā mea hoʻohana ma laila a hoʻolilo kālā i nā lawelawe. I ka wā lōʻihi, haʻalele nā mea hoʻohana. Makemake mākou e ʻike i ka lōʻihi o ke ola ʻana o nā mea hoʻohana, a me ka nui o lākou e hāʻule ma hope o ka mahina 1 a me 2nd o ka hoʻohana ʻana i ka lawelawe.

No ka pane ʻana i kēia mau nīnau, pono mākou e kūkulu i nā cohorts e pili ana i ka mahina o ka hoʻopaʻa inoa. E ana mākou i ka hana ma nā lilo i kēlā me kēia mahina. Ma kahi o nā lilo, aia paha nā kauoha, nā uku hoʻopaʻa inoa, a i ʻole nā hana ʻē aʻe e pili ana i ka manawa.

ʻIkepili mua

Ua hana ʻia nā hiʻohiʻona ma MySQL, akā no nā DBMS ʻē aʻe ʻaʻole pono nā ʻokoʻa koʻikoʻi.

Papakaukau mea hoʻohana - nā mea hoʻohana:

userId
Lā Kakau

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Papa kūʻai - bila:

userId
lā
Mokau

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

E koho i nā mea hoʻohana āpau a me ka lā hoʻopaʻa inoa:

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

Hualoaʻa:

userId
lā
Lā Kakau

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

Kūkulu mākou i nā cohorts ma ka mahina; e hana i kēia, hoʻololi mākou i nā lā āpau i mau mahina:

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

I kēia manawa pono mākou e ʻike i ka nui o nā mahina i hana ai ka mea hoʻohana - ʻo ia ka ʻokoʻa ma waena o ka mahina o ka hōʻaiʻē a me ka mahina o ka hoʻopaʻa inoa. He hana ko MySQL PERIOD_DIFF() - ka ʻokoʻa ma waena o ʻelua mahina. Hoʻohui PERIOD_DIFF() i ka noi:

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
Mahina Pili
Lā Kakau
Māhinaʻokoʻa

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

Helu mākou i nā mea hoʻohana i hana ʻia i kēlā me kēia mahina - hui mākou i nā moʻolelo e BillingMonth, RegistrationMonth a me 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

Hualoaʻa:

Ka helu mea hoʻohana
Mahina Pili
Kakau inoaMahina
Māhinaʻokoʻa

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

I Ianuali, Fepeluali a me Malaki, ua ʻike ʻia hoʻokahi mea hoʻohana hou i kēlā me kēia - MonthsDiff = 0. Hoʻokahi mea hoʻohana o Ianuali i hana ma Pepeluali - RegistrationMonth = 2019-01, BillingMonth = 2019-02, a hoʻokahi mea hoʻohana Fepeluali i hana ma Malaki.

Ma keʻano maʻamau, ʻoi aku ka maikaʻi o nā hiʻohiʻona i kahi ʻikepili nui.

Pehea e hoʻoili ai i nā cohorts iā Grafana

Ua aʻo mākou i ka hana ʻana i nā cohorts, akā inā nui nā moʻolelo, ʻaʻole maʻalahi ka nānā ʻana iā lākou. Hiki ke hoʻokuʻu ʻia nā moʻolelo i Excel a hana ʻia i mau papa nani, akā ʻaʻole kēia kā mākou ala!

Hiki ke hōʻike ʻia nā cohorts ma ke ʻano he pakuhi pāʻani ma grafana.

No ka hana ʻana i kēia, hoʻohui mākou i kahi nīnau hou e hoʻohuli i ka ʻikepili i kahi ʻano kūpono no 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

A hoʻouka i ka ʻikepili iā Grafana.

Laʻana pakuhi mai demo:

E pā me kou mau lima:

GitHub waihona me ka laʻana he kiʻi docker me MySQL a me Grafana hiki iā ʻoe ke holo ma kāu kamepiula. Loaʻa ka ʻikepili i ka ʻikepili demo no hoʻokahi makahiki a me ka hapa, mai Ianuali 2018 a Iulai 2019.

Inā makemake ʻoe, hiki iā ʻoe ke hoʻouka i kāu ʻikepili i kēia kiʻi.

Nā ʻatikala PS e pili ana i ka nānā ʻana i ka cohort ma SQL:

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

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

Source: www.habr.com

Pehea e hōʻiliʻili ai i nā cohorts mea hoʻohana e like me nā kiʻi ma Grafana [+ kiʻi docker me ka laʻana]

Hoʻāla manaʻo

1. He aha ka hoʻopaʻa ʻana o ka cohort a no ke aha e pono ai?

2. Pehea e hana ai i nā cohorts ma SQL?

ʻIkepili mua

Pehea e hoʻoili ai i nā cohorts iā Grafana

Pākuʻi i ka manaʻo hoʻopuka ana pane