🥇Sida loo ururiyo kooxaha isticmaala sida garaaf ahaan Grafana [+ sawirka docker oo leh tusaale]

Sida aan u xalinay dhibaatada sawirida kooxaha isticmaalayaasha ee adeega Promopult anagoo adeegsanayna Grafana.

Dhiirigelin - adeeg awood leh oo leh tiro badan oo isticmaaleyaal ah. Muddo 10 sano ah oo la shaqaynayey, tirada diwaangelinta nidaamka ayaa kor u dhaaftay hal milyan. Kuwa la kulmay adeegyo la mid ah waxay og yihiin in isticmaalayaashan kala duwani ay ka fog yihiin kuwa isku mid ah.

Qof baa saxeexay oo "seexday" weligiis. Qof baa illoobay furaha sirta ah oo laba jeer diiwaangeliyay muddo lix bilood ah. Qof baa lacag keenaya diiwaanka kaashka, qofna wuxuu u yimid lacag-la'aan qalab. Way fiicnaan lahayd in qof walba xoogaa faa'iido ah laga helo.

Xogta baaxadda leh ee sidayada oo kale ah, falanqaynta habdhaqanka isticmaale shakhsi ahaaneed iyo samaynta go'aannada yaryar waa macno darro. Laakin qabsashada isbeddellada iyo la shaqeynta kooxo waaweyn waa suurtagal waana lagama maarmaan. Taas oo ah waxa aan samayno.

Soo Koobid

Waa maxay falanqaynta kooxuhu iyo sababta loogu baahan yahay?
Sida loo sameeyo koox-kooxeed bishiiba ee diiwaangelinta isticmaalaha gudaha SQL.
Sida loogu wareejiyo kooxda Grafana.

Haddii aad hore u garanaysay waxa ay tahay falanqaynta kooxuhu iyo sida loo sameeyo SQL, si toos ah ugu bood qaybta u dambaysa.

1. Waa maxay falanqaynta kooxuhu maxaase loogu baahan yahay?

Falanqaynta kooxuhu waa hab ku salaysan isbarbardhigga kooxaha kala duwan (kooxaha) isticmaalayaasha. Inta badan, kooxahayagu waxa la sameeyaa toddobaadka ama bisha uu isticmaaluhu bilaabay isticmaalka adeegga. Halkaa waxa laga xisaabinayaa qofka isticmaala noloshiisa noloshiisa, tanina waa tilmaame ku salaysan falanqaynta adag ee la samayn karo. Tusaale ahaan, faham:

sidee kanaalka wax iibsiga u saameeyaa nolosha isticmaalaha;
sida isticmaalka sifo kasta ama adeeg kasta u saameeyaa nolosha;
Sida soo-bandhigidda astaanta X u saamaysay nolosha marka loo eego sannadkii hore.

2. Sida loo sameeyo koox-kooxeedyo gudaha SQL?

Baaxadda maqaalka iyo caqliga saliimka ah ma ogola inaan soo bandhigno xogtayada dhabta ah halkan - qashinka tijaabada ah, tirakoobka sanad iyo badh: 1200 isticmaalayaasha iyo 53 macaamil ganacsi. Si aad xogtan ugu ciyaarto, waxaanu ku diyaarinay sawir-qaade MySQL iyo Grafana kaas oo aad laftigaaga oo dhan kula kulmi karto. Ku xidhka GitHub dhamaadka maqaalka.

Oo halkan waxaan ku tusi doonaa abuurista koox-kooxeedyo iyadoo la adeegsanayo tusaale la fududeeyay.

Aynu ka soo qaadno in aanu haysano adeeg. Isticmaalayaashu waxay iska diiwaan galiyaan halkaas oo ay lacag ku qaataan adeegyada. Waqti ka dib, isticmaalayaashu way joojiyaan. Waxaan rabnaa inaan ogaano inta isticmaalayaashu ay nool yihiin, iyo intee in le'eg oo iyaga ka mid ah ayaa dhacaya ka dib bisha 1aad iyo 2aad ee isticmaalka adeegga.

Si aan uga jawaabno su'aalahan, waxaan u baahanahay inaan dhisno koox-kooxeedyo ku salaysan bisha diiwaangelinta. Waxaan dhaqdhaqaaqa ku cabbiri doonaa kharashyada bil kasta. Halkii kharashaadka, waxaa jiri kara dalabaad, kharashyo isdiiwaangelin, ama hawlo kale oo waqti ku salaysan.

Xogta ugu horeysa

Tusaalooyinka waxaa lagu sameeyay MySQL, laakiin DBMS-yada kale waa inaysan jirin farqi weyn.

Shaxda isticmaalaha - isticmaalayaasha:

userId
Taariikhda Diiwaangelinta

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Miiska qiimaha - biilasha:

userId
Taariikhda
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

Dooro dhammaan fasaxyada isticmaale iyo taariikhda diiwaangelinta:

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

Natiijada:

userId
Taariikhda
Taariikhda Diiwaangelinta

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

Waxaan dhisnaa koox-kooxeed bishiiba; si tan loo sameeyo, waxaan dhammaan taariikhaha u beddelnaa bilo:

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

Hadda waxaan u baahanahay inaan ogaano inta bilood ee isticmaaluhu uu firfircoonaa - tani waa farqiga u dhexeeya bisha debit iyo bisha diiwaangelinta. MySQL waxay leedahay shaqo PERIOD_DIFF() - farqiga u dhexeeya laba bilood. Ku dar PERIOD_DIFF() codsiga:

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
Taariikhda Diiwaangelinta
Bilaha kala duwan

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

Waxaan xisaabin isticmaalayaasha firfircoonida bil kasta - waxaan ku ururinay diiwaanada BillingMonth, RegistrationMonth iyo 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

Natiijada:

UsersCount
BillingMonth
Bisha Diiwaangelinta
Bilaha kala duwan

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

Bishii Janaayo, Febraayo iyo Maarso, hal isticmaale cusub ayaa soo muuqday mid kasta - MonthsDiff = 0. Hal isticmaale oo Janaayo ayaa shaqeynayay Febraayo - RegistrationMonth = 2019-01, BillingMonth = 2019-02, iyo hal isticmaale Febraayo ayaa shaqeynayay bishii Maarso.

Dabiici ahaan, qaababka ayaa si fiican uga dhex muuqda xog badan.

Sida kooxda loogu wareejinayo Grafana

Waxaan barannay sida loo sameeyo koox-kooxeedyo, laakiin marka ay jiraan diiwaanno badan, ma fududa in la falanqeeyo. Diiwaanada waxaa loo dhoofin karaa Excel waxaana loo sameeyay miisas qurux badan, laakiin tani maaha habkayaga!

Kooxuhu waxa loo tusi karaa garaaf is dhexgal ah Grafana.

Si tan loo sameeyo, waxaan ku darnaa weydiin kale si aan xogta ugu beddelno qaab ku habboon 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

Oo soo rar xogta Grafana.

Tusaale garaaf ka demo:

Ku taabo gacmahaaga:

Kaydka GitHub oo leh tusaale waa sawir docker leh MySQL iyo Grafana oo aad ku dhex socon karto kombayutarkaga. Xogta kaydka waxa ay hore ugu jirtay xogta demo sanad iyo badh, laga bilaabo Jannaayo 2018 ilaa Luulyo 2019.

Haddii aad rabto, waxaad ku shuban kartaa xogtaada sawirkan.

Maqaallada PS ee ku saabsan falanqaynta kooxda ee 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