Cohort analysis for ecommerce: repeat orders and margin

Cohort analysis for ecommerce groups customers by their first purchase and compares repeat buying and contribution at the same age. Keep each cohort's original membership fixed and show refunds alongside orders: a customer who buys again can still lose money for the company.

Start with orders and a fixed definition

Use one row per paid, non-test order, excluding cancellations before fulfillment. Aggregate line items and refund transactions to the order before combining them; otherwise the original sale can appear repeatedly. Bring customer IDs, order dates, sales after discounts but before refunds, refund amounts, product costs, and variable fulfillment, payment, and return costs.

For this report, a cohort is the calendar month of first purchase. Age zero is that month; age one is the next calendar month. Monthly repeat purchasing means an original customer placed another qualifying order during age one, including an order later refunded. It measures buying behavior; contribution measures the economics.

Repeat buyer rate at age one = Distinct original customers ordering at age one / Original cohort customers

Order contribution = Sales after discounts - Refunds - Net product cost - Variable order costs

These are order economics, not subscription renewal measures. The Excel retention-table guide covers the broader table layout; this method adds the costs and refunds needed for ecommerce decisions.

Work from a small order export

Consider a US home-goods retailer with the following complete order history for six new customers through March 31, 2026. Put the headers in row one of an Excel sheet named Orders, with columns A:G in the order shown. Store dates as actual spreadsheet dates.

Refunds here include only amounts recorded within 30 days of each order. The extract was checked on April 30, 2026, so every displayed order has that observation window. Apply the same return lag to both cohorts. Sales exclude sales tax; product cost is net of inventory recovered from returns. Variable costs include retained fees and return handling, with no cost counted twice.

J06 was fully refunded and its merchandise recovered. Its product cost is therefore zero, but $18 of variable costs remain. Its contribution is 100 - 100 - 0 - 18 = -$18. Do not delete this order to make the cohort look healthier.

Assign membership and calculate each order

Add helper columns H:N. Enter the following formulas in row two and fill down through row eleven:

H2: =MINIFS($C$2:$C$11,$B$2:$B$11,B2)
I2: =DATE(YEAR(H2),MONTH(H2),1)
J2: =12*(YEAR(C2)-YEAR(H2))+MONTH(C2)-MONTH(H2)
K2: =D2-E2-F2-G2
L2: =--(COUNTIFS($B$2:B2,B2)=1)
M2: =--(COUNTIFS($B$2:B2,B2,$J$2:J2,J2)=1)
N2: =D2-E2

H is first purchase date, I cohort month, J age, K contribution, L first occurrence of the customer, M first occurrence of that customer at that age, and N net sales. Microsoft documents MINIFS as returning the minimum meeting specified criteria; here it finds each customer's earliest order date. In a real export, verify that date against complete purchase history before filtering the analysis period.

L counts each customer once for membership. M counts each customer once per age, even if they place several orders. COUNTIFS tests multiple criteria together, which keeps customer A's two February orders from becoming two returning buyers.

Summarize customers and contribution at equal ages

On a sheet named Cohorts, put January 1, 2026, in A2 and February 1, 2026, in A3. Use the following headers and formulas, filling row two down to row three:

B2: =SUMIFS(Orders!$L$2:$L$11,Orders!$I$2:$I$11,A2)
C2: =SUMIFS(Orders!$M$2:$M$11,Orders!$I$2:$I$11,A2,Orders!$J$2:$J$11,1)
D2: =COUNTIFS(Orders!$I$2:$I$11,A2,Orders!$J$2:$J$11,1)
E2: =C2/B2
F2: =SUMIFS(Orders!$E$2:$E$11,Orders!$I$2:$I$11,A2,Orders!$J$2:$J$11,"<=1")
G2: =SUMIFS(Orders!$N$2:$N$11,Orders!$I$2:$I$11,A2,Orders!$J$2:$J$11,"<=1")
H2: =SUMIFS(Orders!$K$2:$K$11,Orders!$I$2:$I$11,A2,Orders!$J$2:$J$11,"<=1")
I2: =H2/B2

SUMIFS adds values satisfying the chosen conditions. Here the conditions select one cohort and, for cumulative dollars, ages zero through one. Reject negative ages as a data error before summarizing.

Percentages round to one decimal and dollars per buyer to cents; calculations retain full precision. January's rate is 2 / 3 = 66.7%, while February's is 1 / 3 = 33.3%. Yet February produces $75.00 per original buyer against January's $62.33. More repeat buying did not produce more contribution.

Compare completed ages only. If February's age-one month were incomplete, leave its result blank. For the meaning of those blanks and the cohort diagonal, use how to read a cohort retention table.

Prove the totals before choosing an action

The order rows reconcile as follows:

The proof is 1,000 - 120 - 360 - 108 = $412, also 187 + 225. Original membership totals 3 + 3 = 6 distinct customers. Tie sales, refunds, and costs back to the corresponding export totals under the same exclusions and refund cutoff.

Finance owns the cost definition and reconciliation; ecommerce operations owns return reasons; the retention team owns the repeat-purchase action. Investigate January's refunded purchases before expanding its offer. Acquisition spending is excluded here, so use ecommerce ROI analysis before turning this contribution comparison into an advertising return claim. Channel grouping belongs in marketing cohort analysis.

Where it goes wrong

• Changing membership after returns. Removing refunded buyers improves the denominator after seeing the outcome. Keep membership fixed and expose the refund dollars.
• Counting orders as people. January has three age-one orders but two returning customers. Use customer-and-age deduplication for retention.
• Different observation windows. An older cohort has more time to buy and return goods. Match completed ages and return lags, and label unresolved returns as provisional.
• Incomplete costs or repeated rows. Line items combined with several refunds can multiply sales; recovered inventory can also be credited twice. Reconcile one order at a time before grouping.
• Overreading small cohorts. These six customers demonstrate the method. They do not establish that a campaign caused better retention or justify a spending change without broader evidence.

Covirage is not affiliated with Microsoft.

Compare your own ecommerce cohorts in Covirage

Bring an order export with customer IDs, dates, refunds, and costs; Covirage maps the columns, and its tools compute and check cohort retention and contribution. 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.

See customer profitability for the business context behind the comparison.