Basic Step-by-Step Funnel

Kapan Pakai Query Ini

Pakai query ini ketika kamu mau tahu berapa banyak user yang berhasil melewati setiap tahap funnel. Dari signup, verifikasi, onboarding, first action, sampai purchase. Hasilnya satu tabel rapi yang langsung bisa kamu bawa ke slide deck atau dashboard. Cocok buat weekly product review atau ngecek health funnel secara keseluruhan.

Yang Perlu Diganti

• your_dataset.events → nama tabel event kamu
• user_id → kolom identifier user
• event_name → kolom nama event / step funnel
• 'signup', 'verify_email', 'onboarding_complete', 'first_action', 'purchase' → nama event di tabel kamu

Query

Salin
WITH funnel_steps AS (
  SELECT
    user_id,
    COUNTIF(event_name = 'signup')              > 0 AS did_signup,
    COUNTIF(event_name = 'verify_email')        > 0 AS did_verify,
    COUNTIF(event_name = 'onboarding_complete') > 0 AS did_onboard,
    COUNTIF(event_name = 'first_action')        > 0 AS did_first_action,
    COUNTIF(event_name = 'purchase')            > 0 AS did_purchase
  FROM your_dataset.events
  GROUP BY user_id
),

totals AS (
  SELECT
    COUNTIF(did_signup)       AS step1_signup,
    COUNTIF(did_verify)       AS step2_verify,
    COUNTIF(did_onboard)      AS step3_onboard,
    COUNTIF(did_first_action) AS step4_first_action,
    COUNTIF(did_purchase)     AS step5_purchase
  FROM funnel_steps
)

SELECT
  step,
  user_count,
  ROUND(user_count / step1_signup * 100, 1) AS pct_of_top
FROM totals,
UNNEST([
  STRUCT('1. Signup'           AS step, step1_signup       AS user_count),
  STRUCT('2. Verify Email'     AS step, step2_verify       AS user_count),
  STRUCT('3. Onboarding'       AS step, step3_onboard      AS user_count),
  STRUCT('4. First Action'     AS step, step4_first_action AS user_count),
  STRUCT('5. Purchase'         AS step, step5_purchase     AS user_count)
]) AS funnel
ORDER BY step;

Contoh Output

Variasi

• Tambah filter WHERE DATE(event_timestamp) >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY) untuk funnel 30 hari terakhir
• Ganti COUNTIF flag dengan MIN timestamp per step untuk bisa analisis time-to-convert antar step
• Tambah kolom pct_of_prev (% dari step sebelumnya) untuk lihat drop-off rate antar step

Variasi Dialect

PostgreSQL:

Salin
-- Tidak ada COUNTIF — pakai COUNT(*) FILTER (WHERE ...)
-- Tidak ada UNNEST STRUCT — pakai UNION ALL manual

WITH funnel_steps AS (
  SELECT
    user_id,
    COUNT(*) FILTER (WHERE event_name = 'signup')              > 0 AS did_signup,
    COUNT(*) FILTER (WHERE event_name = 'verify_email')        > 0 AS did_verify,
    COUNT(*) FILTER (WHERE event_name = 'onboarding_complete') > 0 AS did_onboard,
    COUNT(*) FILTER (WHERE event_name = 'first_action')        > 0 AS did_first_action,
    COUNT(*) FILTER (WHERE event_name = 'purchase')            > 0 AS did_purchase
  FROM your_dataset.events
  GROUP BY user_id
),
totals AS (
  SELECT
    COUNT(*) FILTER (WHERE did_signup)       AS step1_signup,
    COUNT(*) FILTER (WHERE did_verify)       AS step2_verify,
    COUNT(*) FILTER (WHERE did_onboard)      AS step3_onboard,
    COUNT(*) FILTER (WHERE did_first_action) AS step4_first_action,
    COUNT(*) FILTER (WHERE did_purchase)     AS step5_purchase
  FROM funnel_steps
)
SELECT '1. Signup'      AS step, step1_signup       AS user_count, ROUND(step1_signup::numeric       / step1_signup * 100, 1) AS pct_of_top FROM totals
UNION ALL
SELECT '2. Verify Email',        step2_verify,       ROUND(step2_verify::numeric       / step1_signup * 100, 1) FROM totals
UNION ALL
SELECT '3. Onboarding',          step3_onboard,      ROUND(step3_onboard::numeric      / step1_signup * 100, 1) FROM totals
UNION ALL
SELECT '4. First Action',        step4_first_action, ROUND(step4_first_action::numeric / step1_signup * 100, 1) FROM totals
UNION ALL
SELECT '5. Purchase',            step5_purchase,     ROUND(step5_purchase::numeric     / step1_signup * 100, 1) FROM totals
ORDER BY step;

Snowflake / MySQL: Sama pola PostgreSQL. Pakai COUNT(CASE WHEN ... THEN 1 END) untuk MySQL, COUNT_IF untuk Snowflake.