Menghitung Time to First Purchase di SQL

Time to first purchase adalah jarak waktu antara tanggal pelanggan daftar dan tanggal pembelian pertama mereka. Di SQL, kamu ambil tanggal pesanan paling awal per pelanggan lalu kurangi tanggal daftarnya.

Metrik ini nunjukin seberapa cepat pelanggan baru berubah jadi pembeli. Makin pendek jaraknya, makin bagus proses onboarding dan penawaran awal kamu.

Di bawah aku tunjukin query buat hitung selisih harinya, rata-rata, median, sampai segmentasi pelanggan berdasarkan kecepatan beli.

Apa itu time to first purchase?

Time to first purchase adalah selisih hari (atau jam) sejak seorang pelanggan mendaftar sampai dia melakukan transaksi pertama. Angka ini dipakai buat ngukur efektivitas onboarding, promo sambutan, dan kualitas pelanggan yang masuk. Pelanggan yang beli di hari pertama biasanya punya nilai jangka panjang yang lebih tinggi.

Metrik ini nyambung erat sama customer lifetime value. Pelanggan yang cepat beli pertama kali cenderung lebih sering balik belanja.

Gimana cara hitung time to first purchase di SQL?

Cara hitungnya: JOIN tabel pelanggan ke tabel pesanan, ambil MIN tanggal pesanan per pelanggan buat dapat pembelian pertama, lalu kurangi tanggal daftarnya. Hasilnya kolom berisi jumlah hari yang dibutuhkan tiap pelanggan sebelum belanja pertama.

SELECT
    p.pelanggan_id,
    p.tanggal_daftar,
    MIN(o.tanggal_pesan) AS pembelian_pertama,
    MIN(o.tanggal_pesan)::date - p.tanggal_daftar::date AS hari_ke_pembelian_pertama
FROM pelanggan p
JOIN pesanan o ON o.pelanggan_id = p.pelanggan_id
GROUP BY p.pelanggan_id, p.tanggal_daftar;

Di PostgreSQL, kurangin dua nilai date langsung ngasih jumlah hari. Fungsi MIN yang milih tanggal pesanan paling awal per pelanggan, jadi kamu nggak perlu urutin manual.

Pakai JOIN biasa (bukan LEFT JOIN) di sini disengaja. Pelanggan yang belum pernah belanja otomatis kebuang, karena mereka belum punya time to first purchase.

Query rata-rata dan median time to first purchase

Buat dapat gambaran umum, hitung rata-rata dan median dari kolom selisih hari tadi. Median lebih tahan sama pencilan, jadi lebih jujur kalau ada segelintir pelanggan yang baru beli setelah berbulan-bulan. Bungkus query pertama jadi CTE biar rapi.

WITH ttfp AS (
    SELECT
        p.pelanggan_id,
        MIN(o.tanggal_pesan)::date - p.tanggal_daftar::date AS hari
    FROM pelanggan p
    JOIN pesanan o ON o.pelanggan_id = p.pelanggan_id
    GROUP BY p.pelanggan_id, p.tanggal_daftar
)
SELECT
    ROUND(AVG(hari), 1) AS rata_rata_hari,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY hari) AS median_hari
FROM ttfp;

Fungsi PERCENTILE_CONT(0.5) ngasih nilai tengah. Kalau rata-rata jauh lebih besar dari median, artinya ada ekor pelanggan yang lama banget baru beli, dan mereka narik rata-ratanya ke atas.

Cara segmentasi berdasarkan kecepatan beli

Angka tunggal kadang nyembunyiin pola. Pecah pelanggan ke beberapa bucket biar keliatan berapa persen yang beli di hari pertama, minggu pertama, atau lebih lama. Pakai CASE buat bikin kelompoknya.

WITH ttfp AS (
    SELECT
        p.pelanggan_id,
        MIN(o.tanggal_pesan)::date - p.tanggal_daftar::date AS hari
    FROM pelanggan p
    JOIN pesanan o ON o.pelanggan_id = p.pelanggan_id
    GROUP BY p.pelanggan_id, p.tanggal_daftar
)
SELECT
    CASE
        WHEN hari = 0 THEN 'Hari pertama'
        WHEN hari BETWEEN 1 AND 7 THEN 'Minggu pertama'
        WHEN hari BETWEEN 8 AND 30 THEN 'Bulan pertama'
        ELSE 'Lebih dari 30 hari'
    END AS segmen,
    COUNT(*) AS jumlah_pelanggan
