Measure Your Customer Retention In MySQL
Case Study
Lets take an e-commerce website which wants to check their customer retention over time to make informed decisions. Lets assume that the website maintains all data in MySQL. It has 2 tables as follows:
1) customers
+----+------------------+-------+--------+---------------------+ | ID | USERNAME | FNAME | LNAME | REGISTER_DATE | +----+------------------+-------+--------+---------------------+ | | | | | | | 1 | tom@clancy.com | Tom | Clancy | 2017-01-01 10:20:30 | | | | | | | | 2 | dan@brown.com | Dan | Brown | 2017-01-10 11:15:39 | | | | | | | | 3 | emily@bronte.com | Emily | Bronte | 2017-01-20 15:10:10 | +----+------------------+-------+--------+---------------------+
2) sales
+----+---------+--------------+---------------------+ | ID | USER_ID | TOTAL_AMOUNT | SALE_TIME | +----+---------+--------------+---------------------+ | | | | | | 1 | 2 | 1000 | 2017-01-01 10:20:50 | | | | | | | 2 | 1 | 104 | 2017-01-29 10:20:50 | | | | | | | 3 | 3 | 523 | 2017-01-30 10:20:50 | +----+---------+--------------+---------------------+
We can now figure out the customer retention as follows
SELECT
MONTH(customers.register_date) AS month,
COUNT(distinct activity.customer_id) AS unique_customers,
COUNT(future_activity.customer_id) AS future_activity
FROM
sales AS activity
JOIN customers AS users ON activity.user_id = customers.id
AND MONTH(customers.register_date) = MONTH(activity.sale_time)
LEFT JOIN sales AS future_activity ON activity.user_id = future_activity.user_id
AND MONTH(activity.sale_time) = MONTH(
DATE_SUB(
future_activity.sale_time, interval 1 month
)
)
GROUP BY
1;
The customer retention query is going give out a result as follows
+-------+------------------+-----------------+ | MONTH | UNIQUE_CUSTOMERS | FUTURE_ACTIVITY | +-------+------------------+-----------------+ | | | | | 1 | 2628 | 864 | | | | | | 2 | 2652 | 780 | +-------+------------------+-----------------+
The result of the query specifies that in the month of January there were 2628 unique customers out of which 864 came back again in the month of February and purchased from the website. Same goes for February but the future activity column for this will show retained customers for the month of March
Cohort Analysis
Customer Retention is best visualized with Cohort Analysis.
Understanding a Cohort Analysis is pretty simple. Lets try to understand the above sample Monthly Customer Retention Cohort Analysis for a E-Commerce website
- The first column shows the Months for which the analysis is done
- The second column shows the number of unique customers who have purchased from the website in a particular month
- The columns after the Unique column displays the subset of the visitors that have come again on the website in the subsequent month from the unique customers in the previous month
- So for January we can see that there were 257 unique customer who purchased from the website. Out of the total unique customers 44 customers came back again in the month of February and purchased from the website.
- Form the visualization we can see that the month on month retention was fair till the month of April, but in May the website was able to retain customers only for the next 2 months.
Customer retention is of utmost importance mainly for SaaS product companies, retail sector etc. where customer loyalty and repeat customers are required and repeat business consists the maximum revenue percent.
