Customer Lifetime Value SQL: Hitung LTV dari Data Transaksi

Customer lifetime value (LTV) adalah total profit yang kamu dapat dari satu pelanggan selama dia belanja di kamu.

Angka ini nentuin satu hal yang penting banget: berapa maksimal yang boleh kamu keluarin buat dapetin satu pelanggan baru.

Kalau LTV kamu 400 ribu dan biaya iklan per pelanggan baru 600 ribu, kamu rugi 200 ribu tiap kali ada yang daftar. Iklannya makin jalan, ruginya makin gede.

Apa itu customer lifetime value?

LTV itu profit, bukan omzet. Ini bagian yang paling sering keliru.

Pelanggan yang belanja 2 juta seumur hidup dengan margin 25% ngasih kamu LTV 500 ribu, bukan 2 juta.

Rumus dasarnya:

LTV = AOV x Frekuensi per tahun x Umur pelanggan (tahun) x Margin

Contoh konkret:

• AOV (rata-rata nilai transaksi): Rp 200.000
• Frekuensi: 4 kali per tahun
• Umur pelanggan: 2 tahun
• Gross margin: 25%

LTV = 200.000 x 4 x 2 x 0,25 = Rp 400.000

Pakai gross margin (penjualan dikurangi HPP), bukan net margin. Gaji dan sewa kantor nggak naik proporsional tiap kamu dapat satu pelanggan tambahan.

Query dasar: LTV historis per pelanggan

Anggap kamu punya tabel transaksi dengan kolom: order_id, customer_id, tanggal, total_harga.

SELECT
  customer_id,
  COUNT(DISTINCT order_id)        AS total_order,
  SUM(total_harga)                AS total_belanja,
  ROUND(AVG(total_harga), 0)      AS aov,
  MIN(tanggal)                    AS first_order,
  MAX(tanggal)                    AS last_order,
  ROUND(SUM(total_harga) * 0.25, 0) AS ltv_profit
FROM transaksi
GROUP BY customer_id
ORDER BY ltv_profit DESC;

Angka 0.25 itu gross margin kamu. Ganti sesuai bisnis kamu.

Query ini ngasih LTV historis. Total yang udah terjadi sampai hari ini. Sederhana, dan cukup buat 90% keputusan.

Kekurangannya: dia meremehkan pelanggan yang baru gabung bulan lalu tapi bakal belanja terus 3 tahun ke depan.

Kalau GROUP BY masih bikin bingung, cek dulu GROUP BY dan SUM.

Nambahin umur pelanggan dan frekuensi

LTV historis doang belum cukup buat mutusin budget. Kamu perlu tau ritme belanja pelanggan.

SELECT
  customer_id,
  COUNT(DISTINCT order_id) AS total_order,
  ROUND(AVG(total_harga), 0) AS aov,
  (MAX(tanggal) - MIN(tanggal)) AS umur_hari,
  ROUND(
    COUNT(DISTINCT order_id)::numeric
    / NULLIF((MAX(tanggal) - MIN(tanggal)), 0) * 365,
    2
  ) AS order_per_tahun
FROM transaksi
GROUP BY customer_id
HAVING COUNT(DISTINCT order_id) >= 2
ORDER BY order_per_tahun DESC;

Dua hal penting di query ini.

NULLIF(..., 0) nyegah error bagi nol buat pelanggan yang first dan last order-nya di hari yang sama.

HAVING COUNT(...) >= 2 buang pelanggan yang baru sekali belanja. Mereka nggak punya frekuensi yang bisa diukur, dan kalau ikut dihitung mereka bikin rata-rata kamu ngaco.

Contoh kasus: toko_berkah online

Data 18 bulan toko_berkah versi online: 3.240 pelanggan, 11.847 transaksi. Semua angka di bawah dari dataset ngulikdata.

Aku pakai NTILE buat bagi pelanggan jadi 10 kelompok berdasarkan LTV:

