Lesson 25 · Real-World Data Analytics

Python Data Analytics #25: Customer Funnel & Conversion Rate Analysis in Python

Video twenty-five of the hundred-video real-world data analytics series. Over five hundred thousand real invoice lines from a genuine UK online retailer,…

⬇ Download notebook ⬇ Notebook + data (.zip)Open in Colab ↗

CourseReal-World Data Analytics
Lesson25 of 100
Video28 min
FormatJupyter notebook · 30 code cells
Data1 dataset

pandas NumPy Matplotlib

What you'll learn

Real Purchases, Real Funnel
Real Cleaning Before Funnel Work
Real Order-Level Rollup
Real First Purchase Date per Customer
Real Funnel Stage One, Total Customers
Real Funnel Stage Two, Repeat Within Thirty Days
Real Funnel Stage Three, Repeat Within Ninety Days
Real Funnel Stage Four, Ever Repeated

Datasets used in this lesson

Save these next to the notebook. In Google Colab, upload them with the 📁 icon on the left first.

online_retail.csv45.5 MB

📓 Full notebook

Download .ipynb

Data Analytics 100, Video 25: Customer Funnel and Conversion Rate Analysis#

Video twenty-five of the hundred-video real-world data analytics series.
Over five hundred thousand real invoice lines from a genuine UK online retailer, turned into a real repeat-purchase funnel.
Let's get into it.

Part 1: Real Purchases, Real Funnel#

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
retail = pd.read_csv('online_retail.csv')
retail.shape

(541909, 8)

Part 2: Real Cleaning Before Funnel Work#

retail_clean = retail.dropna(subset=['CustomerID']).copy()
retail_clean = retail_clean[retail_clean['Quantity'] > 0]
retail_clean['InvoiceDate'] = pd.to_datetime(retail_clean['InvoiceDate'])
retail_clean['CustomerID'] = retail_clean['CustomerID'].astype(int)
len(retail_clean), retail_clean['CustomerID'].nunique()

(397924, 4339)

Part 3: Real Order-Level Rollup#

retail_clean['Revenue'] = retail_clean['Quantity'] * retail_clean['UnitPrice']
orders = retail_clean.groupby(['CustomerID', 'InvoiceNo']).agg(InvoiceDate=('InvoiceDate', 'min'), Revenue=('Revenue', 'sum')).reset_index()
orders.shape

(18536, 4)

Part 4: Real First Purchase Date per Customer#

first_purchase = orders.groupby('CustomerID')['InvoiceDate'].min().rename('first_date')
orders = orders.merge(first_purchase, on='CustomerID')
orders['days_since_first'] = (orders['InvoiceDate'] - orders['first_date']).dt.days
orders[['CustomerID', 'InvoiceDate', 'first_date', 'days_since_first']].head(5)

	CustomerID	InvoiceDate	first_date	days_since_first
0	12346	2011-01-18 10:01:00	2011-01-18 10:01:00	0
1	12347	2010-12-07 14:57:00	2010-12-07 14:57:00	0
2	12347	2011-01-26 14:30:00	2010-12-07 14:57:00	49
3	12347	2011-04-07 10:43:00	2010-12-07 14:57:00	120
4	12347	2011-06-09 13:01:00	2010-12-07 14:57:00	183

Part 5: Real Funnel Stage One, Total Customers#

total_customers = orders['CustomerID'].nunique()
total_customers

Part 6: Real Funnel Stage Two, Repeat Within Thirty Days#

repeat_30_customers = orders[(orders['days_since_first'] > 0) & (orders['days_since_first'] <= 30)]['CustomerID'].nunique()
repeat_30_pct = round(repeat_30_customers / total_customers * 100, 1)
repeat_30_customers, repeat_30_pct

(812, 18.7)

Part 7: Real Funnel Stage Three, Repeat Within Ninety Days#

repeat_90_customers = orders[(orders['days_since_first'] > 0) & (orders['days_since_first'] <= 90)]['CustomerID'].nunique()
repeat_90_pct = round(repeat_90_customers / total_customers * 100, 1)
repeat_90_customers, repeat_90_pct

(1836, 42.3)

Part 8: Real Funnel Stage Four, Ever Repeated#

repeat_ever_customers = orders[orders['days_since_first'] > 0]['CustomerID'].nunique()
repeat_ever_pct = round(repeat_ever_customers / total_customers * 100, 1)
repeat_ever_customers, repeat_ever_pct

(2783, 64.1)

Part 9: Real Funnel Stage Five, Loyal Customers#

orders_per_customer = orders.groupby('CustomerID').size()
loyal_customers = (orders_per_customer >= 3).sum()
loyal_pct = round(loyal_customers / total_customers * 100, 1)
loyal_customers, loyal_pct

