Membuat Matriks Retensi Cohort dengan SQL

Matriks retensi cohort adalah tabel yang ngelompokin pelanggan berdasarkan bulan pertama mereka aktif, lalu ngukur berapa persen yang balik di bulan-bulan berikutnya. Ini cara paling jujur buat lihat apakah pelanggan beneran betah.

Angka total pelanggan gampang nyesatin. Bisa aja jumlahnya naik, padahal yang lama pada kabur dan cuma ketutup pelanggan baru.

Di bawah aku bikin matriksnya di SQL langkah demi langkah, dari nentuin cohort sampai ngubahnya jadi persen retensi.

Apa itu matriks retensi cohort?

Matriks retensi cohort adalah tabel dua dimensi: tiap baris satu cohort (kelompok pelanggan berdasarkan bulan pertama aktif), dan tiap kolom jarak bulan sejak cohort itu mulai. Isinya persentase pelanggan yang masih aktif di tiap titik waktu. Dari pola angkanya, kamu bisa lihat seberapa cepat pelanggan berhenti belanja.

Ini bentuk paling umum dari cohort analysis, yaitu cara ngelompokin pelanggan biar perilakunya bisa dibandingin adil antar periode.

Gimana cara bikin matriks retensi cohort di SQL?

Bikinnya lewat tiga langkah: tentukan bulan cohort tiap pelanggan, hitung jarak bulan tiap aktivitas dari bulan cohort, lalu hitung pelanggan aktif per cohort per jarak bulan. Setelah itu tinggal bagi dengan ukuran cohort buat dapat persennya. Kita mulai dari langkah pertama.

Langkah pertama, tentukan bulan cohort. Ini bulan pertama tiap pelanggan pernah transaksi, dibulatkan ke awal bulan pakai DATE_TRUNC.

WITH cohort AS (
    SELECT
        pelanggan_id,
        DATE_TRUNC('month', MIN(tanggal_pesan)) AS bulan_cohort
    FROM pesanan
    GROUP BY pelanggan_id
)
SELECT * FROM cohort;

Sekarang tiap pelanggan punya satu bulan cohort. Pelanggan yang pertama beli di Maret masuk cohort Maret, apa pun aktivitas mereka setelahnya.

Hitung jarak bulan tiap aktivitas

Langkah kedua, buat tiap transaksi, hitung jarak bulannya dari bulan cohort. Pelanggan yang beli di bulan cohort dapat nilai 0, bulan depannya 1, dan seterusnya. Fungsi AGE plus EXTRACT yang ngerjain hitungan ini.

aktivitas AS (
    SELECT
        o.pelanggan_id,
        c.bulan_cohort,
        (EXTRACT(YEAR  FROM AGE(DATE_TRUNC('month', o.tanggal_pesan), c.bulan_cohort)) * 12
       + EXTRACT(MONTH FROM AGE(DATE_TRUNC('month', o.tanggal_pesan), c.bulan_cohort)))::int AS bulan_ke
    FROM pesanan o
    JOIN cohort c ON c.pelanggan_id = o.pelanggan_id
)

Kolom bulan_ke ini yang bakal jadi kolom di matriks nanti. Nilai 0 artinya bulan pertama, 1 artinya sebulan setelahnya, dan begitu terus.

Cara ubah jadi persen retensi

Langkah ketiga, hitung pelanggan unik yang aktif per cohort per bulan_ke, lalu bagi dengan ukuran cohort. Ukuran cohort adalah jumlah total pelanggan di cohort itu. Hasilnya persentase yang gampang dibandingin antar cohort walau ukurannya beda.

ukuran_cohort AS (
    SELECT bulan_cohort, COUNT(*) AS ukuran
    FROM cohort
    GROUP BY bulan_cohort
)
SELECT
    a.bulan_cohort,
    a.bulan_ke,
    ROUND(100.0 * COUNT(DISTINCT a.pelanggan_id) / u.ukuran, 1) AS retensi_persen
FROM aktivitas a
JOIN ukuran_cohort u ON u.bulan_cohort = a.bulan_cohort
GROUP BY a.bulan_cohort, a.bulan_ke, u.ukuran
ORDER BY a.bulan_cohort, a.bulan_ke;

Kolom bulan_ke = 0 selalu 100%, karena itu bulan semua pelanggan cohort pertama aktif. Kolom setelahnya nunjukin berapa persen yang balik. Pakai COUNT(DISTINCT) biar pelanggan yang beli dua kali di bulan sama nggak kehitung dobel.

Cara pivot jadi bentuk matriks

Hasil di atas masih bentuk panjang (satu baris per cohort per bulan). Buat jadiin matriks yang enak dibaca, pivot pakai FILTER jadi tiap bulan_ke satu kolom. Ini bikin tiap cohort tampil di satu baris.

