Mathew K Analytics

Lesson 25 · Real-World Data Analytics

Customer Funnel & Repeat Purchase Analysis in Python (Pandas) | Data Analytics #25

How many first-time buyers ever come back? We turn 500,000+ real invoice lines from a UK online retailer into a repeat-purchase funnel in pandas: repeat…

What you'll learn

Datasets used in this lesson

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

📓 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.

This lesson is for learners who can group and merge data in pandas and want to build a customer funnel. A funnel tracks how many customers make it through each stage of a journey, and where they drop off. Most funnel tutorials need website click data, but here you build one from purchases alone. The business question: how many first-time buyers come back, how quickly, and are repeat buyers worth more?

The data is a real UK online retailer's transaction log, loaded from online_retail.csv, with 541,909 invoice lines and 8 columns. The columns that matter are InvoiceNo, CustomerID, InvoiceDate, Quantity, UnitPrice and Country.

In this lesson you will:

  • clean the raw lines and roll them up into one row per order
  • measure repeat purchasing within 30 days, 90 days and ever, plus 3+ order loyalty
  • compare acquisition cohorts, countries and one-time versus repeat spending
  • check revenue concentration among your best customers

Prerequisites: you should be comfortable with pandas groupby, merge and datetime columns. Earlier lessons in the series cover these, including work on this same retail dataset.

Part 1: Real Purchases, Real Funnel#

A funnel is only as good as the transactions behind it, so start by seeing how much raw data you have. This step imports pandas, numpy and matplotlib, reads online_retail.csv into retail and checks .shape. The output (541909, 8) means 541,909 invoice lines across 8 columns. Each row is one product line on an invoice, not one order, so an order with ten different products takes up ten rows.

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#

A repeat-purchase funnel needs to know who each buyer is, so rows without a customer are useless here. dropna(subset=['CustomerID']) removes them, and .copy() makes an independent table to avoid pandas warnings later. Keeping Quantity > 0 removes returns and cancellations. pd.to_datetime turns dates into real datetime values, and .astype(int) turns IDs like 12346.0 into 12346. The output shows 397,924 lines remain from 4,339 unique customers.

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#

Funnels count orders and customers, not product lines, so the lines must be rolled up. The code calculates Revenue as quantity times unit price, then groups by both CustomerID and InvoiceNo. Named aggregation in .agg keeps the earliest timestamp per invoice and sums revenue. reset_index() turns the group keys back into ordinary columns. The output shows 18,536 orders with 4 columns, so the average customer placed a little over four orders.

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#

Every funnel stage here is measured from a customer's first purchase, so you need that date on every order. groupby('CustomerID')['InvoiceDate'].min() finds each customer's first order date, .rename('first_date') names it, and merge attaches it to every order. Subtracting the dates gives a time gap, and .dt.days turns it into whole days. In the preview, customer 12347 first ordered on 7 December 2010 and returned after 49, 120 and 183 days. Note that .dt.days drops partial days, so a second order on the same day shows 0.

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#

The top of the funnel is everyone who bought at least once. nunique() counts distinct customer IDs in the orders table, and the output is 4,339, the same figure seen after cleaning. That consistency is a useful check that the rollup did not lose or invent customers. All later stages are expressed as a share of this number, so it becomes the denominator for the funnel percentages.

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

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

Quick repeat purchases are a strong sign that a customer found real value. The code filters orders placed more than 0 and up to 30 days after the first purchase, combining the two conditions with &, then counts distinct customers. Dividing by the total and multiplying by 100 gives a percentage. The output shows 812 customers, or 18.7%, came back within a month. Using > 0 rather than >= 0 is important, because otherwise the first order itself would count as a repeat.

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#

Widening the window to 90 days shows how many customers return within a typical quarter. The code is the same as for 30 days, with the upper limit changed to 90. The output shows 1,836 customers, or 42.3%, made a repeat purchase within 90 days. So more than half of the customers who return within a quarter take longer than 30 days to do so, which matters if you are timing follow-up emails or offers.

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#

