Build a Cohort Retention Table from Excel Data

"Are customers sticking around?" is one of the questions a monthly report is most often asked, and one of the worst answered by a single number. A retention rate averaged over everyone mixes customers who joined last month with customers who joined a year ago, and hides whether things are getting better. A cohort table separates them: customers are grouped by the month they first bought, and each row follows one group over the months that follow. Read across a row to see how one cohort decays; read down a column to see whether newer cohorts hold on better than older ones. All it needs is an order export with a customer ID and a date, and a few pandas group-bys. This page is part of Creating Pivot Tables from Excel Data.

Prerequisites

pip install pandas openpyxl xlsxwriter

orders_2026.xlsx holds 3,205 orders from 723 customers between January and August 2026: an order ID, a customer ID, the order date and an amount. It is the shape of export every shop, subscription or B2B billing system can produce.

import pandas as pd

orders = pd.read_excel("orders_2026.xlsx", parse_dates=["OrderDate"])
print(orders.shape, orders["CustomerID"].nunique(), "customers")
print(orders.head(3).to_string(index=False))

Step 1 — Give every order a cohort and an age

A customer's cohort is the month of their first order. Compute it with groupby().transform("min"), which returns the per-customer minimum on every one of that customer's rows. The age of an order is how many whole months after the cohort month it happened:

orders["OrderMonth"] = orders["OrderDate"].dt.to_period("M")
orders["Cohort"] = orders.groupby("CustomerID")["OrderMonth"].transform("min")

def month_index(p):
    return p.dt.year * 12 + p.dt.month

orders["Age"] = month_index(orders["OrderMonth"]) - month_index(orders["Cohort"])
print(orders[["CustomerID", "OrderDate", "Cohort", "Age"]].head(4).to_string(index=False))
print(orders["Age"].value_counts().sort_index().to_dict())

Converting both periods to a running month number — year × 12 + month — and subtracting gives the age as an integer column in one vectorised step. Subtracting the periods directly also works, but returns offset objects that need a slow .apply to turn into numbers. Calendar months are the usual unit; for a product with a weekly rhythm, use to_period("W") and weeks instead.

Step 2 — Count active customers per cohort and age

The core of the table is one group-by: for each cohort and age, how many distinct customers ordered? nunique matters — a customer who ordered twice in a month counts once:

counts = (orders.groupby(["Cohort", "Age"])["CustomerID"].nunique()
                .unstack(fill_value=0))
print(counts)

Each row is a cohort, each column a month of age, and column 0 is the cohort's size, since everyone orders in their first month by definition. The table is a triangle: the January cohort has eight months of history, the June cohort only three, because the data stops at the end of August. Those missing cells are not zeros — they are months that have not happened yet — so the next step keeps them empty rather than filling them.

Step 3 — Turn counts into retention rates

Divide every row by its cohort size, and blank out the future cells:

last = orders["OrderMonth"].max()
sizes = counts[0]
retention = counts.div(sizes, axis=0)
for cohort in retention.index:
    observed = (last - cohort).n
    retention.loc[cohort, retention.columns > observed] = float("nan")
print((retention * 100).round(1))

div(..., axis=0) divides each row by the matching cohort size. unstack(fill_value=0) in the previous step wrote zeros into the future cells; resetting everything beyond each cohort's last observed month to NaN prevents a later average from treating "has not happened yet" as "nobody came back".

Reading the result: the January cohort keeps 56% of its customers into the second month and 23% by month seven. The April, May and June cohorts keep 65–67% into month one, against 50–61% for the first three cohorts — the kind of change a single blended retention number would hide, and exactly what the table is for.

Step 4 — An average retention curve, weighted properly

Management usually wants one curve: "what share of a typical cohort is still buying after n months?" Averaging the percentages in each column treats a cohort of 97 customers the same as one of 139. Weight by size instead — sum the active customers and divide by the sum of the sizes of the cohorts that have reached that age:

observed = retention.notna()
active = counts.where(observed).sum()               # customers active at each age
eligible = observed.mul(sizes, axis=0).sum()        # customers in cohorts old enough
curve = pd.DataFrame({"weighted %": (active / eligible * 100).round(1),
                      "simple mean %": (retention.mean() * 100).round(1),
                      "cohorts": observed.sum()})
print(curve)

