🥇 Otu esi achịkọta ndị ọrụ n'ụdị eserese na Grafana [+docker na ihe atụ]

Otu anyị siri dozie nsogbu nke ịhụta ndị otu ndị ọrụ na ọrụ Promopult site na iji Grafana.

kwalite - ọrụ dị ike nke nwere ọnụ ọgụgụ buru ibu nke ndị ọrụ. N'ime afọ 10 nke arụ ọrụ, ọnụ ọgụgụ nke ndebanye aha na usoro ahụ karịrị otu nde. Ndị ahụ zutere ọrụ ndị yiri ya maara na nke a n'usoro nke ndị ọrụ adịchaghị otu.

Onye debanyere aha wee "daa n'ụra" ruo mgbe ebighị ebi. Otu onye chefuru paswọọdụ ha wee debanye aha di na nwunye ugboro ugboro n'ime ọnwa isii. Onye na-ebute ego n'akwụkwọ ndekọ ego, onye bịara maka freebies ngwa. Ọ ga-adịkwa mma ịnweta uru n'aka onye ọ bụla.

Na nnukwu data dị ka nke anyị, nyochaa omume nke onye ọrụ na ime obere mkpebi enweghị isi. Ma ijide ọnọdụ na ịrụ ọrụ na nnukwu ìgwè dị iche iche ga-ekwe omume ma dị mkpa. Nke bụ kpọmkwem ihe anyị na-eme.

Nchịkọta

Kedu ihe nyocha ndị otu na gịnị kpatara ya?
Otu esi eme ndị otu n'ime ọnwa nke ndebanye aha onye ọrụ na SQL.
Otu esi ebufe ndị otu na Grafana.

Ọ bụrụ na ị maralarị ihe nyocha otu ndị otu na otu esi eme ya na SQL, mafee ozugbo na ngalaba ikpeazụ.

1. Gịnị bụ nyocha ndị otu na gịnị kpatara ya?

Nyocha ndị otu bụ usoro dabere na atụnyere otu dị iche iche (otu) nke ndị ọrụ. Ọtụtụ mgbe, a na-etolite otu anyị site n'izu ma ọ bụ ọnwa onye ọrụ malitere iji ọrụ ahụ. Site na ebe a na-agbakọ oge ndụ onye ọrụ, nke a bụ ihe na-egosi na ndabere nke enwere ike ịme nyocha dị mgbagwoju anya. Dịka ọmụmaatụ, ghọta:

kedu ka ọwa nnweta si emetụta ndụ onye ọrụ;
ka ojiji nke njirimara ma ọ bụ ọrụ ọ bụla si emetụta oge ndụ;
Kedu ka mmalite nke atụmatụ X si metụta ndụ ndụ ma e jiri ya tụnyere afọ gara aga.

2. Kedu otu esi eme ndị otu na SQL?

The size nke isiokwu na ezi uche adịghị ekwe ka anyị na-ewetara anyị ezigbo data ebe a - na ule mkpofu, ọnụ ọgụgụ maka otu afọ na ọkara: 1200 ọrụ na 53 azụmahịa. Ka ị nwee ike iji data a gwuo egwu, anyị akwadola ihe onyonyo docker na MySQL na Grafana nke ị nwere ike nweta ya n'onwe gị. Njikọ na GitHub na njedebe nke akụkọ.

Na ebe a, anyị ga-egosi okike nke ndị otu na-eji ihe atụ dị mfe.

Ka anyị were na anyị nwere ọrụ. Ndị ọrụ debanyere aha ebe ahụ wee na-emefu ego na ọrụ. Ka oge na-aga, ndị ọrụ kwụsịrị. Anyị chọrọ ịchọpụta ogologo oge ndị ọrụ na-adị ndụ, na ole n'ime ha dara mgbe ọnwa 1st na nke abụọ nke iji ọrụ ahụ gasịrị.

