How to Calculate Customer Retention Rate from an Order Export

The retention rate formula, the three rates people mix up, a cohort table in Excel or Sheets, and a real report on 36,969 orders with its SQL.

Founder, Glow Reports. , 4 min read
  • Retention
  • Excel
  • Report Examples
Order receipts sorted into monthly cohort folders, with fewer receipts in each later month
On this page
  1. How do you calculate customer retention rate?
  2. Which retention rate do you need?
  3. How do you build a cohort retention table in Excel or Google Sheets?
  4. What does a real cohort report show?
  5. Why does the first cohort look so much better?
  6. What else does the table tell you?
  7. Where does Glow Reports fit?
  8. Common questions

Customer retention rate is the share of customers who come back. The formula takes three numbers, but "retention" hides three different measures, and the one you want from an order export is usually cohort retention. Of the customers who first bought in a given month, how many ordered again 1, 2 or 12 months later? Below: the formula, the three measures, the steps in Excel or Google Sheets, and a worked report on 36,969 real orders from a UK gift-ware retailer, with every figure checked three times.

How do you calculate customer retention rate?

Take the customers you have at the end of a period, subtract the new customers you gained during it, and divide by the customers you had at the start. Multiply by 100 for a percentage.

Customer retention rate
Retention rate = (customers at end - new customers) / customers at start x 100

HubSpot gives the same formula and suggests picking the period first: a week, a month, a quarter or a year HubSpot. For a shop, "a customer" has no contract, so you define it as someone who placed at least one order in the period.

On the retailer's data, 4,266 customers ordered between December 2009 and November 2010. In the next 12 months, 4,297 customers ordered, and 1,584 of them were new. So 2,713 of the first year's customers came back.

63.6% of the first year's customers ordered again in the second year.

That figure is a good headline. It hides when customers came back and whether newer customers behave like older ones. That is the job of cohorts.

Which retention rate do you need?

Three measures go by the name, and they answer different questions. Pick the one that matches your question before you build anything.

What it countsOn the retailer's data
Period retentionCustomers from one period who order again in the next63.6% from year 1 to year 2
Cohort retentionCustomers first seen in month M who order in month M + N9.5% to 42.5% at month 3
Repeat purchase rateCustomers with more than one order, over the whole data71.8% (4,200 of 5,850)

Period retention suits a board slide. The repeat purchase rate grows with the length of the data, so compare it only across windows of the same length. Cohort retention compares customers at the same age, which is the comparison Shopify merchants ask about when they wonder whether repeat customers are down Shopify Community.

How do you build a cohort retention table in Excel or Google Sheets?

Give every order a month, a cohort and an age in months, keep one row per customer and month, then count customers by cohort and age in a pivot table. Four helper columns and one pivot table do it.

Start from an export with one row per order: an order ID, a date and a customer ID. Line-item exports work too after the de-duplication in step 4. Say the order date sits in column B and the customer ID in column C.

  1. Order month. In G2, , then fill down. Every order now carries the first day of its month.
  2. Cohort month. In H2, . It returns the customer's first order month Microsoft Support. Google Sheets has the same function Google Docs Editors Help.
  3. Age. In I2, . It gives 0 for the first month, 1 for the next, and so on Microsoft Support.
  4. One row per customer and month. Copy columns C, H and I to a new sheet as values, then run DataRemove Duplicates on all three Microsoft Support. In Google Sheets, it is DataData cleanupRemove duplicates. Without this step, a customer with two orders in one month counts twice.
  5. Pivot table. Insert a pivot table on that sheet with Cohort in Rows, Age in Columns and customer ID in Values as Count Microsoft Support. The age 0 column is the cohort size. If the dates show as days, group the Cohort field by Months and Years, as in the pivot table by month guide.
  6. Percentages. Next to the pivot table, divide each cell by the age 0 cell of its row. If the age 0 column is B and age 1 is C, the first cell is , filled right and down.

Two choices change every figure, so write them down above the table. First, retention in month N here means "ordered in that calendar month", so a customer who skips month 2 and returns in month 3 counts in month 3 only. Second, drop the last month when the export stops partway through it, or the last column looks like a collapse. The retailer's data stops on 9 December 2011, so the report leaves December 2011 out.

Clean the export before step 1. Text dates break , and stray spaces in customer IDs split one customer in two. The fixes are in how to clean data in Excel.

What does a real cohort report show?

Among customers who arrived from January 2010 on, about one in five orders again in any given month, from month 1 to month 12. The first row needs a separate reading.

We ran the steps above in Glow Reports on Online Retail II, a public dataset of a UK online gift-ware retailer whose customers are often wholesalers UCI Machine Learning Repository.

After removing cancellations, duplicates and lines without a customer ID, it holds 36,969 orders from 5,878 customers, from 1 December 2009 to 9 December 2011. We recomputed every figure below with our own code and re-ran the report's SQL in Postgres, and the three matched.

Download the full reportPDF

Here are the twelve cohorts at months 1, 3, 6 and 12:

