funnel_report.sql in SQL

Counting how many sessions made it to each step, then the drop between them.

-- Session funnel: visit, signup, first order, repeat order.

WITH stage_events AS (
    SELECT
        session_id,
        kind,
        occurred_at
    FROM events
    WHERE occurred_at >= DATE('now', '-30 days')
),
stages AS (
    SELECT
        1 AS step,
        'visited' AS stage,
        COUNT(DISTINCT session_id) AS sessions
    FROM stage_events
    UNION ALL
    SELECT
        2,
        'signed up',
        COUNT(DISTINCT session_id)
    FROM stage_events
    WHERE kind = 'signup'
    UNION ALL
    SELECT
        3,
        'ordered',
        COUNT(DISTINCT session_id)
    FROM stage_events
    WHERE kind = 'order'
    UNION ALL
    SELECT
        4,
        'ordered again',
        COUNT(DISTINCT session_id)
    FROM stage_events
    WHERE kind = 'reorder'
)
SELECT
    step,
    stage,
    sessions,
    LAG(sessions) OVER (
        ORDER BY step
    ) AS previous_stage,
    ROUND(
        sessions * 100.0 / NULLIF(
            LAG(sessions) OVER (
                ORDER BY step
            ),
            0
        ),
        1
    ) AS conversion_pct,
    ROUND(
        sessions * 100.0 / NULLIF(
            FIRST_VALUE(sessions) OVER (
                ORDER BY step
            ),
            0
        ),
        1
    ) AS of_all_sessions
FROM stages
ORDER BY step;

How it works

One CTE per stage keeps each COUNT(DISTINCT ...) easy to check.
The stages are stacked with UNION ALL so they share one ordering.
LAG over the stacked rows turns counts into a conversion rate.

Keywords and builtins used here

ALL
AS
BY
COUNT
DATE
DISTINCT
FROM
NULLIF
ORDER
SELECT
UNION
WHERE
WITH

The run, in numbers

Lines: 65
Characters to type: 913
Tokens: 184
Three-star pace: 95 tpm

At the three-star pace of 95 tokens a minute, this run takes about 116 seconds.

Type this snippet

Step 2 of 2 in Encore, step 22 of 22 in Joins & aggregation.

← Previous