Faʻafefea ona aoina tagata faʻaoga e pei o kalafi i Grafana [+ docker image ma faʻataʻitaʻiga]

Le auala na matou foia ai le faʻafitauli o le vaʻavaʻai i vaega o tagata faʻaoga i le Promopult auaunaga e faʻaaoga ai le Grafana.

faalauiloa - o se auaunaga mamana ma se numera tele o tagata faʻaoga. I luga o le 10 tausaga o faʻagaioiga, o le numera o le resitalaina i totonu o le faiga ua sili atu i le tasi miliona. O i latou na feagai ma auaunaga faʻapena latou te iloa o lenei faʻaogaina o tagata faʻaoga e mamao ese mai le tutusa.

Sa sainia e se tasi ma “moe” e faavavau. Ua galo i se tasi le latou upu faataga ma toe resitaraina faalua i totonu o le ono masina. E aumai e se tasi se tupe i le tusi tupe, ae sau se tasi e maua fua mea faifaaili. Ma e manaia le maua o se tupe mama mai tagata uma.

I luga o faʻamaumauga tetele e pei o matou, o le suʻesuʻeina o amioga a se tagata faʻaoga ma le faia o filifiliga laiti e leai se aoga. Ae o le pu'eina o aga ma le galulue faatasi ma vaega tetele e mafai ma talafeagai. O le mea tonu lava lea tatou te faia.

Aotelega

1. O le a le suʻesuʻega faʻapitoa ma aisea e manaʻomia ai?
2. Faʻafefea ona fai faʻaupuga ile masina ole resitalaina ole tagata ile SQL.
3. Fa'afefea ona fesiita'i vaega i tusifana.

Afai ua uma ona e iloa po'o le a le su'esu'ega a le cohort ma pe fa'afefea ona fai ile SQL, fa'ase'e sa'o ile vaega mulimuli.

1. O le a le suʻesuʻega faʻapitoa ma aisea e manaʻomia ai?

O su'esu'ega fa'atasi o se metotia e fa'atatau i le fa'atusatusaina o vaega eseese (cohorts) o tagata fa'aoga. O le tele o taimi, o matou vaega e faʻavaeina i le vaiaso poʻo le masina lea na amata ai e le tagata faʻaoga le auaunaga. Mai iinei o loʻo fuafua ai le olaga o le tagata faʻaoga, ma o se faʻailoga lea e faʻavae ai se suʻesuʻega lavelave e mafai ona faia. Mo se faʻataʻitaʻiga, malamalama:

• fa'afefea ona fa'aaafia e le ala o le mauaina le olaga o le tagata fa'aoga;
• pe fa'afefea ona aafia le olaga atoa i le fa'aogaina o so'o se vaega po'o se auaunaga;
• Fa'afefea le fa'alauiloaina o le vaega X na a'afia ai le olaga fa'atusatusa i le tausaga talu ai.

2. Fa'afefea ona fai fa'atasi i SQL?

O le tele o le tusiga ma le mafaufau masani e le mafai ai ona matou tuʻuina atu a matou faʻamatalaga moni iinei - i le suʻega suʻega, fuainumera mo le tausaga ma le afa: 1200 tagata faʻaoga ma 53 fefaʻatauaiga. Ina ia mafai ona e taʻalo i nei faʻamatalaga, ua matou saunia se ata faʻapipiʻi ma MySQL ma Grafana lea e mafai ai ona e iloa uma oe lava. So'oga ile GitHub ile pito ole tala.

Ma o iinei o le a tatou faʻaalia ai le fausiaina o cohorts e faʻaaoga ai se faʻataʻitaʻiga faigofie.

Sei faapea o loo i ai sa tatou auaunaga. Tagata fa'aoga resitala iina ma fa'aalu tupe i auaunaga. I le aluga o taimi, o tagata e faʻaaogaina. Matou te fia suʻeina pe o le a le umi e ola ai tagata faʻaoga, ma pe toafia i latou e paʻu pe a maeʻa le 1st ma le 2nd masina o le faʻaaogaina o le auaunaga.

Ina ia taliina nei fesili, e manaʻomia ona tatou fausia ni vaega e faʻatatau i le masina o le resitalaina. O le a tatou fuaina gaoioiga e ala i tupe faaalu i masina taitasi. Nai lo tupe faʻaalu, atonu e iai ni oka, totogi o le lesitala, poʻo soʻo se isi gaioiga faʻavae taimi.

Fa'amatalaga muamua

O faʻataʻitaʻiga na faia i MySQL, ae mo isi DBMS e le tatau ona i ai ni eseesega taua.

Fa'aoga laulau - tagata fa'aoga:

userId
Aso Fa'amauina

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Lisi o tau - pili:

userId
aso
Aotelega

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

Filifili uma tagata fa'aaoga tusi-off ma le aso resitala:

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

Taunuʻuga:

userId
aso
Aso Fa'amauina

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

Matou te fausia vaega i masina; ina ia faia lenei mea, matou te faaliliuina aso uma i masina:

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

Ole taimi nei e tatau ona tatou iloa pe fia masina na galue ai le tagata faʻaoga - o le eseesega lea i le va o le masina o aitalafu ma le masina o le resitalaina. MySQL ei ai lana galuega PERIOD_DIFF() - o le eseesega i le va o le lua masina. Fa'aopoopo PERIOD_DIFF() ile talosaga:

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
Aso Fa'amauina
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

Matou te faitauina tagata fa'aoga o lo'o fa'agaoioia i masina ta'itasi - matou te fa'avasegaina fa'amaumauga e BillingMonth, RegistrationMonth ma 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

Taunuʻuga:

UsersCount
BillingMonth
Fa'amauinaMasina
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

Ia Ianuari, Fepuari ma Mati, e tasi le tagata faʻaoga fou na faʻaalia i le taʻitasi - MonthsDiff = 0. E tasi le tagata faʻaoga Ianuari na galue ia Fepuari - RegistrationMonth = 2019-01, BillingMonth = 2019-02, ma le tasi Fepuari na faʻaaogaina ia Mati.

E masani lava, o mamanu e sili atu ona vaʻaia i se seti faʻamaumauga tele.

E faʻafefea ona faʻafeiloaʻi cohorts i Grafana

Ua matou aʻoaʻoina pe faʻapefea ona faʻapipiʻi vaega, ae a tele faʻamaumauga, e le toe faigofie ona suʻesuʻeina. E mafai ona auina atu faʻamaumauga i Excel ma faia i ni laulau matagofie, ae e le o la tatou metotia!

E mafai ona fa'aalia fa'atasi o se kalafi fegalegaleai i totonu tusifana.

Ina ia faia lenei mea, matou te faʻaopoopoina se isi fesili e faʻaliliu ai faʻamatalaga i se faatulagaga talafeagai mo 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 lafo faʻamaumauga i Grafana.

Faataitaiga kalafi mai fa'aaliga:

Pa'i i ou lima:

GitHub faleoloa ma faʻataʻitaʻiga o se faʻailoga faʻapipiʻi ma MySQL ma Grafana e mafai ona e tamoe i luga o lau komepiuta. O loʻo iai i le database faʻamatalaga faʻamatalaga mo le tausaga ma le afa, mai ia Ianuari 2018 ia Iulai 2019.

Afai e te manaʻo ai, e mafai ona e utaina au faʻamatalaga i lenei ata.

PS Mataupu e uiga i suʻesuʻega faʻaputu i SQL:

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

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

puna: www.habr.com