Calculate Customer Lifetime Value from Order History

"How much is a customer worth?" decides how much a business can spend to win one. Customer lifetime value (CLV) answers it, and an order history in Excel contains everything needed for a first, honest estimate. The common mistakes are in the method, not the arithmetic: averaging the revenue of all customers to date mixes people who joined three years ago with people who joined last week; using revenue instead of margin overstates what a customer contributes; and a formula with an assumed customer lifetime can produce almost any number. This page builds CLV in layers with pandas — value to date, cohort curves that compare customers at the same age, and a simple formula whose inputs are measured from the data — then compares channels and writes a summary that states its assumptions. It belongs to Creating Pivot Tables from Excel Data.

Prerequisites

pip install pandas openpyxl

orders.xlsx holds about 1,500 customers' orders from January 2024 to September 2026: order ID, customer, date, the channel through which the customer was acquired, revenue and gross margin. Customers joined throughout the period, place repeat orders at irregular intervals, and eventually stop — at different rates depending on the channel.

Step 1 — Value to date, and why it is not lifetime value

Start with what each customer has actually spent so far:

import pandas as pd

orders = pd.read_excel("orders.xlsx")
AS_OF = pd.Timestamp("2026-09-30")
cust = orders.groupby("Customer").agg(
    first=("Date", "min"), last=("Date", "max"), orders=("Order", "count"),
    revenue=("Revenue", "sum"), margin=("Margin", "sum"), channel=("Channel", "first"))
cust["months_known"] = ((AS_OF - cust["first"]).dt.days / 30.44).round(1)
print(cust[["orders", "revenue", "margin", "months_known"]].describe().round(1).loc[["mean", "50%", "max"]])
print(cust.groupby(pd.cut(cust["months_known"], [0, 6, 12, 24, 36]), observed=True)["margin"].mean().round(2))

The average margin per customer to date looks like a CLV but is not one: it mixes customers who have been around for nearly three years with customers who placed their first order last month. The second table makes the problem plain — customers known for 0–6 months have far less margin to date than those known for 24–36 months, simply because they have had less time to buy. Any average across them depends mostly on when customers were acquired, not on how valuable they are. Margin rather than revenue is used throughout, because a customer is worth what they contribute after the cost of the goods they buy.

Step 2 — Cohort curves: compare customers at the same age

The fair comparison is by customer age: cumulative margin per customer after 1, 3, 6, 12 … months, for each group of customers who joined in the same quarter:

