Product Analytics in Python: Funnel Conversion, Drop-off & User Journey Mapping

import pandas as pd
import numpy as np
import plotly.express as px
import plotly.graph_objects as go
from datetime import datetime, timedelta
 
# Set seed for reproducible data generation
np.random.seed(42)
n_users = 10000
 
# Funnel step definitions in chronological order
funnel_steps = [
    '1_Landing_Page',
    '2_Signup',
    '3_Product_Search',
    '4_Add_to_Cart',
    '5_Purchase'
]
 
# Step-to-next-step transition probabilities
transition_probs = {
    '1_Landing_Page': 0.72,   # 72% proceed to Signup
    '2_Signup': 0.58,         # 58% search for a product
    '3_Product_Search': 0.44, # 44% add an item to cart
    '4_Add_to_Cart': 0.65     # 65% complete checkout
}
 
events = []
base_time = datetime(2026, 8, 1, 9, 0, 0)
 
for user_id in range(1001, 1001 + n_users):
    device = np.random.choice(['Mobile', 'Desktop', 'Tablet'], p=[0.60, 0.32, 0.08])
    current_time = base_time + timedelta(seconds=int(np.random.exponential(scale=3600 * 24)))
    
    # Step 1: Every user hits the landing page
    events.append({
        'user_id': user_id,
        'step_name': '1_Landing_Page',
        'timestamp': current_time,
        'device': device
    })
    
    # Simulate sequential funnel progression
    for current_step, next_step in zip(funnel_steps[:-1], funnel_steps[1:]):
        if np.random.random() < transition_probs[current_step]:
            # Add realistic delay between actions (15s to 8 mins)
            step_delay_sec = int(np.random.gamma(shape=2.5, scale=60))
            current_time += timedelta(seconds=step_delay_sec)
            
            events.append({
                'user_id': user_id,
                'step_name': next_step,
                'timestamp': current_time,
                'device': device
            })
        else:
            # User dropped off at this stage
            break
 
df_events = pd.DataFrame(events)
print(f"Total Logged Events: {len(df_events):,}")
print(f"Unique Users: {df_events['user_id'].nunique():,}")
df_events.head(8)

# 1. Count unique users per step
funnel_df = (
    df_events.groupby('step_name')['user_id']
    .nunique()
    .reset_index()
    .rename(columns={'step_name': 'Step', 'user_id': 'Unique_Users'})
)
 
# 2. Sort by the numeric prefix in Step name
funnel_df = funnel_df.sort_values('Step').reset_index(drop=True)
 
# 3. Calculate step-over-step conversion and drop-off
top_of_funnel_users = funnel_df.loc[0, 'Unique_Users']
 
funnel_df['Prev_Step_Users'] = funnel_df['Unique_Users'].shift(1)
funnel_df['Step_Conversion_Pct'] = (
    (funnel_df['Unique_Users'] / funnel_df['Prev_Step_Users']) * 100
).fillna(100.0)
 
funnel_df['Step_Dropoff_Pct'] = (
    (1 - (funnel_df['Unique_Users'] / funnel_df['Prev_Step_Users'])) * 100
).fillna(0.0)
 
funnel_df['Cumulative_Conversion_Pct'] = (
    (funnel_df['Unique_Users'] / top_of_funnel_users) * 100
)
 
# 4. Clean formatting for reporting
formatted_funnel = funnel_df.copy()
formatted_funnel['Step_Conversion_Pct'] = formatted_funnel['Step_Conversion_Pct'].map('{:.2f}%'.format)
formatted_funnel['Step_Dropoff_Pct'] = formatted_funnel['Step_Dropoff_Pct'].map('{:.2f}%'.format)
formatted_funnel['Cumulative_Conversion_Pct'] = formatted_funnel['Cumulative_Conversion_Pct'].map('{:.2f}%'.format)
 
formatted_funnel[['Step', 'Unique_Users', 'Step_Conversion_Pct', 'Step_Dropoff_Pct', 'Cumulative_Conversion_Pct']]

fig_px = px.funnel(
    funnel_df,
    x='Unique_Users',
    y='Step',
    title='E-Commerce Product Conversion Funnel',
    labels={'Unique_Users': 'Active Users', 'Step': 'Funnel Stage'},
    color_discrete_sequence=['#0d9488']
)
fig_px.update_layout(template='plotly_white')
fig_px.show()

# Aggregate unique users per step by device
device_funnel = (
    df_events.groupby(['step_name', 'device'])['user_id']
    .nunique()
    .reset_index()
    .sort_values(['step_name'])
)
 
fig_segmented = go.Figure()
 
colors = {'Mobile': '#0d9488', 'Desktop': '#6366f1', 'Tablet': '#f59e0b'}
 
for device in ['Mobile', 'Desktop', 'Tablet']:
    sub_df = device_funnel[device_funnel['device'] == device]
    
    fig_segmented.add_trace(go.Funnel(
        name=device,
        y=sub_df['step_name'],
        x=sub_df['user_id'],
        textinfo="value+percent initial+percent previous",
        marker={"color": colors[device]}
    ))
 