observed marks the cells that exist. active adds up the customers still ordering at each age across the cohorts that have reached it, and eligible adds up the sizes of those same cohorts; dividing one by the other gives the share of all eligible customers who were active. The simple mean of the percentages is shown alongside for comparison: where large and small cohorts retain differently, the two disagree, and the weighted figure is the one that answers "what share of customers". In this data they are within half a point of each other, because the cohorts are of similar size — 97 to 139 customers. The gap opens up when a campaign month brings in a cohort several times the usual size: its behaviour should dominate the average in proportion to its size, and only the weighted version does that. Calculating both and comparing them is a cheap sanity check.

The later months rest on fewer cohorts — month seven is the January cohort alone — so the tail of the curve is less certain than its start. Showing the number of cohorts behind each point, as the cohorts column does, keeps readers from over-reading it.

Step 5 — Write the table to Excel as a heatmap

Cohort tables are read by colour first. Write the percentages with a percent format and a colour scale, so the decay along each row and the improvement down each column are visible at a glance:

with pd.ExcelWriter("cohort_retention.xlsx", engine="xlsxwriter") as xw:
    out = retention.copy()
    out.index = out.index.astype(str)
    out.insert(0, "Customers", sizes.values)
    out.columns = ["Customers"] + [f"M{c}" for c in retention.columns]
    out.to_excel(xw, sheet_name="Retention", index_label="Cohort")
    wb, ws = xw.book, xw.sheets["Retention"]
    pct = wb.add_format({"num_format": "0%"})
    ws.set_column(2, len(out.columns), 7, pct)
    ws.conditional_format(1, 3, len(out), len(out.columns),
                          {"type": "2_color_scale", "min_color": "#FFFFFF", "max_color": "#4338CA"})
    counts.where(observed).rename(columns=lambda c: f"M{c}").rename(index=str).to_excel(xw, sheet_name="Counts")
print(pd.read_excel("cohort_retention.xlsx", sheet_name="Retention").iloc[:2, :5])

The colour scale starts at column D, month one, so the 100% of month zero does not stretch the scale and wash out the differences that matter. Empty future cells stay uncoloured because a colour scale ignores blanks. The Counts sheet keeps the absolute numbers next to the percentages — a retention of 60% means something different for a cohort of 10 than for one of 1,000. Turn a Pivot Table into a Heatmap in Excel covers colour scales in more detail.

Common pitfalls

• Counting orders instead of customers. Use nunique on the customer ID; two orders in a month is still one retained customer.
• Zeros in future cells. unstack(fill_value=0) and fillna(0) turn "not yet observed" into "nobody returned"; blank them out.
• Averaging percentages. Weight by cohort size when combining cohorts.
• Partial current month. If the export was taken mid-month, the latest column understates retention. Cut the data at the end of the last complete month.
• Customers who existed before the data starts. Anyone who first appears in the first month of the export may be an old customer; exclude that cohort or load enough history to know.

Conclusion

Assign every customer to the month of their first order with groupby().transform("min"), compute each order's age in months, count distinct customers per cohort and age, and divide each row by the cohort size — leaving months that have not happened yet empty. Combine cohorts into one curve by weighting with cohort sizes, and write the table to Excel with a colour scale from month one onwards, next to the raw counts.

Frequently asked questions

What is a cohort retention table? A table that groups customers by when they first bought and shows, for each group, the share still buying in each following month. Rows follow one cohort over time; columns compare cohorts at the same age.

How do I calculate months since first purchase in pandas? Convert the order date and the cohort month to periods, turn each into a running month number with year × 12 + month, and subtract. That gives an integer age in months for every order.

Why are the bottom-right cells of my cohort table empty? Those cohorts have not been observed that long yet — the data ends before they reach that age. The cells should stay empty rather than zero, so averages only use cohorts old enough.

How do I average retention across cohorts? Sum the retained customers at each age across the cohorts that have reached it and divide by the sum of those cohorts' sizes. A simple mean of percentages gives small cohorts too much weight.

Related

• Up one level: Creating Pivot Tables from Excel Data — group-bys, crosstabs and summaries.
• Count Distinct Values per Group in pandas — the nunique step in depth.
• Build an RFM Customer Segmentation from Excel Data — another way to look at the same customers.
• Group Excel Rows by Month and Quarter with pandas — periods and month grouping.