Mar a chruinnicheas tu buidhnean luchd-cleachdaidh mar ghrafaichean ann an Grafana [+ ìomhaigh docker le eisimpleir]

Mar a dh’ fhuasgail sinn an duilgheadas a thaobh a bhith a’ faicinn buidhnean de luchd-cleachdaidh san t-seirbheis Promoult a’ cleachdadh Grafana.

adhartachadh - seirbheis chumhachdach le àireamh mhòr de luchd-cleachdaidh. Thairis air na 10 bliadhna de dh'obair, tha an àireamh de chlàraidhean san t-siostam air a dhol thairis air millean. Tha fios aig an fheadhainn a choinnich ri seirbheisean coltach ris gu bheil an raon seo de luchd-cleachdaidh fada bho bhith aon-ghnèitheach.

Chuir cuideigin ainm ris agus “thuit e na chadal” gu bràth. Dhìochuimhnich cuideigin am facal-faire aca agus chlàraich iad grunn thursan thairis air sia mìosan. Bheir cuideigin airgead don chlàr airgid, agus thàinig cuideigin airson freebies ionnsramaidean. Agus bhiodh e math beagan prothaid fhaighinn bhon a h-uile duine.

Air seataichean dàta cho mòr ris an fheadhainn againn, tha e gun fheum mion-sgrùdadh a dhèanamh air giùlan neach-cleachdaidh fa-leth agus mion-cho-dhùnaidhean a dhèanamh. Ach tha e comasach agus riatanach gluasadan a ghlacadh agus obrachadh le buidhnean mòra. Dè dìreach a tha sinn a’ dèanamh.

Geàrr-chunntas

1. Dè a th’ ann am mion-sgrùdadh cohort agus carson a tha feum air?
2. Mar a nì thu buidhnean a rèir mìos de chlàradh luchd-cleachdaidh ann an SQL.
3. Mar a ghluaiseas tu buidhnean gu grafana.

Ma tha fios agad mu thràth dè a th’ ann am mion-sgrùdadh cohort agus mar a nì thu e ann an SQL, leum gu dìreach chun roinn mu dheireadh.

1. Dè a th' ann am mion-sgrùdadh cohort agus carson a tha feum air?

Tha mion-sgrùdadh cohort mar dhòigh stèidhichte air coimeas a dhèanamh eadar diofar bhuidhnean (buidheann) de luchd-cleachdaidh. Mar as trice, bidh na buidhnean againn air an cruthachadh ron t-seachdain no a ’mhìos anns an do thòisich an neach-cleachdaidh a’ cleachdadh na seirbheis. Às an seo tha beatha an neach-cleachdaidh air a thomhas, agus tha seo na chomharradh air am faodar mion-sgrùdadh caran toinnte a dhèanamh. Mar eisimpleir, tuig:

• mar a tha an sianal togail a’ toirt buaidh air beatha neach-cleachdaidh;
• mar a bheir cleachdadh feart no seirbheis sam bith buaidh air beatha;
• Mar a thug foillseachadh feart X buaidh air beatha an taca ris an-uiridh.

2. Ciamar a nì thu cohortan ann an SQL?

Chan eil meud an artaigil agus an t-seagh cumanta a 'leigeil leinn ar fìor dhàta a thaisbeanadh an seo - anns an dump deuchainn, staitistig airson bliadhna gu leth: 1200 neach-cleachdaidh agus 53 malairt. Gus an urrainn dhut cluich leis an dàta seo, tha sinn air ìomhaigh docker ullachadh le MySQL agus Grafana anns am faigh thu eòlas air fhèin. Ceangal gu GitHub aig deireadh an artaigil.

Agus an seo seallaidh sinn cruthachadh bhuidhnean a’ cleachdadh eisimpleir nas sìmplidhe.

Gabhamaid ris gu bheil seirbheis againn. Bidh luchd-cleachdaidh a’ clàradh an sin agus a’ cosg airgead air seirbheisean. Thar ùine, bidh luchd-cleachdaidh a 'fàgail. Tha sinn airson faighinn a-mach dè cho fada ‘s a bhios luchd-cleachdaidh beò, agus cia mheud dhiubh a bhios a’ tuiteam dheth às deidh a’ 1mh agus an 2na mìos de bhith a’ cleachdadh na seirbheis.

