![Cara mengumpul kohort pengguna sebagai graf dalam Grafana [+ imej docker dengan contoh]](https://prohoster.info/wp-content/plugins/wp-fastest-cache-premium/pro/images/blank.gif)
Cara kami menyelesaikan masalah menggambarkan kohort pengguna dalam perkhidmatan Promopult menggunakan Grafana.
- perkhidmatan berkuasa dengan bilangan pengguna yang ramai. Sepanjang 10 tahun beroperasi, bilangan pendaftaran dalam sistem telah melebihi satu juta. Mereka yang pernah mengalami perkhidmatan serupa tahu bahawa susunan pengguna ini jauh dari homogen.
Seseorang telah mendaftar dan "tertidur" selama-lamanya. Seseorang terlupa kata laluan mereka dan mendaftar beberapa kali lagi dalam tempoh enam bulan. Seseorang membawa wang ke daftar tunai, dan seseorang datang untuk mendapatkan hadiah percuma . Dan adalah bagus untuk mendapatkan sedikit keuntungan daripada semua orang.
Pada set data yang besar seperti kami, menganalisis tingkah laku pengguna individu dan membuat keputusan mikro adalah sia-sia. Tetapi menangkap trend dan bekerja dengan kumpulan besar adalah mungkin dan perlu. Yang betul-betul apa yang kita lakukan.
Ringkasan
- Apakah analisis kohort dan mengapa ia diperlukan?
- Cara membuat kohort mengikut bulan pendaftaran pengguna dalam SQL.
- Cara memindahkan kohort ke .
Jika anda sudah tahu apa itu analisis kohort dan cara melakukannya dalam SQL, langkau terus ke bahagian terakhir.
1. Apakah analisis kohort dan mengapa ia diperlukan?
Analisis kohort ialah kaedah berdasarkan membandingkan kumpulan (kohort) pengguna yang berbeza. Selalunya, kumpulan kami dibentuk mengikut minggu atau bulan di mana pengguna mula menggunakan perkhidmatan tersebut. Dari sini hayat pengguna dikira, dan ini adalah penunjuk berdasarkan analisis yang agak kompleks boleh dijalankan. Sebagai contoh, fahami:
- bagaimana saluran pemerolehan mempengaruhi seumur hidup pengguna;
- bagaimana penggunaan mana-mana ciri atau perkhidmatan mempengaruhi seumur hidup;
- Bagaimana pelancaran ciri X mempengaruhi seumur hidup berbanding tahun lepas.
2. Bagaimana untuk membuat kohort dalam SQL?
Saiz artikel dan akal sehat tidak membenarkan kami membentangkan data sebenar kami di sini - dalam longgokan ujian, statistik selama setahun setengah: 1200 pengguna dan 53 transaksi. Supaya anda boleh bermain dengan data ini, kami telah menyediakan imej docker dengan MySQL dan Grafana di mana anda boleh mengalami semuanya sendiri. Pautan ke GitHub pada penghujung artikel.
Dan di sini kami akan menunjukkan penciptaan kohort menggunakan contoh yang dipermudahkan.
Katakan kita mempunyai perkhidmatan. Pengguna mendaftar di sana dan membelanjakan wang untuk perkhidmatan. Lama kelamaan, pengguna tercicir. Kami ingin mengetahui berapa lama pengguna hidup, dan berapa ramai daripada mereka yang hilang selepas bulan ke-1 dan ke-2 menggunakan perkhidmatan tersebut.
Untuk menjawab soalan ini, kita perlu membina kohort berdasarkan bulan pendaftaran. Kami akan mengukur aktiviti mengikut perbelanjaan pada setiap bulan. Daripada perbelanjaan, mungkin terdapat pesanan, yuran langganan atau sebarang aktiviti berasaskan masa yang lain.
Data mentah
Contoh-contoh dibuat dalam MySQL, tetapi untuk DBMS lain tidak sepatutnya terdapat sebarang perbezaan yang ketara.
Jadual pengguna - pengguna:
ID Pengguna
Tarikh pendaftaran
1
2019-01-01
2
2019-02-01
3
2019-02-10
4
2019-03-01
Jadual kos - pengebilan:
ID Pengguna
Tarikh
Sum
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 semua hapus kira pengguna dan tarikh pendaftaran:
SELECT
b.userId,
b.Date,
u.RegistrationDate
FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
Keputusan:
ID Pengguna
Tarikh
Tarikh 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
Kami membina kohort mengikut bulan; untuk melakukan ini, kami menukar semua tarikh kepada bulan:
DATE_FORMAT(Date, '%Y-%m')Sekarang kita perlu tahu berapa bulan pengguna itu aktif - ini adalah perbezaan antara bulan debit dan bulan pendaftaran. MySQL mempunyai fungsi PERIOD_DIFF() - perbezaan antara dua bulan. Tambahkan PERIOD_DIFF() pada permintaan:
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 Pengguna
BilBulan
Tarikh pendaftaran
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
4
2019-03
2019-03
0
Kami mengira pengguna yang diaktifkan pada setiap bulan - kami mengumpulkan rekod mengikut BillingMonth, RegistrationMonth dan 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
Keputusan:
Bilangan Pengguna
BilBulan
Bulan Pendaftaran
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
Pada bulan Januari, Februari dan Mac, seorang pengguna baharu muncul setiap satu - MonthsDiff = 0. Seorang pengguna Januari aktif pada bulan Februari - RegistrationMonth = 2019-01, BillingMonth = 2019-02 dan seorang pengguna Februari aktif pada bulan Mac.
Sememangnya, corak lebih kelihatan dalam set data yang besar.
Bagaimana untuk memindahkan kohort ke Grafana
Kami telah belajar cara membentuk kohort, tetapi apabila terdapat banyak rekod, tidak lagi mudah untuk menganalisisnya. Rekod boleh dieksport ke Excel dan dibentuk menjadi jadual yang cantik, tetapi ini bukan kaedah kami!
Kohort boleh ditunjukkan sebagai graf interaktif dalam .
Untuk melakukan ini, kami menambah pertanyaan lain untuk menukar data kepada format yang sesuai untuk 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
Dan muat naik data ke Grafana.
Contoh graf daripada :
Sentuh dengan tangan anda:
ialah imej docker dengan MySQL dan Grafana yang boleh anda jalankan pada komputer anda. Pangkalan data sudah mengandungi data demo selama setahun setengah, dari Januari 2018 hingga Julai 2019.
Jika anda mahu, anda boleh memuatkan data anda ke dalam imej ini.
Artikel PS mengenai analisis kohort dalam SQL:
Sumber: www.habr.com