Product Funnel Analysis: Where Users Drop and How to Prove It in Python

Every growth team has a funnel and every funnel leaks. This notebook quantifies exactly where, using a clickstream event log and both bar and Sankey visuals, sourced from courses/workbooks/generators/funnel.py.

What does a funnel prove that a total conversion rate does not?

Total "visitors -> purchasers = 3%" hides the bottleneck. A stepped funnel (Landing -> Product -> Add to Cart -> Checkout -> Purchase) shows stepwise survival — the product question is "which transition breaks?" plus "how much revenue does fixing it recover?" Complement with Customer Analytics RFM for who leaks and A/B testing for whether a fix worked.

Ingredients: 10,000 users with decreasing survival probs: Landing 100%, Product 60%, Cart 45%, Checkout 35%, Purchase 25% (planted), plus timestamped events per user.

How do you simulate clickstream events?

Setup and funnel definition:

import pandas as pd
import numpy as np
import plotly.graph_objects as go
import plotly.express as px

steps = ['1_Landing_Page','2_Product_Page','3_Add_to_Cart','4_Checkout','5_Purchase']
conversion_probs = {
    '1_Landing_Page': 0.60,
    '2_Product_Page': 0.45,
    '3_Add_to_Cart': 0.35,
    '4_Checkout': 0.70,
}
np.random.seed(42)
n_users = 10000
data=[]
for user in range(n_users):
    data.append((user, '1_Landing_Page'))
    current = '1_Landing_Page'
    for nxt in steps[1:]:
        if np.random.rand() < conversion_probs[current]:
            data.append((user, nxt))
            current = nxt
        else:
            break
df = pd.DataFrame(data, columns=['user_id','step'])
print(df.head(10))
print(df['step'].value_counts().sort_index())

Rendered output: 10 rows of (user_id, step) with monotonic step order per user; value_counts reads roughly 10000 / 6000 / 2700 / 950 / 665 — the intended decay.

How do you compute the funnel table correctly?

Count distinct users per step, not events.

funnel = df.groupby('step')['user_id'].nunique().reindex(steps)
funnel_df = pd.DataFrame({'users': funnel})
funnel_df['pct_of_first'] = funnel_df['users'] / funnel_df['users'].iloc[0] * 100
funnel_df['step_conversion'] = funnel_df['users'] / funnel_df['users'].shift(1) * 100
funnel_df['drop'] = funnel_df['users'].shift(1) - funnel_df['users']
print(funnel_df.round(1))

Rendered output: a 5-row table where step_conversion shows Landing->Product 60%, Product->Cart 45%, Cart->Checkout 35%, Checkout->Purchase 70% — the third transition is the bottleneck, worth the first experiment.

# Bar + Sankey
import plotly.graph_objects as go
fig = go.Figure(go.Funnel(y=steps, x=funnel_df['users'].tolist()))
fig.update_layout(title='Acquisition Funnel — Users per Step')
fig.show()
 
# Sankey: source=step i, target=step i+1, value=users reaching next
sources, targets, values = [], [], []
for i in range(len(steps)-1):
    sources.append(i); targets.append(i+1); values.append(int(funnel_df['users'].iloc[i+1]))
sankey = go.Figure(data=[go.Sankey(
    node=dict(label=steps), link=dict(source=sources, target=targets, value=values)
)])
sankey.update_layout(title='User Flow — Sankey')
sankey.show()

Rendered output: bars decaying left-to-right; Sankey ribbons narrow sharply between Product and Cart, the visual twin of the table.

How do you turn leak into action?

Prioritise the lowest step_conversion with the largest absolute drop — here Product->Cart. Pair the fix with an A/B read from the AB testing playbook before claiming recovery, and enrich with text mining on exit surveys at that step.

Download the Notebook and Practise

This article is a walkthrough of a runnable Jupyter notebook. Download the original .ipynb and run it locally or on Colab — every code block above appears in order.

Continue your track: Data Analyst Roadmap · Python and Pandas Guide · SQL NULL Handbook · SQL JOIN Fan-Out · Topfolio Practice · Data Analyst vs Engineer

Dataset generators where applicable are in courses/workbooks/generators/ — see citations atop for the exact *.py source for this notebook.

Frequently Asked Questions

What is funnel drop-off analysis?

Counting distinct users at each ordered step (Landing -> Product -> Cart -> Checkout -> Purchase) and computing step-to-step survival to locate the leakiest transition.

How do you compute funnel conversion correctly?

Count distinct users per step, not events, then divide by the prior step's users. Use df.groupby('step')['user_id'].nunique() to avoid double-counting repeat events.

What is a Sankey diagram and when is it better than bars?

Sankey shows flow volume between steps including skips and loops, while bars show only stepwise totals. Use Sankey when users can jump or revisit steps.

How do you prove a funnel change caused lift (not seasonality)?

Pair the funnel rebuild with an A/B read using the same chi-square logic as the AB Testing Playbook — never claim lift from a before/after alone.