Build a Customer Churn Report from Subscription Data

For a subscription business, the monthly revenue figure hides the story that matters: how many customers left, how much revenue left with them, and whether the customers who stayed are paying more or less than before. A churn report answers those questions from the same data the billing system already holds — what each customer paid each month. This page builds one with pandas: for every month, the customers at the start, gained and lost, and the logo churn rate; a monthly recurring revenue (MRR) bridge from opening to closing MRR through new, expansion, contraction and churned revenue; net revenue retention; and a cohort table showing what share of each month's new customers is still subscribed months later. Each month's bridge is checked to add up, and the report is written to a workbook with a colour scale on the cohort table. It belongs to Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl

The input is a long table with one row per customer per month they were billed: customer ID, month and MRR. Most billing systems can export this directly, or it can be built from subscription start and end dates. Twelve months are used here, from October 2025 to September 2026; the first month is the opening book of customers.

Step 1 — A customer-by-month grid

Pivot the long table into one row per customer and one column per month, with 0 where a customer was not billed. Every movement can then be found by comparing a month's column with the previous one:

import pandas as pd

mrr = pd.read_excel("subscription_mrr.xlsx")
grid = mrr.pivot_table(index="customer_id", columns="month", values="mrr", aggfunc="sum", fill_value=0)
months = list(grid.columns)
print(grid.shape[0], "customers over", len(months), "months;", f"MRR in {months[-1]:%b %Y}: {grid[months[-1]].sum():,.0f}")

The aggfunc="sum" matters if a customer can have more than one subscription: their lines are added together, so movements are measured per customer, which is what churn means — a customer who swaps one plan for another has not churned. Filling gaps with 0 turns "not billed" into an explicit zero that the comparisons below can use.

Step 2 — Monthly movements and the MRR bridge

For each month, classify every customer by comparing this month's MRR with last month's:

rows = []
for prev, cur in zip(months, months[1:]):
    a, b = grid[prev], grid[cur]
    was, now = a > 0, b > 0
    rows.append({
        "month": cur,
        "customers_start": int(was.sum()),
        "new_customers": int((~was & now).sum()),
        "churned_customers": int((was & ~now).sum()),
        "customers_end": int(now.sum()),
        "mrr_start": a.sum(),
        "new_mrr": b[~was & now].sum(),
        "expansion_mrr": (b - a)[was & now & (b > a)].sum(),
        "contraction_mrr": (b - a)[was & now & (b < a)].sum(),
        "churned_mrr": -a[was & ~now].sum(),
        "mrr_end": b.sum(),
    })
report = pd.DataFrame(rows).set_index("month")
report["logo_churn"] = report["churned_customers"] / report["customers_start"]
report["nrr"] = (report["mrr_start"] + report["expansion_mrr"] + report["contraction_mrr"] + report["churned_mrr"]) / report["mrr_start"]

bridge = report["mrr_start"] + report["new_mrr"] + report["expansion_mrr"] + report["contraction_mrr"] + report["churned_mrr"]
assert (bridge - report["mrr_end"]).abs().max() < 0.01, "MRR bridge does not add up"
cols = ["customers_start", "new_customers", "churned_customers", "logo_churn", "mrr_start", "new_mrr",
        "expansion_mrr", "contraction_mrr", "churned_mrr", "mrr_end", "nrr"]
print(report[cols].tail(3).round(3).to_string())

A customer billed last month but not this month has churned, taking last month's MRR with them; one billed this month but not last is new. Customers billed in both months either pay more (expansion), less (contraction) or the same. Contraction and churn are stored as negative numbers so the bridge reads naturally — opening MRR plus every movement equals closing MRR — and the assertion proves it for every month. A bridge that does not add up means a customer was double-counted or missed, which is worth knowing before the figures reach a board pack.

Logo churn is the share of opening customers lost in the month. Net revenue retention (NRR) looks only at revenue from customers who were there at the start of the month: what fraction of it remains after their expansions, contractions and cancellations. NRR above 100% means existing customers grew faster than they left — the business would grow even without new sales.

Over the year, logo churn moves between 2.7% and 6.0% a month and NRR between 95% and 102%. September is the weakest month for revenue retention: 19 of 347 customers left, taking 1,691 of MRR, and NRR fell to 95.0%. MRR still grew, from 27,033 to 27,271, because 31 new customers added 1,579 — growth that depends entirely on new sales is the pattern a churn report is there to expose.

Step 3 — Cohort retention

Monthly churn rates mix customers of every age. A cohort table separates them: group customers by the month they started and show, for each later month, the share still billed:

first = grid.gt(0).idxmax(axis=1)                         # first month with MRR
active = grid.gt(0)
records = []
for start in months[1:]:
    members = first.index[first == start]
    later = [m for m in months if m >= start]
    shares = [active.loc[members, m].mean() for m in later]
    records.append([len(members)] + shares + [None] * (len(months) - 1 - len(later)))
