Funnel Analysis dengan SQL: Mengukur Conversion Rate Step-by-Step

Apa Itu Funnel Analysis?

Pernah dapet pertanyaan kayak gini dari product manager: "Kenapa sih banyak user yang add to cart tapi ga jadi beli?"

Nah, funnel analysis itu jawabannya.

Funnel analysis adalah teknik analisis yang nge-track user journey dari step awal sampai konversi akhir. Disebut "funnel" (corong) karena biasanya jumlah user makin mengecil di setiap step, kayak bentuk corong.

Kenapa penting banget? Soalnya dari funnel analysis, kamu bisa:

• Identifikasi bottleneck di mana user paling banyak drop
• Ukur conversion rate di setiap step
• Prioritas improvement berdasarkan data
• Track impact dari product changes

Di e-commerce, ini wajib banget. Kamu bisa tau apakah masalahnya di halaman product, checkout form, atau payment gateway.

Jenis-Jenis Funnel

1. Marketing Funnel (AIDA)

Awareness → Interest → Desire → Action

Contoh: Visitor website → Sign up → Activated → Subscribed

2. Sales Funnel

Lead → Qualified → Proposal → Negotiation → Closed

Contoh: Contact form → Demo booked → Contract sent → Deal won

3. Product Funnel (E-commerce)

Browse → Add to Cart → Checkout → Payment → Complete

Ini yang bakal kita fokuskan di tutorial ini.

Dataset yang Akan Kita Pakai

Skenarionya: kamu Data Analyst di platform e-commerce Indonesia. Kamu pegang data event tracking dari user.

Tabel: user_events

Funnel Steps

1. product_view - User liat halaman produk
2. add_to_cart - User masukin produk ke keranjang
3. begin_checkout - User mulai checkout
4. add_payment - User isi info pembayaran
5. purchase - User selesai beli

Basic Funnel: Hitung User per Step

Langkah pertama, hitung berapa unique user di setiap step.

SELECT
    'product_view' AS step,
    1 AS step_order,
    COUNT(DISTINCT user_id) AS users
FROM user_events
WHERE event_name = 'product_view'

UNION ALL

SELECT
    'add_to_cart',
    2,
    COUNT(DISTINCT user_id)
FROM user_events
WHERE event_name = 'add_to_cart'

UNION ALL

SELECT
    'begin_checkout',
    3,
    COUNT(DISTINCT user_id)
FROM user_events
WHERE event_name = 'begin_checkout'

UNION ALL

SELECT
    'add_payment',
    4,
    COUNT(DISTINCT user_id)
FROM user_events
WHERE event_name = 'add_payment'

UNION ALL

SELECT
    'purchase',
    5,
    COUNT(DISTINCT user_id)
FROM user_events
WHERE event_name = 'purchase'

ORDER BY step_order;

Hasil:

Interpretasi:
- 80% user yang liat produk add to cart (bagus!)
- 75% yang add to cart mulai checkout
- 66.7% yang checkout isi payment (drop terbesar!)
- Overall, 40% yang liat produk akhirnya beli

Menghitung Drop-off Rate

Drop-off rate itu kebalikan dari conversion rate.

WITH funnel AS (
    -- ... (query sama kayak sebelumnya)
),
with_lag AS (
    SELECT
        step,
        step_order,
        users,
        LAG(users) OVER (ORDER BY step_order) AS prev_users
    FROM funnel
)
SELECT
    step,
    users,
    prev_users,
    prev_users - users AS dropped_users,
    ROUND(100.0 * (prev_users - users) / prev_users, 1) AS drop_off_rate
FROM with_lag
WHERE prev_users IS NOT NULL
ORDER BY step_order;

Hasil:

Insight: Drop-off terbesar di step add_payment (33.3%). Mungkin ada masalah dengan payment form atau opsi payment yang kurang.

Funnel dengan CASE WHEN (Metode Alternatif)

Cara yang lebih compact pake CASE WHEN:

SELECT
    COUNT(DISTINCT user_id) AS total_users,
    COUNT(DISTINCT CASE WHEN event_name = 'product_view' THEN user_id END) AS step1_product_view,
    COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS step2_add_to_cart,
    COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN user_id END) AS step3_checkout,
    COUNT(DISTINCT CASE WHEN event_name = 'add_payment' THEN user_id END) AS step4_payment,
    COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step5_purchase
FROM user_events;

Hasil:

Lebih simple, tapi kurang fleksibel untuk analisis lebih lanjut.

Sequential Funnel (Ordered Steps)

Funnel di atas cuma ngitung user yang pernah melakukan event, tanpa memperhatikan urutan. Untuk strict sequential funnel:

