How to do a customer cohort analysis for an ecommerce store

Most store owners know their overall repeat-purchase rate. Far fewer know whether the customers they acquired last quarter are behaving better or worse than those acquired a year earlier. That gap matters, because a blended number can look steady while the business underneath it quietly deteriorates. A customer cohort analysis is the simplest way to see what is actually happening to retention, and it needs nothing more than an order export and a spreadsheet.

What is a cohort analysis in ecommerce?

A cohort is a group of customers who share a starting point. In ecommerce, the most useful starting point is the month of a customer's first order: everyone who first bought in March is the March cohort, and so on. A cohort analysis follows each group forward and asks the same questions at every step: what share ordered again, and how much have they spent in total?

The output is a cohort table. Each row is a cohort, each column is the number of months since the first order, and each cell holds a percentage or a dollar figure. Retention becomes a shape rather than a single number, which is where the useful insight lives.

Why does cohort analysis matter more than your overall repeat rate?

A blended repeat rate mixes customers acquired at different times, through different channels and on different offers. That creates three blind spots.

• Growth dilutes it. When a store acquires many new customers quickly, the blended rate falls simply because they have not had time to return.
• It hides slipping acquisition quality. If a new campaign or deep first-order discount brings in buyers who rarely return, the damage is spread across the whole base and takes months to surface. In a cohort table it shows up as soon as that cohort's first few months of data arrive.
• It cannot tell you payback. An average return rate does not tell you when the cumulative margin from a customer exceeds what you paid to acquire them. Cohorts answer that directly.

This is why cohort curves are the evidence underneath any credible customer lifetime value calculation. Lifetime value is a single number; the cohort table shows the curve it comes from, and whether that curve is improving.

What data do you need to run a cohort analysis?

Less than most people expect. The minimum is an order-level export with three fields: a customer identifier (customer ID, or email for guest checkouts), the order date and the order value. To make it genuinely useful, add the first order's acquisition channel or UTM source, any discount code used, the product or category bought first, and refunds so you can net them out.

As a rough guide, you want at least six months of order history to see a first pattern and twelve or more to see seasonality. Cohorts of only a few dozen customers produce noisy percentages, so smaller stores are often better off grouping by quarter. Some platforms include a basic cohort report, but building your own gives you the channel and discount cuts that built-in reports often lack.

How do you build a cohort table step by step?

Excel or Google Sheets is enough.

1. Clean the export. Remove test, cancelled and fully refunded orders. Express order values excluding GST and net of partial refunds.
2. Normalise the customer key. If you rely on email, convert it to lowercase and trim spaces. Some people will still appear under two addresses, which slightly understates retention. Accept it and stay consistent.
3. Find each customer's first order date. A MINIFS formula against the customer key does this. That month is the customer's cohort.
4. Calculate the period number for every order. This is the number of whole months between the cohort month and the order month: 0 for the first month, 1 for the next, and so on.
5. Pivot. Rows are cohort months, columns are period numbers, and values are distinct customers who ordered in that period. A second pivot sums net revenue.
6. Convert to rates. Divide each retention cell by the cohort's size. Divide running revenue totals by cohort size to get cumulative revenue per customer.
7. Add a colour scale. Conditional formatting turns the table into a heat map so patterns stand out.

The result looks something like this. The figures are purely illustrative, to show the shape, and are not benchmarks for any category.

Cohort	Customers	Month 1	Month 2	Month 3	Month 6
Cohort A	420	14%	9%	8%	6%
Cohort B	510	12%	8%	7%	5%
Cohort C	760	8%	5%	4%	-
Cohort D	480	13%	-	-	-

Younger cohorts have fewer columns filled because the time has not yet passed. Cohort C was the largest but retained noticeably worse, exactly the signal a blended repeat rate buries.

How do you read a cohort table?

Read it in three directions. Down a column compares cohorts at the same age, your clearest read on whether acquisition and the first-order experience are improving. Along a row shows one cohort's decay curve, and where it flattens marks your loyal core. Along a diagonal shows calendar effects: a sale or large email send lights up the same calendar month across every cohort at once. The patterns below cover most of what you will see.

Pattern	What it usually means	What to check next
Steep drop after the first month that never flattens	The product is bought once, or the first experience disappoints	Reviews, return reasons, delivery experience, post-purchase emails
Recent cohorts retain worse than older ones at the same age	Acquisition quality has slipped, often from a new channel or heavier discounting	Cut the same cohorts by channel and first-order discount
Curve flattens at a stable floor	A genuine loyal base exists and lifetime value is real	Calculate payback and protect that segment
A diagonal stripe of higher values	A calendar event, such as an EOFY or Black Friday sale, reactivated every cohort at once	Separate promotion-driven reorders from organic ones
Revenue per customer rising while retention is flat	Fewer customers return, but those who do spend more	Repeat order value, bundles, subscription uptake

Which cohort cuts are worth running?

Cohorts by first-order month are the starting point. The real decisions come from splitting them by how customers arrived. Four cuts tend to earn their keep:

• Acquisition channel. Compare first orders from paid social, paid search, organic, email and referral. A channel with higher upfront cost can still be your best if its customers return, which should shape how you weigh customer acquisition cost by channel.
• First-order discount versus full price. Deep welcome offers sometimes attract buyers who never pay full price. The cohort view tells you whether that is true for your store.
• First product or category. Some products lead to a second order; others are dead ends. That should influence what you advertise to new customers.
• Metro versus regional. Delivery times and shipping costs can differ sharply outside the capital cities, which can show up as weaker regional retention.

Treat each cut as a strong hint that deserves a test, not as proof of cause.

How do you use cohorts to work out CAC payback?

This is where cohort analysis becomes commercially decisive. Replace revenue with contribution margin (revenue less product cost, freight, payment fees and packaging) and plot cumulative margin per customer for each cohort. Then draw a horizontal line at what you paid to acquire those customers.

The month the curve crosses the line is your payback period. Suppose, purely as an illustration, a cohort cost $45 per customer to acquire and produced $28 of margin per customer on the first order, $39 by month three and $47 by month six. It pays back at around month six, and everything after that funds further growth. If a curve flattens below the line, you are buying customers at a loss, however healthy the revenue looks. This number decides how hard you can push ad spend, and it sits at the centre of the growth and data work we do with ecommerce businesses.

What mistakes make cohort analysis misleading?

• Judging young cohorts too early. Only compare cohorts at the same age. A two-month-old cohort cannot be judged on six-month retention.
• Using gross revenue. Revenue that includes GST and ignores refunds flatters every cohort. Use net revenue at minimum, and margin where you can.
• Ignoring seasonality. Customers acquired during a major sale often behave differently from those acquired in a quiet month. Where you can, compare a cohort with the same month a year earlier.
• Over-reading small cohorts. A swing from 10% to 15% in a cohort of 40 customers is two people. Group by quarter until cohorts are large enough to trust.

How often should you review your cohorts?

Update the table monthly as each new column of data arrives, and review it alongside your KPI dashboard. Each quarter, run the channel, discount and first-product cuts and recheck payback. Once the process is stable, it is a good candidate for automation so nobody rebuilds it by hand.

The point is not the table. It is the habit of asking, every month, whether the customers you acquire now are worth as much as the ones before, and acting on the answer. Pair it with the tactics in our guide to increasing repeat customers and you can see whether they are working rather than guessing. If you would like help building a cohort model from your own order data, book a consultation and we will work through it with you.