WITH ltv AS (
  SELECT
    customer_id,
    SUM(total_harga) * 0.25 AS ltv_profit
  FROM transaksi
  GROUP BY customer_id
)
SELECT
  NTILE(10) OVER (ORDER BY ltv_profit DESC) AS desil,
  COUNT(*) AS jumlah_pelanggan,
  ROUND(AVG(ltv_profit), 0) AS ltv_rata2,
  ROUND(SUM(ltv_profit), 0) AS total_ltv
FROM ltv
GROUP BY desil
ORDER BY desil;

Hasilnya (dipendekin ke 4 baris kunci):

Desil	Jumlah pelanggan	LTV rata-rata	% dari total profit
1 (teratas)	324	Rp 1.842.000	41,2%
2	324	Rp 687.000	15,4%
5 (tengah)	324	Rp 218.000	4,9%
10 (terbawah)	324	Rp 41.000	0,9%

10% pelanggan teratas ngasih 41% dari total profit.

Rata-rata LTV keseluruhan: Rp 447.000. Tapi angka rata-rata itu nyesatin. Desil 1 punya LTV 4x lipat dari rata-rata, desil 10 punya sepersepuluhnya.

Kalau kamu nyetel budget iklan berdasarkan rata-rata, kamu bakal kemahalan buat pelanggan biasa dan kemurahan buat pelanggan bagus.

Yang toko_berkah lakuin: bikin program khusus buat desil 1 dan 2. Gratis ongkir permanen, akses stok baru duluan. Biayanya kecil, dan mereka nyumbang 56% profit.

LTV per cohort: lihat apakah kualitas pelanggan naik

Pertanyaan yang lebih tajam: pelanggan yang kamu dapat bulan ini lebih bagus atau lebih jelek dari yang kamu dapat 6 bulan lalu?

WITH cohort AS (
  SELECT
    customer_id,
    DATE_TRUNC('month', MIN(tanggal)) AS cohort_bulan
  FROM transaksi
  GROUP BY customer_id
)
SELECT
  c.cohort_bulan,
  COUNT(DISTINCT c.customer_id) AS pelanggan,
  ROUND(
    SUM(t.total_harga) * 0.25
    / COUNT(DISTINCT c.customer_id),
    0
  ) AS ltv_rata2
FROM cohort c
JOIN transaksi t ON c.customer_id = t.customer_id
GROUP BY c.cohort_bulan
ORDER BY c.cohort_bulan;

Satu catatan penting waktu baca hasilnya: cohort yang lebih lama pasti punya LTV lebih tinggi, soalnya mereka punya lebih banyak waktu buat belanja.

Buat bandingin adil, potong semuanya di jendela waktu yang sama (misalnya LTV di 90 hari pertama tiap pelanggan). Tambahin ini di JOIN:

JOIN transaksi t
  ON c.customer_id = t.customer_id
  AND t.tanggal < c.cohort_bulan + INTERVAL '90 days'

Sekarang kamu bandingin apel sama apel.

Rasio LTV terhadap CAC

LTV sendirian nggak bilang apa-apa. Dia baru berguna kalau dibandingin sama CAC (customer acquisition cost): biaya yang kamu keluarin buat dapetin satu pelanggan.

CAC = Total biaya marketing / Jumlah pelanggan baru

Rasio LTV : CAC	Artinya
Di bawah 1 : 1	Rugi di tiap pelanggan baru. Stop iklan, benerin produk.
1 : 1 sampai 3 : 1	Tipis. Bisa jalan, tapi rapuh.
3 : 1	Patokan sehat yang umum dipakai.
Di atas 5 : 1	Kamu mungkin terlalu pelit spend iklan.

Toko_berkah punya LTV rata-rata Rp 447.000 dan CAC Rp 138.000. Rasionya 3,2 : 1. Sehat, dan masih ada ruang buat nambah spend.

Kesalahan umum waktu ngitung LTV

Pakai omzet, bukan profit. Ini bikin LTV kamu kelihatan 4x lebih besar dari aslinya, dan bikin kamu berani spend iklan yang sebenernya rugi.

