Pulogalamu ya ProHoster > Blog > Ulamuliro > Momwe mungasonkhanitsire magulu ogwiritsa ntchito ngati ma graph mu Grafana [+ docker chithunzi ndi chitsanzo]

Momwe mungasonkhanitsire magulu ogwiritsa ntchito ngati ma graph mu Grafana [+ docker chithunzi ndi chitsanzo]

Momwe mungasonkhanitsire magulu ogwiritsa ntchito ngati ma graph mu Grafana [+ docker chithunzi ndi chitsanzo]

Momwe tidathetsera vuto lowonera magulu ogwiritsa ntchito mu Promopult pogwiritsa ntchito Grafana.

Kutsatsa - ntchito yamphamvu yokhala ndi ogwiritsa ntchito ambiri. Pazaka 10 zogwira ntchito, chiŵerengero cha olembetsa m’dongosololi chaposa miliyoni imodzi. Iwo omwe akumanapo ndi ntchito zofananira amadziwa kuti gulu ili la ogwiritsa ntchito silikhala lofanana.

Wina adalembetsa ndi "kugona" kosatha. Wina anayiwala mawu achinsinsi awo ndipo adalembetsa kangapo m'miyezi isanu ndi umodzi. Wina amabweretsa ndalama ku kaundula wa ndalama, ndipo wina adabwera kwaulere zida. Ndipo zingakhale bwino kupeza phindu kwa aliyense.

Pazinthu zazikuluzikulu monga zathu, kusanthula khalidwe la wogwiritsa ntchito payekha ndikupanga zisankho zazing'ono ndizopanda pake. Koma kugwira machitidwe ndikugwira ntchito ndi magulu akuluakulu ndizotheka komanso kofunikira. Zomwe ndi zomwe timachita.

Chidule

  1. Kodi kusanthula kwamagulu ndi chiyani ndipo chifukwa chiyani kuli kofunikira?
  2. Momwe mungapangire ma cohorts mwezi wolembetsa ogwiritsa ntchito ku SQL.
  3. Momwe mungasamutsire ma cohorts kupita grafana.

Ngati mukudziwa kale kusanthula kwamagulu ndi momwe mungachitire mu SQL, pitani molunjika mpaka gawo lomaliza.

1. Kodi kusanthula kwamagulu ndi chiyani ndipo chifukwa chiyani kuli kofunikira?

Kusanthula kwamagulu ndi njira yozikidwa pakuyerekeza magulu osiyanasiyana (magulu) a ogwiritsa ntchito. Nthawi zambiri, magulu athu amapangidwa ndi sabata kapena mwezi womwe wogwiritsa ntchito adayamba kugwiritsa ntchito. Kuchokera apa nthawi ya moyo wa wogwiritsa ntchito imawerengedwa, ndipo ichi ndi chizindikiro chomwe chikhoza kuchitidwa kusanthula kovuta kwambiri. Mwachitsanzo, mvetsetsani:

  • momwe njira yopezera imakhudzira moyo wa wogwiritsa ntchito;
  • momwe kugwiritsa ntchito chinthu chilichonse kapena ntchito iliyonse kumakhudzira moyo wonse;
  • Momwe kukhazikitsidwa kwa mawonekedwe X kudakhudzira moyo wonse poyerekeza ndi chaka chatha.

2. Kodi kupanga cohorts mu SQL?

Kukula kwa nkhaniyo ndi nzeru wamba sikulola kuti tipereke deta yathu yeniyeni pano - muzitsulo zoyesa, ziwerengero za chaka ndi theka: ogwiritsa ntchito 1200 ndi 53. Kuti mutha kusewera ndi izi, takonzekera chithunzi cha docker ndi MySQL ndi Grafana momwe mungadziwire nokha. Lumikizani ku GitHub kumapeto kwa nkhaniyi.

Ndipo apa tiwonetsa kupangidwa kwa magulu pogwiritsa ntchito chitsanzo chosavuta.

