THE INTRODUCTION

Note: this is the Cohort Retention analysis section of this project. Data cleaning and manipulation were performed using Microsoft SQL and this process is documented here. Pet Store Cohort Analysis SQL query

Data Source: Pet Food Customers Order Online . This dataset was downloaded from Kaggle.

Project Objective: James has been running an online pet shop for 15 months and is seeking trends and insights in customer retention data. We are going to utilize Cohort Retention analysis to understand customer retention patterns.

Before we dive in, what is Cohort Analysis and how to interpret this graph?

Cohort analysis is an analysis of several different cohorts to get a better understanding of behaviors, patterns, and trends. This is a time-based cohort analysis.

Cohort period is the month that a customer first placed an order.

Cohort index is an integer representation of the number of months that has passed since the customers first place an order.

THE COHORT ANALYSIS BEGIN

▶️ Here is a link to the Cohort Analysis Dashboard

The Cohort Table below shows how many customers from each cohort (group of customers who made their first purchase in a specific month) continued to make purchases in subsequent months. Let's take a look at the month of July as an example.

Now, let's take a look at the Cohort Retention Table, which shows the retention rates of customers over 15 months. Continuing with the July example:

What is the overall trend in retention rates?

On average, approximately 68% of customers from the initial cohort return and make a purchase in the second month (cohort index 2).

The retention rate drops slightly to 59% in the fourth month (cohort index 4) and remains within the 50% range through the 10th month (>=cohort index 10).

After 10th month (cohort index 10<), retention rates do drop below 50%.

New Customer Acquisition:

New customers consistently increased month-over-month, peaking in October 2019 with 1,649 new customers. This is a 85% increase from the previous month, indicating a significant surge in new customers during that period.

In March, new customer acquisition hit its lowest point, which could be attributed to a surge in new customers in the preceding months. This suggests a possible seasonal or cyclical patters in customer acquisition.

Retention Trends in Q4, 2019:

In October 2019, despite the increased scale of new customers, 70% of these customers returned in the second month, indicating strong initial retention. This could be attributed to holiday shopping or promotions.

However, this rate declined to 57% in the third month, which is a trend observed in the Q4 2019.

2020 Impact:

In January 2020, there is a substantial drop in retention, which may be related to the new year and potential changes in customer behavior.

THE CONCLUSION

The analysis highlights opportunities for re-engaging customers, especially during periods of decline. Strategies such as targeted promotions could be implemented to boost retention during these times. You can find the exploratory analysis and strategy suggestions here E-commerce audience analysis using SQL

Thank you so much for reading.