SQL funnel analysis: conversion rates step by step

#Why it matters

SQL funnel analysis measures how many people (or orders) make it through each step of a process: viewed, added to cart, paid; or placed, approved, shipped, delivered. The step with the biggest drop is where the money leaks, and finding it is a core job for product, growth and operations analysts at every Indian e-commerce, fintech and food delivery company. SQL appears in 88% of Data Analyst job descriptions we studied, and "compute the share of users who viewed, added to cart and purchased within 7 days" is one of the most common SQL interview questions for those roles.

A funnel looks simple, and that is the danger. Counting events instead of people, ignoring the order of steps, or forgetting the time window can turn a 20% conversion into an 80% one. This lesson builds funnels the careful way, on real order data.

#The concept

#Event data

Funnels start from an event table: one row per thing that happened, with who, what and when.

Sometimes the steps live as timestamp columns on one row instead (placed_at, approved_at, shipped_at), as in most order tables. Same idea: each step either happened, and when, or did not.

#Four decisions that change the answer

1. Unit. Count distinct users (or orders), never events. One user refreshing a product page ten times is one viewer.

2. Order. A strict funnel counts step 2 only if it happened after step 1. An any-order funnel only asks whether each step happened at all. Strict is what people usually mean.

3. Window. "Purchased within 7 days of first view." Without a window, someone who viewed in January and bought in June counts as a conversion.

4. Entry. Who is in the funnel? Users who did step 1. Someone who purchased without a view (an app deep link, a repeat order) is not in a view-to-purchase funnel at all.

#Two kinds of conversion rate

Report both. A step with a 90% step rate can still lose more people than a 50% step later on, because more people reach it.

#Building it in SQL

• Conditional aggregation counts each step in one pass: count(*) FILTER (WHERE ...) or COUNT(DISTINCT CASE WHEN ... THEN user_id END).

• Strict order with windows: find each user's first step 1, then the first step 2 after it (and inside the window), then the first step 3 after that, joining step by step.

• Step-to-step rates: put the steps in rows and use LAG(count) OVER (ORDER BY step_no) and FIRST_VALUE.

• Segments: add GROUP BY device (or channel, state, cohort) to the same query.

#Sessions

Web and app events are usually grouped into sessions: a new session starts when the gap since the user's previous event is more than 30 minutes. In SQL: LAG(at) to get the gap, a flag where gap > 30 minutes, and a running SUM of the flag to number the sessions. Session-level funnels ("of sessions with a view, how many had a purchase") are a different metric from user-level funnels; say which one you report.

#Dirty events

Bots view thousands of pages and never buy, inflating the top of the funnel. Retries and tracking bugs fire the same event twice. Steps logged out of order (a clock on another server) break strict funnels. Before trusting a funnel, count events per user, look for exact duplicates, and check how many step timestamps run backwards.

#Worked example: Olist order fulfilment funnel

We use the Olist Brazilian e-commerce data (99,441 orders, CC BY-NC-SA 4.0) in PostgreSQL 16. Each order has timestamps for purchase, payment approval, hand-over to the carrier and delivery, plus the delivery date promised to the customer. Every output below is from a real run.

#Step 1: one row per order with its step timestamps

Orders purchased from January 2017 to June 2018. Starting in 2017 skips Olist's thin first months; stopping at June 2018 gives every order time to arrive before the data ends in October 2018.

CREATE VIEW order_steps AS
SELECT o.order_id,
       c.customer_state AS state,
       o.order_status,
       o.order_purchase_timestamp      AS placed_at,
       o.order_approved_at             AS approved_at,
       o.order_delivered_carrier_date  AS shipped_at,
       o.order_delivered_customer_date AS delivered_at,
       o.order_estimated_delivery_date AS promised_date
FROM orders AS o
JOIN customers AS c USING (customer_id)
WHERE o.order_purchase_timestamp >= DATE '2017-01-01'
  AND o.order_purchase_timestamp <  DATE '2018-07-01';

SELECT count(*) AS placed,
       count(approved_at) AS approved,
       count(shipped_at) AS shipped,
       count(delivered_at) AS delivered,
       count(*) FILTER (WHERE delivered_at::date <= promised_date::date) AS on_time
FROM order_steps;

CREATE VIEW
 placed | approved | shipped | delivered | on_time 
--------+----------+---------+-----------+---------
  86288 |    86207 |   84759 |     83697 |   77766
(1 row)

This is the any-order funnel: each count only asks whether the timestamp exists. count(column) skips NULLs, which makes it a natural step counter.

#Step 2: the strict funnel with step and overall rates