(np.int64(2010), np.float64(46.3))

Part 10: Assembling the Real Funnel Table#

funnel_table = pd.DataFrame({'stage': ['All Customers', 'Repeat within 30 Days', 'Repeat within 90 Days', 'Ever Repeated', 'Loyal (3+ Orders)'], 'customers': [total_customers, repeat_30_customers, repeat_90_customers, repeat_ever_customers, loyal_customers]})
funnel_table['pct_of_total'] = round(funnel_table['customers'] / total_customers * 100, 1)
funnel_table

	stage	customers	pct_of_total
0	All Customers	4339	100.0
1	Repeat within 30 Days	812	18.7
2	Repeat within 90 Days	1836	42.3
3	Ever Repeated	2783	64.1
4	Loyal (3+ Orders)	2010	46.3

Part 11: Visualizing the Real Funnel#

plt.figure(figsize=(9, 5))
plt.barh(funnel_table['stage'], funnel_table['customers'], color='teal')
plt.xlabel('Real Number of Customers')
plt.title('Real Customer Purchase Funnel')
plt.gca().invert_yaxis()
plt.tight_layout()
plt.savefig('purchase_funnel.png', dpi=120)
plt.close()

Part 12: Real Stage-to-Stage Conversion Rates#

funnel_table['stage_conversion_pct'] = round(funnel_table['customers'] / funnel_table['customers'].shift(1) * 100, 1)
funnel_table[['stage', 'customers', 'stage_conversion_pct']]

	stage	customers	stage_conversion_pct
0	All Customers	4339	NaN
1	Repeat within 30 Days	812	18.7
2	Repeat within 90 Days	1836	226.1
3	Ever Repeated	2783	151.6
4	Loyal (3+ Orders)	2010	72.2

Part 13: Real Time to Second Purchase#

second_purchase_gap = orders[orders['days_since_first'] > 0].groupby('CustomerID')['days_since_first'].min()
second_purchase_gap.describe().round(1)

count    2783.0
mean       81.7
std        75.9
min         1.0
25%        27.0
50%        56.0
75%       116.0
max       365.0
Name: days_since_first, dtype: float64

Part 14: Real Median Time to Repeat#

median_days_to_repeat = second_purchase_gap.median()
median_days_to_repeat

np.float64(56.0)

Part 15: Visualizing Real Time-to-Repeat#

capped_gap = second_purchase_gap[second_purchase_gap <= 200]
plt.figure(figsize=(9, 5))
plt.hist(capped_gap, bins=40, color='slateblue', edgecolor='white')
plt.axvline(median_days_to_repeat, color='crimson', linestyle='--', label=f'Real Median: {median_days_to_repeat:.0f} days')
plt.xlabel('Real Days Until Second Purchase')
plt.ylabel('Real Number of Customers')
plt.title('Real Distribution of Time to Second Purchase')
plt.legend()
plt.tight_layout()
plt.savefig('time_to_repeat.png', dpi=120)
plt.close()

Part 16: Real First-Order Value vs Repeat-Order Value#

first_order_value = orders[orders['days_since_first'] == 0].groupby('CustomerID')['Revenue'].first()
repeat_order_value = orders[orders['days_since_first'] > 0]['Revenue']
round(first_order_value.mean(), 2), round(repeat_order_value.mean(), 2)

(np.float64(425.56), np.float64(497.64))

Part 17: Real Monthly Acquisition Cohorts#

orders['first_month'] = orders['first_date'].dt.to_period('M')
cohort_sizes = orders.drop_duplicates('CustomerID').groupby('first_month').size()
cohort_sizes

first_month
2010-12    885
2011-01    417
2011-02    380
2011-03    452
2011-04    300
2011-05    284
2011-06    242
2011-07    188
2011-08    169
2011-09    299
2011-10    358
2011-11    324
2011-12     41
Freq: M, dtype: int64

Part 18: Real Repeat Rate by Acquisition Cohort#

cohort_customers = orders.drop_duplicates('CustomerID')[['CustomerID', 'first_month']]
repeat_flag = orders.groupby('CustomerID')['days_since_first'].apply(lambda s: (s > 0).any())
cohort_customers = cohort_customers.merge(repeat_flag.rename('repeated'), on='CustomerID')
cohort_repeat_rate = cohort_customers.groupby('first_month')['repeated'].mean().round(3) * 100
cohort_repeat_rate

