· 13 min read

Cohort Analysis for Ecommerce: A D2C How-To (2026)

Cohort analysis for D2C brands, step by step. Build a first-order cohort grid from your Shopify export in Google Sheets, then read it like an operator.

Cohort analysis for ecommerce, a D2C how-to that builds the cohort grid from a Shopify order export in Google Sheets

Updated October 2026.

Cohort analysis groups customers by when they first bought, then tracks what each group does in the months after. For a D2C brand, the most useful version is an order-level grid built from your Shopify export, with one row per first-order month and one column per month since that first order. It shows how many people come back, when they come back, and where they stop.

I've built and read these grids for D2C brands as a retention agency, and as the person leading ecommerce and CRM at HealthifyStore. This guide is the method I use, in the order I use it.

What is cohort analysis?

A cohort is a group of customers who share a starting point. In ecommerce that starting point is almost always the month of their first order. Everyone who first bought in January is the January cohort, and they stay in it forever.

Cohort analysis then asks one question of each group. Of the people who started here, what share came back in month 1, month 2, month 3 and so on?

This beats a single blended retention rate because the blend mixes old loyal customers with last week's first-timers. A brand can look stable on a blended number while every new cohort behaves worse than the last. The grid separates them, so you can see it happening.

Most guides on page one explain this for apps and SaaS, where the event is a login. For a D2C brand, the event that matters is an order. So the grid below counts orders, not sessions.

Start with total audience versus reachable audience

Before I build any grid, I check one thing. How many of these customers can we actually reach?

If your messages reach 20 of every 100 customers, a cohort of 10,000 is effectively a cohort of 2,000 for anything CRM does. I check reachability channel by channel. Is WhatsApp delivering? Is email connected to a sending provider at all? Are SMS and RCS set up? I've written up the WhatsApp side of this in how to fix your WhatsApp delivery rate.

Here is why it matters in rupees. Say a brand's January cohort has 10,000 customers, a second order is worth ₹1,200, and 5% of the people a journey reaches go on to buy.

Audience People reached Second orders at 5% Revenue
Total audience 10,000 500 ₹6,00,000
Reachable (20%) 2,000 100 ₹1,20,000

The plan built on the total audience is five times bigger than what the channel can deliver. Fix the pipe first, then read the cohorts.

How to do cohort analysis in Google Sheets from a Shopify export

This is the part most guides skip. They show a finished grid, or they walk through GA4, which counts website users rather than every order. Your Shopify order export already holds every order, so build from that.

Step 1. Export and clean the orders

  1. In Shopify admin, export all orders as a CSV and import it into a new Google Sheet.
  2. Keep five columns. The order number (the Name column), Email, Created at, Financial Status and Total.
  3. Shopify writes one row per product, so an order with three products appears three times. Use Data, then Data cleanup, then Remove duplicates on the order number.
  4. Filter out refunded, voided and test orders using Financial Status.
  5. Sort the sheet by Created at, oldest first.

If many customers check out without an email, use the phone number as the customer ID instead. The point is one stable ID per person.

Step 2. Add five helper columns

Assume Email sits in column B and Created at in column C, with data from row 2.

Column What it holds Formula in row 2
D. Order date The date alone =DATEVALUE(LEFT(C2,10))
E. First order date That customer's earliest order =MINIFS($D:$D,$B:$B,B2)
F. Cohort month First day of the first-order month =EOMONTH(E2,-1)+1
G. Month number Months since the first order =(YEAR(D2)-YEAR(E2))*12+MONTH(D2)-MONTH(E2)
H. Order number 1st, 2nd, 3rd order for that customer =COUNTIF($B$2:B2,B2)

Column H only works because you sorted by date in Step 1. Column G uses calendar months, so a customer who first bought on 30 January and again on 2 February lands in month 1. That's fine for a monthly grid. If you want strict 30-day windows, use =INT((D2-E2)/30) instead.

Step 3. Build the grid

On a new tab, put cohort months down column A and month numbers 0, 1, 2, 3 across row 1. Then type this formula in B2.

=COUNTUNIQUEIFS(Orders!$B:$B,Orders!$F:$F,$A2,Orders!$G:$G,B$1)

Copy it across and down. Each cell now counts the distinct customers from that cohort who ordered in that month. Month 0 is the cohort size.

If you prefer a pivot table, put Cohort month in Rows, Month number in Columns, and Email in Values summarised by COUNTUNIQUE. It gives the same counts.

Step 4. Turn counts into percentages

Make a second grid where each cell divides by the month 0 count for that row, for example =C2/$B2. That's your retention grid.