Now require every step to come after the previous one, put the steps in rows, and compute both rates with window functions:

WITH counts AS (
  SELECT count(*) AS placed,
         count(*) FILTER (WHERE approved_at >= placed_at) AS approved,
         count(*) FILTER (WHERE approved_at >= placed_at AND shipped_at >= approved_at) AS shipped,
         count(*) FILTER (WHERE approved_at >= placed_at AND shipped_at >= approved_at
                            AND delivered_at >= shipped_at) AS delivered,
         count(*) FILTER (WHERE approved_at >= placed_at AND shipped_at >= approved_at
                            AND delivered_at >= shipped_at
                            AND delivered_at::date <= promised_date::date) AS on_time
  FROM order_steps
),
funnel AS (
  SELECT v.step_no, v.step, v.orders
  FROM counts
  CROSS JOIN LATERAL (VALUES (1, 'placed', placed), (2, 'approved', approved),
                             (3, 'shipped', shipped), (4, 'delivered', delivered),
                             (5, 'on time', on_time)) AS v(step_no, step, orders)
)
SELECT step_no, step, orders,
       round(100.0 * orders / lag(orders) OVER (ORDER BY step_no), 1) AS step_pct,
       round(100.0 * orders / first_value(orders) OVER (ORDER BY step_no), 1) AS overall_pct,
       lag(orders) OVER (ORDER BY step_no) - orders AS lost_here
FROM funnel
ORDER BY step_no;

 step_no |   step    | orders | step_pct | overall_pct | lost_here 
---------+-----------+--------+----------+-------------+-----------
       1 | placed    |  86288 |          |       100.0 |          
       2 | approved  |  86207 |     99.9 |        99.9 |        81
       3 | shipped   |  84038 |     97.5 |        97.4 |      2169
       4 | delivered |  82962 |     98.7 |        96.1 |      1076
       5 | on time   |  77048 |     92.9 |        89.3 |      5914
(5 rows)

89.3% of orders arrive complete and on time. The biggest leak is not cancellation or loss but lateness: 5,914 orders were delivered after the promised date, more than all the other steps combined. The CROSS JOIN LATERAL (VALUES ...) turns one wide row of counts into one row per step, which is the shape LAG needs (SQL pivot: rows to columns and unpivot covers that unpivot).

#Step 3: why strict and any-order disagree

The strict funnel has 84,038 shipped orders; the any-order count was 84,759. The difference is orders whose carrier timestamp is earlier than their payment approval:

SELECT count(*) AS orders,
       count(*) FILTER (WHERE shipped_at::date = approved_at::date) AS same_day,
       percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch FROM approved_at - shipped_at) / 3600) AS median_hours_early,
       max(approved_at - shipped_at) AS worst
FROM order_steps
WHERE shipped_at < approved_at;

 orders | same_day | median_hours_early |      worst      
--------+----------+--------------------+-----------------
    707 |      326 | 18.349722222222223 | 9 days 08:55:48
(1 row)

707 orders were "shipped" a median of 18 hours before they were approved, one of them by more than 9 days. Physically that is unlikely; more likely the timestamps come from different systems or are entered late. The other 14 of the 721 difference have a carrier timestamp but no approval timestamp at all. A strict funnel drops all of these, an any-order funnel keeps them. Neither is wrong, but the choice must be stated, and orders with backwards timestamps are worth raising with the data owners.

#Step 4: time between steps and a time window

SELECT round(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch FROM approved_at - placed_at) / 3600)::numeric, 1) AS placed_to_approved_h,
       round(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch FROM shipped_at - approved_at) / 3600)::numeric, 1) AS approved_to_shipped_h,
       round(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch FROM delivered_at - shipped_at) / 3600)::numeric, 1) AS shipped_to_delivered_h,
       round(percentile_cont(0.5) WITHIN GROUP (ORDER BY extract(epoch FROM delivered_at - placed_at) / 86400)::numeric, 1) AS placed_to_delivered_days
FROM order_steps
WHERE approved_at >= placed_at AND shipped_at >= approved_at AND delivered_at >= shipped_at;

SELECT count(*) AS placed,
       count(*) FILTER (WHERE approved_at <= placed_at + interval '1 hour') AS approved_1h,
       count(*) FILTER (WHERE approved_at <= placed_at + interval '24 hours') AS approved_24h,
       count(approved_at) AS approved_ever
FROM order_steps;

 placed_to_approved_h | approved_to_shipped_h | shipped_to_delivered_h | placed_to_delivered_days 
----------------------+-----------------------+------------------------+--------------------------
                  0.3 |                  45.8 |                  179.4 |                     11.0