first_month
2010-12    87.2
2011-01    81.3
2011-02    76.6
2011-03    70.6
2011-04    66.0
2011-05    66.9
2011-06    62.4
2011-07    58.0
2011-08    53.3
2011-09    47.2
2011-10    32.7
2011-11    20.4
2011-12     0.0
Freq: M, Name: repeated, dtype: float64

Part 19: Real Country-Level Funnel Comparison#

customer_country = retail_clean.groupby('CustomerID')['Country'].first()
orders_with_country = orders.merge(customer_country.rename('Country'), on='CustomerID')
top_countries = orders_with_country['Country'].value_counts().head(5).index
top_countries.tolist()

['United Kingdom', 'Germany', 'France', 'EIRE', 'Belgium']

Part 20: Real Repeat Rate by Country#

country_customers = orders_with_country[orders_with_country['Country'].isin(top_countries)].drop_duplicates('CustomerID')[['CustomerID', 'Country']]
country_customers = country_customers.merge(repeat_flag.rename('repeated'), on='CustomerID')
country_repeat_rate = country_customers.groupby('Country')['repeated'].mean().round(3) * 100
country_repeat_rate.sort_values(ascending=False)

Country
EIRE              100.0
Belgium            75.0
Germany            71.3
France             67.8
United Kingdom     64.2
Name: repeated, dtype: float64

Part 21: Real Order Frequency Distribution#

orders_per_customer.describe().round(2)
orders_per_customer.value_counts().sort_index().head(10)

1     1494
2      835
3      508
4      387
5      243
6      172
7      143
8       98
9       68
10      54
Name: count, dtype: int64

Part 22: Real High-Value Repeat Customers#

customer_total_spend = orders.groupby('CustomerID')['Revenue'].sum()
high_value_repeat = customer_total_spend[(orders_per_customer >= 3) & (customer_total_spend > customer_total_spend.median())]
len(high_value_repeat)

Part 23: Real Revenue Concentration Check#

total_revenue = customer_total_spend.sum()
top_20_pct_customers = customer_total_spend.sort_values(ascending=False).head(int(len(customer_total_spend) * 0.2))
top_20_revenue_share = round(top_20_pct_customers.sum() / total_revenue * 100, 1)
top_20_revenue_share

np.float64(74.6)

Part 24: Real Never-Repeated Segment#

never_repeated = orders_per_customer[orders_per_customer == 1]
never_repeated_pct = round(len(never_repeated) / total_customers * 100, 1)
never_repeated_pct

34.4

Part 25: Real Average Spend, One-Time vs Repeat#

one_time_spend = customer_total_spend[orders_per_customer == 1].mean()
repeat_spend = customer_total_spend[orders_per_customer >= 2].mean()
round(one_time_spend, 2), round(repeat_spend, 2)

(np.float64(412.52), np.float64(2915.68))

Part 26: Saving the Real Funnel Table#

funnel_table.to_csv('customer_funnel.csv', index=False)
reloaded_funnel = pd.read_csv('customer_funnel.csv')
reloaded_funnel.equals(funnel_table)

True

Part 27: Real Sanity Check, Funnel Monotonically Shrinks#

all(funnel_table['customers'].diff().dropna() <= 0)

False

Part 28: Real Sanity Check, Percentages in Range#

all((0 <= funnel_table['pct_of_total']) & (funnel_table['pct_of_total'] <= 100))

True

Part 29: Real Actionable Recommendation#

biggest_drop_stage = funnel_table.loc[funnel_table['stage_conversion_pct'].idxmin(), 'stage']
biggest_drop_stage

'Repeat within 30 Days'

Part 30: Real Recap Print#

print(f'Across {total_customers} real customers, {repeat_ever_pct}% made a repeat purchase and {loyal_pct}% became loyal 3+ order customers, with a median of {median_days_to_repeat:.0f} real days to that second order.')

Across 4339 real customers, 64.1% made a repeat purchase and 46.3% became loyal 3+ order customers, with a median of 56 real days to that second order.

Wrap-Up: What You Learned#

A purchase funnel does not require page-view or clickstream data, order timestamps alone are enough to build a real, actionable funnel.
Stage-to-stage conversion rates reveal where customers genuinely drop off far more clearly than raw percentage-of-total numbers.
Repeat customers in this real dataset spent noticeably more per order than first-time buyers, a real signal worth acting on.
Acquisition cohorts and country segments can each convert to repeat purchasing at genuinely different rates, worth investigating separately.
A small share of top-spending customers can account for a disproportionate share of real total revenue, the classic real Pareto pattern.
Next video: real hashtag and topic trend analysis, returning to the real social post data from earlier in this domain.

Found this useful?

All lessons, notebooks and datasets here are free. If they helped you, a coffee keeps new lessons coming.

☕ Buy me a coffee Other ways to help