Event Funnel and Conversion Analysis

Funnels answer: "Of everyone who started, how many completed each step?" This is the backbone of product analytics, growth metrics, and marketing attribution. Every product manager, growth team, and UX researcher relies on funnel analysis to identify where users drop off and where to focus optimisation effort.

A 1% improvement in checkout conversion can generate hundreds of thousands in additional revenue for a large e-commerce business — and SQL is how you find exactly where that 1% is being lost.

What Makes a Good Funnel

A meaningful funnel has three properties:

Sequential steps: Each step logically follows the previous
Mutually inclusive: Every user at step N must have passed through step N-1
Time-bounded: Typically analysed within a session or rolling window (e.g., 30 days)

Bad funnels use ec_events that don't represent a sequential user journey, or count users who completed ec_events out of order.

Dataset

The ec_events table captures every user action on the platform:

-- ec_events table: customer_id, event_type, created_at, session_id, properties (jsonb)
-- ec_orders table: id, customer_id, created_at, total_amount, status
-- ec_customers table: id, email, created_at, acquisition_channel

Common funnel event sequences in e-commerce:

page_view → product_detail → add_to_cart → checkout_start → purchase
signup → email_verified → first_login → first_purchase

Step 1 — Unordered Funnel (Did They Ever Do This?)

The simplest funnel checks whether each user performed each event at any point — regardless of order. This is appropriate when the steps aren't strictly sequential (e.g., browsing behaviour), or as a quick sanity check before building a stricter ordered funnel.

The technique uses MAX(CASE WHEN event_type = 'X' THEN 1 ELSE 0 END) — this returns 1 if the user ever fired that event, 0 if they never did. We then SUM across all users to get the count at each step.

WITH funnel_steps AS (
  SELECT
    customer_id,
    MAX(CASE WHEN event_type = 'page_view' THEN 1 ELSE 0 END) AS step1_view,
    MAX(CASE WHEN event_type = 'product_detail' THEN 1 ELSE 0 END) AS step2_product,
    MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS step3_cart,
    MAX(CASE WHEN event_type = 'checkout_start' THEN 1 ELSE 0 END) AS step4_checkout,
    MAX(CASE WHEN event_type = 'purchase' THEN 1 ELSE 0 END) AS step5_purchase
  FROM ec_events
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY customer_id
)
SELECT
  SUM(step1_view) AS views,
  SUM(step2_product) AS product_views,
  SUM(step3_cart) AS add_to_cart,
  SUM(step4_checkout) AS checkout_started,
  SUM(step5_purchase) AS purchases,
  ROUND(SUM(step2_product)::numeric / NULLIF(SUM(step1_view), 0) * 100, 1) AS view_to_product_pct,
  ROUND(SUM(step3_cart)::numeric / NULLIF(SUM(step2_product), 0) * 100, 1) AS product_to_cart_pct,
  ROUND(SUM(step4_checkout)::numeric / NULLIF(SUM(step3_cart), 0) * 100, 1) AS cart_to_checkout_pct,
  ROUND(SUM(step5_purchase)::numeric / NULLIF(SUM(step4_checkout), 0) * 100, 1) AS checkout_to_purchase_pct
FROM funnel_steps;

What This Returns

A single-row funnel summary with absolute counts and step-to-step conversion rates. cart_to_checkout_pct is typically one of the most actionable metrics — if 60% of users who add to cart don't start checkout, that's a significant friction point worth investigating (complex cart page? security concerns? unexpected shipping costs?).

The step-to-step rates show where the biggest drop-offs are. If product_to_cart is 40% but cart_to_checkout is 25%, focus on the checkout experience. If view_to_product is only 15%, the homepage or category pages are not surfacing relevant ec_products.

Purchase this course to unlock the full lesson.

← Previous Next →