Measure loyalty program customer value after rewards, fulfillment and administration costs. Compare equal-age member and nonmember cohorts, reconcile contribution to source exports, and separate observed differences from evidence of program-caused lift.
Customer lifetime value in a loyalty program should reflect contribution after rewards and program operating costs, measured across customers with the same follow-up period. Compare member and nonmember economics, but do not call their difference program-caused lift: customers who enroll may already have different purchasing habits.
Begin with a specific decision: continue the program, change a reward, or test an invitation to join. Each needs the same basic economics, but only a comparison designed around the intervention can support a claim about its effect.
Fader and Hardie's customer-base analysis tutorial distinguishes future customer lifetime value from historical profitability. A completed purchase window tells you what members contributed during that window. Extending it into lifetime value requires additional assumptions about future behavior and costs.
Use the general LTV calculation guide for that extension. First make the program's observed economics reliable enough to inform it.
Fix membership status at the start of the observation window. Keep customers who later stop purchasing in the original roster. Do not label someone a member from their first purchase if they actually enroll much later; that would assign pre-enrollment spending to the program.
Compare customers at equal relationship ages and record their earlier purchase history. A recently acquired nonmember should not be treated as equivalent to a long-standing member simply because both bought during the same calendar quarter.
Bring a membership roster, dated transactions, returns, product costs, reward fulfillment records and a program expense summary. Use a stable customer ID to match them. Prepare one row per customer covering the agreed observation period.
Keep net revenue after ordinary discounts and returns. For this example, rewards are separate merchandise whose fulfillment cost is recorded outside product costs; they are not discounts already deducted from revenue. If your rewards work differently, change the cost mapping before calculating.
| Data sheet column | Field | Treatment |
|---|---|---|
| A | Customer ID | One row for each original customer |
| B | Member group | Status fixed before follow-up begins |
| C | Original customer | 1 for every roster member |
| D | Active customer | 1 when a follow-up purchase occurs, otherwise 0 |
| E | Completed orders | Unique purchase orders in the window |
| F | Net revenue (USD) | After discounts and returns |
| G | Product costs (USD) | Matched to the sales |
| H | Variable service costs (USD) | Fulfillment, payment and support costs |
| I | Reward costs (USD) | Costs not already included elsewhere |
| J | Program operation (USD) | Incremental administration expense |
| K | Contribution (USD) | Revenue less the four cost fields |
In Excel, enter the customer contribution formula in K2 and fill down:
=F2-SUM(G2:J2)
For a summary group name in A2, total its contribution and divide by its original customer count:
=SUMIFS(Data!$K$2:$K$201,Data!$B$2:$B$201,A2)
=SUMIFS(Data!$K$2:$K$201,Data!$B$2:$B$201,A2)/SUMIFS(Data!$C$2:$C$201,Data!$B$2:$B$201,A2)
Microsoft's SUMIFS documentation explains how the function sums values matching specified criteria. Keep the ranges aligned and retain zero-purchase customers in the Data sheet. Covirage is not affiliated with Microsoft.
Finance should approve the economic cost schedule and reconcile it to the relevant source reports. Do not treat the face value of points, reward fulfillment spending and a loyalty accounting liability as interchangeable amounts. They answer different questions and need an explicit reconciliation rather than being added together.
An illustrative US specialty retailer follows existing customers for the same six-month window. Membership is known at the opening date, and both groups contain customers with an earlier purchase. All figures below come from their follow-up transactions and matched costs.
| Measure | Members | Nonmembers |
|---|---|---|
| Original customers | 100 | 100 |
| Customers who purchase | 80 | 60 |
| Completed orders | 320 | 180 |
| Net revenue (USD) | 32,000 | 18,000 |
| Product costs (USD) | 19,200 | 10,800 |
| Variable service costs (USD) | 3,200 | 1,800 |
| Reward costs (USD) | 1,600 | 0 |
| Program operation (USD) | 400 | 0 |
| Contribution (USD) | 7,600 | 5,400 |
| Contribution per original customer (USD) | 76.00 | 54.00 |
The member calculation is $32,000 - $19,200 - $3,200 - $1,600 - $400 = $7,600. The nonmember calculation is $18,000 - $10,800 - $1,800 = $5,400. Dividing each by its original 100 customers gives $76.00 and $54.00.
Members therefore show an observed contribution difference of $7,600 - $5,400 = $2,200, or $22.00 per original customer. Neither amount is an estimate of what the program caused.
The operating drivers help explain the observed difference. Member follow-up retention is 80 / 100 = 80.0%, compared with 60 / 100 = 60.0% for nonmembers. Orders per purchasing customer are 320 / 80 = 4.0 and 180 / 60 = 3.0. Both groups have net revenue per order of $100.00, calculated as 32,000 / 320 and 18,000 / 180.
Display percentages to one decimal and per-customer amounts to two decimals, while keeping unrounded calculations in the workbook. The customer profitability versus lifetime value comparison explains why this observed contribution result still needs a separate future-value interpretation.
Before increasing reward generosity, hold purchasing behavior and every other cost fixed. Suppose reward fulfillment cost rises from $1,600 to $4,000 for the member cohort. This is a sensitivity case, not a prediction of customer behavior.
| Member measure | Observed case (USD) | Higher reward cost case (USD) |
|---|---|---|
| Net revenue | 32,000 | 32,000 |
| Product costs | 19,200 | 19,200 |
| Variable service costs | 3,200 | 3,200 |
| Reward costs | 1,600 | 4,000 |
| Program operation | 400 | 400 |
| Contribution | 7,600 | 5,200 |
| Contribution per original customer | 76.00 | 52.00 |
The higher-cost case gives $32,000 - $19,200 - $3,200 - $4,000 - $400 = $5,200. The added reward cost is $4,000 - $1,600 = $2,400, exactly matching the contribution decline of $7,600 - $5,200.
Against the unchanged nonmember contribution of $5,400, the observed group difference would become $5,200 - $5,400 = -$200, or -$2.00 per original customer. This shows the reward budget the current purchasing pattern can support. It does not show whether more generous rewards would change that pattern.
Frequent shoppers may be more likely to join. Employees may invite high-spending customers first. Members and nonmembers may also differ in acquisition channel, tenure or product mix. Comparing their totals cannot remove those differences.
Where practical, randomly assign a program invitation or reward change among eligible customers before it happens. NIST's randomized design guidance describes random assignment of treatment levels to experimental units. For a business test, define the assignment groups in advance and retain everyone assigned, including customers who decline an invitation.
Compare net contribution per assigned customer over an equal window. Account for uncertainty before expanding the change, and check the next purchase cycle for orders merely brought forward. If random assignment is unavailable, describe the analysis as observational and state which earlier customer differences remain unresolved.
Finance owns cost definitions and reconciliation; the loyalty team owns eligibility, rewards and intervention records. Use a customer lifetime value dashboard to maintain the agreed measures after the test design and definitions are settled.
Bring a customer roster, transaction summary and reward cost export; Covirage maps the columns, and its tools compute and check cohort contribution after the supplied costs. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own data; creating the account takes about a minute.
See customer profitability for the wider customer contribution analysis.
They may show higher observed spending or contribution, but people who join can differ from nonmembers before the program begins. Compare prior purchasing behavior and customer age, then use an appropriate experiment or comparison design before attributing the difference to the program.
Include the economic cost of rewards, incremental fulfillment, customer service and program operation under a consistent policy. Avoid subtracting a redeemed discount again if net revenue already reflects it. Keep the statutory accounting reconciliation separate from the management contribution calculation.
Use the original eligible or enrolled cohort when comparing cohort economics, including customers who make no follow-up purchase. Contribution per active member can be useful operationally, but label it separately because its denominator excludes customers the program did not retain.
Historical contribution describes the observed period. Lifetime value looks beyond the measurement date and needs assumptions about future purchases, retention, costs and timing. Show the observed result first, then disclose the assumptions used to extend it into a future estimate.