(1 row)
 
 placed | approved_1h | approved_24h | approved_ever 
--------+-------------+--------------+---------------
  86288 |       55486 |        71044 |         86207
(1 row)

Median times tell you where the time goes: approval is minutes, the seller takes about two days to hand over, and the carrier takes about seven and a half. Medians are used because a few orders that took months would drag an average far off (SQL percentiles and median: PERCENTILE_CONT and NTILE).

The second result shows what a window does. "Approved" is 99.9% with no window, 82.3% within 24 hours (71,044 of 86,288) and 64.3% within one hour. The same step has three conversion rates depending on the window, so a funnel number without its window is meaningless. Boleto (bank slip) payments, which customers pay later, are the usual reason for slow approval in Brazil; that is an inference to check against the payments table, not something these columns show.

#Step 5: segment by state

The same measures grouped by customer state, for states with at least 1,000 orders:

SELECT state, count(*) AS placed,
       round(100.0 * count(delivered_at) / count(*), 1) AS delivered_pct,
       round(100.0 * count(*) FILTER (WHERE delivered_at::date > promised_date::date)
             / count(delivered_at), 1) AS late_pct,
       round(percentile_cont(0.5) WITHIN GROUP (
             ORDER BY extract(epoch FROM delivered_at - placed_at) / 86400)::numeric, 1) AS median_days
FROM order_steps
GROUP BY state
HAVING count(*) >= 1000
ORDER BY late_pct DESC;

 state | placed | delivered_pct | late_pct | median_days 
-------+--------+---------------+----------+-------------
 CE    |   1184 |          95.8 |     15.4 |        19.0
 BA    |   2961 |          96.2 |     13.3 |        17.9
 RJ    |  11330 |          96.1 |     13.2 |        12.8
 ES    |   1801 |          98.1 |     11.6 |        14.2
 PE    |   1423 |          96.1 |     10.3 |        16.7
 SC    |   3221 |          97.5 |      8.9 |        13.8
 GO    |   1776 |          96.9 |      7.0 |        14.3
 RS    |   4825 |          97.9 |      6.6 |        13.9
 DF    |   1823 |          96.9 |      6.0 |        12.1
 MG    |  10225 |          97.6 |      5.0 |        11.0
 PR    |   4372 |          97.5 |      4.3 |        11.0
 SP    |  35591 |          97.0 |      4.1 |         7.6
(12 rows)

Two findings a manager would act on. Rio de Janeiro (RJ) is the second biggest market and late on 13.2% of deliveries, three times São Paulo's 4.1%, even though its median delivery takes only 12.8 days. And slow is not the same as late: lateness is measured against the promised date, and Olist promises distant states much longer (a median of 19.4 days for SP, 25.1 for RJ and 31.6 for CE, from promised_date - placed_at). Ceará still misses that longer promise 15.4% of the time. HAVING count(*) >= 1000 hides tiny states whose percentages swing on a handful of orders; always show the base next to a rate.

#Step 6: sessionise events with LAG and a 30-minute gap

Olist has no web events, so we sessionise purchases in the Online Retail data instead (UCI, CC BY 4.0), using the orders table built in SQL cohort analysis and customer retention: one row per invoice with customer_id and ordered_at. Wholesale customers often place several invoices in a row; a new "shopping session" starts after a gap of more than 30 minutes.

WITH gaps AS (
  SELECT customer_id, invoice_no, ordered_at,
         ordered_at - lag(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at, invoice_no) AS gap
  FROM orders
),
flagged AS (
  SELECT *, CASE WHEN gap IS NULL OR gap > interval '30 minutes' THEN 1 ELSE 0 END AS new_session
  FROM gaps
)
SELECT invoice_no, ordered_at, gap, new_session,
       sum(new_session) OVER (PARTITION BY customer_id ORDER BY ordered_at, invoice_no) AS session_no
FROM flagged
WHERE customer_id = 17850
ORDER BY ordered_at, invoice_no
LIMIT 8;

 invoice_no |     ordered_at      |   gap    | new_session | session_no 
------------+---------------------+----------+-------------+------------
 536365     | 2010-12-01 08:26:00 |          |           1 |          1
 536366     | 2010-12-01 08:28:00 | 00:02:00 |           0 |          1
 536372     | 2010-12-01 09:01:00 | 00:33:00 |           1 |          2
 536373     | 2010-12-01 09:02:00 | 00:01:00 |           0 |          2
 536375     | 2010-12-01 09:32:00 | 00:30:00 |           0 |          2
 536377     | 2010-12-01 09:34:00 | 00:02:00 |           0 |          2
 536396     | 2010-12-01 10:51:00 | 01:17:00 |           1 |          3
 536399     | 2010-12-01 10:52:00 | 00:01:00 |           0 |          3
