E-commerce Conversion Funnel

Conversion funnel analysis is one of the highest-leverage analytical tasks in e-commerce. A 5-percentage-point improvement in checkout conversion on a $10M/year store is worth $500K in incremental revenue—without acquiring a single new visitor. In this project, I demonstrate a systematic SQL approach to identifying and diagnosing drop-off in an e-commerce purchase funnel.

Note: This analysis uses synthetic data generated to mimic realistic e-commerce patterns. The store, metrics, and revenue figures are fictional.

The fictional store Meridian Commerce had been experiencing declining conversion rates through Q3 2023 despite steady inbound traffic. The data team was asked to identify where in the funnel users were dropping off and generate hypotheses for the product team to test.

The analysis works with three tables:

-- User sessions with source and device context
sessions (
  session_id, user_id, started_at,
  utm_source, device_type, landing_page
)

-- Events fired during each session
events (
  event_id, session_id, event_type,
  event_at, product_id, revenue
)
-- event_type values: product_view, add_to_cart,
--   checkout_start, checkout_step_*, purchase

-- User profile data
users (user_id, created_at, country, is_returning)

I defined a 5-step conversion funnel:

1. Session start — any user lands on any page
2. Product view — views at least one product detail page
3. Add to cart — adds at least one item
4. Checkout start — enters the checkout flow
5. Purchase — completes an order

WITH funnel_steps AS (
  SELECT
    s.session_id,
    s.user_id,
    s.device_type,
    s.utm_source,
    MAX(CASE WHEN e.event_type = 'product_view'   THEN 1 ELSE 0 END) AS viewed_product,
    MAX(CASE WHEN e.event_type = 'add_to_cart'    THEN 1 ELSE 0 END) AS added_to_cart,
    MAX(CASE WHEN e.event_type = 'checkout_start' THEN 1 ELSE 0 END) AS started_checkout,
    MAX(CASE WHEN e.event_type = 'purchase'       THEN 1 ELSE 0 END) AS purchased
  FROM sessions s
  LEFT JOIN events e ON s.session_id = e.session_id
  WHERE s.started_at >= '2023-07-01'
    AND s.started_at <  '2023-10-01'
  GROUP BY 1, 2, 3, 4
)
SELECT
  COUNT(*)                                                                       AS total_sessions,
  SUM(viewed_product)                                                            AS viewed_product,
  SUM(added_to_cart)                                                             AS added_to_cart,
  SUM(started_checkout)                                                          AS started_checkout,
  SUM(purchased)                                                                 AS purchased,
  ROUND(100.0 * SUM(viewed_product)   / COUNT(*),                           1)  AS pct_viewed,
  ROUND(100.0 * SUM(added_to_cart)    / NULLIF(SUM(viewed_product),   0),   1)  AS pct_cart_of_views,
  ROUND(100.0 * SUM(started_checkout) / NULLIF(SUM(added_to_cart),    0),   1)  AS pct_checkout_of_carts,
  ROUND(100.0 * SUM(purchased)        / NULLIF(SUM(started_checkout), 0),   1)  AS pct_purchase_of_checkouts
FROM funnel_steps;

The largest single drop is Add to Cart at 21% of product viewers—well below the 30–35% industry benchmark for comparable apparel stores. This became the primary focus of the investigation.

SELECT
  device_type,
  COUNT(*)                                                                    AS sessions,
  ROUND(100.0 * SUM(added_to_cart) / NULLIF(SUM(viewed_product), 0), 1)     AS cart_rate,
  ROUND(100.0 * SUM(purchased)     / COUNT(*),                         1)   AS overall_cvr
FROM funnel_steps
GROUP BY device_type
ORDER BY overall_cvr DESC;

Mobile cart rate is less than half of desktop. Since mobile accounts for 54% of all traffic, this single gap is the dominant driver of the overall shortfall.

Estimated revenue impact: Bringing mobile cart rate from 14.3% to desktop parity at 28.4% would generate approximately $1.2M in additional annual revenue at current traffic and average order value.

To understand why mobile users aren’t converting, I measured the time between product_view and add_to_cart events:

WITH view_to_cart AS (
  SELECT
    s.session_id,
    s.device_type,
    MIN(CASE WHEN e.event_type = 'product_view' THEN e.event_at END) AS first_view_at,
    MIN(CASE WHEN e.event_type = 'add_to_cart'  THEN e.event_at END) AS first_cart_at
  FROM sessions s
  JOIN events e ON s.session_id = e.session_id
  WHERE s.started_at >= '2023-07-01'
    AND s.started_at <  '2023-10-01'
  GROUP BY 1, 2
  HAVING first_cart_at IS NOT NULL
)
SELECT
  device_type,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (first_cart_at - first_view_at))
  ) AS median_seconds_to_cart
FROM view_to_cart
GROUP BY device_type;

Mobile users who do add to cart take 4× longer than desktop users. This points to UX friction—likely the add-to-cart button being below the fold, too small to tap accurately, or obscured by the mobile navigation overlay—rather than lack of purchase intent.

Based on the data, three initiatives are prioritized:

Priority 1 — Mobile product page UX audit (high confidence, high impact) The data strongly suggests friction at the add-to-cart CTA on mobile product pages. Recommended A/B tests: sticky add-to-cart button, larger touch targets, above-the-fold CTA placement, and simplified size/color selection UI.

Priority 2 — Cart abandonment recovery (medium confidence, medium impact) Only 45% of users who add to cart proceed to checkout. A triggered email or SMS for cart abandoners (absent from current flows) is a low-effort, high-ROI intervention standard in e-commerce.

Priority 3 — Checkout friction reduction (medium confidence, medium impact) The checkout-to-purchase rate of 45% is below the 60–65% benchmark. Guest checkout and saved payment methods should be evaluated as friction reducers.

This analysis uses session-level boolean flags. In a production analytics stack I’d:

• Build a dbt model for the funnel that refreshes daily and feeds a BI dashboard
• Use window functions to capture time between every funnel step, enabling timeout and abandonment modeling at each stage
• Segment by user cohort (new vs. returning, acquisition channel) to separate behavioral differences from product issues
• Add statistical significance testing to the device breakdown before presenting to stakeholders