Course index · Previous · Next

Analyze Customer Cohorts and Retention Denominators

Module 5: Exploration and Visualization · Lecture 20 of 32

Retention requires an identity, a cohort definition, and enough time to observe a return. Build monthly purchase cohorts while keeping anonymous orders and not-yet-observable periods from becoming misleading zeros.

What you will learn

• Assign first-observed-purchase cohorts to identified customers.
• Calculate distinct-customer retention by cohort age.
• Distinguish an unobserved future period from observed inactivity.

Understand the problem

Define a cohort carefully

A cohort groups customers by a shared starting event. Here the event is first observed non-cancelled purchase in the provided window. That is not necessarily the customer's first-ever purchase: the export begins in January. Label it first-observed cohort so the limitation is visible. Anonymous orders cannot establish a stable customer history and are excluded from this analysis.

Count people, not orders

Retention at month age one is the number of distinct cohort customers with a non-cancelled order in the following calendar month divided by the cohort's original distinct-customer count. Several orders by one customer count once. Fully refunded orders remain qualifying events under this course's purchase contract; a different retention definition would need a different status rule.

Observation windows censor later cells

A March cohort has no observable age-one month in a dataset ending March 31. That cell is unavailable, not zero retention. A January cohort can have observed ages zero, one, and two. Distinguish the triangular observation window from actual inactivity. Filling every blank cell with zero unfairly penalizes newer cohorts.

Interpret descriptively

A cohort table can reveal a pattern worth investigating, but this fictional generator deliberately repeats customers and creates artificial structure. Do not claim a real retention benchmark or a causal channel effect. In production, account for acquisition history, identity changes, timezones, data coverage, and the event that constitutes an active customer.

Run the worked example

Download and extract the course kit linked below. Open a terminal in the extracted folder, complete the setup in Lecture 2, and run python run_course.py --lesson 20 --examples-only. Each script starts from the shared dataset; it does not depend on a notebook cell from another lecture. All retail records are fictional and amounts are integer paise unless a chart explicitly says INR.

from pathlib import Path
import numpy as np
import pandas as pd
from course_data import load_orders, load_raw, load_customers, clean_orders, ROOT, OUT

orders = load_orders()
active = orders.loc[orders['status'].ne('cancelled') & orders['customer_id'].notna()].copy()
local = active['ordered_at'].dt.tz_convert('Asia/Kolkata')
active['month_number'] = local.dt.year * 12 + local.dt.month
active['cohort'] = active.groupby('customer_id')['month_number'].transform('min')
active['age'] = active['month_number'] - active['cohort']
counts = active.groupby(['cohort', 'age'])['customer_id'].nunique().unstack('age')
retention = counts.div(counts[0], axis=0)
end_month = 2026 * 12 + 3
for cohort in retention.index:
    for age in retention.columns:
        if cohort + age <= end_month:
            retention.loc[cohort, age] = 0 if pd.isna(retention.loc[cohort, age]) else retention.loc[cohort, age]
        else:
            retention.loc[cohort, age] = np.nan
print(retention.round(3).to_string())
assert retention[0].eq(1).all()
assert retention.stack().between(0, 1).all()

Verified output

age       0      1      2
cohort                   
24313   1.0  0.979  0.979

Walk through the result

The numeric month representation supports age subtraction across year boundaries without treating months as a fixed number of days. groupby-transform assigns each customer's first observed month back to every order. nunique prevents repeat orders from inflating retained customers. The final loop fills only cells inside the known reporting window and preserves future cells as unavailable. Age zero must equal one by construction.

Common mistakes and diagnosis

Do not call the first observed purchase a first-ever purchase without history. Do not divide each period by the previous period if the label promises original-cohort retention. Do not treat an anonymous ID placeholder as one repeat customer. A missing future cell is not a failed retention outcome. A funnel requires event-level population and ordering data; this order export alone cannot establish browse-to-cart conversion.

Practice assignment

Calculate the percentage of identified purchasing customers who appear in at least two distinct observed months. Compare it with the percentage who placed at least two orders, and explain why those measures need not match.

per_customer = active.groupby('customer_id').agg(months=('month_number', 'nunique'), orders=('order_id', 'size'))
repeat_month_share = per_customer['months'].ge(2).mean()
repeat_order_share = per_customer['orders'].ge(2).mean()
assert repeat_order_share >= repeat_month_share
print('Repeat-month share:', round(float(repeat_month_share), 4))

Check your understanding

Why must a newer cohort sometimes show a blank cell instead of zero?

Reference and next step

• pandas group transformations

Download the complete course kit: datasets, all examples, solutions, and capstone.

Return to the full Data Analysis with Python and pandas curriculum.

Course index · Previous · Next