Belajar Data Analyst - Analisis Cohort & Retention

#Pendahuluan

Di episode 4 kalian sempat melihat benih analisis cohort lewat query retention. Episode ini mematangkannya menjadi alat analisis lengkap. Mengapa cohort penting? Karena angka churn agregat menyesatkan: churn 5% bulan ini bisa berasal dari cohort lama yang justru membaik — atau cohort baru yang langsung buruk. Cohort analysis menyingkap kualitas tiap gelombang pelanggan secara terpisah.

Episode ini membangun tiga hal: cara membangun cohort dengan benar, cara membaca retention curve, dan cara menghitung LTV — metrik yang menghubungkan seluruh analisis retensi ke nilai finansial. Kita tutup dengan praktik retention bulanan pada dataset order.

#Membangun Cohort: Kunci ke Periode Akuisisi

Cohort = sekelompok pengguna yang memulai perjalanan mereka di periode yang sama. Untuk bisnis dengan order, periode akuisisi paling masuk akal adalah bulan order pertama.

WITH first_order AS (
    SELECT customer_id,
           DATE_TRUNC('month', MIN(ordered_at)) AS cohort
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT * FROM first_order ORDER BY cohort;

Keputusan penting di sini: definisikan "akuisisi" — order pertama yang completed? Atau signup? Untuk bisnis freemium, signup lebih tepat; untuk bisnis transaksional, order pertama. Kunci konsistensinya: cohort harus menandai awal hubungan dengan bisnis.

#Retention Table dan Retention Curve

Retention dihitung per "bulan ke-N": dari cohort yang akuisisi di bulan M, berapa persen yang masih aktif di bulan M+1, M+2, dan seterusnya.

WITH first_order AS (
    SELECT customer_id,
           DATE_TRUNC('month', MIN(ordered_at)) AS cohort
    FROM orders
    GROUP BY customer_id
),
monthly_active AS (
    SELECT customer_id,
           DATE_TRUNC('month', ordered_at) AS bulan
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id, DATE_TRUNC('month', ordered_at)
)
SELECT
    to_char(f.cohort, 'YYYY-MM') AS cohort,
    COUNT(DISTINCT f.customer_id) AS ukuran,
    COUNT(DISTINCT CASE WHEN m.bulan = f.cohort + INTERVAL '1 month' THEN f.customer_id END) * 100.0 / COUNT(DISTINCT f.customer_id) AS m1,
    COUNT(DISTINCT CASE WHEN m.bulan = f.cohort + INTERVAL '2 month' THEN f.customer_id END) * 100.0 / COUNT(DISTINCT f.customer_id) AS m2,
    COUNT(DISTINCT CASE WHEN m.bulan = f.cohort + INTERVAL '3 month' THEN f.customer_id END) * 100.0 / COUNT(DISTINCT f.customer_id) AS m3
FROM first_order f
LEFT JOIN monthly_active m ON m.customer_id = f.customer_id
GROUP BY f.cohort
ORDER BY f.cohort;

Hasilnya sebuah retention table: baris = cohort, kolom = bulan ke-N, isi = persentase retensi. Dari tabel ini kita menggambar retention curve — garis yang menunjukkan "sisa pelanggan" seiring waktu.

#Membaca Retention Curve

Pola kurva retention membawa diagnosa berbeda:

Pola paling penting yang dicari analis: apakah cohort yang lebih baru lebih baik? Kalau ya, perbaikan produk sedang bekerja; kalau tidak, pertumbuhan pengguna hanyalah memompa air ke ember bocor.

Visualisasi retention curve

import pandas as pd
import matplotlib.pyplot as plt
 
# pivot hasil query SQL: index=cohort, columns=bulan_ke_n
retention = pd.DataFrame({
    "Jan": [100, 45, 30, 25],
    "Feb": [100, 40, 27],
    "Mar": [100, 42],
    "Apr": [100],
}, index=["M1", "M2", "M3", "M4"]).T
 
retention.plot(marker="o", figsize=(8, 4))
plt.ylabel("Retention (%)")
plt.xlabel("Bulan ke-N")
plt.title("Retention Curve per Cohort")
plt.grid(alpha=0.3)
plt.show()

#LTV: Nilai Seumur Hidup Pelanggan

LTV (Lifetime Value) memperkirakan total revenue yang dihasilkan satu pelanggan selama berhubungan dengan bisnis. Rumus sederhana:

LTV = AOV × Frekuensi pembelian per tahun × Umur pelanggan (tahun)

Versi praktis untuk bisnis berlangganan:

LTV = ARPU (revenue rata-rata per pelanggan) × (1 / churn rate)

WITH per_customer AS (
    SELECT customer_id,
           SUM(amount) AS total_revenue,
           COUNT(DISTINCT DATE_TRUNC('month', ordered_at)) AS bulan_aktif
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
)
SELECT
    COUNT(*) AS jumlah_customer,
    ROUND(AVG(total_revenue / NULLIF(bulan_aktif, 0)), 0) AS arpu,
    ROUND(AVG(total_revenue), 0) AS ltv_sejauh_ini
FROM per_customer;

Penting: LTV "sejauh ini" berbeda dari LTV "prediksi seumur hidup". Untuk estimasi lengkap kita menggabungkan retention curve dan margin — topik yang sering jadi pembahasan dengan finance. Yang harus kalian pahami sekarang: LTV tanpa margin adalah revenue, bukan profit — selalu perjelas yang mana.

#Praktik: Analisis Retention Bulanan

Alur kerja end-to-end analisis retention bulanan:

1. Ekstrak cohort & aktivitas bulanan dengan query di atas.
2. Bentuk retention table (pivot).
3. Visualisasi retention curve + heatmap cohort.
4. Temukan cohort yang menyimpang dari pola.
5. Dalami satu anomali: misal cohort Maret tajam turun — cek event saat itu (promo berakhir? harga naik? bug onboarding?).

Langkah 5 adalah bagian yang paling sering dilewatkan analis pemula — padahal di sanalah nilai analisis berada.

#Kesalahan Umum

• Membandingkan cohort dengan usia berbeda. Selalu potong ke jumlah bulan yang sama.
• Mengabaikan ukuran cohort. Retention 0% dari cohort 2 orang tidak bermakna.
• Menghitung retention dari order, bukan dari user. Gunakan COUNT(DISTINCT customer_id), bukan COUNT(*).
• LTV tanpa margin dan tanpa definisi. Nilai "seumur hidup" tanpa batas waktu adalah angka imajiner.

#Penutup

Inti yang harus dibawa pulang:

• Cohort = kelompok pelanggan berdasarkan periode akuisisi; kunci untuk membaca churn yang sesungguhnya.
• Retention table + curve menunjukkan kualitas tiap gelombang pelanggan.
• Pola kurva membawa diagnosa berbeda — bandingkan hanya cohort dengan usia sama.
• LTV = AOV × frekuensi × umur pelanggan (atau ARPU / churn); selalu sertakan margin dan definisi.

Di episode 15 selanjutnya kita menelusuri perjalanan pelanggan dari ujung ke ujung: Analisis Funnel & Conversion — funnel analysis, conversion rate, drop-off, dan channel attribution, lengkap dengan audit funnel checkout. Cohort sudah menunjukkan siapa yang bertahan; sekarang kita cari tahu di mana mereka berhenti!