SELECT
    bulan_cohort,
    MAX(retensi_persen) FILTER (WHERE bulan_ke = 0) AS bln_0,
    MAX(retensi_persen) FILTER (WHERE bulan_ke = 1) AS bln_1,
    MAX(retensi_persen) FILTER (WHERE bulan_ke = 2) AS bln_2,
    MAX(retensi_persen) FILTER (WHERE bulan_ke = 3) AS bln_3
FROM matriks_persen
GROUP BY bulan_cohort
ORDER BY bulan_cohort;

Anggap matriks_persen itu hasil query persen tadi yang dibungkus jadi CTE. Sekarang tiap cohort satu baris, dan tiap kolom nunjukin retensi di bulan ke berapa. Ini bentuk yang biasa kamu lihat sebagai segitiga retensi.

Contoh kasus: retensi promo mega_retail

Di dataset mega_retail, cohort bulan promo besar (Agustus) masuk paling ramai, 1.100 pelanggan baru. Tapi matriks retensinya nunjukin cuma 18% yang balik di bulan berikutnya, dan tinggal 9% di bulan ketiga.

Bandingin sama cohort Juli yang datang organik: dari 620 pelanggan, 34% balik di bulan pertama dan masih 21% di bulan ketiga. Angkanya jelas: pelanggan hasil promo besar datang banyak tapi cepat kabur, sementara pelanggan organik lebih betah. Tanpa matriks cohort, dua pola ini ketutup sama total pelanggan yang kelihatan naik.

Kesalahan umum

• Lupa COUNT(DISTINCT). Pelanggan yang beli beberapa kali di bulan sama bikin retensi kelihatan lebih dari 100%.
• Cohort dari tanggal daftar, bukan transaksi pertama. Pilih patokan yang sesuai pertanyaan, dan konsisten dipakai.
• Ngebandingin cohort muda dan tua langsung. Cohort baru belum punya data bulan ke-3, jadi kolomnya masih kosong dan jangan dianggap nol.

FAQ

Apa beda retensi cohort dan repeat purchase rate?

Repeat purchase rate ngukur berapa persen pelanggan yang belanja lagi secara keseluruhan, tanpa mandang kapan mereka mulai. Retensi cohort mecah angka itu per kelompok bulan, jadi kamu bisa lihat pola betah tiap generasi pelanggan dari waktu ke waktu. Cohort ngasih gambaran lebih detail karena nunjukin di bulan ke berapa pelanggan mulai kabur, bukan cuma satu angka rata-rata.

Kenapa pakai DATE_TRUNC di cohort?

DATE_TRUNC ngebulatin tanggal ke awal periode, misalnya awal bulan, biar semua transaksi di bulan yang sama dianggap satu titik. Tanpa ini, tiap tanggal beda dianggap cohort beda dan matriksnya jadi kacau. Dengan DATE_TRUNC ke bulan, kamu dapat cohort bulanan yang rapi. Kamu juga bisa ganti ke minggu atau kuartal tinggal ubah argumennya sesuai kebutuhan analisis.

Gimana kalau mau retensi mingguan, bukan bulanan?

Tinggal ganti argumen DATE_TRUNC dari 'month' jadi 'week' di bagian cohort dan aktivitas. Perhitungan jarak periodenya juga disesuaikan pakai selisih minggu. Retensi mingguan berguna buat produk yang dipakai sering, kayak aplikasi atau layanan langganan, karena pola kaburnya keliatan lebih cepat. Buat toko yang transaksinya nggak terlalu sering, retensi bulanan biasanya lebih pas dibaca.

Kenapa sebagian sel matriks kosong?

Karena cohort yang baru terbentuk belum punya cukup waktu buat ngisi kolom bulan berikutnya. Cohort September nggak mungkin punya data bulan ke-3 kalau sekarang baru Oktober. Sel kosong ini normal dan jangan diperlakukan sebagai nol, karena artinya bukan retensinya nol, tapi datanya memang belum ada. Waktu baca matriks, fokus ke cohort yang udah cukup matang buat perbandingan yang adil.

Baca perilaku pelanggan, bukan cuma jumlahnya

Intinya: matriks retensi cohort dibangun dari tiga langkah, yaitu tentukan bulan cohort, hitung jarak bulan aktivitas, lalu bagi pelanggan aktif dengan ukuran cohort. Pivot pakai FILTER buat dapat bentuk segitiga yang gampang dibaca.

Metrik ini nyambung erat sama time to first purchase dan repeat purchase rate. Mau latihan nulis query cohort sendiri? Kulik langsung di NgulikSQL.

Detail fungsi tanggal yang aku pakai ada di dokumentasi PostgreSQL.