Here is what a finished grid looks like. The numbers are made up for illustration.

Cohort (customers) Month 1 Month 2 Month 3
Jan (1,000) 9% 5% 4%
Feb (1,200) 8% 5% 4%
Mar (900) 12% 7% 6%
Apr (1,100) 11% 6% Not yet

Step 5. Add revenue per customer

Counts tell you who came back. Revenue tells you whether it paid. Add a grid using SUMIFS on the Total column instead of COUNTUNIQUEIFS, then make it cumulative and divide by the cohort size.

Cumulative revenue per customer = all revenue from the cohort so far ÷ customers in the cohort.

Say the January cohort is 1,000 customers whose first orders came to ₹15,00,000. By month 3, repeat orders have added ₹3,00,000. Cumulative revenue per customer is ₹18,00,000 ÷ 1,000, or ₹1,800, against ₹1,500 on day one. Put that number into the customer lifetime value calculator and compare it with what you paid to acquire the cohort. The LTV to CAC ratio guide covers that comparison.

How to read a cohort retention table

I read a grid in three passes.

Month 1 first. Month 1 is the share of a cohort that came back in the month after their first order. It's the most important column because it's where most second orders happen. At HealthifyStore, month-1 return was a small slice of each monthly cohort, and each cohort then thinned further month by month. The median gap to a second order was about 24 days, and most second orders came within 30 days. The first month is the window. If your post-purchase messages start in week three, you're late. My post-purchase flow guide covers what to send in that window.

Then where it flattens. Read along each row. The curve drops fast and then levels off. That flat stretch is your retention curve's floor, the customers who have made you a habit. In the illustration above, January flattens at about 4% to 5%. A cohort that flattens higher is worth more than one that starts higher and keeps falling.

Then down the columns. Read month 1 from top to bottom. Is each new cohort better or worse than the one before? In the illustration, March jumps. That's the moment to ask what changed, and to remember that the grid can't answer it alone (more on that below).

Split by order number and the gap between orders

The monthly grid tells you when people come back. The next cut tells you where they get stuck. I split customers by order number and by the gap between orders. Order 1, the gap to order 2, order 2, the gap to order 3.

Column H from Step 2 gives you the order number. For the gap, add a column with each customer's previous order date (=MAXIFS($D:$D,$B:$B,B2,$D:$D,"<"&D2)) and subtract it from the order date. Then take the median gap for second orders and for third orders.

In one D2C brand I worked with, the picture looked like this.

Step Share who made it Typical gap
Order 1 to order 2 About 16% About 40 days
Order 2 to order 3 About 24% About 28 days

About 84% of customers were stuck at order one. But the people who did make a second order were far more likely to make a third, and they came back faster. So the job was clear. If we cracked order one to two, they would move to order three on their own.

Say a brand with the same rates gets 1,000 first-time buyers a month at an order value of ₹1,500. At 16%, that's 160 second orders. Lift it to 20% and you get 200, which is 40 more orders and ₹60,000 more a month from one cohort. At a 24% onward rate, about 10 of those 40 become third orders too, another ₹15,000. Check your own starting point with the repeat purchase rate calculator, and see how it compares in repeat purchase rate benchmarks.

Acquisition cohorts vs behavioural cohorts

Everything above uses acquisition cohorts, grouped by when people first bought. They answer "is retention getting better or worse over time".

Behavioural cohorts group people by what they did. The first product they bought, the channel they came from, whether they used a discount. These answer "what should we send them next".

The first product is the cut I use most. At HealthifyStore, the smart scale brought in a large share of first orders, but it's a one-time purchase. Nobody needs two. Its buyers came back roughly a ninth as often as people whose first order was protein. Grouping by first product made that obvious, and it changed what we pushed as the entry product.

At the D2C brand above, the method went like this.

  1. Map each entry product and what it leads to. Go by the order data, not by what the team assumes.
  2. Cohort buyers by what they bought first.
  3. Send each cohort the next product its own data points to.
  4. Automate it as journeys, so it runs without a weekly broadcast.

The data showed small add-ons first. Pan buyers most often added a chopping board and spatulas next, and only then a dosa tawa. So the first follow-up offered the small add-on, not the next big piece. We set up those journeys around that order. The thinking behind it is in my guide to replenishment and cross-sell, and the scoring side in RFM analysis.

What should you send each cohort in months 0 to 3?

Send each cohort what its grid says it's missing. That means a good first experience in month 0, the second order in month 1, the next product in month 2, and a win-back in month 3 for anyone who hasn't returned. Set the timing from your own gap data, not from a fixed calendar.