CohortCustomersMonth 1Month 3Month 6Month 12
Dec 200995535.3%42.5%37.7%37.6%
Jan 201038320.6%30.5%25.8%22.2%
Feb 201037423.8%29.1%19.3%15.2%
Mar 201044319.0%24.2%24.6%20.1%
Apr 201029419.4%16.3%27.6%13.9%
May 201025415.7%17.3%21.3%15.4%
Jun 201027017.4%20.4%12.6%14.8%
Jul 201018615.6%29.6%11.3%13.4%
Aug 201016220.4%32.1%9.9%15.4%
Sep 201024322.6%12.3%13.6%21.8%
Oct 201037725.7%12.5%13.0%19.1%
Nov 201032517.5%9.5%12.9%25.5%

The cohort SQL, with the internal table name replaced by , shortened to month 3:

Month 3 retention, 42.5% for the December 2009 cohort
WITH orders_f AS (
  SELECT customer_id, DATE_TRUNC('month', order_date) AS order_month
  FROM orders
  WHERE order_date < TIMESTAMP '2011-12-01'
),
cohorts AS (
  SELECT customer_id, MIN(order_month) AS cohort_month
  FROM orders_f
  GROUP BY customer_id
)
SELECT
  c.cohort_month,
  COUNT(DISTINCT c.customer_id) AS cohort_size,
  ROUND(100.0 * COUNT(DISTINCT o.customer_id) / COUNT(DISTINCT c.customer_id), 1) AS month_3
FROM cohorts c
LEFT JOIN orders_f o
  ON o.customer_id = c.customer_id
  AND o.order_month = c.cohort_month + INTERVAL '3 months'
WHERE c.cohort_month < TIMESTAMP '2010-12-01'
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

Why does the first cohort look so much better?

Because it is not a cohort. The data starts on 1 December 2009, so every customer who ordered that month lands in the "December 2009" cohort, including customers who had been buying for years. The first month of any export holds your whole active base, not your new customers.

The numbers show it. The 955 customers of December 2009 placed 15.8 orders each on average over the two years. The 3,311 customers of January to November 2010 placed 5.2. The report's text reads December 2009 as the best cohort and says the data gives no reason for its level. The reason is the start of the data. Read the table from January 2010 down, and treat the first row as your existing base.

The same edge explains another line of the report: all of December 2009's revenue counts as "new customer revenue", because no earlier month exists to call anyone returning.

20% of the January to November 2010 customers ordered again in their first month after the first order, and 18% in month 12.

What else does the table tell you?

The calendar moves retention as much as the age of the cohort. The retailer sells gifts, and September to November are its busy months.

The July and August 2010 cohorts peak at month 3, which falls in October and November. The September to November cohorts fall to 9.5% to 12.5% at month 3, which lands in the quiet months of December to February.

So a single column hides the season. A late-autumn cohort looks weak at month 3 and strong at month 12. November 2010 sits at 9.5% at month 3 and 25.5% at month 12, second in that column. Compare cohorts at the same age and, when the business is seasonal, the same time of year too.

The report also splits revenue. Over the 24 months, returning customers bring 13,858,895 of 16,857,596 in revenue, 82%. The figures are in pounds sterling, even though the chart axis in the PDF shows a dollar sign.

Where does Glow Reports fit?

The spreadsheet steps work and teach you what each figure means. They take a while on a large export, and the helper columns need rebuilding for each new file.

With Glow Reports, you upload the CSV or Excel export and ask for the report in plain language, with your own definitions: what counts as a cohort, which months to leave out. The AI writes the SQL, builds the charts and drafts the text. The SQL sits under each figure, so you can check a definition or edit it. The report above came from one request of that kind, and its text still needed a human read on the first cohort. The free plan gives 300 credits once, and a report costs 100. For subscription businesses, calculating real MRR churn covers the revenue side.

Common questions

What is a good customer retention rate?

It depends on how often people need what you sell. A gift shop and a coffee subscription will never match. Compare your cohorts with each other at the same age and season rather than with an outside benchmark.

What is the difference between retention rate and repeat purchase rate?

Retention rate counts customers who come back in a given period or month. The repeat purchase rate counts customers with two or more orders over the whole data. On the retailer's data, the repeat purchase rate is 71.8%, while 107 of the 132 monthly cells of the January to November 2010 cohorts sit between 10% and 30%.

Why does my cohort table have a drop in the last column?

Most often, the export ends partway through the last month, so that month counts only a few days of orders. Leave the incomplete month out, as the report does with December 2011.

Can I calculate retention rate in Excel without a pivot table?

Yes. Say the de-duplicated sheet from step 4 is named Visits, with the cohort in column B and the age in column C. On a new sheet, put the cohorts down column A and the ages 0 to 12 across row 4. Then counts the customers of one cohort at one age. Fill it across the grid, then divide by the age 0 column.

El Mehdi Benane
Founder, Glow Reports

I am a product engineer and the founder of Glow Reports. I spent more than 6 years building products in tech, data and cybersecurity, for large groups, startups and SMEs. In most organizations I saw data treated as a technical topic, when it drives business decisions. I built the reporting tool I was looking for, so the people who run small and mid-sized organizations can read their own numbers without a data team.

Upload your CSV or Excel export to Glow Reports and get a retention report with the SQL behind each figure.

Keep reading