fig_segmented.update_layout(
    title='Segmented Funnel Conversion by Device Type',
    template='plotly_white',
    legend=dict(orientation="h", yanchor="bottom", y=1.02, xanchor="right", x=1)
)
 
fig_segmented.show()

# 1. Extract first timestamp for each step per user
user_step_times = (
    df_events.groupby(['user_id', 'step_name'])['timestamp']
    .min()
    .unstack(level='step_name')
)
 
# 2. Compute time deltas in minutes between consecutive steps
ttc_df = pd.DataFrame(index=user_step_times.index)
ttc_df['Landing_to_Signup_Min'] = (
    user_step_times['2_Signup'] - user_step_times['1_Landing_Page']
).dt.total_seconds() / 60.0
 
ttc_df['Signup_to_Search_Min'] = (
    user_step_times['3_Product_Search'] - user_step_times['2_Signup']
).dt.total_seconds() / 60.0
 
ttc_df['Search_to_Cart_Min'] = (
    user_step_times['4_Add_to_Cart'] - user_step_times['3_Product_Search']
).dt.total_seconds() / 60.0
 
ttc_df['Cart_to_Purchase_Min'] = (
    user_step_times['5_Purchase'] - user_step_times['4_Add_to_Cart']
).dt.total_seconds() / 60.0
 
ttc_df['Total_Journey_Min'] = (
    user_step_times['5_Purchase'] - user_step_times['1_Landing_Page']
).dt.total_seconds() / 60.0
 
# 3. Calculate summary statistics (Median, 75th percentile, 90th percentile)
velocity_summary = pd.DataFrame({
    'Metric': [
        'Landing -> Signup',
        'Signup -> Search',
        'Search -> Add to Cart',
        'Add to Cart -> Purchase',
        'Full Journey (Landing -> Purchase)'
    ],
    'Median_Time_Min': [
        ttc_df['Landing_to_Signup_Min'].median(),
        ttc_df['Signup_to_Search_Min'].median(),
        ttc_df['Search_to_Cart_Min'].median(),
        ttc_df['Cart_to_Purchase_Min'].median(),
        ttc_df['Total_Journey_Min'].median()
    ],
    'P75_Time_Min': [
        ttc_df['Landing_to_Signup_Min'].quantile(0.75),
        ttc_df['Signup_to_Search_Min'].quantile(0.75),
        ttc_df['Search_to_Cart_Min'].quantile(0.75),
        ttc_df['Cart_to_Purchase_Min'].quantile(0.75),
        ttc_df['Total_Journey_Min'].quantile(0.75)
    ],
    'P90_Time_Min': [
        ttc_df['Landing_to_Signup_Min'].quantile(0.90),
        ttc_df['Signup_to_Search_Min'].quantile(0.90),
        ttc_df['Search_to_Cart_Min'].quantile(0.90),
        ttc_df['Cart_to_Purchase_Min'].quantile(0.90),
        ttc_df['Total_Journey_Min'].quantile(0.90)
    ]
})
 
velocity_summary.round(2)

# Prepare Sankey flow indices (Source -> Target -> Volume)
label_list = funnel_df['Step'].tolist() + ['Drop_Off_Exit']
label_map = {name: i for i, name in enumerate(label_list)}
 
drop_off_node_idx = label_map['Drop_Off_Exit']
 
sources = []
targets = []
values = []
link_colors = []
 
for i in range(len(funnel_df) - 1):
    current_step = funnel_df.loc[i, 'Step']
    next_step = funnel_df.loc[i + 1, 'Step']
    
    current_users = funnel_df.loc[i, 'Unique_Users']
    converted_users = funnel_df.loc[i + 1, 'Unique_Users']
    dropped_users = current_users - converted_users
    
    # 1. Flow of users who successfully converted to next step
    sources.append(label_map[current_step])
    targets.append(label_map[next_step])
    values.append(converted_users)
    link_colors.append('rgba(13, 148, 136, 0.45)') # Teal
    
    # 2. Flow of users who dropped off at this stage
    sources.append(label_map[current_step])
    targets.append(drop_off_node_idx)
    values.append(dropped_users)
    link_colors.append('rgba(239, 68, 68, 0.35)') # Red
    
fig_sankey = go.Figure(data=[go.Sankey(
    node=dict(
        pad=18,
        thickness=24,
        line=dict(color="black", width=0.5),
        label=label_list,
        color=['#0f766e', '#0d9488', '#14b8a6', '#2dd4bf', '#5eead4', '#ef4444']
    ),
    link=dict(
        source=sources,
        target=targets,
        value=values,
        color=link_colors
    )
)])
 
fig_sankey.update_layout(
    title_text="User Journey Conversion Flow & Drop-off Sink (Sankey)",
    font_size=12,
    template='plotly_white'
)
fig_sankey.show()