(8 rows)

Three details to notice. The first event of each customer has a NULL gap and must start a session (gap IS NULL). Invoice 536375 came exactly 30 minutes after the previous one and stayed in session 2, because the rule is "more than 30 minutes"; >= would have split it, so write the boundary down. And session 2 lasts 33 minutes in total: the gap is measured from the previous event, not from the session start. Across all customers:

WITH gaps AS (
  SELECT customer_id, invoice_no, ordered_at,
         ordered_at - lag(ordered_at) OVER (PARTITION BY customer_id ORDER BY ordered_at, invoice_no) AS gap
  FROM orders
),
flagged AS (
  SELECT *, CASE WHEN gap IS NULL OR gap > interval '30 minutes' THEN 1 ELSE 0 END AS new_session
  FROM gaps
),
numbered AS (
  SELECT *, sum(new_session) OVER (PARTITION BY customer_id ORDER BY ordered_at, invoice_no) AS session_no
  FROM flagged
)
SELECT count(*) AS invoices,
       count(DISTINCT (customer_id, session_no)) AS sessions,
       count(*) FILTER (WHERE gap = interval '0') AS same_minute_invoices
FROM numbered;

 invoices | sessions | same_minute_invoices 
----------+----------+----------------------
    18532 |    17040 |                  122
(1 row)

18,532 invoices collapse into 17,040 sessions. 122 invoices share the exact minute with the customer's previous one, which is why invoice_no is in the ORDER BY as a tie-breaker: without it, the order of tied rows is not defined and session numbers could change between runs.

#Common mistakes

• Counting events instead of users. Duplicate and repeated events inflate every step. Use COUNT(DISTINCT user_id).

• Any-order when you meant strict. A purchase before the view is not a conversion from that view.

• No time window. Conversion "eventually" is always higher and not comparable between cohorts of different ages.

• Letting people in at step 2. Users who never did step 1 must not appear in later steps.

• Only step-to-step rates. Show overall conversion and the absolute number lost at each step too.

• Rates without bases. A state with 39 orders can top any ranking by chance.

• Averages for durations. A few stuck orders distort the mean; use medians or percentiles.

• Ignoring timestamp quality. 707 orders here had carrier dates before approval.

• Ambiguous session boundaries and no tie-breaker in window functions over timestamps.

• Joining a child table without checking duplicates. One extra review row per order silently adds rows (see the practice task).

#Interview angle

WITH events(user_id, event, at) AS (
  VALUES (1, 'view', TIMESTAMP '2024-03-01 10:00'), (1, 'view', '2024-03-01 10:00'),
         (1, 'cart', '2024-03-01 10:05'), (1, 'purchase', '2024-03-02 09:00'),
         (2, 'view', '2024-03-01 11:00'), (2, 'cart', '2024-03-01 11:10'),
         (3, 'view', '2024-03-01 12:00'), (3, 'cart', '2024-03-12 12:00'),
         (3, 'purchase', '2024-03-12 12:30'),
         (4, 'cart', '2024-03-02 08:00'), (4, 'purchase', '2024-03-02 08:05'),
         (5, 'view', '2024-03-03 09:00'),
         (6, 'purchase', '2024-03-03 10:00'), (6, 'view', '2024-03-03 10:01'),
         (6, 'cart', '2024-03-03 10:02')
),
firsts AS (
  SELECT user_id, min(at) AS viewed_at FROM events WHERE event = 'view' GROUP BY user_id
),
carts AS (
  SELECT f.user_id, f.viewed_at, min(e.at) AS carted_at
  FROM firsts AS f
  LEFT JOIN events AS e
    ON e.user_id = f.user_id AND e.event = 'cart'
   AND e.at >= f.viewed_at AND e.at <= f.viewed_at + interval '7 days'
  GROUP BY f.user_id, f.viewed_at
),
buys AS (
  SELECT c.user_id, c.viewed_at, c.carted_at, min(e.at) AS bought_at
  FROM carts AS c
  LEFT JOIN events AS e
    ON e.user_id = c.user_id AND e.event = 'purchase'
   AND e.at >= c.carted_at AND e.at <= c.viewed_at + interval '7 days'
  GROUP BY c.user_id, c.viewed_at, c.carted_at
)
SELECT count(*) AS viewed,
       count(carted_at) AS carted,
       count(bought_at) AS purchased,
       round(100.0 * count(carted_at) / count(*), 1) AS view_to_cart_pct,
       round(100.0 * count(bought_at) / nullif(count(carted_at), 0), 1) AS cart_to_purchase_pct,
       round(100.0 * count(bought_at) / count(*), 1) AS overall_pct
