Analisis Kohort Retensi Pelanggan Pakai Pivot Table

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.

Apa itu analisis kohort retensi?

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.

Kenapa retensi lebih penting dari jumlah pelanggan baru?

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.

Data apa yang dibutuhkan?

Cuma tiga kolom di data mentah:

• id_pelanggan, penanda unik tiap pelanggan. Nomor HP atau email boleh, asal konsisten.
• tanggal_transaksi, bertipe tanggal beneran, bukan teks.
• nilai_transaksi, opsional. Kepakai kalau kamu mau ngitung retensi pendapatan, bukan cuma jumlah orang.

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.

Langkah 1: Bikin kolom bulan transaksi

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.

Langkah 2: Cari bulan pertama tiap pelanggan

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.

Langkah 3: Hitung selisih bulan

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.

Langkah 4: Susun pivot table-nya

Blok seluruh data termasuk header, lalu buat pivot table baru.

1. Tarik bulan_pertama ke area Rows.
2. Tarik selisih_bulan ke area Columns.
3. Tarik id_pelanggan ke area Values.
4. Ubah perhitungannya jadi Distinct Count. Di Excel, opsi ini muncul kalau waktu bikin pivot kamu centang "Add this data to the Data Model".

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.

Langkah 5: Ubah jadi persentase

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.

Gimana cara baca tabel kohortnya?

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.

Contoh kasus: retensi 1.847 pelanggan toko_berkah

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".

Kesalahan umum waktu bikin kohort di pivot table

1. Pakai Count, bukan Distinct Count. Ini kesalahan paling sering. Pelanggan yang beli 4 kali dalam sebulan kehitung 4 orang, dan retensimu jadi bisa lebih dari 100 persen.
2. Bulan pertama dihitung dari transaksi mana saja. Bulan pertama harus transaksi paling awal pelanggan itu, bukan baris pertama yang kebetulan muncul di data.
3. Ngerata-rata kolom yang selnya kosong. Kohort baru belum punya bulan ke-4. Kalau ikut dihitung, rata-ratanya jatuh tanpa alasan nyata.
4. Bandingin kohort dengan jumlah anggota timpang. Kohort berisi 12 orang gak layak dibandingkan sama kohort 400 orang. Gabungin jadi kuartalan.
5. Lupa memisahkan pelanggan lama. Kalau datamu mulai Januari 2025 padahal toko udah jalan sejak 2023, pelanggan lama bakal masuk kohort Januari dan bikin angkanya kelihatan bagus palsu.
6. ID pelanggan gak konsisten. Nomor HP ditulis 0812 dan +62812 buat orang yang sama bakal kehitung dua pelanggan berbeda.

Kapan mending pindah ke SQL?

Tiga tanda kamu udah melewati batas nyaman pivot table:

• Data di atas 300 ribu baris. Rumus MINIFS di tiap baris bikin file lambat sampai gak kepakai.
• Kohort harus dihitung ulang tiap minggu buat lebih dari satu kategori produk.
• Kamu butuh retensi pendapatan, bukan cuma jumlah pelanggan.

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.

FAQ

Butuh berapa banyak data buat analisis kohort yang berarti?

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.

Kenapa angka bulan ke-0 selalu 100 persen?

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.

Bedanya retensi pelanggan sama tingkat pembelian ulang apa?

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 gak dipakai buat bisnis jasa yang transaksinya jarang?

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.

Kohort terakhir angkanya kecil banget, itu masalah?

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.

Penutup

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.