Tiyerekeze kuti tili ndi ntchito. Ogwiritsa amalembetsa kumeneko ndikugwiritsa ntchito ndalama pazantchito. Pakapita nthawi, ogwiritsa ntchito amasiya. Tikufuna kudziwa kuti ogwiritsa ntchito amakhala nthawi yayitali bwanji, ndi angati omwe amagwa pambuyo pa 1st ndi 2nd mwezi wogwiritsa ntchito ntchitoyi.

Kuti tiyankhe mafunsowa, tifunika kupanga magulu osiyanasiyana malinga ndi mwezi wolembetsa. Tidzayesa ntchito ndi ndalama zomwe timawononga mwezi uliwonse. M'malo mwa ndalama, pangakhale maoda, ndalama zolipirira, kapena ntchito ina iliyonse yotengera nthawi.

Zambiri

Zitsanzo zinapangidwa mu MySQL, koma kwa ma DBMS ena sikuyenera kukhala kusiyana kwakukulu.

Ogwiritsa ntchito - ogwiritsa:

Dzina Lolowera
Tsiku Lolembetsa

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Mtengo wa tebulo - kulipira:

Dzina Lolowera
Date
Chidule

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

Sankhani zonse zolembetsera ndi tsiku lolembetsa:

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

Zotsatira:

Dzina Lolowera
Date
Tsiku Lolembetsa

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

Timamanga magulu pamwezi; kuti tichite izi, timatembenuza masiku onse kukhala miyezi:

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

Tsopano tikuyenera kudziwa kuti ndi miyezi ingati yomwe wogwiritsa ntchitoyo adagwira - uku ndiko kusiyana pakati pa mwezi wa debit ndi mwezi wolembetsa. MySQL ili ndi ntchito PERIOD_DIFF() - kusiyana pakati pa miyezi iwiri. Onjezani PERIOD_DIFF() ku pempho:

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

Dzina Lolowera
BillingMonth
Tsiku Lolembetsa
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

Timawerengera ogwiritsa ntchito mwezi uliwonse - timayika ma rekodi ndi BillingMonth, RegistrationMonth ndi 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

Zotsatira:

UsersCount
BillingMonth
RegistrationMonth
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

Mu Januwale, February ndi Marichi, wogwiritsa ntchito m'modzi watsopano adawonekera aliyense - MonthsDiff = 0. Wogwiritsa ntchito Januware m'modzi anali akugwira ntchito mu February - RegistrationMonth = 2019-01, BillingMonth = 2019-02, ndipo wogwiritsa ntchito m'modzi wa February anali akugwira ntchito mu Marichi.

Mwachibadwa, machitidwe amawonekera bwino mu deta yaikulu.

Momwe mungasamutsire ma cohorts kupita ku Grafana

Taphunzira kupanga magulu amagulu, koma pakakhala zolemba zambiri, sizikhalanso zophweka kuzisanthula. Zolemba zitha kutumizidwa ku Excel ndikupanga matebulo okongola, koma iyi si njira yathu!

Ma Cohorts amatha kuwonetsedwa ngati graph yolumikizana mu grafana.

Kuti tichite izi, timawonjezera funso lina kuti tisinthe deta kukhala mawonekedwe oyenera 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

Ndipo kwezani deta ku Grafana.

Chitsanzo graph kuchokera chidziwitso:

Momwe mungasonkhanitsire magulu ogwiritsa ntchito ngati ma graph mu Grafana [+ docker chithunzi ndi chitsanzo]

Gwirani ndi manja anu:

GitHub yosungirako ndi chitsanzo ndi chithunzi cha docker chokhala ndi MySQL ndi Grafana chomwe mutha kuyendetsa pa kompyuta yanu. Nawonso database ili kale ndi ziwonetsero za chaka chimodzi ndi theka, kuyambira Januware 2018 mpaka Julayi 2019.

Ngati mukufuna, mutha kuyika deta yanu pachithunzichi.

Zolemba za PS zokhudzana ndi kusanthula kwamagulu mu 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

Kuwonjezera ndemanga