FROM buys;

 viewed | carted | purchased | view_to_cart_pct | cart_to_purchase_pct | overall_pct 
--------+--------+-----------+------------------+----------------------+-------------
      5 |      3 |         1 |             60.0 |                 33.3 |        20.0
(1 row)

-- same events CTE as above, then:
SELECT count(*) FILTER (WHERE event = 'view') AS view_events,
       count(DISTINCT user_id) FILTER (WHERE event = 'view') AS viewers,
       count(DISTINCT user_id) FILTER (WHERE event = 'cart') AS carters,
       count(DISTINCT user_id) FILTER (WHERE event = 'purchase') AS buyers
FROM events;

 view_events | viewers | carters | buyers 
-------------+---------+---------+--------
           6 |       5 |       5 |      4
(1 row)

#Practice task

Extend the Olist funnel past delivery, using the reviews file and the order_steps view from the worked example.

1. Load olist_order_reviews_dataset.csv into a table reviews (review_id, order_id, review_score, review_comment_title, review_comment_message, review_creation_date, review_answer_timestamp).

2. Check whether an order can have more than one review, and what a plain join would do to your counts.

3. For delivered orders, split by late vs on time and count: delivered, reviewed, reviewed with a written comment, and five-star. Express comment and five-star rates as a share of reviewed orders.

4. Write two sentences on what the segmented funnel says.

CREATE TABLE reviews (
  review_id text, order_id text, review_score int,
  review_comment_title text, review_comment_message text,
  review_creation_date timestamp, review_answer_timestamp timestamp
);
\copy reviews FROM 'olist_order_reviews_dataset.csv' WITH (FORMAT csv, HEADER true)

SELECT count(*), count(DISTINCT review_id), count(DISTINCT order_id) FROM reviews;
SELECT count(*) FROM (SELECT order_id FROM reviews GROUP BY 1 HAVING count(*) > 1) t;

CREATE TABLE
COPY 99224
 count | count | count 
-------+-------+-------
 99224 | 98410 | 98673
(1 row)
 
 count 
-------
   547
(1 row)

SELECT count(*) AS joined_rows, count(DISTINCT s.order_id) AS orders
FROM order_steps s JOIN reviews r USING (order_id) WHERE s.delivered_at IS NOT NULL;

 joined_rows | orders 
-------------+--------
       83617 |  83113
(1 row)

WITH latest_review AS (
  SELECT DISTINCT ON (order_id) order_id, review_score, review_comment_message
  FROM reviews
  ORDER BY order_id, review_answer_timestamp DESC, review_id
),
delivered AS (
  SELECT s.order_id,
         CASE WHEN s.delivered_at::date > s.promised_date::date THEN 'late' ELSE 'on time' END AS delivery
  FROM order_steps AS s
  WHERE s.delivered_at IS NOT NULL
)
SELECT d.delivery,
       count(*) AS delivered,
       count(r.order_id) AS reviewed,
       count(*) FILTER (WHERE trim(r.review_comment_message) <> '') AS commented,
       count(*) FILTER (WHERE r.review_score = 5) AS five_star,
       round(100.0 * count(r.order_id) / count(*), 1) AS reviewed_pct,
       round(100.0 * count(*) FILTER (WHERE trim(r.review_comment_message) <> '') / count(r.order_id), 1) AS comment_pct_of_reviewed,
       round(100.0 * count(*) FILTER (WHERE r.review_score = 5) / count(r.order_id), 1) AS five_star_pct_of_reviewed
FROM delivered AS d
LEFT JOIN latest_review AS r USING (order_id)
GROUP BY d.delivery
ORDER BY d.delivery;

 delivery | delivered | reviewed | commented | five_star | reviewed_pct | comment_pct_of_reviewed | five_star_pct_of_reviewed 
----------+-----------+----------+-----------+-----------+--------------+-------------------------+---------------------------
 late     |      5931 |     5794 |      3460 |       884 |         97.7 |                    59.7 |                      15.3
 on time  |     77766 |    77319 |     30512 |     47666 |         99.4 |                    39.5 |                      61.6
(2 rows)

#Further reading

• PostgreSQL: window functions: LAG, FIRST_VALUE and running sums for sessions.

• PostgreSQL: aggregate FILTER and percentile_cont.

• Olist datasets: orders, customers and reviews (CC BY-NC-SA 4.0).

• UCI Online Retail dataset (CC BY 4.0) for the session example.