Mokhoa oa ho bokella lihlopha tsa basebelisi joalo ka li-graph ho Grafana [+ setšoantšo sa docker ka mohlala]

Re rarolle bothata ba ho bona lihlopha tsa basebelisi ba ts'ebeletso ea Promopult re sebelisa Grafana.

phahamisa - tšebeletso e matla e nang le palo e kholo ea basebelisi. Lilemong tse 10 tsa ts'ebetso, palo ea ba ngolisitseng tsamaisong e fetile milione e le 'ngoe. Ba kileng ba kopana le lits'ebeletso tse tšoanang baa tseba hore palo ena ea basebelisi e hole le ho ts'oana.

Motho e mong o ile a ingolisa 'me a "robala" ka ho sa feleng. Motho e mong o lebetse phasewete ea hae mme a ngolisa makhetlo a 'maloa nakong ea likhoeli tse tšeletseng. Motho e mong o tlisa chelete polokelong ea chelete, 'me e mong o tlile bakeng sa mahala liletsa. 'Me ho ka ba monate ho fumana phaello ho motho e mong le e mong.

Ho li-data tse kholo joalo ka tsa rona, ho sekaseka boitšoaro ba mosebelisi ka mong le ho etsa liqeto tse nyane ha ho na thuso. Empa ho tšoasa mekhoa le ho sebetsa le lihlopha tse kholo hoa khoneha ebile hoa hlokahala. E leng hantle seo re se etsang.

Kakaretso

1. Tlhahlobo ea lihlopha ke eng, 'me ke hobane'ng ha e hlokahala?
2. Mokhoa oa ho etsa li-cohort ka khoeli ea ho ngolisa basebelisi ho SQL.
3. Mokhoa oa ho fetisetsa lihlopha ho grafana.

Haeba u se u ntse u tseba hore na tlhahlobo ea cohort ke eng le hore na u ka e etsa joang ho SQL, tlola ka kotloloho karolong ea ho qetela.

1. Tlhahlobo ea lihlopha ke eng, 'me ke hobane'ng ha e hlokahala?

Tlhahlobo ea sehlopha ke mokhoa o ipapisitseng le ho bapisa lihlopha tse fapaneng (lihlopha) tsa basebelisi. Hangata, lihlopha tsa rona li thehiloe ke beke kapa khoeli eo mosebelisi a qalileng ho sebelisa ts'ebeletso. Ho tloha mona nako ea bophelo ba mosebedisi e baloa, 'me sena ke pontšo motheong oa hore tlhahlobo e rarahaneng e ka etsoa. Ka mohlala, utloisisa:

• mocha oa ho reka o ama bophelo ba mosebedisi joang;
• kamoo tšebeliso ea tšobotsi kapa tšebeletso e amang bophelo bohle;
• Ho hlahisoa ha tšobotsi X ho amme bophelo bohle joang ha ho bapisoa le selemong se fetileng.

2. Joang ho etsa cohorts ka SQL?

Boholo ba sehlooho le kutloisiso e tloaelehileng ha li re lumelle ho hlahisa lintlha tsa rona tsa sebele mona - sebakeng sa teko ea teko, lipalo-palo tsa selemo le halofo: basebelisi ba 1200 le litšebelisano tsa 53. E le hore u ka bapala ka lintlha tsena, re lokiselitse setšoantšo sa docker se nang le MySQL le Grafana moo u ka iphumanang u le mong. Sehokela ho GitHub qetellong ea sengoloa.

Mme mona re tla bonts'a popo ea li-cohorts re sebelisa mohlala o nolofalitsoeng.

Ha re nke hore re na le tshebeletso. Basebelisi ba ngolisa moo mme ba sebelisa chelete litšebeletsong. Ha nako e ntse e ea, basebelisi ba khaotsa. Re batla ho tseba hore na basebelisi ba phela nako e kae, le hore na ke ba bakae ba bona ba oelang ka mor'a khoeli ea 1 le ea 2 ea ho sebelisa tšebeletso.

Ho araba lipotso tsena, re hloka ho theha lihlopha ho latela khoeli ea ngoliso. Re tla lekanya tšebetso ka litšenyehelo khoeli le khoeli. Ho e-na le litšenyehelo, ho ka ’na ha e-ba le liodara, litefello tsa peeletso, kapa mosebetsi ofe kapa ofe o itšetlehileng ka nako.

Lintlha tsa pele

Mehlala e entsoe ho MySQL, empa bakeng sa li-DBMS tse ling ha hoa lokela ho ba le liphapang tse kholo.

Tafole ea basebelisi - basebelisi:

Theneketso
Letsatsi la Ngodiso

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tafole ea litefiso - litefiso:

Theneketso
letsatsi
Kakaretso

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

Khetha lintlha tsohle tsa ho ngolisoa ha basebelisi le letsatsi la ngoliso:

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

Sephetho:

Theneketso
letsatsi
Letsatsi la Ngodiso

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

Re theha lihlopha ka khoeli; ho etsa sena, re fetolela matsatsi ohle hore e be likhoeli:

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

Joale re hloka ho tseba hore na mosebelisi o sebelitse likhoeli tse kae - ena ke phapang lipakeng tsa khoeli ea debit le khoeli ea ngoliso. MySQL e na le tšebetso PERIOD_DIFF() - phapang lipakeng tsa likhoeli tse peli. Kenya PERIOD_DIFF() kopong:

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

Theneketso
Khoeli ea Khoeli
Letsatsi la Ngodiso
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

Re bala basebelisi ba kentsoeng khoeling e 'ngoe le e 'ngoe - re hlophisa lirekoto ka BillingMonth, RegistrationMonth le 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

Sephetho:

UsersCount
Khoeli ea Khoeli
Kgwedi ya Ngodiso
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

Ka Pherekhong, Hlakola le Hlakubele, mosebelisi a le mong e mocha o ile a hlaha e mong le e mong - MonthsDiff = 0. Mosebelisi a le mong oa Pherekhong o ne a sebetsa ka Hlakola - RegistrationMonth = 2019-01, BillingMonth = 2019-02, mme mosebelisi a le mong oa Hlakola o ne a sebetsa ka Hlakubele.

Ka tlhaho, lipaterone li bonahala hantle ho sete e kholo ea data.

Mokhoa oa ho fetisetsa lihlopha ho Grafana

Re ithutile mokhoa oa ho theha lihlopha, empa ha ho na le litlaleho tse ngata, ha ho sa le bonolo ho li hlahloba. Lirekoto li ka romelloa ho Excel mme tsa etsoa litafole tse ntle, empa ona ha se mokhoa oa rona!

Lihlopha li ka bonts'oa e le kerafo e sebetsanang grafana.

Ho etsa sena, re kenya potso e 'ngoe ho fetolela data ho sebopeho se loketseng 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

Ebe u kenya data ho Grafana.

Mohlala kerafo ho tloha moemeli:

Tšoara ka matsoho a hau:

Sebaka sa polokelo ea GitHub ka mohlala ke setšoantšo sa docker se nang le MySQL le Grafana seo u ka se tsamaisang khomphuteng ea hau. Database e se e ntse e na le data ea demo bakeng sa selemo le halofo, ho tloha ka Pherekhong 2018 ho isa Phupu 2019.

Haeba u lakatsa, u ka kenya data ea hau setšoantšong sena.

Lingoloa tsa PS mabapi le tlhahlobo ea sehlopha ho 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