How To Measure Retention Rate Using Cohort Analysis in Excel

In my 7 years of digital marketing experience at performance agencies and on the client-side, I’ve worked with multiple businesses in different verticals ranging from e-commerce, travel, fitness, retail, etc. While the day to day performance operations conversations is mostly around metrics like CPA, ROI, CoS (cost of sale), CVR (conversion rate), etc, when it comes to customer retention rate, it’s not a very easy topic to talk about. The reason I think that is simply because many businesses don’t even define what is customer retention (wiki) for them and even if they have defined this, they don’t know how to measure retention rate. Recently at work, I had to work on a yearly detailed forecast that included retention rate or revenue contribution from returning customers as a separate line item. In order to fill that, I had to define and calculate the retention for the company. Since it was an interesting exercise, I thought of sharing it here as it might come in handy for lots of people in similar positions.

While in my experience, most of the companies use Google Analytics as a measurement and reporting platform, I’ve also covered some details on the Cohort Analysis report within Google Analytics. Ok, since its enough context on what this blog post is going to be about, let’s dive straight into this.

What is Retention Rate?

It’s very crucial for a business to define what does retention means for them, what is the duration of a repeated purchase for them, what is the avg lifetime value for them. Quite simply put, Retention Rate is the number of customers a business has retained over a certain period. This duration or period of time can be a month, quarter, half-year, or annually based on the avg repeated purchase rate. For a SaaS business, you could look at a monthly or annual retention rate, for an e-comm business you could look at monthly or quarterly retention rate based on the category you are in. Let’s assume you are looking at the quarterly retention rate of Q1 2019 at the end of Q2 2019. In this case, it would mean how many customers who made their first purchase in Q1 2019, came back and shopped with you in Q2 2019. Another example, let’s say you want to measure retention rate monthly for your subscription business for Jan in Feb. This would mean how many number of customers who subscribed for the first time in Jan, renewed their subscription in Feb.

If you want to understand the value of this metric, here is a simple example for you to see the power of retention. E-commerce business with 0 retention rate has 100 new customers each month from Jan to Dec (12 months) with avg order value of 100 USD, resulting in 10,000 USD revenue each month. At a CPO of 10 USD, they are continuously spending 1000 USD per month to hit the revenue target. Now for the same business, if 10% of the overall customers return each month starting Feb until Dec, they would have a total of 2,138 customers instead of 1200, and revenue would be 21,380 USD which is 78% more. Get the point? With just 10% retention per month, they were able to generate 78% more revenue in 12 months.

What is a Cohort Analysis?

Cohort Analysis is a behavior analysis on a subset or group of users who share some common characteristics in a certain time frame. This subset or group of people who share common characteristics is called a “Cohort”. For example, the number of people who downloaded the gold package of your software can be a cohort and you could study their behavior separately to other users combined. For an e-commerce business, customers with avg basket value over 300 dollars can be a cohort.

In order to understand how to measure retention rate, its also important to understand what are cohorts and how to do cohort analysis. In this post, I am going to use Cohort analysis on purchasers for an e-comm business to study how many % of customers (cohort) come back to shop with us each quarter to calculate quarterly retention rates.

Cohort Analysis is very important as it focuses more on the engagement side of things rather than the growth side. In the example above I shared for retention rate, it signifies that it is very easy for a growing business to undervalue or unwillingly ignore retention rate as growth numbers mostly overshadow retention numbers. New customer acquisition requires marketing dollars and continuously spending money to generate new customers without worrying about retention is a horrible business strategy that usually ends up with consistently high CAC and low to negative profitability for many businesses.

Cohort Analysis in Google Analytics

Most of the small-medium businesses around the world, even bigger companies, and enterprises use Google Analytics as their measurement and reporting tool due to the ease of use and easy integration with other advertising Google tools which again are used by almost all of these companies. Since Google Analytics already has Cohort Analysis built into it, the question is why am I talking about measuring retention rate using cohort analysis in excel? Well, if you have checked the Cohort Analysis section in Google Analytics, chances already know the limitations it has. If not, let me list down the limitations for you.

