Funnel Analysis with CTEs

Funnel analysis answers: "Of all the users who started a process, how many completed each subsequent step?" It's fundamental in product analytics, sales, and marketing. CTEs make funnel queries readable and maintainable.

What Is a Funnel?

A funnel tracks users through a sequential series of ec_events. At each step, some users drop off. The shape narrows — like a funnel — because completion rates decrease.

Classic examples:

E-commerce: page_view → add_to_cart → checkout_start → purchase
SaaS: signup → email_verified → first_login → feature_used → subscription
Finance: invoice_sent → invoice_viewed → payment_made

Ordered vs Unordered Funnels

Unordered funnel: Did the user do all these things (in any order)? Ordered funnel: Did the user do these things in this specific sequence?

We'll cover both, starting with the simpler ordered funnel.

Example 1: Basic Event Funnel (ShopMetrics)

WITH funnel_steps AS (
    -- Count distinct users who performed each event type
    SELECT
        event_type,
        COUNT(DISTINCT customer_id) AS users
    FROM ec_events
    WHERE event_type IN ('page_view', 'add_to_cart', 'purchase')
    GROUP BY event_type
),

ordered_funnel AS (
    SELECT
        event_type,
        users,
        CASE event_type
            WHEN 'page_view'   THEN 1
            WHEN 'add_to_cart' THEN 2
            WHEN 'purchase'    THEN 3
        END AS step_num
    FROM funnel_steps
),

with_rates AS (
    SELECT
        step_num,
        event_type,
        users,
        FIRST_VALUE(users) OVER (ORDER BY step_num) AS top_of_funnel,
        LAG(users) OVER (ORDER BY step_num)          AS prev_step_users
    FROM ordered_funnel
)

SELECT
    step_num,
    event_type       AS step,
    users,
    ROUND(100.0 * users / top_of_funnel, 1)  AS pct_of_top,
    ROUND(100.0 * users / NULLIF(prev_step_users, 0), 1) AS step_conversion_pct
FROM with_rates
ORDER BY step_num;

This gives you:

Absolute users at each step
% of top-of-funnel (overall conversion rate)
Step-to-step conversion rate (where is the biggest drop-off?)

Example 2: Ordered Funnel — Strict Sequence

The previous query counts users who ever did the event — but not necessarily in order. A stricter funnel requires each step to occur after the previous one:

WITH page_views AS (
    SELECT DISTINCT customer_id, MIN(created_at) AS first_view
    FROM ec_events WHERE event_type = 'page_view'
    GROUP BY customer_id
),

add_to_carts AS (
    SELECT DISTINCT e.customer_id, MIN(e.created_at) AS first_cart
    FROM ec_events e
    JOIN page_views pv ON pv.customer_id = e.customer_id
    WHERE e.event_type = 'add_to_cart'
      AND e.created_at > pv.first_view   -- must happen AFTER view
    GROUP BY e.customer_id
),

purchases AS (
    SELECT DISTINCT e.customer_id, MIN(e.created_at) AS first_purchase
    FROM ec_events e
    JOIN add_to_carts atc ON atc.customer_id = e.customer_id
    WHERE e.event_type = 'purchase'
      AND e.created_at > atc.first_cart   -- must happen AFTER cart
    GROUP BY e.customer_id
),

funnel AS (
    SELECT COUNT(DISTINCT customer_id) AS viewers   FROM page_views
    UNION ALL
    SELECT COUNT(DISTINCT customer_id)              FROM add_to_carts
    UNION ALL
    SELECT COUNT(DISTINCT customer_id)              FROM purchases
),

numbered AS (
    SELECT *, ROW_NUMBER() OVER () AS step FROM funnel
)

SELECT
    step,
    CASE step WHEN 1 THEN 'Page View' WHEN 2 THEN 'Add to Cart' WHEN 3 THEN 'Purchase' END AS step_name,
    viewers AS users,
    ROUND(100.0 * viewers / FIRST_VALUE(viewers) OVER (ORDER BY step), 1) AS overall_pct,
    ROUND(100.0 * viewers / LAG(viewers) OVER (ORDER BY step), 1) AS step_pct
FROM numbered
ORDER BY step;

Example 3: Funnel by Cohort

Break the funnel down by when the customer joined (their signup month):

WITH customer_cohorts AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', created_at)::DATE AS cohort_month
    FROM ec_customers
),

funnel_by_cohort AS (
    SELECT
        cc.cohort_month,
        e.event_type,
        COUNT(DISTINCT e.customer_id) AS users
    FROM ec_events e
    JOIN customer_cohorts cc ON cc.customer_id = e.customer_id
    WHERE e.event_type IN ('page_view', 'add_to_cart', 'purchase')
    GROUP BY cc.cohort_month, e.event_type
),

pivoted AS (
    SELECT
        cohort_month,
        MAX(CASE WHEN event_type = 'page_view'   THEN users END) AS viewers,
        MAX(CASE WHEN event_type = 'add_to_cart' THEN users END) AS added_to_cart,
        MAX(CASE WHEN event_type = 'purchase'    THEN users END) AS purchasers
    FROM funnel_by_cohort
    GROUP BY cohort_month
)

SELECT
    cohort_month,
    viewers,
    added_to_cart,
    purchasers,
    ROUND(100.0 * added_to_cart / NULLIF(viewers, 0), 1)     AS view_to_cart_pct,
    ROUND(100.0 * purchasers   / NULLIF(added_to_cart, 0), 1) AS cart_to_purchase_pct,
    ROUND(100.0 * purchasers   / NULLIF(viewers, 0), 1)       AS overall_conversion_pct
FROM pivoted
ORDER BY cohort_month;

Example 4: Invoice Payment Funnel (Finance Dataset)

Track invoices through their payment lifecycle:

WITH invoice_stages AS (
    SELECT
        i.invoice_id,
        i.client_id,
        i.issue_date,
        i.due_date,
        SUM(ii.quantity * ii.unit_price) AS invoice_total,
        MAX(p.payment_date)              AS payment_date,
        SUM(p.amount)                    AS paid_amount
    FROM invoices i
    JOIN invoice_items ii ON ii.invoice_id = i.invoice_id
    LEFT JOIN payments p ON p.invoice_id = i.invoice_id
    GROUP BY i.invoice_id, i.client_id, i.issue_date, i.due_date
),

classified AS (
    SELECT
        invoice_id,
        client_id,
        invoice_total,
        CASE
            WHEN payment_date IS NULL AND due_date < CURRENT_DATE THEN 'overdue'
            WHEN payment_date IS NULL THEN 'pending'
            WHEN paid_amount >= invoice_total THEN 'paid_full'
            ELSE 'paid_partial'
        END AS status
    FROM invoice_stages
),

funnel AS (
    SELECT
        status,
        COUNT(*)         AS invoice_count,
        SUM(invoice_total) AS total_value
    FROM classified
    GROUP BY status
)

SELECT
    status,
    invoice_count,
    ROUND(total_value, 2) AS total_value,
    ROUND(100.0 * invoice_count / SUM(invoice_count) OVER (), 1) AS pct_of_total
FROM funnel
ORDER BY invoice_count DESC;

Purchase this course to unlock the full lesson.

← Previous Next →