Retensi D7 User Baru

Pertanyaan

"Kami baru meluncurkan onboarding versi baru di awal September. Saya mau tahu retensi D7 user baru per tanggal daftar. Data aktivitas terakhir yang masuk tanggal 30 September 2026. Coba jelaskan definisinya dulu, lalu tulis query-nya."

Tabel pengguna:

Tabel events (setiap baris = user membuka aplikasi):

Jawaban Singkat

Retensi D7 = dari user yang daftar di hari X (hari 0), berapa persen yang aktif lagi tepat di hari X+7. Sebelum menghitung, saya pastikan tiga hal: definisi D7 (tepat hari ke-7, atau kapan saja di hari 1 s.d. 7), penyebut hanya berisi user yang hari ke-7-nya sudah lewat, dan aktivitas di hari daftar tidak dihitung sebagai "kembali". Dengan definisi tepat hari ke-7, kohort 1 September 33,3% dan kohort 2 September 50%. U6 tidak ikut karena hari ke-7-nya (4 Oktober) belum terjadi.

Pembahasan

1. Sepakati definisi D7

Ada dua definisi yang sama-sama dipakai di industri, dan hasilnya bisa sangat berbeda:

Pada data ini, definisi tepat hari ke-7 menghasilkan 2 dari 5 user (40%), sedangkan definisi hari 1 s.d. 7 menghasilkan 4 dari 5 (80%). Angka yang sama-sama disebut "retensi D7" bisa berbeda dua kali lipat, jadi tulis definisinya di laporan. Query di bawah memakai definisi tepat hari ke-7.

2. Keluarkan kohort yang belum matang

U6 daftar 27 September. Hari ke-7-nya adalah 4 Oktober, setelah data terakhir. Kalau U6 masuk penyebut, U6 dihitung "tidak kembali" padahal belum sempat. Syaratnya: tanggal_daftar <= 2026-09-30 - 7 hari, yaitu paling lambat 23 September.

3. Hari 0 bukan retensi

U1 dan U3 aktif di hari daftar. Itu aktivitas onboarding, bukan kembali. Dengan mencocokkan tanggal tepat tanggal_daftar + 7, hari 0 otomatis tidak terhitung.

4. Ubah timestamp jadi tanggal

waktu_event adalah TIMESTAMP. Bandingkan setelah CAST(... AS DATE). Kalau membandingkan timestamp langsung dengan tanggal_daftar + 7 hari, hanya event tepat jam 00:00:00 yang cocok.

5. Hindari fan-out

U2 punya dua event. Kalau pengguna langsung di-join ke events lalu COUNT(*), penyebut ikut berlipat (8 baris dari 6 user). Karena itu user yang kembali dikumpulkan dulu di CTE kembali_d7 dengan DISTINCT, baru di-LEFT JOIN ke kohort, sehingga penyebut tetap satu baris per user.

Penelusuran per user

Query

Salin
WITH kohort AS (
  SELECT
    user_id,
    tanggal_daftar
  FROM pengguna
  WHERE tanggal_daftar <= DATE '2026-09-30' - INTERVAL '7 days'
),
kembali_d7 AS (
  SELECT DISTINCT k.user_id
  FROM kohort AS k
  INNER JOIN events AS e
    ON e.user_id = k.user_id
   AND CAST(e.waktu_event AS DATE) = k.tanggal_daftar + INTERVAL '7 days'
)
SELECT
  k.tanggal_daftar,
  COUNT(*) AS user_baru,
  COUNT(r.user_id) AS kembali_d7,
  ROUND(100.0 * COUNT(r.user_id) / COUNT(*), 1) AS retensi_d7_persen
FROM kohort AS k
LEFT JOIN kembali_d7 AS r
  ON r.user_id = k.user_id
GROUP BY k.tanggal_daftar
ORDER BY k.tanggal_daftar;

Di produksi, DATE '2026-09-30' diganti tanggal data terakhir yang benar-benar lengkap (biasanya CURRENT_DATE - 1, karena data hari ini belum selesai masuk).

Contoh Output

Jebakan Umum

• Kohort belum matang ikut dihitung. Retensi kohort terbaru selalu terlihat anjlok, lalu ada yang panik "onboarding baru gagal".
• Tidak menyebut definisi. Membandingkan D7 tim kamu (tepat hari ke-7) dengan D7 kompetitor atau laporan lama (hari 1 s.d. 7) seperti membandingkan apel dengan durian.
• Menghitung aktivitas hari 0. Dengan definisi rolling, syaratnya harus selisih hari BETWEEN 1 AND 7, bukan <= 7.
• Fan-out dari join ke events. COUNT(*) setelah join menghitung event, bukan user.
• INNER JOIN di query akhir. User yang tidak kembali hilang dari penyebut, dan retensi selalu 100%.
• Rata-rata dari persentase. Retensi total bukan rata-rata 33,3% dan 50% (41,7%), tapi total kembali dibagi total user: 2 / 5 = 40%. Kohort yang lebih besar harus berbobot lebih besar.
• Zona waktu. Event jam 23:30 WIB yang tersimpan dalam UTC bisa jatuh ke hari yang berbeda dan menggeser hari ke-7.

Follow-up dari Interviewer

• "Ganti ke definisi hari 1 s.d. 7." Ubah kondisi join jadi CAST(e.waktu_event AS DATE) - k.tanggal_daftar BETWEEN 1 AND 7. Pada data ini hasilnya 66,7% dan 100%.
• "Tambahkan total keseluruhan di baris terakhir." Pakai GROUP BY ROLLUP (k.tanggal_daftar) (DuckDB, PostgreSQL), atau hitung terpisah lalu UNION ALL. Ingat, total dihitung dari jumlah user, bukan rata-rata persentase.
• "Bagaimana menghitung D1, D7, D30 sekaligus?" Hitung selisih_hari = CAST(waktu_event AS DATE) - tanggal_daftar per event, lalu COUNT(DISTINCT CASE WHEN selisih_hari = 7 THEN user_id END) dan seterusnya. Setiap kolom D-n juga punya syarat kematangan sendiri, jadi D30 untuk kohort yang belum 30 hari harus NULL, bukan 0.
• "Retensi D7 kohort September 40%. Apakah onboarding baru berhasil?" Belum bisa disimpulkan dari satu angka. Bandingkan dengan kohort sebelum peluncuran di hari yang sama dalam minggu (efek hari kerja dan akhir pekan), periksa ukuran sampel, dan idealnya pakai A/B test, bukan perbandingan sebelum dan sesudah.

Yang Sebenarnya Diuji

Retensi adalah metrik yang terlihat sederhana tapi penuh jebakan. Interviewer menilai apakah kamu mendefinisikan metrik sebelum menulis SQL, sadar soal kohort yang belum matang (right-censoring), menjaga penyebut tetap benar (LEFT JOIN, tanpa fan-out), dan bisa menjelaskan kenapa angkanya bisa berbeda antar tim.

Catatan Dialect

• PostgreSQL: query sama persis. DATE + INTERVAL menghasilkan TIMESTAMP, tetap bisa dibandingkan dengan DATE. Alternatifnya k.tanggal_daftar + 7 (DATE ditambah integer), yang juga jalan di DuckDB.
• MySQL: DATE_ADD(k.tanggal_daftar, INTERVAL 7 DAY) dan DATE(e.waktu_event).
• BigQuery: DATE_ADD(k.tanggal_daftar, INTERVAL 7 DAY), DATE(e.waktu_event, 'Asia/Jakarta') kalau kolomnya TIMESTAMP UTC, dan SAFE_DIVIDE untuk persentase.