Siapa yang Kembali? Analisis Retensi Pelanggan Kedai Kopi dengan SQL

Latar belakang

Kedai kopi dengan aplikasi member ingin tahu apakah pelanggan baru benar-benar kembali, dan pelanggan seperti apa yang layak dijaga. Tanpa analisis, semua promo disebar rata ke semua orang.

Pertanyaan yang dijawab:

1. Berapa persen pelanggan baru yang datang lagi di bulan-bulan berikutnya?
2. Siapa pelanggan setia, dan berapa porsi belanja mereka?
3. Adakah pola di kunjungan pertama yang menandai pelanggan yang akan kembali?

Data

Data contoh yang saya bangkitkan untuk latihan: 1.850 pelanggan dan 6.751 transaksi, Januari–Juni 2026, dalam tiga tabel SQLite.

Alat

SQLite dengan teknik dari kelas SQL Lanjutan: COALESCE, CASE, subkueri, WITH, dan fungsi tanggal strftime.

Proses

1. Merapikan nilai kosong

Sebagian pelanggan mendaftar tanpa mengisi kanal. COALESCE mengganti NULL supaya tidak hilang dari hitungan:

SELECT COALESCE(kanal, 'tidak diisi') AS kanal, COUNT(*) AS jumlah
FROM pelanggan
GROUP BY 1
ORDER BY jumlah DESC;

2. Kunjungan pertama tiap pelanggan

WITH pertama AS (
  SELECT pelanggan_id, MIN(tanggal) AS tgl_pertama
  FROM transaksi
  GROUP BY pelanggan_id
)
SELECT strftime('%Y-%m', tgl_pertama) AS kohort, COUNT(*) AS pelanggan
FROM pertama
GROUP BY kohort;

3. Tabel retensi kohort

Selisih bulan dihitung dari strftime, lalu tiap kohort dibagi ukurannya sendiri:

WITH pertama AS (
  SELECT pelanggan_id, MIN(tanggal) AS tgl_pertama FROM transaksi GROUP BY pelanggan_id
),
aktif AS (
  SELECT DISTINCT t.pelanggan_id,
         strftime('%Y-%m', p.tgl_pertama) AS kohort,
         (CAST(strftime('%Y', t.tanggal) AS INTEGER) - CAST(strftime('%Y', p.tgl_pertama) AS INTEGER)) * 12
         + CAST(strftime('%m', t.tanggal) AS INTEGER) - CAST(strftime('%m', p.tgl_pertama) AS INTEGER) AS bulan_ke
  FROM transaksi t JOIN pertama p ON p.pelanggan_id = t.pelanggan_id
)
SELECT kohort, bulan_ke,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM pertama WHERE strftime('%Y-%m', tgl_pertama) = a.kohort), 1) AS retensi_persen
FROM aktif a
GROUP BY kohort, bulan_ke
ORDER BY kohort, bulan_ke;

4. Segmen pelanggan dengan CASE

WITH per_pelanggan AS (
  SELECT pelanggan_id, COUNT(*) AS kunjungan, SUM(total) AS belanja
  FROM transaksi GROUP BY pelanggan_id
)
SELECT CASE
         WHEN kunjungan >= 8 THEN 'Setia'
         WHEN kunjungan >= 3 THEN 'Kembali'
         ELSE 'Coba-coba'
       END AS segmen,
       COUNT(*) AS pelanggan,
       ROUND(100.0 * SUM(belanja) / (SELECT SUM(total) FROM transaksi), 1) AS persen_omzet
FROM per_pelanggan
GROUP BY segmen;

5. Apa yang dibeli di kunjungan pertama?

Subkueri mengambil kategori menu pada transaksi pertama, lalu dibandingkan dengan apakah pelanggan datang lagi di bulan berikutnya:

SELECT m.kategori,
       COUNT(*) AS pelanggan,
       ROUND(100.0 * AVG(CASE WHEN t.pelanggan_id IN (
         SELECT t2.pelanggan_id FROM transaksi t2
         WHERE strftime('%Y-%m', t2.tanggal) = strftime('%Y-%m', date(t.tanggal, 'start of month', '+1 month'))
       ) THEN 1 ELSE 0 END), 1) AS kembali_bulan_1
FROM transaksi t
JOIN menu m ON m.id = t.menu_id
WHERE t.tanggal = (SELECT MIN(tanggal) FROM transaksi WHERE pelanggan_id = t.pelanggan_id)
  AND t.tanggal < '2026-06-01'
GROUP BY m.kategori
ORDER BY kembali_bulan_1 DESC;

Hasil

• Rata-rata hanya 37% pelanggan baru yang datang lagi di bulan berikutnya, dan tinggal 24% di bulan ke-3.
• Pelanggan Setia (≥8 kunjungan) hanya 11% dari pelanggan, tetapi menyumbang 33% omzet.
• Pelanggan yang pertama kali membeli Paket sarapan kembali 55% di bulan berikutnya, dibanding 39% untuk kopi susu.
• Ada 351 pelanggan Setia/Kembali yang tidak datang lebih dari 45 hari per 30 Juni. Total belanja mereka selama ini Rp54.620.000.

Rekomendasi

1. Tawarkan paket sarapan ke pelanggan baru. Kunjungan pertama dengan paket sarapan paling sering berlanjut; ini bisa jadi promo sambutan di aplikasi.
2. Kirim ajakan kembali ke 351 pelanggan yang menghilang sebelum mereka benar-benar pindah. Daftarnya bisa langsung diambil dari kueri segmen dengan syarat tanggal terakhir.
3. Pantau retensi bulan ke-1 tiap bulan. Kueri kohort di atas cukup dijalankan ulang; angka di bawah 37% berarti ada yang berubah.

Keterbatasan

Ini korelasi, bukan sebab-akibat: pelanggan sarapan mungkin memang pekerja kantoran di sekitar kedai. Untuk membuktikan efek promo, perlu uji coba dengan kelompok pembanding.

Yang saya pelajari

WITH membuat kueri panjang bisa dibaca seperti langkah-langkah, dan CASE mengubah angka mentah jadi segmen yang langsung dipahami pemilik usaha.