The broadest repeat measure counts anyone who came back at any point in the data. The filter keeps only orders more than 0 days after the first purchase, and nunique() counts the customers. The output shows 2,783 customers, or 64.1%, bought again. Bear in mind that the data covers only about a year, so customers who first bought near the end had little time to return. This figure is a lower bound on true repeat behaviour.

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#

Loyalty is usually defined by order count rather than timing. groupby('CustomerID').size() counts orders per customer, and comparing with >= 3 then using .sum() counts customers with three or more orders. The output shows 2,010 customers, 46.3% of the total, placed at least three orders. That is a high share for an online shop, likely because many of this retailer's customers are businesses that reorder regularly.

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#

Putting all stages into one table makes the funnel easy to read and share. pd.DataFrame is built from a dictionary of stage names and customer counts, and a new column divides each count by the total for a percentage. The output shows the five stages from 4,339 customers down to 812, then back up to 1,836 and 2,783, then 2,010. The counts do not shrink steadily, because the 30-day, 90-day and ever stages are nested time windows, each including the one before, rather than sequential steps.

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#

A horizontal bar chart is a common way to show a funnel. plt.barh draws one bar per stage, and plt.gca().invert_yaxis() flips the order so the first stage sits at the top, as readers expect. The chart is saved to purchase_funnel.png and closed, so nothing is displayed. When you open it, the bars will not form a neat narrowing shape: the 30-day bar is the shortest, and the 90-day and ever-repeated bars are longer, for the reason explained in the funnel table step.

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#

Stage-to-stage conversion shows what share of each stage reaches the next. .shift(1) moves the customer counts down one row, so each row can be divided by the previous one. The first row is NaN because it has nothing before it. The output exposes the nesting issue clearly: 226.1% and 151.6% are impossible as real conversion rates and appear only because those stages are wider windows. Only 18.7% (all customers to 30-day repeaters) and 72.2% (ever repeated to loyal) are meaningful here.

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#

Knowing how long customers take to return tells you when to send reminders or offers. The code filters to repeat orders, then takes each customer's smallest days_since_first value, which is the gap to their second purchase. The summary covers 2,783 customers. The average gap is 81.7 days, but the median is 56, and a quarter of customers return within 27 days. The longest gap is 365 days. A mean well above the median signals a long tail of slow returners.

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#

The median is the better single figure for time to repeat, because a few customers who return after almost a year pull the mean upwards. .median() on the gap series returns 56.0 days. So half of repeat customers come back within about eight weeks. That is a practical anchor for a win-back campaign: if a customer has not reordered by around day 56, they are already slower than the typical repeat buyer.

median_days_to_repeat = second_purchase_gap.median()
median_days_to_repeat
np.float64(56.0)

Part 15: Visualizing Real Time-to-Repeat#

A histogram shows whether repeat purchases cluster at particular times. The code keeps gaps of 200 days or fewer so the long tail does not squash the chart, plots 40 bins, and adds a dashed vertical line at the median with plt.axvline. The figure is saved to time_to_repeat.png and closed. In the image, look for most bars piled up on the left, in the first two months, with the red median line at 56 days and a gradually thinning tail after it.

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#

