Cohort Analysis dengan SQL: Panduan Lengkap untuk Data Analyst

Apa Itu Cohort Analysis?

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:

• Ngukur seberapa "sticky" produk kamu
• Identifikasi kapan user mulai churn
• Bandingin kualitas user dari campaign berbeda
• Evaluasi dampak dari product changes

Sebagai Data Analyst di startup Indonesia, skill ini wajib banget kamu kuasai. Trust me.

Jenis-Jenis Cohort

Sebelum masuk ke SQL, kamu perlu tau dulu ada beberapa jenis cohort:

1. Acquisition Cohort (Paling Umum)

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.

2. Behavioral Cohort

User dikelompokin berdasarkan behavior tertentu.

Contoh: "User yang pernah pake fitur transfer" vs "User yang belum pernah transfer".

3. Time-based Cohort

Mirip acquisition, tapi bisa berdasarkan event apapun.

Contoh: "User yang pertama kali top-up di Q1 2024".

Dataset yang Akan Kita Pakai

Contoh kasusnya gini: kamu Data Analyst di aplikasi e-wallet Indonesia. Kamu punya data transaksi user selama 6 bulan.

Tabel: users

Tabel: transactions

Datanya cukup buat ilustrasi. Di production, kamu bakal deal dengan jutaan records, tapi konsepnya sama aja.

Step 1: Tentukan Cohort Bulan

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:

DATE_TRUNC('month', date) itu fungsi buat "memotong" tanggal jadi awal bulan. Jadi 2024-01-15 jadi 2024-01-01.

Step 2: Hitung Cohort Index (Periode Sejak Signup)

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:

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!

Step 3: Hitung Unique Users per Cohort per Period

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 Januari punya 3 user di period 0 (bulan pertama). Di period 1, tinggal 2 user yang aktif. Ini normal, ada user yang churn.

Step 4: Hitung Cohort Size

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:

Step 5: Full Retention Cohort Query

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:

Nah ini dia! Sekarang kamu bisa liat retention rate per cohort per bulan.

Step 6: Bikin Pivot Table (Cohort Heatmap Format)

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:

Ini format yang biasanya dipake buat bikin heatmap di Excel atau BI tools. Tinggal copy paste, kasih conditional formatting, kelar deh.

Interpretasi Hasil

Oke, data udah ada. Sekarang gimana cara bacanya?

Cohort Januari 2024

• Month 0: 100% (semua user transaksi di bulan pertama, expected)
• Month 1: 66.7% (1 user churn, 2 masih aktif)
• Month 2: 100% (wow, ada yang balik! atau emang aktif semua)
• Month 3: 66.7% (konsisten dengan Month 1)

Cohort ini lumayan bagus. Retention-nya stabil di sekitar 66%.

Cohort Februari 2024

• Month 0: 100%
• Month 1: 33.3% (drop drastis! ada masalah nih)
• Month 2: 33.3% (stabil tapi rendah)

Cohort ini perlu diinvestigasi. Kenapa drop-nya gede banget di Month 1?

Insights yang Bisa Diambil

1. Cohort Januari lebih bagus dari Februari - Mungkin ada perbedaan channel acquisition atau ada product change di Februari?

2. Drop terbesar di Month 1 - Ini common pattern. User yang ga ngerasain value di bulan pertama biasanya churn.

3. Cohort April ada anomali - Month 0 cuma 50%. Ada user yang signup tapi ga transaksi sama sekali. Perlu cek onboarding flow.

Template Query yang Bisa Langsung Dipakai

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;

Variasi: Weekly Cohort

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.

Variasi: Retention by Segment

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?"

Common Mistakes yang Harus Dihindari

Mistake 1: Lupa DATE_TRUNC

-- 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);

Mistake 2: Pake COUNT(*) Instead of COUNT(DISTINCT)

-- 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;

Mistake 3: Ga Handle User Tanpa Activity

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
)

Tips dan Best Practices

1. Pilih Activity yang Tepat

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

2. Tentukan Periode yang Masuk Akal

• Product dengan daily usage: Weekly cohort
• Subscription business: Monthly cohort
• B2B SaaS: Quarterly cohort

3. Exclude Anomali

Kalau ada bulan dengan acquisition spike (misalnya karena promo gede), pertimbangkan buat analyze terpisah.

4. Bandingin Apple to Apple

Kalau mau bandingin cohort, pastikan periode observasinya sama. Cohort yang lebih baru pasti punya data lebih sedikit.

5. Combine dengan Qualitative Data

Angka aja ga cukup. Kalau ada drop, investigate:
- Ada product bug ga?
- Ada competitor baru ga?
- Campaign acquisition-nya gimana?

Latihan

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;

FAQ

Cohort analysis bisa dikerjain di semua database ga?

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.

Bedanya cohort analysis sama retention rate biasa apa?

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.

Berapa lama data minimal biar hasilnya bisa dipercaya?

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.

Kenapa retention rate bisa naik lagi setelah turun?

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.

Kesimpulan

Cohort analysis itu skill yang wajib banget buat Data Analyst. Inget poin-poin ini:

1. Cohort = kelompok user berdasarkan waktu atau karakteristik tertentu
2. Retention rate = persentase user yang masih aktif setelah periode tertentu
3. Pake DATE_TRUNC buat grouping by bulan/minggu
4. Selalu pake COUNT(DISTINCT user_id) buat hitung unique users
5. Interpretasi sama pentingnya dengan bikin query

Intinya, cohort analysis itu cara ngerti customer behavior lewat angka. Angka yang sama bisa punya makna berbeda tergantung konteks bisnis.

Happy analyzing!

Selanjutnya

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