Gus na ceistean sin a fhreagairt, feumaidh sinn buidhnean a thogail stèidhichte air a’ mhìos clàraidh. Tomhaisidh sinn gnìomhachd a rèir cosgaisean gach mìos. An àite cosgaisean, dh’ fhaodadh gum bi òrdughan, cìsean ballrachd, no gnìomhachd sam bith eile stèidhichte air ùine.

Fiosrachadh cruaidh

Chaidh na h-eisimpleirean a dhèanamh ann am MySQL, ach airson DBMSan eile cha bu chòir eadar-dhealachaidhean mòra a bhith ann.

Clàr luchd-cleachdaidh - luchd-cleachdaidh:

ID neach-cleachdaidh
Ceann-latha Clàraidh

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Clàr cosgais - cunntas:

ID neach-cleachdaidh
Ceann-là
Suim

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

Tagh a h-uile sgrìobhadh cleachdaiche agus ceann-latha clàraidh:

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

Toradh:

ID neach-cleachdaidh
Ceann-là
Ceann-latha Clàraidh

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

Bidh sinn a’ togail bhuidhnean a rèir mìos; gus seo a dhèanamh, bidh sinn ag atharrachadh a h-uile ceann-latha gu mìosan:

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

A-nis feumaidh fios a bhith againn cia mheud mìos a bha an neach-cleachdaidh gnìomhach - is e seo an diofar eadar mìos an fhiachan agus mìos a 'chlàraidh. Tha gnìomh aig MySQL PERIOD_DIFF () - an diofar eadar dà mhìos. Cuir PERIOD_DIFF() ris an iarrtas:

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

ID neach-cleachdaidh
Mìos bileachaidh
Ceann-latha Clàraidh
Mìosan Diff

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

Bidh sinn a’ cunntadh an luchd-cleachdaidh a chaidh an gnìomh gach mìos - bidh sinn a’ cruinneachadh nan clàran le BillingMonth, RegistrationMonth agus 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

Toradh:

Cunntas Cleachdaichean
Mìos bileachaidh
Mìos Clàraidh
Mìosan Diff

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

Anns an Fhaoilleach, sa Ghearran agus sa Mhàrt, nochd aon neach-cleachdaidh ùr gach fear - MonthsDiff = 0. Bha aon neach-cleachdaidh Faoilleach gnìomhach sa Ghearran - RegistrationMonth = 2019-01, BillingMonth = 2019-02, agus bha aon neach-cleachdaidh sa Ghearran gnìomhach sa Mhàrt.

Gu nàdarra, tha pàtrain rim faicinn nas fheàrr ann an seata dàta mòr.

Mar a ghluaiseas tu buidhnean gu Grafana

Tha sinn air ionnsachadh mar a chruthaicheas sinn buidhnean, ach nuair a tha tòrr chlàran ann, chan eil e furasta an sgrùdadh tuilleadh. Faodar clàran a chuir a-mach gu Excel agus an cruthachadh ann an clàran brèagha, ach chan e seo an dòigh againn!

Faodar cohortan a shealltainn mar ghraf eadar-ghnìomhach ann an grafana.

Gus seo a dhèanamh, cuiridh sinn ceist eile ris gus an dàta a thionndadh gu cruth a tha iomchaidh airson 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

Agus luchdaich suas an dàta gu Grafana.

Eisimpleir graf bho demo:

Dèan suathadh le do làmhan:

Stòr GitHub le eisimpleir na ìomhaigh docker le MySQL agus Grafana as urrainn dhut a ruith air do choimpiutair. Anns an stòr-dàta mu thràth tha dàta demo airson bliadhna gu leth, bhon Fhaoilleach 2018 chun Iuchar 2019.

Ma thogras tu, faodaidh tu an dàta agad a luchdachadh a-steach don ìomhaigh seo.

Artaigilean PS mu mhion-sgrùdadh cohort ann an 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