If repeat orders are bigger than first orders, retention is worth even more than customer counts suggest. First orders are those with days_since_first == 0, and .first() keeps one per customer. Repeat orders are all orders after day 0. The output shows the average first order is 425.56, against 497.64 for repeat orders. Repeat orders are about 72 larger on average (in the dataset's price units), so returning customers spend more each time as well as buying more often.

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#

Grouping customers by the month they first bought, called an acquisition cohort, lets you compare groups that started at the same time. .dt.to_period('M') turns the first date into a year-month value, drop_duplicates('CustomerID') keeps one row per customer, and groupby(...).size() counts them. December 2010 is by far the largest cohort with 885 customers, partly because it is the first month of data, so it includes existing customers seen for the first time. December 2011 has only 41, because the data ends early that month.

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#

Comparing repeat rates by cohort shows whether newer customers behave differently. The code flags each customer who ever had an order after day 0, using .any() inside a lambda, merges that flag onto the cohort table, and averages it per month. Repeat rates fall from 87.2% for December 2010 to 0.0% for December 2011. Most of that decline is simply time: later cohorts had fewer months to come back. Compare cohorts only over equal time windows, such as 90 days, to make a fair comparison.

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#

Different markets can have very different buying habits, so a country split is a natural next step. The code assigns each customer the first country listed on their lines, merges it onto the orders, and uses value_counts().head(5).index to keep the five countries with the most orders. The output lists the United Kingdom, Germany, France, EIRE (the Republic of Ireland) and Belgium. Because the UK dominates, overall figures mostly describe UK customers.

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#

This step compares repeat rates across the top five countries. The code keeps customers from those countries with .isin, removes duplicate customers, attaches the repeat flag, and averages it per country. EIRE shows 100.0%, then Belgium 75.0%, Germany 71.3%, France 67.8% and the UK 64.2%. A perfect 100% almost always means a small group, so check how many Irish customers there are before reading much into it. The UK, with by far the most customers, sits at the bottom.

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#

The distribution of order counts shows how spread out loyalty is. The code calls describe() first, but only the last expression is displayed, so you see value_counts().sort_index().head(10): how many customers placed exactly 1, 2, 3 orders and so on, up to 10. The output shows 1,494 one-order customers, 835 with two orders and 508 with three, then a steady fall to 54 with ten orders. Most customers sit at the low end, with a long tail of frequent buyers.

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#

The most valuable customers usually combine frequent orders with high spend. The code sums revenue per customer, then keeps those with 3 or more orders and total spend above the median. Both conditions are Series indexed by CustomerID, so pandas lines them up by customer before combining them with &. The output shows 1,725 customers meet both tests. That is most of the 2,010 loyal customers, which shows that frequent buyers also tend to be big spenders.

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)
1725

Part 23: Real Revenue Concentration Check#

Many businesses earn most of their revenue from a small group of customers, often called the Pareto or 80/20 pattern. The code sorts customers by total spend from highest to lowest, takes the top 20% with head(int(len(...) * 0.2)), and divides their revenue by the total. The output shows the top 20% of customers produce 74.6% of revenue. That is close to the classic 80/20 split, and it means losing even a few top customers would hurt revenue badly.

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#

One-time buyers are the biggest opportunity in a repeat funnel, so it helps to size them. The code keeps customers whose order count is exactly 1 and divides by the total. The output shows 34.4%. Note that this plus the 64.1% who ever repeated does not quite reach 100%: some customers placed two orders within the first 24 hours, so they have more than one order but no order with days_since_first above 0.

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#

Comparing total spend per customer shows how much a repeat customer is worth compared with a one-time buyer. The code averages total spend for customers with exactly one order and for those with two or more. One-time buyers spent 412.52 on average, while repeat buyers spent 2,915.68, about seven times as much. That gap is the strongest argument in this lesson for investing in getting first-time buyers to come back.

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#

Saving the funnel table means it can feed a dashboard or report without re-running the whole analysis. to_csv(index=False) writes it without the row index, and reading it back then calling .equals checks that values, column names and types all match the original. The output is True. .equals is stricter than comparing shapes: it also catches changes such as numbers being read back as text or rounding differences.

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#

A classic funnel should shrink at each stage, so this check tests that assumption. .diff() calculates the change from one stage to the next, .dropna() removes the first empty value, and all(... <= 0) asks whether every change is zero or negative. The output is False, because the counts rise from 812 to 1,836 to 2,783. This is not a data error: it confirms that the time-window stages are nested rather than sequential, so this table is not a strict funnel.

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

Part 28: Real Sanity Check, Percentages in Range#

Shares of a total must sit between 0 and 100; anything outside that range points to a wrong denominator. The code builds two True/False conditions, joins them with &, and all() checks every row passes. The output is True. Unlike stage-to-stage conversion, pct_of_total always divides by the same total customer count, which is why it stays in range even though the stages are nested.

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

Part 29: Real Actionable Recommendation#