Iji zaa ajụjụ ndị a, anyị kwesịrị iwulite ndị otu na-adabere na ọnwa ndebanye aha. Anyị ga-atụle ọrụ site na mmefu n'ọnwa ọ bụla. Kama mmefu, enwere ike ịnwe iwu, ụgwọ ndenye aha, ma ọ bụ ọrụ ọ bụla dabere na oge.

Nkọ data

Emere ihe atụ ndị a na MySQL, mana maka DBMS ndị ọzọ ekwesighi inwe nnukwu ọdịiche ọ bụla.

Tebụl onye ọrụ - ndị ọrụ:

Njirimara Onye Ọrụ
Ụbọchị ndebanye aha

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tebụl ọnụ - ịgba ụgwọ:

Njirimara Onye Ọrụ
Ụbọchị
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

Họrọ ụbọchị ndebanye aha onye ọrụ niile:

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

Nsonaazụ:

Njirimara Onye Ọrụ
Ụbọchị
Ụbọchị ndebanye aha

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

Anyị na-ewu ndị otu n'ọnwa; ime nke a, anyị na-atụgharị ụbọchị niile ka ọ bụrụ ọnwa:

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

Ugbu a, anyị kwesịrị ịma ọnwa ole onye ọrụ na-arụ ọrụ - nke a bụ ọdịiche dị n'etiti ọnwa nke debit na ọnwa nke ndebanye aha. MySQL nwere ọrụ PERIOD_DIFF() - ọdịiche dị n'etiti ọnwa abụọ. Tinye PERIOD_DIFF() na arịrịọ:

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

Njirimara Onye Ọrụ
Ọnwa ịgba ụgwọ
Ụbọchị ndebanye aha
Ọnwa dị iche

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

Anyị na-agụ ndị ọrụ na-arụ ọrụ n'ime ọnwa ọ bụla - anyị na-achịkọta ndekọ ahụ site na BillingMonth, RegistrationMonth na 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

Nsonaazụ:

Ndị ọrụ Count
Ọnwa ịgba ụgwọ
Ọnwa ndebanye aha
Ọnwa dị iche

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

Na Jenụwarị, Febụwarị na Maachị, otu onye ọrụ ọhụrụ pụtara nke ọ bụla - MonthsDiff = 0. Otu onye ọrụ Jenụwarị nọ na-arụ ọrụ na February - RegistrationMonth = 2019-01, BillingMonth = 2019-02, na otu onye ọrụ February nọ na-arụ ọrụ na Maachị.

Dị ka o kwesịrị ịdị, a na-ahụ ụkpụrụ ka mma na nnukwu data data.

Otu esi ebufe ndị otu na Grafana

Anyị amụtala ka e si etolite otu, ma mgbe enwere ọtụtụ ndekọ, ọ naghị adịkwa mfe nyocha ha. Enwere ike ibuga ihe ndekọ na Excel wee guzobe ya na tebụl mara mma, mana nke a abụghị usoro anyị!

Enwere ike igosi otu ndị otu dị ka eserese mmekọrịta Grafana.

Iji mee nke a, anyị na-agbakwunye ajụjụ ọzọ iji tọghata data ka ọ bụrụ usoro kwesịrị ekwesị maka 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

Ma bulite data na Grafana.

Ihe atụ eserese si ngosi:

Jiri aka gị metụ:

Ebe nchekwa GitHub nwere ọmụmaatụ bụ ihe oyiyi docker na MySQL na Grafana nke ị nwere ike ịgba ọsọ na kọmputa gị. Ebe nchekwa data nwere data ngosi maka otu afọ na ọkara, site na Jenụwarị 2018 ruo Julaị 2019.

Ọ bụrụ na-ịchọrọ, ịnwere ike ibunye data gị n'ime onyonyo a.

Edemede PS gbasara nyocha ndị otu na SQL:

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

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

isi: www.habr.com

Otu esi achịkọta ndị ọrụ dị ka eserese na Grafana [+ docker image with example]

Nchịkọta

1. Gịnị bụ nyocha ndị otu na gịnị kpatara ya?

2. Kedu otu esi eme ndị otu na SQL?

Nkọ data

Otu esi ebufe ndị otu na Grafana

Tinye a comment Отменить ответ