WITH user_funnel AS (
    SELECT
        user_id,
        MAX(CASE WHEN event_name = 'product_view' THEN 1 ELSE 0 END) AS did_product_view,
        MAX(CASE WHEN event_name = 'add_to_cart' THEN 1 ELSE 0 END) AS did_add_to_cart,
        MAX(CASE WHEN event_name = 'begin_checkout' THEN 1 ELSE 0 END) AS did_checkout,
        MAX(CASE WHEN event_name = 'add_payment' THEN 1 ELSE 0 END) AS did_payment,
        MAX(CASE WHEN event_name = 'purchase' THEN 1 ELSE 0 END) AS did_purchase,
        -- Check sequence
        MIN(CASE WHEN event_name = 'product_view' THEN event_timestamp END) AS ts_product_view,
        MIN(CASE WHEN event_name = 'add_to_cart' THEN event_timestamp END) AS ts_add_to_cart,
        MIN(CASE WHEN event_name = 'begin_checkout' THEN event_timestamp END) AS ts_checkout,
        MIN(CASE WHEN event_name = 'add_payment' THEN event_timestamp END) AS ts_payment,
        MIN(CASE WHEN event_name = 'purchase' THEN event_timestamp END) AS ts_purchase
    FROM user_events
    GROUP BY user_id
),
sequential_check AS (
    SELECT
        user_id,
        -- Sequential: setiap step harus setelah step sebelumnya
        CASE WHEN did_product_view = 1 THEN 1 ELSE 0 END AS reached_step1,
        CASE WHEN did_add_to_cart = 1 AND ts_add_to_cart > ts_product_view THEN 1 ELSE 0 END AS reached_step2,
        CASE WHEN did_checkout = 1 AND ts_checkout > ts_add_to_cart THEN 1 ELSE 0 END AS reached_step3,
        CASE WHEN did_payment = 1 AND ts_payment > ts_checkout THEN 1 ELSE 0 END AS reached_step4,
        CASE WHEN did_purchase = 1 AND ts_purchase > ts_payment THEN 1 ELSE 0 END AS reached_step5
    FROM user_funnel
)
SELECT
    SUM(reached_step1) AS step1_users,
    SUM(reached_step2) AS step2_users,
    SUM(reached_step3) AS step3_users,
    SUM(reached_step4) AS step4_users,
    SUM(reached_step5) AS step5_users
FROM sequential_check;

Segmented Funnel: By Device

Sekarang kita breakdown funnel berdasarkan device.

WITH funnel_by_device AS (
    SELECT
        device,
        COUNT(DISTINCT CASE WHEN event_name = 'product_view' THEN user_id END) AS step1,
        COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS step2,
        COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN user_id END) AS step3,
        COUNT(DISTINCT CASE WHEN event_name = 'add_payment' THEN user_id END) AS step4,
        COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step5
    FROM user_events
    GROUP BY device
)
SELECT
    device,
    step1 AS product_view,
    step2 AS add_to_cart,
    step3 AS checkout,
    step4 AS payment,
    step5 AS purchase,
    ROUND(100.0 * step5 / step1, 1) AS overall_conversion
FROM funnel_by_device
ORDER BY overall_conversion DESC;

Hasil:

Insight keren nih: Desktop overall conversion 50%, mobile 33.3%. Sample-nya kecil banget (cuma 5 user), jadi jangan langsung ambil kesimpulan besar. Tapi polanya beda: di desktop user baru drop pas isi payment, sedangkan di mobile banyak yang berhenti sebelum masuk checkout. Kalau pola ini bertahan di data yang lebih gede, mobile checkout flow layak dicek.

Time-based Funnel

Analisis berapa lama user butuh waktu dari step ke step.

WITH user_journey AS (
    SELECT
        user_id,
        MIN(CASE WHEN event_name = 'product_view' THEN event_timestamp END) AS ts_step1,
        MIN(CASE WHEN event_name = 'add_to_cart' THEN event_timestamp END) AS ts_step2,
        MIN(CASE WHEN event_name = 'begin_checkout' THEN event_timestamp END) AS ts_step3,
        MIN(CASE WHEN event_name = 'purchase' THEN event_timestamp END) AS ts_step5
    FROM user_events
    GROUP BY user_id
),
time_to_convert AS (
    SELECT
        user_id,
        EXTRACT(EPOCH FROM (ts_step2 - ts_step1)) / 60 AS mins_to_cart,
        EXTRACT(EPOCH FROM (ts_step3 - ts_step2)) / 60 AS mins_to_checkout,
        EXTRACT(EPOCH FROM (ts_step5 - ts_step1)) / 60 AS total_mins
    FROM user_journey
    WHERE ts_step5 IS NOT NULL  -- Hanya yang convert
)
SELECT
    ROUND(AVG(mins_to_cart), 1) AS avg_mins_to_cart,
    ROUND(AVG(mins_to_checkout), 1) AS avg_mins_to_checkout,
    ROUND(AVG(total_mins), 1) AS avg_total_mins,
    ROUND(MIN(total_mins), 1) AS min_total_mins,
    ROUND(MAX(total_mins), 1) AS max_total_mins
