Bii o ṣe le gba awọn akojọpọ olumulo bi awọn aworan ni Grafana [+ aworan docker pẹlu apẹẹrẹ]

Bii a ṣe yanju iṣoro ti wiwo awọn akojọpọ awọn olumulo ninu iṣẹ Promopult nipa lilo Grafana.

igbega - iṣẹ agbara pẹlu nọmba nla ti awọn olumulo. Lori awọn ọdun 10 ti iṣẹ, nọmba awọn iforukọsilẹ ninu eto naa ti kọja milionu kan. Awọn ti o ti pade awọn iṣẹ ti o jọra mọ pe ọpọlọpọ awọn olumulo ko jina si isokan.

Ẹnikan forukọsilẹ ati “sun oorun” lailai. Ẹnikan ti gbagbe ọrọ igbaniwọle wọn ati forukọsilẹ awọn tọkọtaya diẹ sii ni akoko oṣu mẹfa. Ẹnikan mu owo wá si owo iforukọsilẹ, ati ẹnikan wá fun Ofe Irinse. Ati pe yoo dara lati gba èrè diẹ lati ọdọ gbogbo eniyan.

Lori iru awọn eto data nla bi tiwa, itupalẹ ihuwasi ti olumulo kọọkan ati ṣiṣe awọn ipinnu bulọọgi jẹ asan. Ṣugbọn mimu awọn aṣa ati ṣiṣẹ pẹlu awọn ẹgbẹ nla ṣee ṣe ati pataki. Eyi ti o jẹ gangan ohun ti a ṣe.

Akopọ

1. Kini itupalẹ ẹgbẹ ati kilode ti o nilo?
2. Bii o ṣe le ṣe awọn akojọpọ nipasẹ oṣu ti iforukọsilẹ olumulo ni SQL.
3. Bii o ṣe le gbe awọn ẹgbẹ lọ si Grafana.

Ti o ba ti mọ tẹlẹ kini itupalẹ ẹgbẹ jẹ ati bii o ṣe le ṣe ni SQL, fo taara si apakan ti o kẹhin.

1. Kini itupalẹ ẹgbẹ ati kilode ti o nilo?

Itupalẹ ẹgbẹ jẹ ọna ti o da lori ifiwera awọn ẹgbẹ oriṣiriṣi (awọn ẹgbẹ) ti awọn olumulo. Nigbagbogbo, awọn ẹgbẹ wa ni idasile nipasẹ ọsẹ tabi oṣu ti olumulo bẹrẹ lilo iṣẹ naa. Lati ibi igbesi aye olumulo jẹ iṣiro, ati pe eyi jẹ itọkasi lori ipilẹ eyiti o le ṣe itupalẹ eka kuku. Fun apẹẹrẹ, ni oye:

• bawo ni ikanni imudani ṣe ni ipa lori igbesi aye olumulo;
• bawo ni lilo eyikeyi ẹya tabi iṣẹ ṣe ni ipa lori igbesi aye;
• Bawo ni ifilọlẹ ẹya X ṣe kan igbesi aye ni akawe si ọdun to kọja.

2. Bawo ni lati ṣe awọn ẹgbẹ ni SQL?

Iwọn ti nkan naa ati oye ti o wọpọ ko gba wa laaye lati ṣafihan data gidi wa nibi - ni idalenu idanwo, awọn iṣiro fun ọdun kan ati idaji: awọn olumulo 1200 ati awọn iṣowo 53. Ki o le mu ṣiṣẹ pẹlu data yii, a ti pese aworan docker kan pẹlu MySQL ati Grafana ninu eyiti o le ni iriri gbogbo rẹ funrararẹ. Ọna asopọ si GitHub ni ipari nkan naa.

Ati pe nibi a yoo ṣe afihan ẹda ti awọn ẹgbẹ ni lilo apẹẹrẹ ti o rọrun.

Jẹ ki a ro pe a ni iṣẹ kan. Awọn olumulo forukọsilẹ nibẹ ati lo owo lori awọn iṣẹ. Lori akoko, awọn olumulo silẹ jade. A fẹ lati wa bi o ṣe gun awọn olumulo laaye, ati melo ni wọn ṣubu lẹhin oṣu 1st ati 2nd ti lilo iṣẹ naa.

