ProHoster > Blog > Administrasi > Kumaha cara ngumpulkeun cohorts pangguna salaku grafik dina Grafana [+ gambar docker sareng conto]

Kumaha cara ngumpulkeun cohorts pangguna salaku grafik dina Grafana [+ gambar docker sareng conto]

Kumaha cara ngumpulkeun cohorts pangguna salaku grafik dina Grafana [+ gambar docker sareng conto]

Kumaha urang ngajawab masalah visualizing cohorts pamaké dina layanan Promopult maké Grafana.

ngamajukeun - jasa anu kuat sareng sajumlah ageung pangguna. Salila 10 taun operasi, jumlah pendaptaran dina sistem geus ngaleuwihan hiji juta. Anu parantos mendakan jasa anu sami terang yén Asép Sunandar Sunarya ieu tebih tina homogen.

Aya anu ngadaptarkeun sareng "saré" salamina. Aya anu hilap kecap koncina sareng ngadaptarkeun sababaraha kali deui salami genep bulan. Aya anu mawa artos ka kasir, sareng aya anu sumping gratis alat-alat. Sarta eta bakal hade ka meunang sababaraha kauntungan ti dulur.

Dina set data ageung sapertos urang, nganalisa paripolah pangguna individu sareng ngadamel kaputusan mikro henteu aya gunana. Tapi catching tren jeung gawé bareng grup badag nyaéta dimungkinkeun sarta diperlukeun. Nu kahayang urang ngalakukeun.

Ringkesan

  1. Naon analisis cohort sareng naha éta diperyogikeun?
  2. Kumaha cara ngadamel cohorts ku bulan pendaptaran pangguna dina SQL.
  3. Kumaha nransper cohorts ka grafana.

Upami anjeun parantos terang naon analisa cohort sareng kumaha cara ngalakukeunana dina SQL, angkat langsung ka bagian anu terakhir.

1. Naon ari analisis kohort jeung naha éta diperlukeun?

Analisis kohort nyaéta métode dumasar kana ngabandingkeun grup béda (cohorts) pamaké. Seringna, grup kami dibentuk ku minggu atanapi bulan dimana pangguna mimiti nganggo jasa éta. Ti dieu hirupna pamaké diitung, sarta ieu mangrupa indikator dina dasar nu analisis rada kompléks bisa dilaksanakeun. Contona, ngartos:

  • kumaha saluran akuisisi mangaruhan hirupna pamaké;
  • kumaha pamakean fitur atanapi jasa naon waé mangaruhan umur hirup;
  • kumaha peluncuran fitur X mangaruhan hirupna dibandingkeun taun ka tukang.

2. Kumaha carana ngadamel cohorts dina SQL?

Ukuran artikel jeung akal sehat teu ngidinan urang nampilkeun data nyata urang di dieu - dina dump test, statistik keur sataun satengah: 1200 pamaké sarta 53 transaksi. Janten anjeun tiasa maénkeun data ieu, kami parantos nyiapkeun gambar docker sareng MySQL sareng Grafana dimana anjeun tiasa ngalaman éta nyalira. Tautan ka GitHub dina tungtung tulisan.

Sareng di dieu urang bakal nunjukkeun kreasi cohorts nganggo conto saderhana.

Anggap urang boga jasa. Pamaké ngadaptar di dinya sarta méakkeun duit kana jasa. Kana waktu, pamaké kaluar. Kami hoyong terang sabaraha lami pangguna hirup, sareng sabaraha seueur aranjeunna murag saatos bulan ka-1 sareng ka-2 ngagunakeun jasa éta.

Pikeun ngajawab patarosan ieu, urang kedah ngawangun cohorts dumasar kana bulan pendaptaran. Urang bakal ngukur aktivitas ku expenses dina unggal bulan. Gantina waragad, bisa jadi aya pesenan, waragad langganan, atawa kagiatan dumasar-waktu lianna.

Sumber data

Conto-conto éta dilakukeun dina MySQL, tapi pikeun DBMS séjén henteu kedah aya béda anu signifikan.

Méja pamaké - pamaké:

ID Pangguna
Tanggal Pendaptaran

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabel biaya - tagihan:

ID Pangguna
tanggal
Jumlah

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

Pilih sadaya pangaluaran pangguna sareng tanggal pendaptaran:

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

Hasil:

ID Pangguna
tanggal
Tanggal Pendaptaran

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

Urang ngawangun cohorts dumasar bulan; pikeun ngalakukeun ieu, urang ngarobih sadaya tanggal janten bulan:

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

Ayeuna urang kedah terang sabaraha bulan pangguna aktip - ieu mangrupikeun bédana antara bulan debit sareng bulan pendaptaran. MySQL boga fungsi PERIOD_DIFF () - bédana antara dua bulan. Tambahkeun PERIOD_DIFF() kana pamundut:

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 Pangguna
BillingMonth
Tanggal Pendaptaran
BulanDiff

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

Kami ngitung pangguna anu diaktipkeun dina unggal bulan - kami ngagolongkeun rékaman ku BillingMonth, RegistrationMonth sareng 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

Hasil:

UsersCount
BillingMonth
Bulan Pendaptaran
BulanDiff

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

Dina Januari, Pébruari jeung Maret, hiji pamaké anyar mucunghul unggal - MonthsDiff = 0. Hiji pamaké Januari aktip dina bulan Pebruari - RegistrationMonth = 2019-01, BillingMonth = 2019-02, sarta hiji pamaké Pébruari aktip dina Maret.

Alami, pola anu hadé katingali dina set data badag.

Kumaha cara nransper cohorts ka Grafana

Kami geus diajar kumaha carana ngawangun cohorts, tapi lamun aya loba rékaman, éta euweuh gampang pikeun nganalisis aranjeunna. Rékaman tiasa diékspor ka Excel sareng ngawujud kana tabel anu saé, tapi ieu sanés metode kami!

Cohorts tiasa ditingalikeun salaku grafik interaktif dina grafana.

Jang ngalampahkeun ieu, urang tambahkeun pamundut sejen pikeun ngarobah data kana format cocog pikeun 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

Sareng unggah data ka Grafana.

Conto grafik ti démo:

Kumaha cara ngumpulkeun cohorts pangguna salaku grafik dina Grafana [+ gambar docker sareng conto]

Toel ku leungeun anjeun:

Repository GitHub sareng conto mangrupikeun gambar docker sareng MySQL sareng Grafana anu anjeun tiasa jalankeun dina komputer anjeun. Pangkalan data parantos ngandung data demo salami sataun satengah, ti Januari 2018 dugi ka Juli 2019.

Upami anjeun hoyong, anjeun tiasa ngamuat data anjeun kana gambar ieu.

Artikel PS ngeunaan analisis kohort dina SQL:

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

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

sumber: www.habr.com

Tambahkeun komentar