Funnel analysis should end with where to act. The code uses idxmin() to find the row with the lowest stage-to-stage conversion and .loc to read its stage name. The output is Repeat within 30 Days, where conversion is 18.7%. Given the nesting issue, this really says that the jump from first purchase to a quick second purchase is the weakest point. A sensible action is a follow-up offer within the first month, timed before the 56-day median.

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#

The recap sentence pulls together the headline figures, built from variables so it stays correct if the data changes. The output reports 4,339 customers, 64.1% who made a repeat purchase, 46.3% who became loyal with three or more orders, and a median of 56 days to the second order. In the f-string, :.0f formats the median with no decimal places, which reads more naturally in a sentence.

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.

Practice on your own

  • Rebuild the cohort repeat rates using only repeat orders within 90 days of the first purchase, and compare the December 2010 and June 2011 cohorts on that fairer basis.
  • Count how many customers sit behind each country's repeat rate with country_customers.groupby('Country').size(), and decide which percentages you would trust.
  • Turn the funnel into a true sequence of stages, for example 1 order, 2+ orders, 3+ orders and 5+ orders, using orders_per_customer, then recalculate stage-to-stage conversion and confirm it never goes above 100%.

The notebook says the next video is a hashtag and topic trend analysis, returning to the social post data from earlier in this domain. Watch the video walkthrough to see each funnel step built live.

🧠 Test yourself

Pick an answer to check it. Your best score is saved in this browser.

  1. How does the lesson build a customer funnel without any page-view data?

  2. Why is the repeat filter written as days_since_first > 0 rather than >= 0?

  3. A funnel has these customer counts by stage: 1000, 300, 200. What is the stage-to-stage conversion at the third stage?

    funnel['stage_conversion_pct'] = round(funnel['customers'] / funnel['customers'].shift(1) * 100, 1)
    
  4. How does the lesson find the stage with the biggest drop-off?

  5. Why is the count for "Repeat within 90 Days" always at least as large as "Repeat within 30 Days" in the funnel?

💻 Practice exercises

