Funnel Analysis: Optimize Your Conversion Flow

events (user_id, event_type, event_timestamp, session_id)

WITH funnel_events AS (
  SELECT
    session_id,
    MAX(CASE WHEN event_type = 'product_view' THEN 1 ELSE 0 END) AS viewed_product,
    MAX(CASE WHEN event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS added_to_cart,
    MAX(CASE WHEN event_type = 'checkout_start' THEN 1 ELSE 0 END) AS started_checkout,
    MAX(CASE WHEN event_type = 'payment_info' THEN 1 ELSE 0 END) AS entered_payment,
    MAX(CASE WHEN event_type = 'order_complete' THEN 1 ELSE 0 END) AS completed_order
  FROM events
  WHERE DATE(event_timestamp) = '2026-03-22'
  GROUP BY session_id
)
SELECT
  COUNT(*) AS total_sessions,
  SUM(viewed_product) AS step1_product_view,
  SUM(added_to_cart) AS step2_add_to_cart,
  SUM(started_checkout) AS step3_checkout,
  SUM(entered_payment) AS step4_payment,
  SUM(completed_order) AS step5_complete,

  -- Conversion rates (step-to-step)
  ROUND(100.0 * SUM(added_to_cart) / NULLIF(SUM(viewed_product), 0), 1) AS view_to_cart_pct,
  ROUND(100.0 * SUM(started_checkout) / NULLIF(SUM(added_to_cart), 0), 1) AS cart_to_checkout_pct,
  ROUND(100.0 * SUM(entered_payment) / NULLIF(SUM(started_checkout), 0), 1) AS checkout_to_payment_pct,
  ROUND(100.0 * SUM(completed_order) / NULLIF(SUM(entered_payment), 0), 1) AS payment_to_complete_pct,

  -- Overall conversion
  ROUND(100.0 * SUM(completed_order) / NULLIF(SUM(viewed_product), 0), 1) AS overall_conversion_pct
FROM funnel_events
WHERE viewed_product = 1;  -- Only sessions that started funnel

WITH step_times AS (
  SELECT
    session_id,
    MIN(CASE WHEN event_type = 'product_view' THEN event_timestamp END) AS view_time,
    MIN(CASE WHEN event_type = 'add_to_cart' THEN event_timestamp END) AS cart_time,
    MIN(CASE WHEN event_type = 'checkout_start' THEN event_timestamp END) AS checkout_time,
    MIN(CASE WHEN event_type = 'order_complete' THEN event_timestamp END) AS complete_time
  FROM events
  WHERE DATE(event_timestamp) = '2026-03-22'
  GROUP BY session_id
),
constrained_funnel AS (
  SELECT
    session_id,
    CASE WHEN view_time IS NOT NULL THEN 1 ELSE 0 END AS step1,
    CASE WHEN cart_time <= view_time + INTERVAL '1 hour' THEN 1 ELSE 0 END AS step2,
    CASE WHEN checkout_time <= cart_time + INTERVAL '1 hour' THEN 1 ELSE 0 END AS step3,
    CASE WHEN complete_time <= checkout_time + INTERVAL '1 hour' THEN 1 ELSE 0 END AS step4
  FROM step_times
  WHERE view_time IS NOT NULL
)
SELECT
  SUM(step1) AS viewed_product,
  SUM(step2) AS added_to_cart_1hr,
  SUM(step3) AS checkout_1hr,
  SUM(step4) AS completed_1hr,
  ROUND(100.0 * SUM(step4) / SUM(step1), 1) AS overall_conversion_1hr_pct
FROM constrained_funnel;

WITH funnel_events AS (
  SELECT
    e.session_id,
    e.device_type,  -- 'mobile', 'desktop'
    MAX(CASE WHEN e.event_type = 'product_view' THEN 1 ELSE 0 END) AS viewed,
    MAX(CASE WHEN e.event_type = 'add_to_cart' THEN 1 ELSE 0 END) AS carted,
    MAX(CASE WHEN e.event_type = 'order_complete' THEN 1 ELSE 0 END) AS completed
  FROM events e
  WHERE DATE(e.event_timestamp) = '2026-03-22'
  GROUP BY e.session_id, e.device_type
)
SELECT
  device_type,
  SUM(viewed) AS step1_views,
  SUM(carted) AS step2_carts,
  SUM(completed) AS step3_orders,
  ROUND(100.0 * SUM(carted) / NULLIF(SUM(viewed), 0), 1) AS view_to_cart_pct,
  ROUND(100.0 * SUM(completed) / NULLIF(SUM(carted), 0), 1) AS cart_to_order_pct,
  ROUND(100.0 * SUM(completed) / NULLIF(SUM(viewed), 0), 1) AS overall_conversion_pct
FROM funnel_events
WHERE viewed = 1
GROUP BY device_type;

import pandas as pd
import plotly.graph_objects as go

# Funnel data
funnel_data = {
    'Stage': ['Product View', 'Add to Cart', 'Checkout', 'Payment', 'Complete'],
    'Users': [100000, 25000, 10000, 7000, 6000],
    'Conversion': [100.0, 25.0, 10.0, 7.0, 6.0]
}
df = pd.DataFrame(funnel_data)

# Plotly funnel chart
fig = go.Figure(go.Funnel(
    y=df['Stage'],
    x=df['Users'],
    textposition="inside",
    textinfo="value+percent initial",
    marker=dict(color=['#4CAF50', '#8BC34A', '#CDDC39', '#FFEB3B', '#FFC107']),
    connector={"line": {"color": "#999", "dash": "dot", "width": 2}}
))

fig.update_layout(
    title='E-commerce Purchase Funnel',
    showlegend=False,
    width=800,
    height=500
)
fig.show()

import matplotlib.pyplot as plt
import numpy as np

stages = ['View→Cart', 'Cart→Checkout', 'Checkout→Payment', 'Payment→Complete']
step_conversion = [25.0, 40.0, 70.0, 86.0]
drop_off = [100 - x for x in step_conversion]

fig, ax = plt.subplots(figsize=(10, 6))
x = np.arange(len(stages))
width = 0.35

bars1 = ax.bar(x - width/2, step_conversion, width, label='Converted', color='#4CAF50')
bars2 = ax.bar(x + width/2, drop_off, width, label='Dropped Off', color='#F44336')

ax.set_xlabel('Funnel Step')
ax.set_ylabel('Percentage')
ax.set_title('Step-to-Step Conversion vs Drop-off')
ax.set_xticks(x)
ax.set_xticklabels(stages, rotation=15, ha='right')
ax.legend()
ax.set_ylim(0, 100)

# Add percentage labels
for bar in bars1:
    height = bar.get_height()
    ax.text(bar.get_x() + bar.get_width()/2., height,
            f'{height:.0f}%', ha='center', va='bottom', fontsize=10)

plt.tight_layout()
plt.show()

import plotly.graph_objects as go

# Define flows (source → target, value)
labels = ['View', 'Cart', 'Checkout', 'Payment', 'Complete', 'Drop-off']
source = [0, 1, 2, 3,   0, 1, 2, 3]  # From stages
target = [1, 2, 3, 4,   5, 5, 5, 5]  # To stages or drop-off
values = [25000, 10000, 7000, 6000,  75000, 15000, 3000, 1000]  # Flow volumes

fig = go.Figure(go.Sankey(
    node=dict(
        pad=15,
        thickness=20,
        line=dict(color='black', width=0.5),
        label=labels,
        color=['#4CAF50', '#8BC34A', '#CDDC39', '#FFEB3B', '#FFC107', '#F44336']
    ),
    link=dict(
        source=source,
        target=target,
        value=values,
        color=['rgba(76,175,80,0.4)' if t != 5 else 'rgba(244,67,54,0.4)' for t in target]
    )
))

fig.update_layout(
    title='Funnel Flow Analysis (100K Sessions)',
    font=dict(size=12),
    width=900,
    height=600
)
fig.show()

WITH funnel_times AS (
  SELECT
    session_id,
    MIN(CASE WHEN event_type = 'product_view' THEN event_timestamp END) AS view_time,
    MIN(CASE WHEN event_type = 'add_to_cart' THEN event_timestamp END) AS cart_time,
    MIN(CASE WHEN event_type = 'order_complete' THEN event_timestamp END) AS complete_time
  FROM events
  WHERE DATE(event_timestamp) = '2026-03-22'
  GROUP BY session_id
),
time_diffs AS (
  SELECT
    session_id,
    EXTRACT(EPOCH FROM (cart_time - view_time)) / 60 AS view_to_cart_mins,
    EXTRACT(EPOCH FROM (complete_time - cart_time)) / 60 AS cart_to_complete_mins,
    EXTRACT(EPOCH FROM (complete_time - view_time)) / 60 AS overall_mins
  FROM funnel_times
  WHERE view_time IS NOT NULL AND complete_time IS NOT NULL
)
SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY view_to_cart_mins) AS median_view_to_cart_mins,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY cart_to_complete_mins) AS median_cart_to_complete_mins,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY overall_mins) AS median_overall_mins
FROM time_diffs;

