Retention Report

How to make your own Retention report and use it to sell more

If you have a long-term strategy for your ecommerce business, you know you should be focusing on customer retention, not only on acquisition.

In this article, we’ll show you how to make your own retention report in order to measure your retention efforts now and see what can be improved.

For those of you who don’t want to do it in spreadsheets themselves (and run on Shopify, Magento or WooCommerce), we have something very convenient for you at the end.

What a retention report will tell you

Why do you need it and how will it help you?

  • A retention report shows how you’re doing in terms of customer loyalty and getting repeat orders.
  • How often and exactly when customers buy and for how long spans their customer lifetime.
  • Which products, campaigns and periods bring more loyal customers.
  • What revenue you earn, what’s the average order value and number of orders for each customer cohort.
  • How order values vary for the different cohorts over time.

And why should you care?
Because knowing all these lets you:
– focus on the products and campaigns that bring loyal customers;
– push marketing only when it’s needed, optimizing your budgets;
– plan more accurately revenue, customer lifecycles and even inventory;
– create better loyalty programs to significantly boost repeat orders;
– adjust the amount of discounts given out at each step of the customer journey and so on.

Related: Retention marketing for online stores

Are you excited to do all that for your store and sell more? Let’s dive in!

How to create your own retention report

Now, I’ll show you how to create your own database that’s accessible to use and analyse.

Open an excel or Google Sheets file and load up your patience (we worked in Sheets). :)

1. Fill in your order data

We’ll work with 2 tables to make it easier.

The first one will contain order values (in columns named Order 1, Order 2, etc.) and the second one – order count in the week when it happened (week 1, week 2, etc.).

All else is the same:
Customer
Cohort by month in which the customer falls in
Cohort by first product bought in which the customer falls in
Cohort by campaign in which the customer falls in
Monetary value of every order they made

Database

 

Note about cohorts: This is a categorization of all customers based on a common trait – all people who became customers through a specific campaign or in a given month, or bought a specific product as their first order ever.

Why we do cohort analysis? It helps put some order among all the customers and group them in a meaningful way that gives context for their reason for converting. Those customer groups can be monitored over time to see consistent behaviors.

Related: Cohort analysis for better customer experience

So fill in the first product bought, the month and the campaign carefully in – they should all be written in the same way (not “Spring sale” and “spring” for one and the same campaign).

Cohorts

Also, put in columns to calculate average order value (AOV) and customer lifetime value (CLV). For AOV, calculate the average of each customer’s orders. For CLV, sum them up.

Then, copy the same table and substitute 1st order, 2nd order and so on with week 1, week 2, week 3 (or month 1, month 2, etc. if that fits your business better). This is because we want to measure when the consecutive orders happen after the first one.

Also, substitute the order values with simple 1 to be able to count them and make a column that does it for each customer (instead of the AOV and CLV ones).

When The Order Happened

Now, you have your database. I know this is a lot of work especially if you have more customers and products.

Don’t forget you’ll have to input the new data coming in when you want to update this retention report – maybe once a month.

But bear with me – I have a solution for this so you don’t need to fill out all this manually.

2. Create a pivot table out of your input table

Select the first table with the order values you created and create a pivot out of it. For Google Sheets that option is in the Data menu -> Pivot table.

You can completely customize this table – the idea is to synthesize the whole database to get meaningful insights at a glance.

Cohort KPIs

This is probably the most important retention analysis you can do. To create your first pivot table for it, do the following:

  1. From the pivot table editor, select “cohort by month” as rows for the table.
  2. In the “Values” section, you get a dropdown list of all columns of your database to choose what to put in the pivot. Add “customer”, “AOV”, and “CLV” twice.
    You have options how you want these values to be displayed – as a sum, as a count, as an average, etc. That’s the point of the pivot table – to summarize your data without you calculating stuff.

So choose:
CUSTOMER – COUNTA
AOV – AVERAGE
CLV – AVERAGE
CLV – SUM

Pivot Table Editor

