ProHoster > Ibhulogi > Ulawulo > Ukuqokelela njani amaqela abasebenzisi njengegrafu eGrafana [+ umfanekiso wedocker ngomzekelo]

Ukuqokelela njani amaqela abasebenzisi njengegrafu eGrafana [+ umfanekiso wedocker ngomzekelo]

Ukuqokelela njani amaqela abasebenzisi njengegrafu eGrafana [+ umfanekiso wedocker ngomzekelo]

Indlela esiyisombulule ngayo ingxaki yokubona amaqela abasebenzisi kwinkonzo yePromopult sisebenzisa iGrafana.

khuthaza - inkonzo enamandla kunye nenani elikhulu labasebenzisi. Kwiminyaka eyi-10 yokusebenza, inani labantu ababhalise kule nkqubo liye ladlula kwisigidi esinye. Abo baye badibana neenkonzo ezifanayo bayazi ukuba olu luhlu lwabasebenzisi lukude kwi-homogeneous.

Umntu ubhalise kwaye "walala" ngonaphakade. Umntu ulibale igama eliyimfihlo lakhe kwaye ubhalise isibini amaxesha amaninzi kwisithuba seenyanga ezintandathu. Umntu uzisa imali kwirejista yemali, kwaye umntu uze simahla izixhobo. Kwaye kuya kuba kuhle ukufumana inzuzo kuwo wonke umntu.

Kwiiseti ezinkulu zedatha njengezethu, ukuhlalutya ukuziphatha komsebenzisi ngamnye kunye nokwenza izigqibo ezincinci akukho nto. Kodwa ukubamba iindlela kunye nokusebenza kunye namaqela amakhulu kunokwenzeka kwaye kuyimfuneko. Nto leyo kanye esiyenzayo.

Isishwankathelo

  1. Yintoni uhlalutyo lwamaqela kwaye kutheni lufuneka?
  2. Uwenza njani amaqela ngenyanga yobhaliso lwabasebenzisi kwiSQL.
  3. Indlela yokudlulisela amaqela kwi IGrafana.

Ukuba sele uyazi ukuba yintoni uhlalutyo lweqela kunye nendlela yokwenza kwi-SQL, tsiba ngqo kwicandelo lokugqibela.

1. Yintoni uhlalutyo lweqela kwaye kutheni luyimfuneko?

Uhlalutyo lweqela yindlela esekelwe ekuthelekiseni amaqela ahlukeneyo (amaqela) abasebenzisi. Amaxesha amaninzi, amaqela ethu enziwa yiveki okanye inyanga umsebenzisi aqale ngayo ukusebenzisa inkonzo. Ukusuka apha ubomi bomsebenzisi bubalwa, kwaye esi sisalathisi ngesiseko apho uhlalutyo olunzima lunokwenziwa. Umzekelo, qonda:

  • ingaba ijelo lokufumana libuchaphazela njani ubomi bomsebenzisi;
  • indlela ukusetyenziswa kwalo naluphi na uphawu okanye inkonzo echaphazela ubomi bonke;
  • Ukuphehlelelwa kwenqaku X kubuchaphazele njani ubomi xa kuthelekiswa nonyaka ophelileyo.

2. Ukwenza njani amaqela kwiSQL?

Ubungakanani benqaku kunye nengqiqo engasivumeli ukuba sibonise idatha yethu yokwenyani apha - kwindawo yokulahla uvavanyo, izibalo zonyaka kunye nesiqingatha: abasebenzisi be-1200 kunye ne-53 yokuthengiselana. Ukuze ukwazi ukudlala ngale datha, silungiselele umfanekiso we-docker kunye ne-MySQL kunye neGrafana apho unokufumana khona konke ngokwakho. Ikhonkco kwi-GitHub ekupheleni kwenqaku.

Kwaye apha siza kubonisa ukudalwa kwee-cohorts usebenzisa umzekelo owenziwe lula.

