Bagaimana kami memecahkan tugas memvisualisasikan kohort pengguna di layanan Promopult menggunakan Grafana.
Promopult adalah layanan yang kuat dengan sejumlah besar pengguna. Lebih dari 10 tahun beroperasi, jumlah pendaftaran dalam sistem telah melebihi satu juta. Mereka yang menemukan layanan serupa tahu bahwa array pengguna ini jauh dari homogen.
Seseorang mendaftar dan "tertidur" selamanya. Seseorang lupa kata sandi dan mendaftar beberapa kali lebih banyak dalam enam bulan. Seseorang membawa uang ke kasir, dan seseorang datang untuk mendapatkan alat gratis. Dan akan menyenangkan untuk mendapatkan keuntungan dari semua orang.
Pada array data besar seperti milik kami, tidak masuk akal untuk menganalisis perilaku pengguna individu dan membuat keputusan mikro. Tetapi menangkap tren dan bekerja dengan kelompok besar adalah mungkin dan perlu. Sebenarnya apa yang kita lakukan.
Ringkasan
- Apa itu analisis kohort dan mengapa diperlukan.
- Cara membuat kohort berdasarkan bulan pendaftaran pengguna dalam SQL.
- Cara mentransfer kohort ke Grafana .
Jika Anda sudah tahu apa itu analisis kohort dan bagaimana melakukannya dalam SQL, lewati ke bagian terakhir.
1. Apa itu analisis kohort dan mengapa diperlukan
Analisis kohort adalah metode yang didasarkan pada perbandingan berbagai kelompok (kohort) pengguna. Paling sering, grup kami dibentuk pada minggu atau bulan di mana pengguna mulai menggunakan layanan. Masa hidup pengguna dihitung dari sini, dan ini merupakan indikator atas dasar mana analisis yang agak rumit dapat dilakukan. Misalnya, pahami:
- Bagaimana cara saluran menarik pada kehidupan pengguna;
- bagaimana penggunaan suatu fungsi atau layanan mempengaruhi seumur hidup;
- bagaimana peluncuran fitur X mempengaruhi waktu hidup dibandingkan dengan tahun lalu.
2. Bagaimana cara membuat kohort di SQL?
Ukuran artikel dan akal sehat tidak memungkinkan kami untuk memberikan data nyata kami di sini - dalam test dump, statistik selama satu setengah tahun adalah: 1.200 pengguna dan 53.000 transaksi. Agar Anda dapat bermain dengan data ini, kami telah menyiapkan gambar buruh pelabuhan dengan MySQL dan Grafana, di mana Anda bisa merasakan semuanya sendiri. Tautan ke GitHub di akhir artikel.
Dan di sini kami menunjukkan pembuatan kohort dengan contoh sederhana.
Misalkan kita memiliki layanan. Pengguna mendaftar di dalamnya dan menghabiskan uang untuk layanan. Seiring waktu, pengguna jatuh. Kami ingin tahu berapa lama pengguna hidup, dan berapa banyak dari mereka yang jatuh setelah bulan ke-1 dan ke-2 menggunakan layanan ini.
Untuk menjawab pertanyaan ini, kita perlu membangun kohort berdasarkan bulan pendaftaran. Aktivitas akan diukur dengan pengeluaran di setiap bulan. Alih-alih biaya, mungkin ada pesanan, biaya bulanan, atau aktivitas terikat waktu lainnya.
Sumber data
Contoh dibuat di MySQL, tetapi untuk sisa DBMS seharusnya tidak ada perbedaan yang signifikan.
Tabel pengguna - pengguna:
Tabel pengeluaran - penagihan:
Pilih semua biaya pengguna dan tanggal pendaftaran:
SELECT b.userId, b.Date, u.RegistrationDate FROM billing AS b LEFT JOIN users AS u ON b.userId = u.userId
Hasil:
Kami membangun kohort berdasarkan bulan, untuk ini kami akan mengonversi semua tanggal menjadi bulan:
DATE_FORMAT(Date, '%Y-%m')
Sekarang kita perlu tahu berapa bulan pengguna aktif - ini adalah perbedaan antara bulan pendebitan dan bulan pendaftaran. MySQL memiliki fungsi PERIOD_DIFF () - perbedaan antara dua bulan. Tambahkan PERIOD_DIFF () ke 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
Kami menghitung pengguna yang diaktifkan di setiap bulan - kami mengelompokkan catatan berdasarkan BillingMonth, RegistrationMonth, dan MonthDiff:
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:
Pada bulan Januari, Februari dan Maret, satu pengguna baru muncul - MonthsDiff = 0. Satu pengguna Januari aktif dan pada bulan Februari RegistrationMonth = 2019-01, BillingMonth = 2019-02, dan satu pengguna Februari aktif pada bulan Maret.
Pada kumpulan data besar, pola secara alami lebih baik dilihat.
Cara Mentransfer Kohort ke Grafana
Kami belajar cara membentuk kohort, tetapi ketika ada banyak catatan, tidak lagi mudah untuk menganalisisnya. Catatan dapat diekspor ke Excel dan membuat tabel yang indah, tetapi ini bukan metode kami!
Kohort dapat ditampilkan sebagai grafik interaktif di Grafana .
Untuk melakukan ini, tambahkan kueri lain untuk mengonversi data ke dalam format yang cocok 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 (
Dan unggah datanya ke Grafana.
Contoh grafik dari demo :
Sentuh:
Repositori contoh GitHub adalah gambar buruh pelabuhan dengan MySQL dan Grafana, yang dapat dijalankan di komputer Anda. Basis data sudah memiliki data demo selama satu setengah tahun, dari Januari 2018 hingga Juli 2019.
Jika mau, Anda dapat mengunggah data Anda ke gambar ini.
PS Artikel tentang analisis kohort di SQL:
https://chartio.com/resources/tutorials/performing-cohort-analysis-using-mysql/
https://www.holistics.io/blog/calculate-cohort-retention-analysis-with-sql/