Skip to content

Business case queries · Medium

How would you build a conversion funnel from session data?

Short answer

Count sessions reaching each stage, from product view to cart, checkout and purchase, then compute two rates: step conversion, each stage divided by the previous one, and overall conversion, each stage divided by the top of the funnel. Breaking the funnel down by traffic source or device shows where users drop off and which channel is under-performing.

Detailed answer

In web_sessions each stage is a 1/0 flag.

WITH totals AS (
  SELECT COUNT(*)              AS sessions,
         SUM(viewed_product)   AS viewed,
         SUM(added_to_cart)    AS carted,
         SUM(started_checkout) AS checkout,
         SUM(purchased)        AS purchased
  FROM web_sessions
),
stages AS (
  SELECT 1 AS step, 'Session' AS stage, sessions AS users FROM totals
  UNION ALL SELECT 2, 'Viewed product', viewed    FROM totals
  UNION ALL SELECT 3, 'Added to cart',  carted    FROM totals
  UNION ALL SELECT 4, 'Checkout',       checkout  FROM totals
  UNION ALL SELECT 5, 'Purchased',      purchased FROM totals
)
SELECT step, stage, users,
       ROUND(100.0 * users / LAG(users) OVER (ORDER BY step), 1)         AS step_conversion_pct,
       ROUND(100.0 * users / FIRST_VALUE(users) OVER (ORDER BY step), 1) AS overall_conversion_pct
FROM stages
ORDER BY step;

By channel and device, which is usually the follow-up:

SELECT traffic_source, device,
       COUNT(*) AS sessions,
       ROUND(100.0 * SUM(added_to_cart) / COUNT(*), 1) AS cart_rate_pct,
       ROUND(100.0 * SUM(purchased) / NULLIF(SUM(added_to_cart), 0), 1) AS cart_to_purchase_pct
FROM web_sessions
GROUP BY traffic_source, device
ORDER BY sessions DESC;

Caveats to voice: with event-level data (one row per event) you must enforce order — a purchase only counts if it follows a cart event in the same session — usually with MIN(event_time) per stage. And with 80 sessions, small segments swing wildly; flag low sample sizes before drawing conclusions.