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.
Why do you need it and how will it help you?
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.
Are you excited to do all that for your store and sell more? Let’s dive in!
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).
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:
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
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.
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).
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).
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.
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.
This is probably the most important retention analysis you can do. To create your first pivot table for it, do the following:
CUSTOMER – COUNTA
AOV – AVERAGE
CLV – AVERAGE
CLV – SUM
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.
The game cohort, despite being equal to the toy cohort in AOV and number of customers, falls behind in CLV.
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.
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.
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.
For example, the Valentine’s campaign cohort is not performing as well as the other two with a CLV way under.
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.
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.
This retention report gives you info how many people in each cohort have 1, 2, 3 and so on orders.
Some things we notice in our example:
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.
With the last pivot table you’ll see when the different cohorts place orders.
Thus you get a distribution of your orders over time.
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.
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.
Metrilo’s mission is to help you build your ecommerce brand and win your place in the customer’s heart. We share what we learn from our daily work with product innovators and founders here. Subscribe to our weekly newsletter to get the freshest lessons and conquer your niche.