TL;DR
Analisis kohort retensi mengelompokkan pelanggan berdasarkan bulan pertama mereka beli, lalu menghitung berapa persen dari kelompok itu yang masih beli di bulan-bulan berikutnya. Di pivot table, kamu cuma butuh tiga kolom bantu: bulan transaksi, bulan pertama pelanggan, dan selisih bulan. Setelah itu susun pivot dengan bulan pertama sebagai baris, selisih bulan sebagai kolom, dan jumlah pelanggan unik sebagai nilai.
Analisis kohort retensi bisa kamu bikin di pivot table dengan tiga kolom bantu. Gak perlu SQL, gak perlu tool khusus.
Idenya: kelompokkan pelanggan berdasarkan bulan pertama mereka beli, terus lacak berapa persen dari kelompok itu yang masih beli di bulan-bulan setelahnya.
Hasilnya tabel berbentuk segitiga. Baris = kelompok pelanggan, kolom = umur kelompok itu dalam bulan.
Aku pakai cara ini buat data 1.847 pelanggan toko_berkah, dan hasilnya nunjukin satu bulan dengan retensi 2 kali lipat dari bulan lain. Ternyata sebabnya sederhana, dan bakal aku ceritain di bagian contoh kasus.
Analisis kohort retensi adalah cara ngukur kesetiaan pelanggan dengan mengelompokkan mereka berdasarkan waktu pertama kali bertransaksi, lalu menghitung berapa persen tiap kelompok yang masih aktif di periode berikutnya. Kelompok inilah yang disebut cohort.
Bedanya sama laporan penjualan biasa: laporan biasa cuma bilang omzet naik atau turun. Kohort bilang apakah pelanggan baru bulan ini lebih setia dibanding pelanggan baru bulan lalu.
Contohnya begini. Dari 210 pelanggan yang pertama beli di Januari, ada 63 orang yang beli lagi di Februari. Retensi bulan ke-1 kohort Januari = 30 persen.
Karena biaya narik pelanggan baru selalu lebih besar dari biaya bikin pelanggan lama balik.
Toko yang dapet 300 pelanggan baru tiap bulan tapi retensinya 8 persen bakal jalan di tempat. Tiap bulan kamu ngisi ember bocor.
Angka retensi juga lebih cepat ngasih sinyal. Omzet turun baru kelihatan setelah 3 bulan. Retensi bulan ke-1 yang jeblok kelihatan bulan depannya.
Cuma tiga kolom di data mentah:
Satu baris = satu transaksi. Jangan direkap dulu.
Sebelum lanjut, pastiin gak ada id_pelanggan yang kosong. Baris tanpa ID gak bisa dilacak dan bakal bikin angka retensi kelihatan lebih rendah dari kenyataan.
Kolom pertama ngubah tanggal jadi penanda bulan. Semua transaksi di bulan yang sama harus punya nilai identik.
Anggap tanggal ada di kolom B mulai baris 2:
=EOMONTH(B2, 0)Rumus ini balikin tanggal akhir bulan. Semua transaksi Januari 2025 jadi 31/01/2025.
Format kolomnya jadi mmm yyyy biar kebaca "Jan 2025". Nilainya tetap tanggal, jadi urutannya benar waktu masuk pivot.
Cara lain pakai teks:
=TEXT(B2, "yyyy-mm")Versi TEXT ini menghasilkan "2025-01". Urutannya tetap benar karena tahun ditulis duluan. Pilih salah satu, jangan campur.
Ini kolom yang nentuin pelanggan masuk kohort mana. Isinya bulan transaksi paling awal dari pelanggan tersebut.
Anggap id_pelanggan di kolom A dan bulan transaksi di kolom C:
=EOMONTH(MINIFS($B$2:$B$20000, $A$2:$A$20000, A2), 0)MINIFS nyari tanggal paling awal buat pelanggan yang ID-nya sama dengan baris ini. EOMONTH ngubahnya jadi penanda bulan.
Kalau versi Excel kamu belum punya MINIFS, pakai rumus array ini lalu tekan Ctrl+Shift+Enter:
=EOMONTH(MIN(IF($A$2:$A$20000=A2, $B$2:$B$20000)), 0)Buat data di atas 20 ribu baris, rumus ini bakal berat. Salin kolomnya lalu tempel sebagai nilai setelah selesai dihitung sekali.
Kolom ketiga ngitung umur pelanggan dalam bulan, dihitung dari bulan pertama dia beli.
Anggap bulan pertama di kolom D:
=DATEDIF(D2, C2, "m")Karena dua-duanya tanggal akhir bulan, hasilnya bilangan bulat. Transaksi di bulan pertama dapat nilai 0. Transaksi bulan berikutnya dapat 1.
Alternatifnya pakai selisih tahun dan bulan:
=(YEAR(C2)-YEAR(D2))*12 + (MONTH(C2)-MONTH(D2))Dua rumus ini kasih hasil sama. Yang kedua lebih aman kalau tanggalmu bukan akhir bulan.
Sekarang data kamu udah siap. Tiga kolom bantu, itu aja.
Blok seluruh data termasuk header, lalu buat pivot table baru.
Di Google Sheets, pilih COUNTUNIQUE sebagai fungsi ringkasan. Gak perlu pengaturan tambahan.
Hasilnya tabel segitiga. Baris paling atas kohort paling lama, dan makin ke bawah kolomnya makin pendek karena kohort baru belum punya riwayat panjang.
Kalau tampilan pivotnya masih berantakan, cek dulu dasar-dasarnya di panduan pivot table Excel.
Angka jumlah orang belum bisa dibandingkan antar kohort. Kohort Januari 210 orang, kohort Juni 96 orang. Angka 30 dan 30 artinya beda jauh.
Salin hasil pivot ke area kosong sebagai nilai. Di situ baru bagi tiap sel sama kolom bulan ke-0 di baris yang sama.
Anggap tabel salinan mulai di kolom B, dan bulan ke-0 ada di kolom B:
=IFERROR(C3/$B3, "")Kunci kolom B pakai tanda dolar biar pembaginya tetap waktu rumusnya digeser ke kanan. IFERROR bikin sel kosong tetap bersih, bukan nampilin error.
Format hasilnya jadi persentase dengan 0 desimal. Terus kasih conditional formatting skala warna biar polanya kelihatan sekali lihat.
Ada tiga arah baca, dan tiga-tiganya jawab pertanyaan beda.
| Arah baca | Pertanyaan yang dijawab |
|---|---|
| Baca ke kanan (satu baris) | Seberapa cepat kohort ini menyusut seiring waktu? |
| Baca ke bawah (satu kolom) | Apakah pelanggan baru sekarang lebih setia dibanding dulu? |
| Baca diagonal | Apakah ada bulan tertentu yang mempengaruhi semua kohort sekaligus? |
Kolom bulan ke-1 yang paling sering dipakai buat keputusan. Kalau angkanya turun tiga bulan berturut-turut, ada yang berubah di produk atau layananmu.
Diagonal kepakai buat nangkep kejadian sekali waktu. Kalau semua kohort turun barengan di satu bulan, curigai hal di luar produk: hari raya, banjir, atau sistem kasir yang sempat mati.
Dataset toko_berkah, 1.847 pelanggan unik, 9.320 transaksi dari Januari sampai Agustus 2025.
| Kohort | Pelanggan | Bln 1 | Bln 2 | Bln 3 | Bln 4 |
|---|---|---|---|---|---|
| Jan 2025 | 210 | 30% | 21% | 17% | 15% |
| Feb 2025 | 188 | 28% | 19% | 16% | 14% |
| Mar 2025 | 245 | 31% | 22% | 18% | 16% |
| Apr 2025 | 402 | 58% | 41% | 33% | 29% |
| Mei 2025 | 231 | 27% | 18% | 15% | |
| Jun 2025 | 196 | 26% | 17% |
Kohort April langsung kelihatan beda. Retensi bulan ke-1 nya 58 persen, hampir dua kali lipat rata-rata kohort lain yang berkisar 26 sampai 31 persen.
Dugaan pertamaku salah. Aku kira karena Ramadan bikin semua orang belanja lebih sering, jadi angkanya semu.
Setelah dicek, penyebabnya program kartu member yang cuma dibuka April. 267 dari 402 pelanggan April daftar member, dan member dapat potongan Rp10.000 buat pembelian berikutnya di atas Rp75.000.
Yang menarik, efeknya bertahan sampai bulan ke-4. Retensi 29 persen, sementara kohort lain udah di 14 sampai 16 persen.
Kalau dihitung kasar, 402 pelanggan April dengan retensi bulan ke-4 sebesar 29 persen artinya 117 pelanggan masih aktif. Kohort Maret yang 245 orang cuma nyisain 39 orang.
Angka itu yang bikin program membernya dilanjutkan, bukan perasaan bahwa "kayaknya kemarin rame".
Tiga tanda kamu udah melewati batas nyaman pivot table:
Versi SQL-nya lebih ringkas dan bisa dijadwalkan. Langkahnya ada di cohort analysis pakai SQL, dan buat yang fokus ke sisi uangnya ada di kohort pendapatan dengan SQL.
Buat detail perilaku fungsi tanggal di spreadsheet, referensi resminya ada di dokumentasi Microsoft untuk EOMONTH.
Minimal 6 bulan riwayat transaksi dan sekitar 100 pelanggan per kohort bulanan. Kalau kohortmu cuma berisi 15 pelanggan, satu orang yang beli ulang bikin angkanya melonjak 7 persen dan polanya jadi susah dibaca. Kalau pelangganmu sedikit, gabungin jadi kohort kuartalan supaya tiap kelompok isinya cukup besar.
Karena bulan ke-0 itu bulan pertama pelanggan beli, jadi semua anggota kohort pasti ada di situ. Ini bukan hasil perhitungan melainkan definisi. Yang perlu kamu perhatikan justru bulan ke-1, yaitu berapa persen yang balik di bulan berikutnya. Itu angka retensi paling jujur dan paling cepat kelihatan kalau ada masalah.
Tingkat pembelian ulang cuma ngitung berapa persen pelanggan yang pernah beli lebih dari sekali, tanpa peduli kapan. Retensi kohort ngukur kapan mereka baliknya, dikelompokkan berdasarkan waktu mereka pertama masuk. Retensi kohort lebih berguna karena bisa nunjukin apakah pelanggan yang datang bulan ini lebih setia dibanding yang datang enam bulan lalu.
Bisa, asal satuan waktunya kamu sesuaikan. Buat servis AC yang dipanggil tiap enam bulan, pakai kohort kuartalan atau semesteran, bukan bulanan. Kalau tetap pakai bulanan, hampir semua sel bakal nol dan tabelnya gak bilang apa-apa. Patokannya: satu periode kohort harus lebih panjang dari jeda pembelian normal pelangganmu.
Bukan masalah, itu memang wajar. Kohort yang baru terbentuk bulan lalu belum punya kesempatan buat muncul di bulan ke-3 atau ke-6, jadi selnya kosong. Yang keliru itu kalau kamu ikutkan sel kosong ini ke rata-rata. Selalu bandingkan kolom yang sama antar kohort, misalnya bulan ke-1 kohort Januari lawan bulan ke-1 kohort Juni.
Tiga kolom bantu, satu pivot table, satu tabel persentase. Itu seluruh alurnya.
Yang paling sering bikin hasilnya salah cuma satu hal: lupa ganti Count jadi Distinct Count. Cek itu dulu kalau angkamu kelihatan aneh.
Buka data transaksi setahun terakhir, bikin kolom bulan pertama, dan lihat berapa persen pelanggan Januari yang masih beli di Februari. Satu angka itu udah cukup buat mulai obrolan serius soal retensi.
Mau lanjut ke analisis penjualan yang lain di pivot yang sama? Ada di analisis penjualan pakai pivot table.
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Punya ratusan angka dan mau tau sebarannya masuk ke rentang mana aja? FREQUENCY ngitung distribusi frekuensi sekali jalan, tanpa nulis COUNTIFS berkali-kali.
SUMIFS ribet kalau kriterianya banyak dan sering ganti. Rumus database Excel kayak DSUM baca kriteria dari sel terpisah, jadi kamu tinggal ubah tabel kecil tanpa nyentuh rumus.
DATEDIF ngasih umur bulat, tapi kadang kamu butuh angka desimal kayak 27,6 tahun buat hitung masa kerja atau bunga. YEARFRAC ngasih pecahan tahun dengan presisi.