Dalam artikel sebelumnya, saya menggambarkan penggunaan analisis kohort untuk menentukan alasan dinamika basis klien. Hari ini saatnya berbicara tentang trik persiapan data untuk analisis kohort.
Mudah menggambar, tetapi agar dapat dibaca dan ditampilkan dengan benar "di bawah tenda" banyak pekerjaan yang harus dilakukan. Pada artikel ini, kita akan berbicara tentang bagaimana menerapkan analisis kohort. Saya akan berbicara tentang implementasi menggunakan Excel, dan di artikel lain menggunakan R.
Suka atau tidak, tetapi sebenarnya Excel adalah alat analisis data. Lebih banyak analis "arogan" akan percaya bahwa ini adalah alat yang lemah dan tidak nyaman. Di sisi lain, pada kenyataannya, ratusan ribu orang melakukan analisis data dalam Excel dan dalam hal ini akan dengan mudah mengalahkan R / python. Tentu saja, ketika kita berbicara tentang analitik lanjutan dan pembelajaran mesin, kita akan mengerjakan R / python. Dan saya ingin sebagian besar analisis dilakukan hanya dengan alat-alat ini. Tetapi perlu mengakui fakta, sebagian besar perusahaan memproses dan menyajikan data dalam Excel, dan ini adalah alat yang digunakan oleh analis, manajer, dan pemilik produk biasa. Selain itu, Excel sulit dikalahkan dalam hal kesederhanaan dan kejelasan proses, karena Anda menguasai perhitungan dan model Anda secara harfiah dengan tangan Anda.
Jadi, bagaimana kita melakukan analisis kohort di Excel? Untuk menyelesaikan masalah seperti itu, Anda perlu menentukan 2 hal:
Data apa yang kita miliki di awal proses
Seperti apa data kita di akhir proses.
Untuk mengumpulkan analisis kohort, kami tidak hanya akan membutuhkan data kembali pada tanggal dan divisi. Kami membutuhkan data di tingkat pelanggan individu. Di awal proses, kita membutuhkan:
Tanggal kalender
ID pelanggan
Tanggal Pendaftaran Pelanggan
Volume penjualan pelanggan ini pada tanggal kalender ini
Kesulitan pertama yang harus diatasi adalah untuk mendapatkan data ini. Jika Anda memiliki penyimpanan yang benar, maka Anda seharusnya sudah memilikinya. Di sisi lain, jika sejauh ini Anda hanya menerapkan pencatatan data pada total penjualan per hari, maka Anda hanya memiliki data pelanggan pada "prod." Untuk analisis kohort, Anda harus menerapkan ETL dan memasukkan data dalam konteks klien ke penyimpanan Anda, jika tidak, Anda tidak akan berhasil. Dan yang terbaik, jika Anda memisahkan "prod" dan analitik ke dalam basis data yang berbeda, karena Tugas analitik dan tugas fungsi produk Anda memiliki tujuan yang berbeda: persaingan untuk sumber daya. Analis membutuhkan agregat dan perhitungan yang cepat untuk banyak pengguna, produk perlu melayani pengguna tertentu dengan cepat. Saya akan menulis artikel terpisah tentang organisasi penyimpanan.
Jadi, Anda memiliki data awal:
Hal pertama yang perlu kita lakukan adalah mengubahnya menjadi "tangga". Untuk melakukan ini, Anda perlu membuat tabel pivot di atas tabel ini, di baris - tanggal pendaftaran, di kolom - tanggal kalender, sebagai nilai - jumlah id klien. Jika Anda benar mengekstraksi data, maka Anda harus mendapatkan segitiga / tangga:
Secara umum, tangga adalah bagan kohort kami, di mana setiap baris menampilkan dinamika kohort yang terpisah. Klien dalam waktu dalam tampilan ini hanya bergerak dalam satu baris. Dengan demikian, dinamika kohort mencerminkan perkembangan hubungan dengan sekelompok klien yang datang dalam satu periode waktu. Seringkali, untuk kenyamanan dan tanpa kehilangan kualitas, Anda dapat menggabungkan kohort menjadi “blok” baris. Misalnya, Anda dapat mengelompokkannya menurut minggu dan bulan. Dengan cara yang sama, Anda dapat mengelompokkan kolom juga. Mungkin laju pengembangan produk Anda tidak memerlukan perincian hingga beberapa hari.
Berdasarkan tangga ini, Anda dapat membuat grafik dari artikel saya (saya benar-benar menunjukkan bahwa saya telah mengelompokkan beberapa baris menjadi satu sehingga kohort lebih kecil):
Ini adalah grafik dengan area kumulatif di mana setiap baris adalah baris, secara horizontal dari sebuah tanggal.
Logika sedikit lebih rumit untuk mengimplementasikan jadwal "mengalir". Untuk utas, kita perlu melakukan beberapa perhitungan tambahan. Dalam untaian logika, setiap klien tiba di berbagai negara:
- Baru - setiap pelanggan yang memiliki perbedaan antara tanggal pendaftaran dan tanggal kalender <7 hari
- Diaktifkan kembali - setiap pelanggan yang tidak lagi baru, tetapi dalam bulan kalender terakhir tidak menghasilkan pendapatan
- Valid - pelanggan mana pun yang bukan baru, tetapi menghasilkan pendapatan di bulan kalender
- Berangkat - klien mana pun yang tidak menghasilkan pendapatan selama 2 bulan berturut-turut
Pertama-tama, Anda harus memperbaiki definisi ini di perusahaan sehingga Anda dapat menerapkan logika ini dengan benar dan secara otomatis menghitung negara. Keempat definisi ini memiliki implikasi yang luas untuk pemasaran secara umum. Strategi Anda untuk menarik, mempertahankan, dan kembali akan didasarkan pada keadaan di mana Anda pikir klien. Dan jika Anda mulai menerapkan model pembelajaran mesin dalam memprediksi keluar pelanggan, maka definisi akan menjadi landasan Anda untuk kesuksesan model-model ini. Secara umum, saya akan menulis artikel terpisah tentang organisasi kerja dan pentingnya metodologi analitis. Di atas, saya memberikan contoh tentang definisi ini.
Di Excel, Anda harus membuat kolom tambahan tempat memasukkan logika yang dijelaskan di atas. Dalam kasus kami, kami harus "berkeringat". Kami memiliki 2 jenis kriteria:
- Perbedaan antara tanggal pendaftaran dan tanggal kalender - setiap baris memiliki data ini dan kemudian Anda hanya perlu menghitungnya (mengurangi tanggal di Excel hanya memberikan perbedaan dalam hari)
- Data pendapatan untuk bulan ini dan bulan lalu. Data ini tidak tersedia bagi kami di telepon. Selain itu, dengan mempertimbangkan fakta bahwa pesanan tidak dijamin dalam tabel kami, Anda tidak dapat mengatakan dengan tepat di mana Anda memiliki data pada hari-hari lain dalam sebulan untuk klien ini.
Ada 2 cara untuk memecahkan masalah 2 jenis kriteria:
- Minta untuk melakukan ini di database. SQL memungkinkan menggunakan fungsi analitik untuk menghitung untuk setiap klien jumlah pendapatan untuk bulan ini dan bulan lalu (untuk bulan berjalan SUM (pendapatan) LEBIH DARI (PARTISI OLEH client_id, calendar_month, dan kemudian LAG untuk mendapatkan offset untuk bulan lalu):
- Di Excel, Anda harus menerapkannya seperti ini:
- Untuk bulan ini: SUMMES (), kriteria akan menjadi id klien dan bulan sel hari kalender
- Untuk bulan terakhir: SUMMES (), kriteria akan menjadi id klien dan bulan sel hari kalender dikurangi tepat 1 bulan kalender. Dalam hal ini, saya menarik perhatian pada fakta bahwa Anda harus mengurangi bulan kalender, dan bukan 30 hari. Jika tidak, Anda berisiko mendapatkan gambar buram karena jumlah hari yang tidak merata dalam beberapa bulan. Juga gunakan fungsi IF ERROR untuk mengganti nilai yang salah untuk pelanggan yang belum memiliki bulan lalu.
Dengan menambahkan kolom pendapatan bulan saat ini bulan lalu, Anda dapat membangun kondisi tertanam JIKA memperhitungkan semua faktor (perbedaan tanggal dan jumlah pendapatan di bulan berjalan / bulan lalu):
JIKA (perbedaan tanggal <7; "baru";
JIKA (DAN (pendapatan bulan lalu = 0; pendapatan bulan saat ini> 0); "reaktivasi";
JIKA (DAN (pendapatan bulan lalu> 0; pendapatan bulan saat ini> 0); "saat ini"
JIKA (DAN (pendapatan bulan lalu = 0; pendapatan bulan saat ini = 0); "berangkat"; "kesalahan")))))
"Kesalahan" diperlukan di sini hanya untuk mengontrol bahwa Anda tidak salah dalam rekaman. Logika kriteria negara MECE ( https://en.wikipedia.org/wiki/MECE_principle ), yaitu Jika semuanya dilakukan dengan benar, maka masing-masing dari mereka akan ditempelkan satu negara dari 4
Anda harus mendapatkannya seperti ini:
Sekarang tabel ini dapat dibangun kembali menggunakan tabel pivot ke dalam tabel untuk merencanakan. Anda perlu mengubahnya menjadi tabel:
Tanggal kalender (kolom)
Negara (baris)
Jumlah id klien (nilai dalam sel)
Selanjutnya, kita hanya perlu membangun diagram batang diagram berdasarkan data, dengan akumulasi, pada sumbu X tanggal kalender, baris adalah status, jumlah klien adalah tinggi kolom. Anda dapat mengubah urutan status pada bagan dengan mengubah urutan baris dalam menu “pilih data”. Hasilnya, kita mendapatkan gambar berikut:
Sekarang kita dapat mulai menafsirkan dan menganalisis.