Ngerata-ratain semua pelanggan jadi satu angka. Distribusi LTV itu selalu miring. Sedikit pelanggan bagus banget, banyak pelanggan biasa aja. Rata-rata nyembunyiin itu. Pakai median dan desil.

Masukin pelanggan yang baru gabung minggu lalu. LTV mereka pasti kecil, dan itu bukan salah mereka. Filter minimal 90 hari sejak first order, atau pakai analisis cohort.

Lupa refund dan retur. Kalau tabel transaksi kamu nyimpen retur sebagai baris terpisah, pastiin nilainya negatif atau kamu filter statusnya.

Ngeklaim LTV naik gara-gara program loyalti. Ini jebakan sebab-akibat. Pelanggan yang ikut program loyalti mungkin emang udah loyal dari awal. Bahasan lengkapnya ada di Korelasi vs Kausalitas.

Kapan butuh LTV prediktif?

LTV historis mentok kalau bisnis kamu tumbuh cepat. Mayoritas pelanggan kamu baru, jadi angka historisnya selalu kelihatan jelek.

Di titik itu, orang mulai pakai model prediktif kayak BG/NBD atau Gamma-Gamma. Model ini nebak berapa lagi transaksi yang bakal dibikin pelanggan berdasarkan pola belanjanya.

Tapi jangan lompat ke sana kalau LTV historis kamu belum rapi. Model canggih di atas data yang kotor tetap ngasih jawaban yang salah, cuma dengan lebih banyak desimal.

Buat referensi metodenya, dokumentasi library Lifetimes ngejelasin model BG/NBD dengan contoh yang bisa langsung dicoba.

FAQ

Apa itu customer lifetime value?

Customer lifetime value atau LTV adalah total profit yang kamu dapat dari satu pelanggan selama dia jadi pelanggan kamu. Bukan total belanjanya, tapi profit. Jadi harus dikali margin. Angka ini kepakai buat mutusin berapa maksimal biaya yang masuk akal buat dapetin satu pelanggan baru.

Gimana rumus LTV yang paling sederhana?

LTV = AOV x frekuensi belanja per tahun x umur pelanggan dalam tahun x margin profit. Contoh: pelanggan rata-rata belanja 200 ribu, 4 kali setahun, bertahan 2 tahun, margin 25%. LTV-nya = 200.000 x 4 x 2 x 0,25 = 400 ribu. Rumus ini kasar tapi cukup buat ngambil keputusan budget iklan.

Bedanya LTV historis dan LTV prediktif apa?

LTV historis ngitung dari transaksi yang udah terjadi. Kamu jumlahin semua belanja pelanggan sampai hari ini. Gampang dihitung pakai SQL biasa, tapi dia meremehkan pelanggan yang masih aktif. LTV prediktif nebak berapa lagi yang bakal mereka belanjain di masa depan. Buat kebanyakan UMKM dan startup awal, LTV historis udah cukup.

Rasio LTV terhadap CAC yang sehat berapa?

Patokan yang umum dipakai adalah 3 banding 1. LTV kamu minimal tiga kali biaya akuisisi pelanggan. Di bawah 1 banding 1 artinya kamu rugi di tiap pelanggan baru. Di atas 5 banding 1 kedengeran bagus, tapi sering artinya kamu terlalu pelit spend iklan.

Margin berapa yang harus dipakai buat hitung LTV?

Pakai gross margin, bukan net margin. Gross margin itu penjualan dikurangi harga pokok penjualan, dibagi penjualan. Jangan pakai net margin yang udah dikurangi gaji dan sewa, soalnya biaya itu nggak naik proporsional per pelanggan tambahan.

Penutup

Coba jalanin query pertama di data kamu hari ini. Lihat berapa persen profit yang dateng dari 10% pelanggan teratas. Angkanya biasanya bikin kaget.

Query di artikel ini pakai GROUP BY, window function, dan CTE. Kalau tiga itu belum lancar, latihan dulu di NgulikSQL, dan cek glossary cohort analysis buat konteks tambahannya.