I-ProHoster > Π‘Π»ΠΎΠ³ > Ukuphatha > Ungawaqoqa kanjani amaqoqo wabasebenzisi njengamagrafu ku-Grafana [+ isithombe sedokhu ngesibonelo]

Ungawaqoqa kanjani amaqoqo wabasebenzisi njengamagrafu ku-Grafana [+ isithombe sedokhu ngesibonelo]

Ungawaqoqa kanjani amaqoqo wabasebenzisi njengamagrafu ku-Grafana [+ isithombe sedokhu ngesibonelo]

Indlela esiyixazulule ngayo inkinga yokubona amaqoqo abasebenzisi kusevisi ye-Promopult sisebenzisa i-Grafana.

Ukuphromotha - isevisi enamandla enenani elikhulu labasebenzisi. Phakathi neminyaka eyi-10 yokusebenza, inani lababhalisiwe ohlelweni selidlule isigidi esisodwa. Labo abahlangabezane nezinsizakalo ezifanayo bayazi ukuthi lolu hlu lwabasebenzisi lukude kakhulu nokufana.

Omunye ubhalise futhi "walala" unomphela. Othile ukhohlwe iphasiwedi yakhe futhi wabhalisa ezinye izikhathi ezimbalwa phakathi nezinyanga eziyisithupha. Umuntu uletha imali emshinini wemali, futhi omunye weza mahhala amathuluzi. Futhi kungaba kuhle ukuthola inzuzo kuwo wonke umuntu.

Kumasethi edatha amakhulu njengawethu, ukuhlaziya ukuziphatha komsebenzisi ngamunye nokwenza izinqumo ezincane akusizi ngalutho. Kodwa ukubamba izitayela nokusebenza namaqembu amakhulu kungenzeka futhi kuyadingeka. Okuyikho kanye esikwenzayo.

Isifinyezo

  1. Kuyini ukuhlaziywa kweqembu futhi kungani kuyadingeka?
  2. Ungawenza kanjani amaqoqo ngenyanga yokubhaliswa kwabasebenzisi ku-SQL.
  3. Ungawadlulisela kanjani amaqoqo ku UGrafana.

Uma usuvele wazi ukuthi kuyini ukuhlaziywa kweqembu nokuthi kwenziwa kanjani ku-SQL, yeqela uqonde esigabeni sokugcina.

1. Kuyini ukuhlaziywa kweqembu futhi kungani kuyadingeka?

Ukuhlaziywa kweqoqo kuyindlela esekelwe ekuqhathaniseni amaqembu ahlukene (amaqoqo) wabasebenzisi. Ngokuvamile, amaqembu ethu akhiwa isonto noma inyanga umsebenzisi aqale ngayo ukusebenzisa isevisi. Kusuka lapha isikhathi sokuphila somsebenzisi sibalwa, futhi lokhu kuyinkomba ngesisekelo lapho ukuhlaziya okuyinkimbinkimbi kungenziwa khona. Isibonelo, qonda:

  • ingabe isiteshi sokutholwa sinethonya kanjani ukuphila komsebenzisi;
  • ukuthi ukusetshenziswa kwanoma yisiphi isici noma isevisi kukuthinta kanjani ukuphila konke;
  • Ukuthi ukwethulwa kwesici X kube nomthelela kanjani empilweni uma kuqhathaniswa nonyaka odlule.

2. Ungawakha kanjani amaqoqo ku-SQL?

Usayizi we-athikili nomqondo ojwayelekile awusivumeli ukuthi sethule idatha yethu yangempela lapha - endaweni yokulahla yokuhlola, izibalo zonyaka nesigamu: abasebenzisi abangu-1200 kanye nemisebenzi engu-53. Ukuze ukwazi ukudlala ngale datha, silungiselele isithombe se-docker nge-MySQL kanye ne-Grafana lapho ungathola khona konke ngokwakho. Xhuma ku-GitHub ekupheleni kwesihloko.

Futhi lapha sizobonisa ukudalwa kwamaqoqo sisebenzisa isibonelo esenziwe lula.