WITH converters AS (
  SELECT DISTINCT session_id
  FROM events
  WHERE event_type = 'order_complete'
  AND DATE(event_timestamp) = '2026-03-22'
),
session_actions AS (
  SELECT
    e.session_id,
    CASE WHEN c.session_id IS NOT NULL THEN 1 ELSE 0 END AS converted,
    COUNT(DISTINCT CASE WHEN e.event_type = 'product_view' THEN e.product_id END) AS products_viewed,
    COUNT(DISTINCT CASE WHEN e.event_type = 'search' THEN 1 END) AS searches,
    COUNT(DISTINCT CASE WHEN e.event_type = 'filter_click' THEN 1 END) AS filter_uses,
    MAX(CASE WHEN e.event_type = 'review_read' THEN 1 ELSE 0 END) AS read_reviews
  FROM events e
  LEFT JOIN converters c ON e.session_id = c.session_id
  WHERE DATE(e.event_timestamp) = '2026-03-22'
  GROUP BY e.session_id, converted
)
SELECT
  converted,
  AVG(products_viewed) AS avg_products_viewed,
  AVG(searches) AS avg_searches,
  AVG(filter_uses) AS avg_filters,
  SUM(read_reviews) * 100.0 / COUNT(*) AS pct_read_reviews
FROM session_actions
GROUP BY converted;

WITH user_paths AS (
  SELECT
    session_id,
    STRING_AGG(event_type, ' → ' ORDER BY event_timestamp) AS path
  FROM events
  WHERE DATE(event_timestamp) = '2026-03-22'
  GROUP BY session_id
),
path_outcomes AS (
  SELECT
    up.path,
    COUNT(*) AS sessions,
    SUM(CASE WHEN up.path LIKE '%order_complete%' THEN 1 ELSE 0 END) AS conversions,
    ROUND(100.0 * SUM(CASE WHEN up.path LIKE '%order_complete%' THEN 1 ELSE 0 END) / COUNT(*), 1) AS conversion_rate
  FROM user_paths up
  GROUP BY up.path
  HAVING COUNT(*) >= 100  -- Only paths with 100+ sessions
  ORDER BY sessions DESC
  LIMIT 20
)
SELECT * FROM path_outcomes;