TL;DR
Cohort analysis itu teknik buat ngelompokin user berdasarkan waktu pertama kali mereka signup atau transaksi, terus tracking behavior mereka dari waktu ke waktu. Berguna banget buat ngukur retention dan ngerti customer lifecycle.
Pernah dapet pertanyaan kayak gini dari manager: "Gimana sih retention user kita? User yang signup bulan lalu masih aktif ga?"
Nah, cohort analysis itu jawabannya.
Cohort analysis adalah teknik analisis yang ngelompokin user berdasarkan karakteristik atau waktu tertentu, terus tracking behavior mereka dari waktu ke waktu. Biasanya sih yang paling umum itu berdasarkan waktu signup atau waktu transaksi pertama.
Kenapa ini penting banget? Karena dengan cohort analysis, kamu bisa:
Sebagai Data Analyst di startup Indonesia, skill ini wajib banget kamu kuasai. Trust me.
Sebelum masuk ke SQL, kamu perlu tau dulu ada beberapa jenis cohort:
User dikelompokin berdasarkan kapan mereka pertama kali signup atau transaksi. Ini yang bakal kita fokuskan di tutorial ini.
Contoh: "User yang signup di Januari 2024" adalah satu cohort.
User dikelompokin berdasarkan behavior tertentu.
Contoh: "User yang pernah pake fitur transfer" vs "User yang belum pernah transfer".
Mirip acquisition, tapi bisa berdasarkan event apapun.
Contoh: "User yang pertama kali top-up di Q1 2024".
Contoh kasusnya gini: kamu Data Analyst di aplikasi e-wallet Indonesia. Kamu punya data transaksi user selama 6 bulan.
| user_id | signup_date | kota |
|---|---|---|
| 1 | 2024-01-05 | Jakarta |
| 2 | 2024-01-12 | Bandung |
| 3 | 2024-01-20 | Surabaya |
| 4 | 2024-02-03 | Jakarta |
| 5 | 2024-02-15 | Medan |
| 6 | 2024-02-28 | Bandung |
| 7 | 2024-03-10 | Jakarta |
| 8 | 2024-03-22 | Semarang |
| 9 | 2024-04-05 | Surabaya |
| 10 | 2024-04-18 | Jakarta |
| transaction_id | user_id | transaction_date | amount | type |
|---|---|---|---|---|
| 1001 | 1 | 2024-01-05 | 100000 | topup |
| 1002 | 1 | 2024-01-15 | 50000 | transfer |
| 1003 | 2 | 2024-01-12 | 200000 | topup |
| 1004 | 1 | 2024-02-03 | 75000 | payment |
| 1005 | 3 | 2024-01-25 | 150000 | topup |
| 1006 | 2 | 2024-02-10 | 100000 | transfer |
| 1007 | 4 | 2024-02-03 | 300000 | topup |
| 1008 | 1 | 2024-03-12 | 50000 | payment |
| 1009 | 5 | 2024-02-15 | 250000 | topup |
| 1010 | 3 | 2024-03-01 | 80000 | transfer |
| 1011 | 4 | 2024-03-05 | 120000 | payment |
| 1012 | 6 | 2024-02-28 | 180000 | topup |
| 1013 | 2 | 2024-03-20 | 90000 | payment |
| 1014 | 7 | 2024-03-10 | 400000 | topup |
| 1015 | 1 | 2024-04-05 | 60000 | transfer |
| 1016 | 4 | 2024-04-10 | 200000 | topup |
| 1017 | 8 | 2024-03-22 | 350000 | topup |
| 1018 | 3 | 2024-04-15 | 100000 | payment |
| 1019 | 7 | 2024-04-20 | 150000 | transfer |
| 1020 | 9 | 2024-04-05 | 500000 | topup |
Datanya cukup buat ilustrasi. Di production, kamu bakal deal dengan jutaan records, tapi konsepnya sama aja.
Langkah pertama, kita perlu tau setiap user masuk cohort bulan apa. Ini berdasarkan kapan mereka pertama kali signup.
SELECT
user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users;
Hasil:
| user_id | cohort_month |
|---|---|
| 1 | 2024-01-01 |
| 2 | 2024-01-01 |
| 3 | 2024-01-01 |
| 4 | 2024-02-01 |
| 5 | 2024-02-01 |
| 6 | 2024-02-01 |
| 7 | 2024-03-01 |
| 8 | 2024-03-01 |
| 9 | 2024-04-01 |
| 10 | 2024-04-01 |
DATE_TRUNC('month', date) itu fungsi buat "memotong" tanggal jadi awal bulan. Jadi 2024-01-15 jadi 2024-01-01.
Sekarang, untuk setiap transaksi, kita mau tau itu terjadi di bulan ke-berapa sejak user signup. Ini namanya cohort index atau period number.
WITH user_cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users
),
transactions_with_period AS (
SELECT
t.user_id,
t.transaction_date,
uc.cohort_month,
DATE_TRUNC('month', t.transaction_date) AS transaction_month,
-- Hitung selisih bulan
EXTRACT(YEAR FROM DATE_TRUNC('month', t.transaction_date)) * 12
+ EXTRACT(MONTH FROM DATE_TRUNC('month', t.transaction_date))
- EXTRACT(YEAR FROM uc.cohort_month) * 12
- EXTRACT(MONTH FROM uc.cohort_month) AS period_number
FROM transactions t
JOIN user_cohorts uc ON t.user_id = uc.user_id
)
SELECT * FROM transactions_with_period
ORDER BY user_id, transaction_date;
Hasil:
| user_id | transaction_date | cohort_month | transaction_month | period_number |
|---|---|---|---|---|
| 1 | 2024-01-05 | 2024-01-01 | 2024-01-01 | 0 |
| 1 | 2024-01-15 | 2024-01-01 | 2024-01-01 | 0 |
| 1 | 2024-02-03 | 2024-01-01 | 2024-02-01 | 1 |
| 1 | 2024-03-12 | 2024-01-01 | 2024-03-01 | 2 |
| 1 | 2024-04-05 | 2024-01-01 | 2024-04-01 | 3 |
| 2 | 2024-01-12 | 2024-01-01 | 2024-01-01 | 0 |
| 2 | 2024-02-10 | 2024-01-01 | 2024-02-01 | 1 |
| 2 | 2024-03-20 | 2024-01-01 | 2024-03-01 | 2 |
Liat user_id 1? Dia signup bulan Januari, terus transaksi di Januari (period 0), Februari (period 1), Maret (period 2), dan April (period 3). User ini loyal banget!
Sekarang kita hitung berapa user yang aktif di setiap period untuk setiap cohort.
WITH user_cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users
),
user_activities AS (
SELECT
t.user_id,
uc.cohort_month,
EXTRACT(YEAR FROM DATE_TRUNC('month', t.transaction_date)) * 12
+ EXTRACT(MONTH FROM DATE_TRUNC('month', t.transaction_date))
- EXTRACT(YEAR FROM uc.cohort_month) * 12
- EXTRACT(MONTH FROM uc.cohort_month) AS period_number
FROM transactions t
JOIN user_cohorts uc ON t.user_id = uc.user_id
),
cohort_data AS (
SELECT
cohort_month,
period_number,
COUNT(DISTINCT user_id) AS user_count
FROM user_activities
GROUP BY cohort_month, period_number
)
SELECT *
FROM cohort_data
ORDER BY cohort_month, period_number;
Hasil:
| cohort_month | period_number | user_count |
|---|---|---|
| 2024-01-01 | 0 | 3 |
| 2024-01-01 | 1 | 2 |
| 2024-01-01 | 2 | 3 |
| 2024-01-01 | 3 | 2 |
| 2024-02-01 | 0 | 3 |
| 2024-02-01 | 1 | 1 |
| 2024-02-01 | 2 | 1 |
| 2024-03-01 | 0 | 2 |
| 2024-03-01 | 1 | 1 |
| 2024-04-01 | 0 | 1 |
Cohort Januari punya 3 user di period 0 (bulan pertama). Di period 1, tinggal 2 user yang aktif. Ini normal, ada user yang churn.
Kita perlu tau total user di setiap cohort buat ngitung retention rate.
WITH user_cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users
),
cohort_size AS (
SELECT
cohort_month,
COUNT(DISTINCT user_id) AS total_users
FROM user_cohorts
GROUP BY cohort_month
)
SELECT *
FROM cohort_size
ORDER BY cohort_month;
Hasil:
| cohort_month | total_users |
|---|---|
| 2024-01-01 | 3 |
| 2024-02-01 | 3 |
| 2024-03-01 | 2 |
| 2024-04-01 | 2 |
Sekarang kita gabungin semuanya jadi satu query lengkap.
WITH user_cohorts AS (
-- Step 1: Tentukan cohort bulan untuk setiap user
SELECT
user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users
),
cohort_size AS (
-- Step 2: Hitung total user per cohort
SELECT
cohort_month,
COUNT(DISTINCT user_id) AS total_users
FROM user_cohorts
GROUP BY cohort_month
),
user_activities AS (
-- Step 3: Tentukan period number untuk setiap transaksi
SELECT
t.user_id,
uc.cohort_month,
EXTRACT(YEAR FROM DATE_TRUNC('month', t.transaction_date)) * 12
+ EXTRACT(MONTH FROM DATE_TRUNC('month', t.transaction_date))
- EXTRACT(YEAR FROM uc.cohort_month) * 12
- EXTRACT(MONTH FROM uc.cohort_month) AS period_number
FROM transactions t
JOIN user_cohorts uc ON t.user_id = uc.user_id
),
cohort_data AS (
-- Step 4: Hitung unique users per cohort per period
SELECT
cohort_month,
period_number,
COUNT(DISTINCT user_id) AS active_users
FROM user_activities
GROUP BY cohort_month, period_number
)
-- Step 5: Gabungin dengan cohort size dan hitung retention rate
SELECT
cd.cohort_month,
cs.total_users AS cohort_size,
cd.period_number,
cd.active_users,
ROUND(100.0 * cd.active_users / cs.total_users, 1) AS retention_rate
FROM cohort_data cd
JOIN cohort_size cs ON cd.cohort_month = cs.cohort_month
ORDER BY cd.cohort_month, cd.period_number;
Hasil:
| cohort_month | cohort_size | period_number | active_users | retention_rate |
|---|---|---|---|---|
| 2024-01-01 | 3 | 0 | 3 | 100.0 |
| 2024-01-01 | 3 | 1 | 2 | 66.7 |
| 2024-01-01 | 3 | 2 | 3 | 100.0 |
| 2024-01-01 | 3 | 3 | 2 | 66.7 |
| 2024-02-01 | 3 | 0 | 3 | 100.0 |
| 2024-02-01 | 3 | 1 | 1 | 33.3 |
| 2024-02-01 | 3 | 2 | 1 | 33.3 |
| 2024-03-01 | 2 | 0 | 2 | 100.0 |
| 2024-03-01 | 2 | 1 | 1 | 50.0 |
| 2024-04-01 | 2 | 0 | 1 | 50.0 |
Nah ini dia! Sekarang kamu bisa liat retention rate per cohort per bulan.
Buat visualisasi yang lebih gampang dibaca, kita bikin pivot table.
WITH user_cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users
),
cohort_size AS (
SELECT
cohort_month,
COUNT(DISTINCT user_id) AS total_users
FROM user_cohorts
GROUP BY cohort_month
),
user_activities AS (
SELECT
t.user_id,
uc.cohort_month,
EXTRACT(YEAR FROM DATE_TRUNC('month', t.transaction_date)) * 12
+ EXTRACT(MONTH FROM DATE_TRUNC('month', t.transaction_date))
- EXTRACT(YEAR FROM uc.cohort_month) * 12
- EXTRACT(MONTH FROM uc.cohort_month) AS period_number
FROM transactions t
JOIN user_cohorts uc ON t.user_id = uc.user_id
),
cohort_data AS (
SELECT
cohort_month,
period_number,
COUNT(DISTINCT user_id) AS active_users
FROM user_activities
GROUP BY cohort_month, period_number
),
retention_data AS (
SELECT
cd.cohort_month,
cs.total_users,
cd.period_number,
ROUND(100.0 * cd.active_users / cs.total_users, 1) AS retention_rate
FROM cohort_data cd
JOIN cohort_size cs ON cd.cohort_month = cs.cohort_month
)
SELECT
cohort_month,
total_users AS "Cohort Size",
MAX(CASE WHEN period_number = 0 THEN retention_rate END) AS "Month 0",
MAX(CASE WHEN period_number = 1 THEN retention_rate END) AS "Month 1",
MAX(CASE WHEN period_number = 2 THEN retention_rate END) AS "Month 2",
MAX(CASE WHEN period_number = 3 THEN retention_rate END) AS "Month 3"
FROM retention_data
GROUP BY cohort_month, total_users
ORDER BY cohort_month;
Hasil:
| cohort_month | Cohort Size | Month 0 | Month 1 | Month 2 | Month 3 |
|---|---|---|---|---|---|
| 2024-01-01 | 3 | 100.0 | 66.7 | 100.0 | 66.7 |
| 2024-02-01 | 3 | 100.0 | 33.3 | 33.3 | NULL |
| 2024-03-01 | 2 | 100.0 | 50.0 | NULL | NULL |
| 2024-04-01 | 2 | 50.0 | NULL | NULL | NULL |
Ini format yang biasanya dipake buat bikin heatmap di Excel atau BI tools. Tinggal copy paste, kasih conditional formatting, kelar deh.
Oke, data udah ada. Sekarang gimana cara bacanya?
Cohort ini lumayan bagus. Retention-nya stabil di sekitar 66%.
Cohort ini perlu diinvestigasi. Kenapa drop-nya gede banget di Month 1?
Cohort Januari lebih bagus dari Februari - Mungkin ada perbedaan channel acquisition atau ada product change di Februari?
Drop terbesar di Month 1 - Ini common pattern. User yang ga ngerasain value di bulan pertama biasanya churn.
Cohort April ada anomali - Month 0 cuma 50%. Ada user yang signup tapi ga transaksi sama sekali. Perlu cek onboarding flow.
Nih, template yang bisa kamu copy paste dan sesuaikan:
-- =============================================
-- COHORT RETENTION ANALYSIS TEMPLATE
-- =============================================
-- Ganti nama tabel dan kolom sesuai kebutuhan
-- =============================================
WITH user_cohorts AS (
-- Ganti 'users' dengan nama tabel user kamu
-- Ganti 'signup_date' dengan kolom tanggal signup
SELECT
user_id,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users
),
cohort_size AS (
SELECT
cohort_month,
COUNT(DISTINCT user_id) AS total_users
FROM user_cohorts
GROUP BY cohort_month
),
user_activities AS (
-- Ganti 'transactions' dengan tabel activity kamu
-- Bisa events, orders, logins, dll
SELECT
t.user_id,
uc.cohort_month,
EXTRACT(YEAR FROM DATE_TRUNC('month', t.transaction_date)) * 12
+ EXTRACT(MONTH FROM DATE_TRUNC('month', t.transaction_date))
- EXTRACT(YEAR FROM uc.cohort_month) * 12
- EXTRACT(MONTH FROM uc.cohort_month) AS period_number
FROM transactions t -- Ganti dengan tabel activity
JOIN user_cohorts uc ON t.user_id = uc.user_id
-- Optional: tambahin filter
-- WHERE t.transaction_date >= '2024-01-01'
),
cohort_data AS (
SELECT
cohort_month,
period_number,
COUNT(DISTINCT user_id) AS active_users
FROM user_activities
-- Optional: limit period
WHERE period_number <= 12 -- Max 12 bulan
GROUP BY cohort_month, period_number
)
SELECT
TO_CHAR(cd.cohort_month, 'YYYY-MM') AS cohort,
cs.total_users AS cohort_size,
cd.period_number AS month,
cd.active_users,
ROUND(100.0 * cd.active_users / cs.total_users, 1) AS retention_pct
FROM cohort_data cd
JOIN cohort_size cs ON cd.cohort_month = cs.cohort_month
ORDER BY cd.cohort_month, cd.period_number;
Kalau kamu butuh granularity yang lebih detail, bisa pake weekly cohort:
WITH user_cohorts AS (
SELECT
user_id,
DATE_TRUNC('week', signup_date) AS cohort_week
FROM users
),
user_activities AS (
SELECT
t.user_id,
uc.cohort_week,
-- Hitung selisih minggu
FLOOR(EXTRACT(EPOCH FROM DATE_TRUNC('week', t.transaction_date) - uc.cohort_week) / (7 * 24 * 60 * 60)) AS period_number
FROM transactions t
JOIN user_cohorts uc ON t.user_id = uc.user_id
)
SELECT
cohort_week,
period_number AS week_number,
COUNT(DISTINCT user_id) AS active_users
FROM user_activities
GROUP BY cohort_week, period_number
ORDER BY cohort_week, period_number;
Weekly cohort bagus buat produk dengan cycle pendek atau pas lagi running campaign.
Mau bandingin retention antara segmen user? Tambahin aja kolom segmentnya:
WITH user_cohorts AS (
SELECT
user_id,
kota,
DATE_TRUNC('month', signup_date) AS cohort_month
FROM users
),
-- ... (query lanjutan sama kayak sebelumnya)
SELECT
cohort_month,
kota AS segment,
period_number,
COUNT(DISTINCT user_id) AS active_users
FROM user_activities
GROUP BY cohort_month, kota, period_number
ORDER BY cohort_month, kota, period_number;
Ini berguna buat ngejawab pertanyaan kayak "User Jakarta lebih loyal ga dibanding user kota lain?"
-- SALAH (tanggal ga di-truncate, hasilnya berantakan)
SELECT
signup_date AS cohort_month, -- Harusnya pake DATE_TRUNC
COUNT(*)
FROM users
GROUP BY signup_date;
-- BENAR
SELECT
DATE_TRUNC('month', signup_date) AS cohort_month,
COUNT(*)
FROM users
GROUP BY DATE_TRUNC('month', signup_date);
-- SALAH (user yang transaksi berkali-kali kehitung berkali-kali)
SELECT
cohort_month,
period_number,
COUNT(*) AS user_count -- Ini salah!
FROM user_activities
GROUP BY cohort_month, period_number;
-- BENAR
SELECT
cohort_month,
period_number,
COUNT(DISTINCT user_id) AS user_count -- Unique users
FROM user_activities
GROUP BY cohort_month, period_number;
Kadang ada user yang signup tapi ga pernah transaksi. Mereka tetep harus masuk cohort size.
-- Pastikan cohort_size dihitung dari tabel users
-- Bukan dari tabel transactions
cohort_size AS (
SELECT
cohort_month,
COUNT(DISTINCT user_id) AS total_users
FROM user_cohorts -- Dari users, bukan transactions
GROUP BY cohort_month
)
Retention bisa dihitung dari berbagai activity. Pilih yang paling relevan:
- E-commerce: Order completed
- Fintech: Any transaction
- Social app: Daily active (login/open app)
- SaaS: Feature usage
Kalau ada bulan dengan acquisition spike (misalnya karena promo gede), pertimbangkan buat analyze terpisah.
Kalau mau bandingin cohort, pastikan periode observasinya sama. Cohort yang lebih baru pasti punya data lebih sedikit.
Angka aja ga cukup. Kalau ada drop, investigate:
- Ada product bug ga?
- Ada competitor baru ga?
- Campaign acquisition-nya gimana?
Coba kerjain query ini:
Soal: Bikin cohort analysis berdasarkan tipe transaksi pertama user. Kelompokin user jadi cohort "Topup First" (transaksi pertama adalah topup) dan "Other First" (transaksi pertama bukan topup). Hitung retention rate masing-masing.
WITH first_transaction AS (
SELECT
user_id,
type AS first_type,
transaction_date AS first_date,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY transaction_date) AS rn
FROM transactions
),
user_cohorts AS (
SELECT
user_id,
CASE
WHEN first_type = 'topup' THEN 'Topup First'
ELSE 'Other First'
END AS cohort_type,
DATE_TRUNC('month', first_date) AS cohort_month
FROM first_transaction
WHERE rn = 1
),
cohort_size AS (
SELECT
cohort_type,
COUNT(DISTINCT user_id) AS total_users
FROM user_cohorts
GROUP BY cohort_type
),
user_activities AS (
SELECT
t.user_id,
uc.cohort_type,
uc.cohort_month,
EXTRACT(YEAR FROM DATE_TRUNC('month', t.transaction_date)) * 12
+ EXTRACT(MONTH FROM DATE_TRUNC('month', t.transaction_date))
- EXTRACT(YEAR FROM uc.cohort_month) * 12
- EXTRACT(MONTH FROM uc.cohort_month) AS period_number
FROM transactions t
JOIN user_cohorts uc ON t.user_id = uc.user_id
)
SELECT
ua.cohort_type,
ua.period_number,
COUNT(DISTINCT ua.user_id) AS active_users,
cs.total_users,
ROUND(100.0 * COUNT(DISTINCT ua.user_id) / cs.total_users, 1) AS retention_pct
FROM user_activities ua
JOIN cohort_size cs ON ua.cohort_type = cs.cohort_type
GROUP BY ua.cohort_type, ua.period_number, cs.total_users
ORDER BY ua.cohort_type, ua.period_number;
Konsepnya sama di mana-mana. Query di tutorial ini pake syntax PostgreSQL, jadi DATE_TRUNC jalan di Postgres dan Redshift. Di MySQL, ganti pake DATE_FORMAT(tanggal, '%Y-%m-01'). Di BigQuery ada DATE_TRUNC juga, cuma urutan argumennya kebalik. Sisanya kayak JOIN, GROUP BY, dan COUNT(DISTINCT) jalan di semua database kok.
Retention rate biasa cuma kasih satu angka, misalnya "70% user aktif bulan ini". Cohort analysis mecah angka itu per kelompok signup. Jadi kamu bisa liat user Januari bertahan di 66%, sementara user Februari cuma 33%. Dari situ ketahuan batch mana yang bermasalah. Angka rata-rata sering nutupin masalah kayak gini.
Minimal 3 bulan data biar polanya keliatan. Ukuran cohort juga ngaruh banget. Cohort isi 10 user kayak contoh di atas cuma buat latihan. Di production, usahain tiap cohort minimal ratusan user. Kalau cuma puluhan, satu user churn aja udah geser retention rate beberapa persen.
Itu namanya resurrection. User yang sempet nggak aktif, terus balik lagi. Di contoh kita, cohort Januari turun ke 66.7% di Month 1 terus balik ke 100% di Month 2. Biasanya gara-gara promo, notifikasi, atau kebutuhan musiman. Kalau sering kejadian, cek campaign apa yang jalan di bulan itu.
Cohort analysis itu skill yang wajib banget buat Data Analyst. Inget poin-poin ini:
Intinya, cohort analysis itu cara ngerti customer behavior lewat angka. Angka yang sama bisa punya makna berbeda tergantung konteks bisnis.
Happy analyzing!
Kalau kamu udah paham cohort analysis, next step-nya:
- Window Functions - buat analisis yang lebih advanced
- Funnel Analysis - ukur conversion di setiap step
- CTE - bikin query yang lebih readable
Mau praktek langsung? Mulai latihan SQL gratis
Latihan interaktif, langsung di browser.
Cara pakai DATE_TRUNC di SQL buat memotong timestamp jadi awal bulan, minggu, atau jam. Kunci bikin laporan penjualan per periode yang rapi.
Cara mengisi tanggal kosong di time series SQL pakai generate_series dan LEFT JOIN biar grafik dan laporan gak bolong. Contoh penjualan harian UMKM.
Analisa per kuartal, hari kerja, atau musim jadi ribet kalau tiap query ngitung ulang atribut tanggal. Tabel kalender nyimpen semua atribut itu sekali, biar tinggal di-JOIN.