• Cohort Analysis in Google Analytics looks at users and not customers. Users may or may not be unique. To understand what “Users” mean in GA, check here.
• The biggest limitation is Cohort in Google Analytics only looks at a maximum of the last 30 days for the daily split, last 12 weeks for the weekly split, last 3 months for the monthly split. This means anything longer than which most likely most of the businesses would need, you can’t do it in GA.

How to measure Retention Rate using Cohort Analysis in excel?

Now that we have defined what is Retention Rate, what is cohort analysis and what is cohort analysis in Google Analytics, let’s look at the hot topic of how to use cohort analysis to measure retention rate. Lets straight jump into the steps below. In this example below, we will use a sample customer data for an e-commerce business to calculate the monthly retention rate.

Step 1: The first step is to get your customer data with the date of purchase. Regarding the customer data, either you need to have a unique customer ID or a unique email address associated with each customer. If a customer has made multiple purchases in the same month, it should be a separate line item in the raw data. Sample below:

Step 2: The second step would be to calculate the frequency of purchase from each customer. Let’s call this column “Purchase Rate”. This column will essentially tell you if a customer has brought once, twice, thrice and so on from your platform.

Step 3: Now that we know how many times a customer has purchased with us, it’s important for us to understand when was their first purchase. Let’s create a column called “Date of First Purchase”. So in case, a customer had multiple purchases throughout the year in different months, his date of first purchase should give us the date when he made his first purchase and purchases after that would help us to calculate retention. Sample below:

Step 4: Now that we have a date of purchase and date of first purchase, lets calculate the month of these dates as we would need these in order to calculate the monthly retention rate. Therefore, let’s create two new columns called “Month of Purchase” and “Month of First Purchase”. Apply the Month of purchase formula on date of purchase column and Month of First Purchase column on Data of First Purchase column. Once you get the month’s column populated, sort the “Date of First Purchase” column from oldest to newest.

Step 5: Now that we have months of purchase and we also have months of first purchase, we want to find out what is the difference between the first purchase month and recurring purchase months. To simplify, let’s call this “Age by Month”. For a customer who made their first purchase in Jan, followed by purchase in March and June, we should see 0, 2, 5 for Jan, March, and June respectively. Sample below:

Step 6: Now, there would be customers who would have purchased multiple times in the same month. While calculating monthly retention, we just need unique customers per month. This is because if someone brought 3 times in 1 month but never again in the following month, for us monthly retention for that customer is 0% as he never came back. Therefore, in order to do let’s create a column called ” Unique Customers Per Month”.

Step7: Now that we have unique customers per month, lets quickly remove duplicates to remove any customers with multiple purchases in the same months. The shortcut for removing duplicates is to select the entire sheet (CTRL+A), followed by (ALT+A+M). Unselect everything and just select the “Unique Customers Per Month” column.

Step 8: Our Raw Data is ready. The next step is to do a Pivot on the raw data. Since we are talking about an advance topic, since you are reading this, I am assuming you know how to do a pivot (ALT+N+V). In the Pivot, have “Month of First Purchase” as Row, “Age by Month” as Columns and “Count of Unique Customers Per Month” as Values. It should look like below.

Step 9: The pivot table data is not sorted by months, you can sort it using this video. How to read this Pivot?

• Age by Month: 0 means the same month, month 1 is the next month, month 2 is 2 months after the current month. So for January, 0 means January, 2 means March, 5 means June.
• One example, for the month of March, there were 33 new unique customers in March. Out of these 33 new customers, 1 each came back in April, June, July, August & 2 came back on Sep, 1 on October, 1 in December.

Step 10: Now we have a count of customers, let’s change that to %.

• Right-click on the age by month value (b5 cell)
• Select show value as then select “% off”
• Select the Base field as “Age by month”
• Select the Base item as “0”
• You’ll get something not so good looking as below.

Step 11: Time to clean up the mess we have made. If you clean up the Null, sort the data by months and use conditional formatting, the final results of the above table would look like below. There you have, this is how we measure retention rate for an e-commerce business.

If you are having issues with any steps or need help with any steps or formatting, feel free to drop a comment here and I’ll be more than happy to directly get in touch with you via email to help with your data.

"I had written this original article on my blog under the topic "how to measure retention rate using Cohort Analysis in Excel".