You’ll get a table that compares all monthly cohorts by their most important KPIs. Do that for all 3 types of cohorts to get 3 tables (you can copy the first table and just switch the cohort in the rows.

Analysis:
Cohorts By Product
The game cohort, despite being equal to the toy cohort in AOV and number of customers, falls behind in CLV.

Cohorts By Product

In cohorts by campaign we see the Valentine’s one performing poorly, which lead to the conclusion this campaign was not able to bring in many good-fit customers to become loyal.

Related: How to turn holiday shoppers into loyal customers

Cohorts By Month

In cohorts by month notice how April and January have the same number of customers, but very different CLV? It’s due to the AOV – see what huge impact it has.

Compare cohorts by CLV and order value

The next pivot we’ll do is to compare the cohorts by their performance in order value over time.

We’ll do that like this:
1. From the pivot table editor, choose “cohort by campaign” as your rows.
2. Then, select “Values as: columns” and add all orders. It will sum them up by default, that’s just what we need. Also, add CLV there too.
3. Replicate for the other 2 cohort types.

This way you get the first part of your retention report. It lets you compare all cohorts by the orders they generated over time and by their total customer lifetime value.

You see which cohorts get the best CLV – they’re your valuable customers.

Orders Over Time

For example, the Valentine’s campaign cohort is not performing as well as the other two with a CLV way under.

Related: How to increase customer lifetime value in ecommerce

Another thing we notice is that the game cohort consistently gets smaller orders than the toy or book cohort which means game buyers are not as engaged as the others.

Compare cohorts by number of orders

You need a Retention report to see how loyal your customer are in the first place, right?

We give you a way to see that with the next pivot table.
1. Select your second data table where you put in the orders as 1 to mark when they happened. We’ll need just order count this time.
2. Choose a cohort type for rows as before.
3. Choose “number of orders” for columns.
4. Choose Values – “number of orders” and make sure it’s displayed as a sum.
5. Repeat for the other 2 types of cohorts.

Pivot Table 2

This retention report gives you info how many people in each cohort have 1, 2, 3 and so on orders.

Customers With Number Of Orders

Some things we notice in our example:

  • Most January customers have 12 orders each, but none reach to 6 orders.
  • The book cohort is doing extremely well in customer retention with 18 people making 6 orders.

Total and average number of orders

This one’s supplementary – it shows you a break down of the cohorts by the total number of orders they made and and the average number of orders per customer.

Here’s how to do it:
1. Start of as in the previous cases – by setting a cohort type as your rows.
2. Then choose Values: number of orders – summed up and then again the same as an average.

You’re ready – in our example data we see that the March and book cohorts are the strongest with most orders.

Pivot 3

Compare order frequency

With the last pivot table you’ll see when the different cohorts place orders.

  1. Start off as usual with cohorts as rows.
  2. 2. As Values choose all orders (as many as order columns you have – we have 6). The default calculation is SUM, that’s what we need.

Thus you get a distribution of your orders over time.

Order Frequency

Our example shows that:
The game cohort wasn’t very active in the 3rd week.
The Spring campaign cohort was very active in week 5.

Want all the insights from the retention report without the hassle?

I know it’s not exactly exciting to create pivot tables or draw sums and averages.

And definitely it’s not fun to update your database manually.

For all of you who don’t want to do it in spreadsheets, Metrilo can do the retention report for you.

  • Your data is synced automatically as it comes in so you always have the most up-to-date numbers.
  • No need to preset anything.
  • Accurate – any changes in order statuses are synced as they happen.
  • In-built segmentation of new vs returning customers.
  • Ready views of the retention reports in number of orders, order values or share or people from the cohort.
  • Including Average time between orders metric
  • Metrilo integrates with your store via a ready plugin so you don’t need a developer and can start in about 10 minutes.

Learn more about Metrilo’s retention platform

 

About the author

Dimira is the Words Pixie of Metrilo. A daydreamer and a coffee junkie. Can’t live when a comma is misplaced. A dog lover with a geeky sense of humor.

Become a data-powered marketing hero

Learn to use the power of data and turn it into sales. If you want to grow your ecommerce business with data-driven decisions, subscribe for our email newsletter and you’ll be the first to get our content + exclusive examples you can’t read anywhere else.

Oops! Invalid email.

We promise, no spam.

Awesome!

Thank you for subscribing!
See you soon :-)

New blog posts