cohorts = pd.DataFrame(records, index=months[1:], columns=["customers"] + [f"M{i}" for i in range(len(months) - 1)])
print(cohorts.iloc[:4, :6].round(2).to_string())

Each row is a cohort of new customers; column M0 is their first month (always 100%), M1 the next, and so on, so the table forms a triangle — recent cohorts have fewer months of history. The opening month is left out, because customers billed in October 2025 include everyone who joined before the data starts and are not a true cohort. In this data the oldest cohort, November 2025, still has 63% of its customers after ten months, while the April and May 2026 cohorts had already fallen to 83% and 79% by their third month — earlier and steeper losses than the winter cohorts showed at the same age. Reading down a column compares cohorts at the same age: if recent cohorts retain worse at M3 than older ones did, something changed in who is being signed up or how they are onboarded.

Step 4 — Write the report

from openpyxl import load_workbook
from openpyxl.formatting.rule import ColorScaleRule

path = "churn_report_2026-09.xlsx"
out = report[cols].copy()
out.index = out.index.strftime("%b %Y")
coh = cohorts.copy()
coh.index = coh.index.strftime("%b %Y")
with pd.ExcelWriter(path, engine="openpyxl") as xw:
    out.to_excel(xw, sheet_name="Monthly", index_label="Month")
    coh.to_excel(xw, sheet_name="Cohorts", index_label="Cohort")

wb = load_workbook(path)
ws = wb["Monthly"]
for col_idx, name in enumerate(["Month"] + cols, start=1):
    fmt = "0.0%" if name in ("logo_churn", "nrr") else "#,##0"
    for cell in list(ws.iter_cols(min_col=col_idx, max_col=col_idx, min_row=2))[0]:
        cell.number_format = fmt if name != "Month" else "@"
ws_c = wb["Cohorts"]
last_col = ws_c.cell(1, ws_c.max_column).column_letter
ws_c.conditional_formatting.add(f"C2:{last_col}{ws_c.max_row}",
    ColorScaleRule(start_type="num", start_value=0.5, start_color="F8696B", mid_type="num", mid_value=0.8, mid_color="FFEB84",
                   end_type="num", end_value=1, end_color="63BE7B"))
for row in ws_c.iter_rows(min_row=2, min_col=3):
    for cell in row:
        cell.number_format = "0%"
wb.save(path)
latest = report.iloc[-1]
print(f"{months[-1]:%b %Y}: logo churn {latest['logo_churn']:.1%}, NRR {latest['nrr']:.1%}, "
      f"MRR {latest['mrr_start']:,.0f} -> {latest['mrr_end']:,.0f}")

The Monthly sheet holds movements, churn and NRR; the Cohorts sheet holds the retention triangle with a red-to-green colour scale anchored at fixed values (50%, 80%, 100%), so colours mean the same thing every month rather than being relative to whatever the table contains. Blank cells in the triangle are left uncoloured. For a board pack, a chart of the MRR bridge — opening, the four movements, closing — is the natural companion; Build a Waterfall Chart in Excel with Python shows how.

A note on definitions before the report circulates: churn can be measured per customer or per subscription, monthly or annualised, and "active" can mean billed, paid or not yet cancelled. None of these is wrong, but a report that switches definitions between months, or differs from the finance team's numbers without saying why, loses trust quickly. State the definitions on a notes sheet in the workbook — billed MRR per customer, monthly logo churn, NRR excluding new customers — so readers compare like with like.

Common pitfalls

• Counting plan changes as churn. Sum MRR per customer before comparing months.
• A bridge that does not add up. Assert opening plus movements equals closing every month.
• Treating the opening month as a cohort. It contains every customer who joined earlier.
• Mixing logo and revenue churn. Report both; they tell different stories.
• Relative colour scales. Anchor colour thresholds to fixed values.

Conclusion

Pivot billed MRR into a customer-by-month grid and compare each month with the previous one to classify new, churned, expanding and contracting customers. From those, report logo churn, an MRR bridge that is asserted to add up, and net revenue retention, and group customers by start month into a cohort retention triangle. Writing both to Excel with consistent number formats and a fixed colour scale gives a churn report that reads the same way every month.

Frequently asked questions

How do I calculate customer churn rate in pandas? Pivot billing data into customers by month, count customers billed last month but not this month, and divide by the number billed last month.

What is an MRR bridge? A reconciliation from opening to closing monthly recurring revenue through new, expansion, contraction and churned MRR; the movements must add up exactly.

What is net revenue retention? Revenue from customers present at the start of a period, after their expansions, contractions and cancellations, divided by their starting revenue; above 100 percent means existing customers grew on balance.

How do I build a cohort retention table in pandas? Find each customer's first billed month, group customers by it, and for each later month take the share of the group still billed.

Related

• Up one level: Building Finance and Operations Reports — finance reports from Python.
• Build a Monthly KPI Scorecard with Targets — putting churn alongside other KPIs.
• Calculate Customer Lifetime Value from Order History — the value side of retention.
• Build a Deferred Revenue Schedule — subscription revenue recognition.