🥇Jinsi ya kukusanya makundi ya watumiaji kwa namna ya grafu katika Grafana [+picha ya kizimbani yenye mfano]

Jinsi tulivyotatua tatizo la kuibua makundi ya watumiaji katika huduma ya Matangazo kwa kutumia Grafana.

Kukuza - huduma yenye nguvu na idadi kubwa ya watumiaji. Zaidi ya miaka 10 ya uendeshaji, idadi ya usajili katika mfumo imezidi milioni moja. Wale ambao wamekutana na huduma zinazofanana wanajua kuwa safu hii ya watumiaji ni mbali na homogeneous.

Mtu alijiandikisha na "akalala" milele. Mtu alisahau nenosiri lake na akasajili mara kadhaa zaidi katika kipindi cha miezi sita. Mtu huleta pesa kwenye rejista ya pesa, na mtu alikuja kwa bure zana. Na itakuwa nzuri kupata faida kutoka kwa kila mtu.

Kwenye seti kubwa za data kama zetu, kuchambua tabia ya mtumiaji binafsi na kufanya maamuzi madogo hakuna maana. Lakini kukamata mwelekeo na kufanya kazi na vikundi vikubwa kunawezekana na ni muhimu. Ambayo ndiyo hasa tunayofanya.

Muhtasari

Uchambuzi wa kikundi ni nini na kwa nini inahitajika?
Jinsi ya kutengeneza vikundi kwa mwezi wa usajili wa watumiaji katika SQL.
Jinsi ya kuhamisha vikundi kwa grafana.

Ikiwa tayari unajua uchanganuzi wa kundi ni nini na jinsi ya kuifanya katika SQL, ruka moja kwa moja hadi sehemu ya mwisho.

1. Uchambuzi wa kikundi ni nini na kwa nini inahitajika?

Uchambuzi wa kundi ni mbinu inayotokana na kulinganisha vikundi tofauti (makundi) ya watumiaji. Mara nyingi, vikundi vyetu huundwa na wiki au mwezi ambao mtumiaji alianza kutumia huduma. Kuanzia hapa maisha ya mtumiaji huhesabiwa, na hii ni kiashiria kwa msingi ambao uchambuzi tata unaweza kufanywa. Kwa mfano, kuelewa:

jinsi njia ya kupata watumiaji inavyoathiri maisha ya mtumiaji;
jinsi matumizi ya kipengele au huduma yoyote huathiri maisha;
Jinsi uzinduzi wa kipengele X ulivyoathiri maisha ikilinganishwa na mwaka jana.

2. Jinsi ya kufanya cohorts katika SQL?

Ukubwa wa makala na akili ya kawaida haituruhusu kuwasilisha data yetu halisi hapa - katika dampo la majaribio, takwimu za mwaka mmoja na nusu: watumiaji 1200 na shughuli 53. Ili uweze kucheza na data hii, tumeandaa picha ya kizimbani na MySQL na Grafana ambayo unaweza kuitumia mwenyewe. Unganisha kwa GitHub mwishoni mwa kifungu.

Na hapa tutaonyesha uundaji wa vikundi kwa kutumia mfano uliorahisishwa.

Wacha tuchukue tunayo huduma. Watumiaji hujiandikisha huko na kutumia pesa kwenye huduma. Baada ya muda, watumiaji huacha. Tunataka kujua muda ambao watumiaji wanaishi, na ni wangapi kati yao hupotea baada ya mwezi wa 1 na wa 2 wa kutumia huduma.

Ili kujibu maswali haya, tunahitaji kuunda vikundi kulingana na mwezi wa usajili. Tutapima shughuli kwa gharama katika kila mwezi. Badala ya gharama, kunaweza kuwa na maagizo, ada za usajili, au shughuli nyingine yoyote inayolingana na wakati.

Data Chanzo

Mifano zilifanywa katika MySQL, lakini kwa DBMS nyingine haipaswi kuwa na tofauti yoyote muhimu.

Jedwali la mtumiaji - watumiaji:

kitambulisho cha mtumiaji
Tarehe ya Usajili

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Jedwali la gharama - bili:

kitambulisho cha mtumiaji
tarehe
Jumla

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

Chagua tarehe zote za kufutwa kwa watumiaji na tarehe ya usajili:

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

Matokeo:

kitambulisho cha mtumiaji
tarehe
Tarehe ya Usajili

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

Tunaunda vikundi kwa mwezi; ili kufanya hivi, tunabadilisha tarehe zote kuwa miezi:

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

Sasa tunahitaji kujua ni miezi mingapi mtumiaji alikuwa amilifu - hii ndiyo tofauti kati ya mwezi wa malipo na mwezi wa usajili. MySQL ina kazi PERIOD_DIFF() - tofauti kati ya miezi miwili. Ongeza PERIOD_DIFF() kwa ombi:

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

kitambulisho cha mtumiaji
BillingMonth
Tarehe ya Usajili
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

Tunahesabu watumiaji walioamilishwa katika kila mwezi - tunapanga rekodi kulingana 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

Matokeo:

UsersCount
BillingMonth
Mwezi wa Usajili
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

Mnamo Januari, Februari na Machi, mtumiaji mmoja mpya alionekana kila mmoja - MonthsDiff = 0. Mtumiaji mmoja wa Januari alitumika mnamo Februari - RegistrationMonth = 2019-01, BillingMonth = 2019-02, na mtumiaji mmoja wa Februari alianza kutumia Machi.

Kwa kawaida, ruwaza zinaonekana vyema katika seti kubwa ya data.

Jinsi ya kuhamisha vikundi kwa Grafana

Tumejifunza jinsi ya kuunda vikundi, lakini kunapokuwa na rekodi nyingi, si rahisi kuzichanganua. Rekodi zinaweza kusafirishwa kwa Excel na kuunda meza nzuri, lakini hii sio njia yetu!

Makundi yanaweza kuonyeshwa kama grafu shirikishi katika grafana.

Ili kufanya hivyo, tunaongeza swali lingine ili kubadilisha data katika muundo unaofaa kwa 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

Na upakie data kwa Grafana.

Mfano wa grafu kutoka demo:

Gusa kwa mikono yako:

GitHub hazina na mfano ni picha ya kizimbani iliyo na MySQL na Grafana ambayo unaweza kuendesha kwenye kompyuta yako. Hifadhidata tayari ina data ya onyesho kwa mwaka mmoja na nusu, kuanzia Januari 2018 hadi Julai 2019.

Ukipenda, unaweza kupakia data yako kwenye picha hii.

Nakala za PS kuhusu uchanganuzi wa kikundi katika SQL:

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

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

Chanzo: mapenzi.com

Jinsi ya kukusanya vikundi vya watumiaji kama grafu kwenye Grafana [+ picha ya kizimbani na mfano]

Muhtasari

1. Uchambuzi wa kikundi ni nini na kwa nini inahitajika?

2. Jinsi ya kufanya cohorts katika SQL?

Data Chanzo

Jinsi ya kuhamisha vikundi kwa Grafana

Kuongeza maoni Kufuta kujibu