Era Musim Semi Telah Dibuka 😎

Waktunya memperbarui desain dengan nuansa hangat yang menenangkan jiwa. Saya merangkai desain baru agar setiap postingan memancarkan energi musim semi 🔥

Baiklah, sekarang mari kita bicara soal pertanyaan produk 👇

Kamu diberi dua tabel - users dan events. Dan 20 menit untuk mengerjakan tugas:
«Hitunglah retention berdasarkan kohort M0–M3»

Dan di sinilah mereka mengalami kebuntuan.
Jadi dari mana harus memulai? 🤔

Cara termudah - bukan dengan SQL, melainkan dengan gambaran di kepala. Bayangkan sebuah gym.
100 orang membeli keanggotaan - itulah kohortmu.
Selanjutnya, kohort ini biasanya dianggap 100%. Inilah M0.
Tapi ada nuansanya: ini bukan hukum alam, melainkan hanya kesepakatan tidak tertulis.
Keesokan harinya, 60 orang datang - Day 1 = 60%.
Seminggu kemudian, 40 orang datang - Day 7 = 40%.

Selanjutnya logikanya tidak berubah.
Hanya saja, alih-alih hari, gunakan bulan.
Dan alih-alih gym, gunakan produk.

1️⃣ Level Junior - memahami berapa banyak orang yang datang ke setiap kohort
SELECT
  DATE_TRUNC('month', registration_date) AS cohort_month,
  COUNT(DISTINCT user_id) AS new_users
FROM users
GROUP BY 1
ORDER BY 1;

Jika sudah ada kesalahan di sini, semuanya berakhir. Karena DATE_TRUNC dan DISTINCT adalah dasar dari blok produk.
Setidaknya untuk PostgreSQL dan dialek serupa.

2️⃣ Level berikutnya - memahami siapa yang kembali
WITH cohort_dec AS (
  SELECT user_id, registration_date
  FROM users
  WHERE registration_date >= '2023-12-01'
    AND registration_date < '2024-01-01'
)
SELECT
  COUNT(*) AS cohort_size,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 1 THEN user_id END) AS day1,
  COUNT(DISTINCT CASE WHEN event_date = registration_date + 7 THEN user_id END) AS day7
FROM cohort_dec u
LEFT JOIN events e ON u.user_id = e.user_id;

Di sini ada jebakan yang mengintai 🤔

Kode seperti ini normal jika tanggal disimpan sebagai DATE, tanpa waktu.
Tapi jika itu TIMESTAMP, persamaan sederhana bisa merusak perhitungan.
Karena satu orang punya event di 2023-12-08 00:01, yang lain di 2023-12-08 19:42, dan secara formal itu bukan nilai yang sama.

Artinya, kamu perlu mengonversi ke tanggal, atau menghitung melalui rentang.
Jadi logikanya selalu sama:
✅ tetapapkan kohort → lihat siapa yang kembali

Untuk M1, M2, M3 hampir sama, tapi ada poin penting:
👉 yang dibandingkan bukan sekadar tanggal, melainkan pergeseran bulan kalender relatif terhadap bulan pendaftaran (terdengar rumit, akan saya jelaskan sekarang)

Artinya bukan +30 hari. Bulan memiliki panjang berbeda, jadi perhitungan seperti itu meleset.
Untuk retention bulanan, mereka melihat pergeseran bulan kalender, bukan sekadar menambahkan 30 hari.

❌ Di mana lagi jebakan mengintai

1️⃣ Menghitung event, bukan orang
Satu pengguna melakukan 5 event.
Dan tiba-tiba retentionmu lebih dari 100%.
Artinya, kamu menghitung bukan kembalinya orang, melainkan aktivitas.

2️⃣ Menggunakan INNER JOIN
Dan dalam hasil hanya tersisa mereka yang kembali.
Semua yang drop out, lenyap begitu saja.
Hasilnya angka yang cantik. Tapi gambaran yang sepenuhnya palsu.

3️⃣ Tidak menetapkan basis perhitungan
Ukuran kohort biasanya dianggap 100%, dan dari situ semuanya dihitung.
Jika basisnya berubah-ubah, persentase menjadi sampah.

Dan selanjutnya dimulailah masalah utama... tes pemikiran dan soft skills. Inilah jalan paling terjal jika pengalaman minim.
Karena dalam wawancara, kamu tidak akan ditanya:
«Bagaimana cara menghitung retention?»

Tapi akan ditanya:
«D30 turun. Kenapa?»

Selesai...Dan di sini SQL sudah tidak mengesankan siapa pun.

✅ Alur pikir yang normal adalah:
- uraikan retention berdasarkan kohort
- lihat saluran akuisisi
- periksa aktivasi
- pahami apakah onboarding rusak
- bandingkan perilaku sebelum dan sesudah rilis
Bisa digali lebih dalam, tapi untuk memulai, dasar-dasar ini sudah cukup.

❌ Jawaban yang langsung mengakhiri dialogmu
«Hmm… retention menurun karena pengguna menjadi lebih jarang kembali..»

Terima kasih Kapten. Tapi itu bukan jawaban analis, melainkan dugaan.

Jika dijelaskan dengan sederhana:
Retention - ini bukan tentang SQL, ini tentang perilaku.
SQL - hanyalah sekop yang kamu gunakan untuk menggali angka.

Jika kamu tidak bisa menjelaskan mengapa orang berhenti kembali,
maka persentase itu sendiri tidak akan memberi tahu apa pun 🤷‍♀️

Apakah kalian pernah mengalami masalah di blok produk? Dan apakah kalian suka bidang produk atau lebih suka bagian engineering? 👇