FROM time_to_convert;

Hasil:

Di dataset ini cuma 2 user yang sampai purchase, dan dua-duanya butuh 10 menit dari product view sampai beli. Rata-rata ke add to cart 2.5 menit.

Funnel by Time Period

Tracking funnel over time buat liat trend.

WITH daily_funnel AS (
    SELECT
        DATE(event_timestamp) AS date,
        COUNT(DISTINCT CASE WHEN event_name = 'product_view' THEN user_id END) AS step1,
        COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS step2,
        COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step5
    FROM user_events
    GROUP BY DATE(event_timestamp)
)
SELECT
    date,
    step1 AS views,
    step5 AS purchases,
    ROUND(100.0 * step5 / NULLIF(step1, 0), 1) AS conversion_rate
FROM daily_funnel
ORDER BY date;

Template Query Siap Pakai

Nih template yang bisa kamu copy paste:

-- =============================================
-- FUNNEL ANALYSIS TEMPLATE
-- =============================================
-- Ganti event_name sesuai tracking kamu
-- =============================================

WITH funnel_data AS (
    SELECT
        -- Ganti dengan dimensi segmentasi yang kamu mau
        DATE_TRUNC('week', event_timestamp) AS period,
        device,

        -- Step counts
        COUNT(DISTINCT CASE WHEN event_name = 'step1_event' THEN user_id END) AS step1,
        COUNT(DISTINCT CASE WHEN event_name = 'step2_event' THEN user_id END) AS step2,
        COUNT(DISTINCT CASE WHEN event_name = 'step3_event' THEN user_id END) AS step3,
        COUNT(DISTINCT CASE WHEN event_name = 'step4_event' THEN user_id END) AS step4,
        COUNT(DISTINCT CASE WHEN event_name = 'step5_event' THEN user_id END) AS step5
    FROM user_events
    WHERE event_timestamp >= '2024-01-01'  -- Filter periode
    GROUP BY DATE_TRUNC('week', event_timestamp), device
)
SELECT
    period,
    device,
    step1,
    step2,
    step3,
    step4,
    step5,
    -- Conversion rates
    ROUND(100.0 * step2 / NULLIF(step1, 0), 1) AS conv_1_to_2,
    ROUND(100.0 * step3 / NULLIF(step2, 0), 1) AS conv_2_to_3,
    ROUND(100.0 * step4 / NULLIF(step3, 0), 1) AS conv_3_to_4,
    ROUND(100.0 * step5 / NULLIF(step4, 0), 1) AS conv_4_to_5,
    ROUND(100.0 * step5 / NULLIF(step1, 0), 1) AS overall_conv
FROM funnel_data
ORDER BY period, device;

Cara Present ke Stakeholder

1. Lead with the Bottom Line

"Overall conversion rate kita 40%. Dari 1000 user yang liat produk, cuma 400 yang beli."

2. Highlight the Biggest Drop

"Drop-off terbesar ada di step payment (33%). Ini yang harus kita fix dulu."

3. Show Segmentation Insights

"Desktop overall conversion 50%, mobile 33%. Mobile ketinggalan, terutama di langkah sebelum checkout."

4. Give Recommendations

"Rekomendasi: Audit halaman payment dulu, di situ drop-off-nya paling gede."

5. Visualize the Funnel

Product View:     ████████████████████ 100% (1000 users)
Add to Cart:      ████████████████     80% (800 users)
Begin Checkout:   ████████████         60% (600 users)
Add Payment:      ████████             40% (400 users)  ← Biggest drop!
Purchase:         ████████             40% (400 users)

Common Mistakes yang Harus Dihindari

Mistake 1: Ga Define Funnel Window

User bisa add to cart hari ini, tapi baru checkout minggu depan. Tanpa window, funnel kamu bisa misleading.

-- Tambahkan window (misal 7 hari)
WITH user_first_view AS (
    SELECT user_id, MIN(event_timestamp) AS first_view
    FROM user_events WHERE event_name = 'product_view'
    GROUP BY user_id
)
SELECT
    COUNT(DISTINCT e.user_id) AS users_in_window
FROM user_events e
JOIN user_first_view f ON e.user_id = f.user_id
WHERE e.event_timestamp <= f.first_view + INTERVAL '7 days';

Mistake 2: Double Counting

-- SALAH (user bisa add to cart berkali-kali)
SELECT COUNT(*) FROM user_events WHERE event_name = 'add_to_cart';