FROM ttfp
GROUP BY 1
ORDER BY MIN(hari);

Segmentasi kayak gini bantu tim marketing tau kapan harus dorong penawaran. Kalau banyak yang nyangkut di bucket 8 sampai 30 hari, mungkin promo sambutannya perlu dimajuin.

Contoh kasus: onboarding mega_retail

Di dataset mega_retail, dari 4.200 pelanggan yang daftar sepanjang kuartal, median time to first purchase-nya 6 hari, tapi rata-ratanya 19 hari. Selisih jauh ini datang dari sekitar 12% pelanggan yang baru belanja setelah lebih dari 30 hari.

Setelah dipecah pakai query segmentasi, 31% pelanggan beli di hari pertama, dan 54% beli dalam minggu pertama. Kelompok yang beli di hari pertama ternyata punya repeat rate hampir 2 kali lipat kelompok yang beli setelah sebulan.

Insight ini nyambung ke metrik lanjutan kayak repeat purchase rate. Pelanggan yang cepat masuk cenderung cepat balik.

Kesalahan umum

• Pakai LEFT JOIN tanpa filter. Pelanggan yang belum belanja bikin selisih hari jadi NULL dan ngerusak rata-rata.
• Cuma lihat rata-rata. Median dan segmentasi lebih jujur waktu ada pencilan yang beli setelah berbulan-bulan.
• Salah kolom tanggal. Pastiin tanggal daftar diambil dari tabel pelanggan, bukan tanggal pesanan pertama, biar nggak muter.

FAQ

Apa beda time to first purchase dan repeat purchase rate?

Time to first purchase ngukur seberapa cepat pelanggan baru mulai belanja pertama kali. Repeat purchase rate ngukur berapa persen pelanggan yang balik belanja lagi setelah transaksi pertama. Yang satu soal kecepatan masuk, yang satu soal loyalitas setelah masuk. Keduanya sering dipakai bareng buat nilai kualitas pelanggan dari awal sampai jangka panjang.

Gimana kalau tanggal daftar dan tanggal pesanan formatnya timestamp?

Kalau kamu butuh hitungan dalam hari, cukup cast dua-duanya ke date sebelum dikurangi, kayak di contoh. Kalau kamu mau presisi sampai jam, kurangi langsung sebagai timestamp, hasilnya jadi tipe interval. Dari interval itu kamu bisa ambil jumlah jam pakai EXTRACT. Pilih satuannya sesuai kebutuhan analisis, harian biasanya cukup buat metrik ini.

Kenapa median lebih dipercaya daripada rata-rata?

Karena rata-rata gampang ketarik pencilan. Kalau ada sedikit pelanggan yang baru beli setelah 200 hari, rata-rata bisa naik jauh padahal mayoritas beli dalam seminggu. Median ngambil nilai tengah, jadi dia nggak goyah sama angka ekstrem. Buat metrik kayak time to first purchase yang sering punya ekor panjang, median ngasih gambaran yang lebih mewakili pelanggan biasa.

Bisa nggak hitung time to first purchase per channel akuisisi?

Bisa. Tambahin kolom channel dari tabel pelanggan ke GROUP BY, lalu hitung median per channel. Cara ini nunjukin sumber pelanggan mana yang paling cepat konversi. Misalnya pelanggan dari iklan berbayar mungkin beli lebih cepat daripada yang dari organik. Dari situ kamu bisa nilai channel mana yang ngasih pelanggan berkualitas, bukan cuma banyak.

Ubah data jadi keputusan

Intinya: time to first purchase dihitung dari MIN tanggal pesanan dikurangi tanggal daftar, dan paling berguna kalau dipecah jadi median plus segmentasi. Angka tunggal gampang nyesatin, segmentasi yang nunjukin ke mana harus fokus.

Mau lanjut latihan query metrik pelanggan? Coba tulis sendiri di NgulikSQL, terus baca cara menghitung jarak hari antar order buat metrik pelanggan yang saling nyambung.

Detail fungsi tanggal yang aku pakai ada di dokumentasi PostgreSQL.