Conas cohóirt úsáideoirí a bhailiú mar ghraif i Grafana [+ íomhá docker le sampla]

An chaoi ar réitigh muid an fhadhb a bhaineann le cohóirt úsáideoirí sa tseirbhís Promopul a fheiceáil ag baint úsáide as Grafana.

a chur chun cinn - seirbhís chumhachtach le líon mór úsáideoirí. Thar na 10 mbliana oibriúcháin, tá líon na gclárúchán sa chóras níos mó ná milliún. Tá a fhios acu siúd a tháinig trasna ar sheirbhísí comhchosúla go bhfuil an raon úsáideoirí seo i bhfad ó aonchineálach.

Chláraigh duine éigin agus "thit sé ina chodladh" go deo. Rinne duine éigin dearmad ar a bpasfhocal agus chláraigh siad cúpla uair níos mó thar sé mhí. Tugann duine éigin airgead chuig an gclár airgid, agus tháinig duine éigin le haghaidh earraí in aisce ionstraimí. Agus bheadh ​​​​sé go deas brabús éigin a fháil ó gach duine.

Maidir le tacair sonraí chomh mór agus atá againne, ní haon tairbhe é anailís a dhéanamh ar iompar úsáideora aonair agus micrea-chinntí a dhéanamh. Ach is féidir agus is gá treochtaí a ghlacadh agus oibriú le grúpaí móra. Cé acu go díreach cad a dhéanaimid.

Achoimre

1. Cad is anailís chohórt ann agus cén fáth a bhfuil gá léi?
2. Conas cohóirt a dhéanamh de réir míosa clárúcháin úsáideora i SQL.
3. Conas cohóirt a aistriú chuig grafana.

Má tá a fhios agat cheana féin cad is anailís chohóirt ann agus conas é a dhéanamh i SQL, téigh díreach chuig an gcuid dheireanach.

1. Cad is anailís chohórt ann agus cén fáth a bhfuil gá léi?

Is modh é anailís chohórt atá bunaithe ar ghrúpaí éagsúla (cohóirt) úsáideoirí a chur i gcomparáid. Go minic, cruthaítear ár ngrúpaí de réir na seachtaine nó na míosa inar thosaigh an t-úsáideoir ag úsáid na seirbhíse. Ón áit seo ríomhtar saolré an úsáideora, agus is táscaire é seo ar a mbonn is féidir anailís sách casta a dhéanamh. Mar shampla, tuig:

• conas a théann an cainéal fála i bhfeidhm ar shaolré an úsáideora;
• conas a théann úsáid aon ghné nó seirbhíse i bhfeidhm ar shaolré;
• Mar a chuir seoladh ghné X isteach ar an saolré i gcomparáid leis an mbliain seo caite.

2. Conas cohóirt a dhéanamh i SQL?

Ní cheadaíonn méid an ailt agus an chiall choiteann dúinn ár bhfíor-shonraí a chur i láthair anseo - sa dumpáil tástála, staitisticí ar feadh bliana go leith: 1200 úsáideoir agus 53 idirbheart. Ionas gur féidir leat imirt leis na sonraí seo, tá íomhá docker ullmhaithe againn le MySQL agus Grafana inar féidir leat taithí a fháil air go léir tú féin. Nasc le GitHub ag deireadh an ailt.

Agus anseo taispeánfaimid cruthú cohóirt ag baint úsáide as sampla simplithe.

Glacaimis leis go bhfuil seirbhís againn. Cláraíonn úsáideoirí ansin agus caitheann siad airgead ar sheirbhísí. Le himeacht ama, scaoileann úsáideoirí amach. Ba mhaith linn a fháil amach cé chomh fada is a mhaireann úsáideoirí, agus cé mhéad acu a thiteann amach tar éis an 1ú agus an 2ú mí ó úsáid na seirbhíse.

Chun na ceisteanna seo a fhreagairt, ní mór dúinn cohóirt a thógáil bunaithe ar mhí an chláraithe. Déanfaimid gníomhaíocht a thomhas de réir speansais i ngach mí. In ionad speansais, d’fhéadfadh go mbeadh orduithe, táillí síntiúis, nó aon ghníomhaíocht eile bunaithe ar am.

Sonraí tosaigh

Rinneadh na samplaí i MySQL, ach níor cheart go mbeadh aon difríochtaí suntasacha ann do DBMSanna eile.

Tábla úsáideora - úsáideoirí:

ID Úsáideora
Dáta Clárúcháin

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tábla costais - billeáil:

ID Úsáideora
dáta
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

Roghnaigh gach díscríobh úsáideora agus dáta clárúcháin:

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

Toradh:

ID Úsáideora
dáta
Dáta Clárúcháin

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

Tógann muid cohóirt de réir míosa; chun é seo a dhéanamh, déanaimid na dátaí go léir a thiontú ina míonna:

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

Anois ní mór dúinn a fháil amach cé mhéad mí a bhí an t-úsáideoir gníomhach - is é seo an difríocht idir mí an dochair agus an mhí clárúcháin. Tá feidhm ag MySQL PERIOD_DIFF() - an difríocht idir dhá mhí. Cuir PERIOD_DIFF() leis an iarratas:

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 Úsáideora
Mí Billeála
Dáta Clárúcháin
MíonnaDiff

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

Déanaimid na húsáideoirí a chuirtear i ngníomh a chomhaireamh i ngach mí - déanaimid na taifid a ghrúpáil de réir 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:

Líon Úsáideoirí
Mí Billeála
ClárúMí
MíonnaDiff

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

I mí Eanáir, Feabhra agus Márta, bhí úsáideoir nua amháin le feiceáil gach ceann - MonthsDiff = 0. Bhí úsáideoir amháin Eanáir gníomhach i mí Feabhra - RegistrationMonth = 2019-01, BillingMonth = 2019-02, agus bhí úsáideoir Feabhra amháin gníomhach i mí an Mhárta.

Ar ndóigh, tá patrúin le feiceáil níos fearr i dtacar mór sonraí.

Conas cohóirt a aistriú go Grafana

Táimid tar éis foghlaim conas cohóirt a fhoirmiú, ach nuair a bhíonn go leor taifead ann, ní bhíonn sé éasca anailís a dhéanamh orthu a thuilleadh. Is féidir taifid a onnmhairiú go Excel agus iad a fhoirmiú i dtáblaí áille, ach ní hé seo ár modh!

Is féidir cohóirt a thaispeáint mar ghraf idirghníomhach i grafana.

Chun seo a dhéanamh, cuirimid ceist eile leis chun na sonraí a thiontú go formáid atá oiriúnach do 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 na sonraí a uaslódáil chuig Grafana.

Graf samplach ó taispeána:

Déan teagmháil le do lámha:

Stór GitHub le sampla íomhá docker le MySQL agus Grafana is féidir leat a rith ar do ríomhaire. Tá sonraí taispeána ar feadh bliana go leith sa bhunachar sonraí cheana féin, ó Eanáir 2018 go dtí Iúil 2019.

Más mian leat, is féidir leat do shonraí a luchtú isteach san íomhá seo.

Ailt PS faoi anailís chohórt in SQL:

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

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

Foinse: will.com