Masicinge ukuba sinenkonzo. Abasebenzisi babhalisa apho kwaye bachithe imali kwiinkonzo. Ngokuhamba kwexesha, abasebenzisi bayayeka. Sifuna ukufumanisa ukuba abasebenzisi bahlala ixesha elingakanani, kwaye bangaphi kubo abawa emva kwe-1st kunye ne-2nd inyanga yokusebenzisa inkonzo.

Ukuphendula le mibuzo, kufuneka sakhe i-cohorts ngokusekelwe kwinyanga yokubhalisa. Siya kujonga umsebenzi ngokweendleko kwinyanga nganye. Endaweni yeendleko, kusenokubakho iiodolo, iintlawulo zomrhumo, okanye nawuphi na omnye umsebenzi osekelwe kwixesha.

Idatha yemvelaphi

Imizekelo yenziwe kwi-MySQL, kodwa kwezinye ii-DBMS akufuneki kubekho umahluko obalulekileyo.

Itheyibhile yomsebenzisi - abasebenzisi:

Isazisi somsebenzisi
Umhla woBhaliso

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Itheyibhile yeendleko-ityala:

Isazisi somsebenzisi
umhla
ISity

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 lonke ixesha lokucinywa komsebenzisi kunye nomhla wobhaliso:

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

Isiphumo:

Isazisi somsebenzisi
umhla
Umhla woBhaliso

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

Sakha amaqela ngenyanga; ukwenza oku, siguqula yonke imihla ibe ziinyanga:

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

Ngoku kufuneka sazi ukuba zingaphi iinyanga ezisetyenziswa ngumsebenzisi - lo ngumahluko phakathi kwenyanga yokutsalwa kwemali kunye nenyanga yobhaliso. I-MySQL inomsebenzi PERIOD_DIFF () - umahluko phakathi kweenyanga ezimbini. Yongeza PERIOD_DIFF() kwisicelo:

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

Isazisi somsebenzisi
BillingMonth
Umhla woBhaliso
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

Sibala abasebenzisi abavusiweyo kwinyanga nganye - siqokelela iirekhodi ngeBillingMonth, RegistrationMonth kunye neMonthsDiff:

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

Isiphumo:

UsersCount
BillingMonth
Inyanga yoBhaliso
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

NgoJanuwari, ngoFebruwari nangoMatshi, kwavela umsebenzisi omtsha ngamnye - MonthsDiff = 0. Umsebenzisi omnye kaJanuwari ubesebenza ngoFebruwari-RegistrationMonth = 2019-01, BillingMonth = 2019-02, kunye nomsebenzisi omnye kaFebruwari ubesebenza ngoMatshi.

Ngokwemvelo, iipateni zibonakala ngcono kwiseti yedatha enkulu.

Uwatshintshela njani amaqela abantu eGrafana

Sifundile ukwenza amaqela, kodwa xa kukho iirekhodi ezininzi, akusekho lula ukuzihlalutya. Iirekhodi zinokuthunyelwa ngaphandle kwi-Excel kwaye zenziwe zibe ziitafile ezintle, kodwa ayisiyondlela yethu le!

Amaqela angaboniswa njengegrafu esebenzayo kwi IGrafana.

Ukwenza oku, songeza omnye umbuzo ukuguqula idatha kwifomathi efanelekileyo kwiGrafana:

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

Kwaye ulayishe idatha eGrafana.

Umzekelo wegrafu ukusuka umboniso:

Ukuqokelela njani amaqela abasebenzisi njengegrafu eGrafana [+ umfanekiso wedocker ngomzekelo]

Bamba ngezandla zakho:

Indawo yokugcina yeGitHub ngomzekelo ngumfanekiso wedocker oneMySQL kunye neGrafana onokuthi uwuqhube kwikhompyuter yakho. I-database sele inedatha yedemo yonyaka kunye nesiqingatha, ukususela ngoJanuwari 2018 ukuya kuJulayi 2019.

Ukuba unqwenela, unokulayisha idatha yakho kulo mfanekiso.

Amanqaku e-PS malunga nohlalutyo lweqela kwi-SQL:

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

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

umthombo: www.habr.com

Yongeza izimvo