Láti dáhùn àwọn ìbéèrè wọ̀nyí, a ní láti kọ àwọn ẹgbẹ́ àwùjọ tí ó dá lórí oṣù ìforúkọsílẹ̀. A yoo ṣe iwọn iṣẹ ṣiṣe nipasẹ awọn inawo ni oṣu kọọkan. Dipo awọn inawo, awọn aṣẹ le wa, awọn idiyele ṣiṣe alabapin, tabi iṣẹ ṣiṣe orisun-akoko eyikeyi miiran.

Orisun orisun

Awọn apẹẹrẹ ni a ṣe ni MySQL, ṣugbọn fun awọn DBMS miiran ko yẹ ki o jẹ awọn iyatọ pataki eyikeyi.

Tabili olumulo - awọn olumulo:

Idanimọ olumulo
Ọjọ Iforukọsilẹ

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabili idiyele - ìdíyelé:

Idanimọ olumulo
ọjọ
Akopọ

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

Yan gbogbo awọn ifilọlẹ olumulo ati ọjọ iforukọsilẹ:

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

Esi:

Idanimọ olumulo
ọjọ
Ọjọ Iforukọsilẹ

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

A kọ awọn ẹgbẹ ni oṣu; lati ṣe eyi, a yi gbogbo awọn ọjọ pada si awọn oṣu:

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

Bayi a nilo lati mọ iye oṣu ti olumulo n ṣiṣẹ - eyi ni iyatọ laarin oṣu ti debiti ati oṣu ti iforukọsilẹ. MySQL ni iṣẹ kan PERIOD_DIFF() - iyatọ laarin osu meji. Ṣafikun PERIOD_DIFF() si ibeere naa:

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

Idanimọ olumulo
Osù ìdíyelé
Ọjọ Iforukọsilẹ
OsuDiff

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

A ka awọn olumulo ti o ṣiṣẹ ni oṣu kọọkan - a ṣe akojọpọ awọn igbasilẹ nipasẹ BillingMonth, Osu Iforukọsilẹ ati Awọn oṣuDiff:

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

Esi:

Awọn olumuloCount
Osù ìdíyelé
Osu Iforukọsilẹ
OsuDiff

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

Ni Oṣu Kini, Kínní ati Oṣu Kẹta, olumulo tuntun kan han kọọkan - MonthsDiff = 0. Olumulo January kan nṣiṣẹ lọwọ ni Kínní - RegistrationMonth = 2019-01, BillingMonth = 2019-02, ati pe olumulo Kínní kan nṣiṣẹ lọwọ ni Oṣu Kẹta.

Nipa ti, awọn ilana jẹ dara julọ han ni ipilẹ data nla kan.

Bii o ṣe le gbe awọn ẹgbẹ lọ si Grafana

A ti kọ ẹkọ bi a ṣe le ṣe awọn ẹgbẹ ẹgbẹ, ṣugbọn nigbati ọpọlọpọ awọn igbasilẹ ba wa, ko rọrun lati ṣe itupalẹ wọn. Awọn igbasilẹ le ṣe okeere si Tayo ati ṣẹda sinu awọn tabili lẹwa, ṣugbọn eyi kii ṣe ọna wa!

Awọn ẹgbẹ le ṣe afihan bi aworan ibaraenisepo ninu Grafana.

Lati ṣe eyi, a ṣafikun ibeere miiran lati yi data pada si ọna kika ti o yẹ fun 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

Ati gbejade data naa si Grafana.

Apeere awonya lati demo:

Fi ọwọ kan pẹlu ọwọ rẹ:

Ibi ipamọ GitHub pẹlu apẹẹrẹ jẹ aworan docker pẹlu MySQL ati Grafana ti o le ṣiṣẹ lori kọnputa rẹ. Ipamọ data ti ni data demo tẹlẹ fun ọdun kan ati idaji, lati Oṣu Kini ọdun 2018 si Oṣu Keje ọdun 2019.

Ti o ba fẹ, o le gbe data rẹ sinu aworan yii.

Awọn nkan PS nipa itupalẹ ẹgbẹ ni SQL:

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

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

orisun: www.habr.com