orders = orders.merge(cust[["first"]], left_on="Customer", right_index=True)
orders["age_m"] = ((orders["Date"] - orders["first"]).dt.days // 30.44).astype(int)
orders["cohort"] = orders["first"].dt.to_period("Q").astype(str)

size = cust.groupby(cust["first"].dt.to_period("Q").astype(str)).size()
monthly = orders.pivot_table(index="cohort", columns="age_m", values="Margin", aggfunc="sum", fill_value=0)
cum_per_cust = monthly.cumsum(axis=1).div(size, axis=0)

ages_known = ((AS_OF - pd.PeriodIndex(cum_per_cust.index, freq="Q").start_time).days / 30.44).astype(int)
for i, cohort in enumerate(cum_per_cust.index):
    cum_per_cust.loc[cohort, cum_per_cust.columns > ages_known[i]] = float("nan")
print(cum_per_cust[[0, 2, 5, 11, 23]].round(1).to_string())

Each row is a quarterly cohort; each column is months since the customers' first order; each cell is cumulative margin per customer of that cohort by that age. Cells beyond the age a cohort has reached are blanked — the 2026 cohorts simply have no 12-month value yet, rather than a misleadingly low one. The last visible cell of a young cohort can still be partial (2026-Q2's month-5 value, 94, is lower than other cohorts' because not all of its customers have reached month 5 yet), so treat each cohort's final cell with caution. Across complete cells the cohorts are remarkably similar: about 42 in margin per customer in the first month, about 100 by month six, and 140–150 by month twelve. Reading down a column compares cohorts at the same age, so a genuine improvement or decline in customer value is visible. Reading along a row shows the shape of the value curve: steep early, then flattening as customers stop buying. The same idea, applied to retention rather than value, is in Build a Cohort Retention Table from Excel Data.

Step 3 — A simple CLV formula with measured inputs

For a single planning number, the classic formula multiplies average margin per order, orders per year and expected years as a customer. Measure each input from the data rather than assuming it:

months_active = ((cust["last"] - cust["first"]).dt.days / 30.44).clip(lower=1)
per_order_margin = cust["margin"].sum() / cust["orders"].sum()
orders_per_year = (cust["orders"].sum() / (months_active.sum() / 12))

recent = cust["last"] >= AS_OF - pd.DateOffset(months=6)
known_a_year = cust["first"] <= AS_OF - pd.DateOffset(months=18)
churn_rate = 1 - recent[known_a_year].mean()                 # share of established customers gone quiet
annual_churn = churn_rate
expected_years = 1 / annual_churn

clv = per_order_margin * orders_per_year * expected_years
print(f"margin per order {per_order_margin:.2f} | orders per active year {orders_per_year:.2f} | "
      f"annual churn {annual_churn:.0%} | expected years {expected_years:.1f} | CLV ≈ {clv:,.0f}")

Average margin per order and order frequency come straight from the orders. Churn is the hard input: here a customer known for at least 18 months who has not ordered in the last 6 is treated as lost, and the share of such customers among established ones is used as an annual churn rate; expected lifetime is its inverse. Each of those choices — the 6-month inactivity rule, the 18-month minimum, treating the share as annual — is an assumption that moves the result, and should be written down next to it. Now compare the answer with the cohort curves from Step 2. The formula says about 354 in margin per customer; the oldest cohorts reached 176–195 after two years, and their curves are flattening. The formula is clearly too optimistic, and the reason is visible in its inputs: "orders per active year" comes out at 9.4 because customers who ordered once or twice within a few weeks have a very short active span, which inflates their implied yearly frequency. Measuring frequency only over customers known for at least a year, or replacing the formula with the cohorts' observed 24-month value plus a modest tail, gives a far more defensible figure. This is the general lesson: the formula is quick, but the cohort curves are the evidence, and the formula should never be quoted without being checked against them.

Step 4 — Compare acquisition channels

CLV is most useful as a comparison — which channel brings the customers worth having. Compute value at a fixed age per channel, which avoids the age problem entirely:

def value_at_age(group, months=12):
    eligible = group[group["first"] <= AS_OF - pd.DateOffset(months=months)]
    o = orders[orders["Customer"].isin(eligible.index) & (orders["age_m"] < months)]
    return pd.Series({"customers": len(eligible), f"margin_{months}m": round(o["Margin"].sum() / len(eligible), 2)})

by_channel = cust.groupby("channel").apply(value_at_age, include_groups=False)
by_channel["orders_per_customer"] = cust.groupby("channel")["orders"].mean().round(2)
print(by_channel.to_string())

Twelve-month margin per customer, counted only for customers acquired at least twelve months ago, compares like with like: every customer in the figure has had exactly a year. Referral customers come out well ahead and Social customers behind, which matches what the cohort curves suggest. Set against the cost of acquiring a customer in each channel, this is the number that justifies — or not — the marketing spend. A fixed-age figure such as "12-month value" is also easier to defend than a lifetime estimate, because it needs no churn assumption.

Step 5 — Write a summary that shows its assumptions

The report should give the figures and the assumptions behind them, so readers can judge them:

summary = pd.DataFrame({
    "Figure": ["Margin per order", "Orders per active year", "Annual churn (assumed rule)", "Expected years", "Simple CLV (margin)"],
    "Value": [round(per_order_margin, 2), round(orders_per_year, 2), round(annual_churn, 3), round(expected_years, 2), round(clv, 2)],
    "How it was measured": [
        "total margin ÷ total orders", "orders ÷ active customer-years",
        "share of customers known ≥18 months with no order in the last 6", "1 ÷ annual churn", "product of the above"],
})
with pd.ExcelWriter("customer_value.xlsx", engine="openpyxl") as xw:
    summary.to_excel(xw, sheet_name="Summary", index=False)
    by_channel.to_excel(xw, sheet_name="By channel")
    cum_per_cust.round(2).to_excel(xw, sheet_name="Cohort curves")
    cust.to_excel(xw, sheet_name="Customers")
    xw.sheets["Summary"].column_dimensions["A"].width = 30
    xw.sheets["Summary"].column_dimensions["C"].width = 62
print(summary.to_string(index=False))

The Summary sheet lists each input with how it was measured; the By channel sheet carries the fixed-age comparison, the Cohort curves sheet the full picture, and the Customers sheet the per-customer detail for anyone who wants to segment further. A CLV figure without its assumptions invites misuse; with them, a reader can see at once that a different churn rule would change the answer. For a more sophisticated model, probabilistic approaches (BG/NBD and Gamma-Gamma, available in the lifetimes and pymc-marketing packages) estimate each customer's future purchases — worth it when decisions depend on individual predictions rather than averages.

Common pitfalls

• Averaging value to date across all customers. New customers drag it down; compare at the same age.
• Using revenue. Customer value is margin; revenue overstates it.
• An assumed customer lifetime. Measure churn and state the rule used.
• Showing incomplete cohort cells as low values. Blank ages a cohort has not reached.
• A single CLV for everyone. Compare channels or segments; that is where the decisions are.

Conclusion

Customer lifetime value from order history is best built in layers. Value to date per customer is a starting point but mixes customers of different ages; cohort curves of cumulative margin by months since first order compare like with like and show how value builds and flattens. A simple formula — margin per order × orders per year × 1 ÷ annual churn — gives a single planning figure if its inputs are measured and its churn rule stated. Compare channels at a fixed age, such as 12-month margin, and write the assumptions into the report next to the numbers.

Frequently asked questions

How do I calculate customer lifetime value in pandas? Measure average margin per order, orders per active year and an annual churn rate from the order history, and multiply margin per order by orders per year by one divided by churn. Check the result against cohort curves of actual value.

Why shouldn't I just average total spend per customer? Customers acquired recently have had less time to buy, so the average depends on when customers joined. Compare customers at the same age since their first order instead.

How do I compare customer value between acquisition channels? Compute margin per customer within a fixed period after the first order, for example 12 months, counting only customers acquired at least that long ago.

Should CLV use revenue or margin? Margin. A customer is worth the contribution they make after the cost of the goods or services they buy.

Related

• Up one level: Creating Pivot Tables from Excel Data — summaries and crosstabs.
• Build an RFM Customer Segmentation from Excel Data — segmenting the same customers.
• Build a Customer Churn Report from Subscription Data — measuring churn properly.
• Build a Product Margin Report — where the margin figures come from.