Month 0 is the month of the first order, so one customer's month 0 can last a day and another's thirty. That's why I'd trigger messages on days since the first order and use the grid only to check whether the timing worked.

Cohort month Who's in it The job What to send
Month 0 Just placed a first order Make the first order go well Delivery updates, how to use the product, one tip that leads to the next use. No discount yet
Month 1 No second order yet Win the second order inside the usual gap The product their first order points to, a few days before your median gap
Month 2 Some on two orders, most still on one Move repeat buyers to a third order, give one-order buyers a new reason The follow-on product for repeat buyers, a different entry product or a review request for the rest
Month 3 Not back since month 1 Win them back before they forget you One clear win-back offer, then move them to a lower-frequency list

The gaps earlier in this guide show why the calendar differs by brand. At HealthifyStore, second orders typically came about 24 days after the first, so the month-1 nudge had to land well inside the first 30 days. At the D2C brand above, the second order took about 40 days and the third about 28, so the second-order push sat later and the third-order nudge came sooner. Same grid, different timings.

Two rules hold in every month. Each message stops the moment the customer orders, and each journey keeps a holdout, so when the next cohort's month 1 rises you know whether the messages did it. The messages themselves are in my guides to post-purchase flows and WhatsApp win-back messages.

If you run an app, the same grid works with a first core action in place of a first order, and weekly cohorts in place of monthly ones. I cover the first week in app retention for new users, and how to judge daily use in DAU/MAU ratio benchmarks.

What cohort analysis can't tell you

Cohorts show you what happened. They don't prove why.

Say you launch a post-purchase journey in March and the March cohort's month 1 jumps. The journey might have done it. So might a sale, a different mix of entry products, a new ad audience or the season. The grid can't separate those. To know what your messages caused, hold back a random slice of customers who get nothing and compare. I explain how in global control groups.

Watch blended ratios too. At HealthifyStore, the share of orders coming from returning customers rose steadily. That looks like a win. A big part of it came from new-customer orders falling. A rising repeat share can hide a shrinking business, which is exactly why I read the cohort grid and the new-customer count side by side, and why I don't trust a single retention figure on its own. The customer retention rate formula post covers the same trap from the formula side.

Where I fit

Most brands can build this grid in an afternoon. The hard part is what comes after, choosing which step to fix, deciding the next product for each cohort and turning it into journeys that run every day. That's the job of a retention agency, and it's what Compound Lab does. Book a 30-minute call and we'll read your cohort grid together and find your order-one leak.

FAQ

What is cohort analysis? Cohort analysis groups customers by a shared starting point, usually the month of their first order, and tracks what share of each group comes back in each month after. It shows whether retention is improving across newer customers, which a single blended retention rate hides.

How do I perform cohort analysis in Excel? Export your orders, find each customer's first order date with MINIFS, convert it to a cohort month, and calculate the months between each order and the first order. Then build a pivot table with cohort month in rows, month number in columns and distinct customers in values. In Excel, tick "Add this data to the Data Model" when creating the pivot to get a Distinct Count option, then divide each row by its month 0 count.

What is an example of a cohort analysis? A D2C brand groups everyone who first bought in January, February and March, then counts what share of each group ordered again in month 1, month 2 and month 3. If the March group returns more in month 1 than January did, something improved for newer customers, and the brand then tests what caused it.

What is the difference between cohort analysis and segmentation? A cohort is defined by a starting point in time and is tracked over time, such as everyone who first bought in January. A segment is any group defined by a shared trait right now, such as customers who spent over ₹5,000 or bought twice. Cohorts show how behaviour changes over time, and segments decide who gets which message today.

How do you read a cohort table? Read month 1 first, because most second orders happen in the first month. Then read along each row to see where the curve flattens, and down each column to see whether newer cohorts are doing better or worse than older ones.

How do you do cohort analysis in SQL? Find each customer's first order month with MIN of the order date grouped by customer, join it back to the orders table, and calculate the month difference between each order and that first month. Then count distinct customers grouped by cohort month and month number, and divide by the month 0 count. If you want help reading the result, a 30-minute call with Compound Lab covers it.

What is cohort 1 and cohort 2? They're simply two groups of customers you compare. In ecommerce, cohort 1 is usually everyone who placed a first order in one month and cohort 2 everyone from the next month, so you can see whether newer customers come back faster or slower.


Built your cohort grid and not sure which step to fix first? Book a 30-minute call.

Next postWhat Is Retention Marketing? A D2C Guide (2026)

Your customers already paid once. Make them pay again.

Book a Consultation