Ake sicabange ukuthi sinesevisi. Abasebenzisi babhalisa lapho futhi basebenzise imali kumasevisi. Ngokuhamba kwesikhathi, abasebenzisi bayayeka. Sifuna ukuthola ukuthi abasebenzisi baphila isikhathi esingakanani, nokuthi bangaki kubo abawayo ngemva kwenyanga yoku-1 neyesi-2 yokusebenzisa isevisi.

Ukuze siphendule le mibuzo, sidinga ukwakha amaqoqo ngokusekelwe enyangeni yokubhalisa. Sizokala umsebenzi ngezindleko enyangeni ngayinye. Esikhundleni sezindleko, kungase kube nama-oda, izimali zokubhaliselwe, nanoma yimuphi omunye umsebenzi osekelwe esikhathini.

Idatha ebomvu

Izibonelo zenziwe ku-MySQL, kodwa kwamanye ama-DBMS akufanele kube khona umehluko obalulekile.

Ithebula lomsebenzisi - abasebenzisi:

I-ID Yomsebenzisi
Idethi Yokubhalisa

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Ithebula lezindleko - inkokhelo:

I-ID Yomsebenzisi
Usuku
Sum

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 konke ukuvalwa komsebenzisi kanye nedethi yokubhalisa:

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

Umphumela:

I-ID Yomsebenzisi
Usuku
Idethi Yokubhalisa

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 amaqoqo ngenyanga; ukwenza lokhu, siguqula zonke izinsuku zibe izinyanga:

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

Manje sidinga ukwazi ukuthi zingaki izinyanga umsebenzisi abesebenza - lona umehluko phakathi kwenyanga yokudonswa kwemali kanye nenyanga yokubhalisa. I-MySQL inomsebenzi PERIOD_DIFF() - umehluko phakathi kwezinyanga ezimbili. Engeza i-PERIOD_DIFF() esicelweni:

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

I-ID Yomsebenzisi
BillingMonth
Idethi Yokubhalisa
IzinyangaDiff

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 abavuswe enyangeni ngayinye - siqoqa amarekhodi nge-BillingMonth, RegistrationMonth kanye ne-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

Umphumela:

UsersCount
BillingMonth
Inyanga Yokubhalisa
IzinyangaDiff

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, Februwari noMashi, kuvele umsebenzisi oyedwa omusha ngamunye - MonthsDiff = 0. Umsebenzisi oyedwa kaJanuwari ubesebenza ngoFebruwari - RegistrationMonth = 2019-01, BillingMonth = 2019-02, futhi umsebenzisi oyedwa wangoFebhuwari ubesebenza ngoMashi.

Ngokwemvelo, amaphethini abonakala kangcono kusethi enkulu yedatha.

Ungawadlulisela kanjani ama-cohorts ku-Grafana

Sifunde ukuthi singawakha kanjani amaqoqo, kodwa uma kunamarekhodi amaningi, akuselula ukuwahlaziya. Amarekhodi angathunyelwa ku-Excel futhi akhiwe amatafula amahle, kodwa lena akuyona indlela yethu!

Amaqoqo angaboniswa njengegrafu esebenzisanayo UGrafana.

Ukwenza lokhu, sengeza omunye umbuzo ukuze siguqule idatha ibe yifomethi efanele i-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

Futhi ulayishe idatha ku-Grafana.

Isibonelo segrafu kusuka idemo:

Ungawaqoqa kanjani amaqoqo wabasebenzisi njengamagrafu ku-Grafana [+ isithombe sedokhu ngesibonelo]

Thinta ngezandla zakho:

Indawo yokugcina ye-GitHub ngesibonelo yisithombe sedokhu esine-MySQL ne-Grafana ongasisebenzisa kukhompyutha yakho. Isizindalwazi sesivele sinedatha yedemo yonyaka nesigamu, kusukela ngoJanuwari 2018 kuya kuJulayi 2019.

Uma uthanda, ungalayisha idatha yakho kulesi sithombe.

Izindatshana ze-PS mayelana nokuhlaziywa kweqembu ku-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

Engeza amazwana