Try each one in the notebook or in your own editor before opening the solution.

  1. Create an orders table of 15 rows (CustomerID, InvoiceNo, InvoiceDate) for 6 customers. Compute each customer's first_date, add days_since_first, then print the total customers, how many repeated within 30 days, and what percentage that is.

    💡 Hint

    Merge groupby('CustomerID')['InvoiceDate'].min() back, then filter days between 1 and 30.

    ✅ Show solution
    import pandas as pd
    
    orders = pd.DataFrame({
        'CustomerID': [1,1,1,2,2,3,4,4,4,4,5,6,6,6,6],
        'InvoiceNo': range(100, 115),
        'InvoiceDate': pd.to_datetime(['2024-01-01','2024-01-20','2024-03-15','2024-01-05','2024-04-10',
                                       '2024-01-10','2024-01-12','2024-01-25','2024-02-20','2024-05-01',
                                       '2024-02-01','2024-02-03','2024-02-05','2024-04-30','2024-06-01']),
    })
    first_date = orders.groupby('CustomerID')['InvoiceDate'].min().rename('first_date')
    orders = orders.merge(first_date, on='CustomerID')
    orders['days_since_first'] = (orders['InvoiceDate'] - orders['first_date']).dt.days
    
    total_customers = orders['CustomerID'].nunique()
    repeat_30 = orders[(orders['days_since_first'] > 0) & (orders['days_since_first'] <= 30)]['CustomerID'].nunique()
    print('Total customers:', total_customers)
    print('Repeat within 30 days:', repeat_30, f'({round(repeat_30 / total_customers * 100, 1)}%)')
    
  2. From the same type of orders table (use 20 rows, 8 customers), build a five-stage funnel table: All Customers, Repeat within 30 Days, Repeat within 90 Days, Ever Repeated, Loyal (3+ Orders). Add pct_of_total and stage_conversion_pct, check the 90-day count is at least the 30-day count, and print the stage with the biggest drop-off.

    💡 Hint

    Use shift(1) for stage conversion and diff() for the monotonic check.

    ✅ Show solution
    import pandas as pd
    
    orders = pd.DataFrame({
        'CustomerID': [1,1,1,2,2,3,4,4,4,4,5,6,6,6,6,7,7,8,8,8],
        'InvoiceNo': range(200, 220),
        'InvoiceDate': pd.to_datetime(['2024-01-01','2024-01-20','2024-03-15','2024-01-05','2024-04-10',
                                       '2024-01-10','2024-01-12','2024-01-25','2024-02-20','2024-05-01',
                                       '2024-02-01','2024-02-03','2024-02-05','2024-04-30','2024-06-01',
                                       '2024-03-01','2024-03-10','2024-03-05','2024-04-01','2024-08-01']),
    })
    orders['first_date'] = orders.groupby('CustomerID')['InvoiceDate'].transform('min')
    orders['days_since_first'] = (orders['InvoiceDate'] - orders['first_date']).dt.days
    
    total = orders['CustomerID'].nunique()
    rep30 = orders[orders['days_since_first'].between(1, 30)]['CustomerID'].nunique()
    rep90 = orders[orders['days_since_first'].between(1, 90)]['CustomerID'].nunique()
    ever = orders[orders['days_since_first'] > 0]['CustomerID'].nunique()
    loyal = (orders.groupby('CustomerID').size() >= 3).sum()
    
    funnel = pd.DataFrame({
        'stage': ['All Customers', 'Repeat within 30 Days', 'Repeat within 90 Days', 'Ever Repeated', 'Loyal (3+ Orders)'],
        'customers': [total, rep30, rep90, ever, loyal],
    })
    funnel['pct_of_total'] = (funnel['customers'] / total * 100).round(1)
    funnel['stage_conversion_pct'] = (funnel['customers'] / funnel['customers'].shift(1) * 100).round(1)
    print(funnel)
    print('90-day >= 30-day:', rep90 >= rep30)
    print('Biggest drop at:', funnel.loc[funnel['stage_conversion_pct'].idxmin(), 'stage'])
    
  3. Using an orders table with Revenue (about 20 rows, 8 customers), compare average first-order value with average repeat-order value, compare total spend of one-time customers vs repeat customers, and compute the revenue share of the top 20% of customers by spend.

    💡 Hint

    Orders with days_since_first == 0 are first orders; use sort_values().head(...) for the top 20%.

    ✅ Show solution
    import pandas as pd
    
    orders = pd.DataFrame({
        'CustomerID': [1,1,1,2,2,3,4,4,4,4,5,6,6,6,6,7,7,8,8,8],
        'InvoiceDate': pd.to_datetime(['2024-01-01','2024-01-20','2024-03-15','2024-01-05','2024-04-10',
                                       '2024-01-10','2024-01-12','2024-01-25','2024-02-20','2024-05-01',
                                       '2024-02-01','2024-02-03','2024-02-05','2024-04-30','2024-06-01',
                                       '2024-03-01','2024-03-10','2024-03-05','2024-04-01','2024-08-01']),
        'Revenue': [40,70,90,55,120,30,25,45,60,80,35,20,30,40,50,60,90,15,25,35],
    })
    orders['first_date'] = orders.groupby('CustomerID')['InvoiceDate'].transform('min')
    orders['days_since_first'] = (orders['InvoiceDate'] - orders['first_date']).dt.days
    
    first_value = orders[orders['days_since_first'] == 0]['Revenue'].mean()
    repeat_value = orders[orders['days_since_first'] > 0]['Revenue'].mean()
    print('Avg first order:', round(first_value, 2), '| avg repeat order:', round(repeat_value, 2))
    
    orders_per_customer = orders.groupby('CustomerID').size()
    spend = orders.groupby('CustomerID')['Revenue'].sum()
    print('Avg spend, one-time:', round(spend[orders_per_customer == 1].mean(), 2))
    print('Avg spend, repeat:', round(spend[orders_per_customer >= 2].mean(), 2))
    
    top_n = int(len(spend) * 0.2)
    top_share = spend.sort_values(ascending=False).head(top_n).sum() / spend.sum() * 100
    print(f'Top {top_n} customers (20%) hold {top_share:.1f}% of revenue')
    

Found this useful?

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