-- BENAR (unique users)
SELECT COUNT(DISTINCT user_id) FROM user_events WHERE event_name = 'add_to_cart';

Mistake 3: Ignore Seasonality

Conversion rate weekend beda sama weekday. Selalu compare apple to apple.

Tips dan Best Practices

1. Define Events Clearly

Pastikan tracking events udah benar dan konsisten. Garbage in, garbage out.

2. Use Strict vs Loose Funnel

• Strict: User harus lewat semua step secara berurutan
• Loose: User cuma perlu pernah melakukan event (lebih common)

3. Segment Everything

Jangan cuma liat overall. Breakdown by device, platform, user type, acquisition source.

4. Track Over Time

Satu snapshot ga cukup. Track trend mingguan/bulanan buat liat improvement.

5. Set Benchmarks

"Conversion rate 40%" ga ada artinya tanpa konteks. Bandingkan dengan:
- Periode sebelumnya
- Industry benchmark
- Competitor (kalau ada data)

Latihan

Soal: Dari dataset user_events, bikin funnel analysis yang:
1. Breakdown by platform (android, ios, web)
2. Hitung conversion rate tiap step
3. Identifikasi platform mana yang paling butuh improvement

WITH funnel_by_platform AS (
    SELECT
        platform,
        COUNT(DISTINCT CASE WHEN event_name = 'product_view' THEN user_id END) AS step1,
        COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS step2,
        COUNT(DISTINCT CASE WHEN event_name = 'begin_checkout' THEN user_id END) AS step3,
        COUNT(DISTINCT CASE WHEN event_name = 'add_payment' THEN user_id END) AS step4,
        COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS step5
    FROM user_events
    GROUP BY platform
)
SELECT
    platform,
    step1 AS product_view,
    step2 AS add_to_cart,
    step3 AS checkout,
    step4 AS payment,
    step5 AS purchase,
    ROUND(100.0 * step2 / NULLIF(step1, 0), 1) AS view_to_cart,
    ROUND(100.0 * step3 / NULLIF(step2, 0), 1) AS cart_to_checkout,
    ROUND(100.0 * step4 / NULLIF(step3, 0), 1) AS checkout_to_payment,
    ROUND(100.0 * step5 / NULLIF(step1, 0), 1) AS overall_conversion
FROM funnel_by_platform
ORDER BY overall_conversion DESC;

FAQ

Funnel analysis bisa dikerjain tanpa event tracking?

Nggak bisa. Funnel butuh data event per user, minimal user_id, nama event, sama timestamp. Kalau produk kamu belum pasang tracking, mulai dari situ dulu. Tools kayak Google Analytics 4 atau Mixpanel udah otomatis nge-record event standar e-commerce, tinggal export ke database terus analisis pakai SQL.

Bedanya strict funnel sama loose funnel apa?

Loose funnel cuma ngecek user pernah melakukan event, urutan nggak dihitung. Strict funnel maksa urutan. Add to cart harus setelah product view, gitu terus sampai purchase. Loose lebih gampang di-query dan lebih umum dipakai. Pakai strict kalau urutan langkah beneran ngaruh ke analisismu.

Conversion rate berapa yang dianggap bagus buat e-commerce?

Tergantung industri dan step-nya. Overall conversion e-commerce umumnya di kisaran 2-4%, jauh lebih kecil dari contoh 40% di artikel ini. Datanya cuma 5 user biar gampang dipahami. Yang lebih berguna: bandingin sama periode sebelumnya di produk kamu sendiri, bukan cuma sama angka industri.

Kenapa harus COUNT(DISTINCT user_id), bukan COUNT(*)?

Soalnya satu user bisa nge-trigger event yang sama berkali-kali. User yang add to cart 5 kali tetap dihitung 1 orang di funnel. Kalau pakai COUNT(*), angka tiap step bakal menggelembung dan conversion rate-nya jadi salah. Ini kesalahan paling umum di funnel analysis.

Kesimpulan

Funnel analysis itu skill wajib buat Product Analyst dan Growth Analyst. Inget poin-poin ini:

1. Funnel = visualisasi user journey dari step ke step
2. Conversion rate = persentase user yang lanjut ke step berikutnya
3. Drop-off = persentase user yang ga lanjut
4. Selalu pake COUNT(DISTINCT user_id)
5. Segmentasi itu kunci untuk actionable insights
6. Track over time untuk liat improvement

Dengan funnel analysis, kamu bisa kasih rekomendasi berbasis data ke product team. "Fix mobile checkout" jauh lebih convincing daripada "improve conversion".

Happy analyzing!

Selanjutnya

Kalau kamu udah paham funnel analysis, next step-nya:
- Cohort Analysis - analisis retention
- A/B Testing - ukur impact dari changes
- EDA dengan SQL - eksplorasi data lebih dalam