ProHoster > Π‘Π»ΠΎΠ³ > Administrasi > Cara ngumpulake kohort pangguna minangka grafik ing Grafana [+ gambar docker kanthi conto]

Cara ngumpulake kohort pangguna minangka grafik ing Grafana [+ gambar docker kanthi conto]

Cara ngumpulake kohort pangguna minangka grafik ing Grafana [+ gambar docker kanthi conto]

Kepiye carane ngatasi masalah visualisasi kohort pangguna ing layanan Promopult nggunakake Grafana.

ningkatakΓ© - layanan kuat karo nomer akeh kedhaftar. Swara 10 taun operasi, jumlah registrasi ing sistem wis ngluwihi siji yuta. Wong-wong sing wis nemoni layanan sing padha ngerti manawa macem-macem pangguna iki adoh saka homogen.

Ana sing mlebu lan "tidur" ing salawas-lawase. Ana sing lali sandi lan ndhaptar kaping pindho sajrone nem wulan. Ana sing nggawa dhuwit menyang kasir, lan ana sing teka kanthi gratis instrumen. Lan luwih becik entuk bathi saka kabeh wong.

Ing set data gedhe kaya kita, nganalisa prilaku pangguna individu lan nggawe keputusan mikro ora ana gunane. Nanging keno tren lan nggarap kelompok gedhe bisa lan perlu. Kang persis apa kita nindakake.

Ringkesan

  1. Apa analisis kohort lan kenapa perlu?
  2. Cara nggawe kohort miturut sasi registrasi pangguna ing SQL.
  3. Cara nransfer kohort menyang Grafana.

Yen sampeyan wis ngerti apa analisis kohort lan carane nindakake ing SQL, skip langsung menyang bagean pungkasan.

1. Apa analisis kohort lan kenapa perlu?

Analisis kohort minangka cara adhedhasar mbandhingake macem-macem klompok (cohorts) pangguna. Paling asring, klompok kita dibentuk ing minggu utawa wulan nalika pangguna wiwit nggunakake layanan kasebut. Saka kene umur pangguna diwilang, lan iki minangka indikator sing adhedhasar analisis sing rada rumit bisa ditindakake. Contone, ngerti:

  • carane saluran akuisisi mengaruhi umur pangguna;
  • carane nggunakake sembarang fitur utawa layanan mengaruhi umur;
  • Kepiye peluncuran fitur X mengaruhi umur dibandhingake taun kepungkur.

2. Kepiye carane nggawe kohort ing SQL?

Ukuran artikel lan akal sehat ora ngidini kita nampilake data nyata ing kene - ing dump test, statistik kanggo setahun lan setengah: 1200 pangguna lan 53 transaksi. Supaya sampeyan bisa muter karo data iki, kita wis nyiapake gambar docker karo MySQL lan Grafana sing bisa nemu kabeh dhewe. Link menyang GitHub ing pungkasan artikel.

Lan ing kene kita bakal nuduhake nggawe kohort nggunakake conto sing disederhanakake.

Ayo nganggep kita duwe layanan. Pangguna ndhaptar ana lan mbuwang dhuwit kanggo layanan. Sajrone wektu, pangguna mandheg. Kita pengin ngerti suwene pangguna urip, lan pira sing tiba sawise 1st lan 2nd sasi nggunakake layanan kasebut.

Kanggo njawab pitakonan kasebut, kita kudu mbangun kohort adhedhasar sasi registrasi. Kita bakal ngukur aktivitas kanthi biaya saben wulan. Tinimbang biaya, bisa uga ana pesenan, biaya langganan, utawa kegiatan adhedhasar wektu liyane.

Data sumber

Conto kasebut digawe ing MySQL, nanging kanggo DBMS liyane ora ana bedane sing signifikan.

Tabel pangguna - pangguna:

panggunaId
Tanggal Pendaftaran

1
2019-01-01

2
2019-02-01

3
2019-02-10

4
2019-03-01

Tabel biaya - tagihan:

panggunaId
tanggal
Cacahe

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 kabeh penghapusan pangguna lan tanggal registrasi:

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

Asil:

panggunaId
tanggal
Tanggal Pendaftaran

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

Kita mbangun kohort miturut sasi; kanggo nindakake iki, kita ngowahi kabeh tanggal dadi sasi:

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

Saiki kita kudu ngerti carane akeh sasi pangguna aktif - iki prabΓ©dan antarane sasi debit lan sasi registrasi. MySQL nduweni fungsi PERIOD_DIFF() - prabΓ©dan antarane rong sasi. Tambah PERIOD_DIFF() menyang panyuwunan:

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

panggunaId
BillingWulan
Tanggal Pendaftaran
WulanDiff

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

Kita ngetung pangguna sing diaktifake saben wulan - kita nglumpukake cathetan miturut BillingMonth, RegistrationMonth lan 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

Asil:

UsersCount
BillingWulan
Wulan Pendaftaran
WulanDiff

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

Ing Januari, Februari lan Maret, siji pangguna anyar muncul saben - MonthsDiff = 0. Siji pangguna Januari aktif ing Februari - RegistrationMonth = 2019-01, BillingMonth = 2019-02, lan siji pangguna Februari aktif ing Maret.

Alami, pola luwih katon ing kumpulan data gedhe.

Cara nransfer kohort menyang Grafana

Kita wis sinau carane mbentuk kohort, nanging yen ana akeh cathetan, iku ora gampang maneh kanggo njelasno. Rekaman bisa diekspor menyang Excel lan dibentuk dadi tabel sing apik, nanging iki dudu cara kita!

Cohorts bisa ditampilake minangka grafik interaktif ing Grafana.

Kanggo nindakake iki, kita nambah pitakon liyane kanggo ngowahi data menyang format sing cocog kanggo 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

Lan upload data menyang Grafana.

Tuladha grafik saka demo:

Cara ngumpulake kohort pangguna minangka grafik ing Grafana [+ gambar docker kanthi conto]

Tutul nganggo tangan:

Repositori GitHub kanthi conto punika gambar docker karo MySQL lan Grafana sing bisa mbukak ing komputer. Basis data kasebut wis ngemot data demo suwene setengah taun, wiwit Januari 2018 nganti Juli 2019.

Yen sampeyan pengin, sampeyan bisa mbukak data menyang gambar iki.

Artikel PS babagan analisis kohort ing 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

Add a comment