Last updated on May 17th, 2020 at 06:16 am
Retention Rate is defined as the number of customers who continue to use a product/service. It is difficult to calculate cohort retention analysis. Here’s how to calculate retention rate in SQL for customer retention analysis. You can use it to calculate retention rate in MySQL, PostgreSQL, SQL Server & Oracle. We will also look at the SQL query for customer retention. Retention rate is measured as the number of returning users, at a regular interval such as every week or month, grouped by their week of signup.
We will calculate retention by weekly cohort in SQL and end up with a table like the one below, which shows the number of customers who have logged in again after first signing up a few weeks ago, for each week of signing up.
How to Calculate Retention Rate in SQL?
Here are the steps to calculate retention rate in SQL. Let’s say you have the following table that stores user_id and login_date of each user’s visit.
mysql> create table login(login_date date,user_id int, id int not null auto_increment, primary key (id));
mysql> insert into login(login_date,user_id)
values('2020-01-01',10),('2020-01-02',12),('2020-01-03',15),
('2020-01-04',11),('2020-01-05',13),('2020-01-06',9),
('2020-01-07',21),('2020-01-08',10),('2020-01-09',10),
('2020-01-10',2),('2020-01-11',16),('2020-01-12',12),
('2020-01-13',10),('2020-01-14',18),('2020-01-15',15),
('2020-01-16',12),('2020-01-17',10),('2020-01-18',18),
('2020-01-19',14),('2020-01-20',16),('2020-01-21',12),
('2020-01-22',21),('2020-01-23',13),('2020-01-24',15),
('2020-01-25',20),('2020-01-26',14),('2020-01-27',16),
('2020-01-28',15),('2020-01-29',10),('2020-01-30',18);
mysql> select * from login;
+------------+---------+----+
| login_date | user_id | id |
+------------+---------+----+
| 2020-01-01 | 10 | 1 |
| 2020-01-02 | 12 | 2 |
| 2020-01-03 | 15 | 3 |
| 2020-01-04 | 11 | 4 |
| 2020-01-05 | 13 | 5 |
| 2020-01-06 | 9 | 6 |
| 2020-01-07 | 21 | 7 |
| 2020-01-08 | 10 | 8 |
| 2020-01-09 | 10 | 9 |
| 2020-01-10 | 2 | 10 |
| 2020-01-11 | 16 | 11 |
| 2020-01-12 | 12 | 12 |
| 2020-01-13 | 10 | 13 |
| 2020-01-14 | 18 | 14 |
| 2020-01-15 | 15 | 15 |
| 2020-01-16 | 12 | 16 |
| 2020-01-17 | 10 | 17 |
| 2020-01-18 | 18 | 18 |
| 2020-01-19 | 14 | 19 |
| 2020-01-20 | 16 | 20 |
| 2020-01-21 | 12 | 21 |
| 2020-01-22 | 21 | 22 |
| 2020-01-23 | 13 | 23 |
| 2020-01-24 | 15 | 24 |
| 2020-01-25 | 20 | 25 |
| 2020-01-26 | 14 | 26 |
| 2020-01-27 | 16 | 27 |
| 2020-01-28 | 15 | 28 |
| 2020-01-29 | 10 | 29 |
| 2020-01-30 | 18 | 30 |
+------------+---------+----+
We will be creating weekly cohort analysis. Depending on your product/service you can change it to monthly/daily.
We will be using MySQL to calculate retention rate in SQL. You can also calculate churn rate for PostgreSQL.
1. Bucket Visits By Week
To calculate retention rate in SQL, first, we will group each visit by its week of login.
mysql> SELECT
user_id,
week(login_date) AS login_week
FROM login
GROUP BY user_id,week(login_date);
+---------+------------+
| user_id | login_week |
+---------+------------+
| 2 | 1 |
| 9 | 1 |
| 10 | 0 |
| 10 | 1 |
| 10 | 2 |
| 10 | 4 |
| 11 | 0 |
| 12 | 0 |
| 12 | 2 |
| 12 | 3 |
| 13 | 1 |
| 13 | 3 |
| 14 | 3 |
| 14 | 4 |
| 15 | 0 |
| 15 | 2 |
| 15 | 3 |
| 15 | 4 |
| 16 | 1 |
| 16 | 3 |
| 16 | 4 |
| 18 | 2 |
| 18 | 4 |
| 20 | 3 |
| 21 | 1 |
| 21 | 3 |
+---------+------------+
Also check out How to Calculate Weekly Active Users (WAU) in MySQL.
2. Calculate FIRST WEEK of login for each user
Next, to calculate retention rate in SQL, we need to calculate the first week of login for each user. We will simply use MIN function and GROUP BY to calculate first login week for each user
mysql> SELECT
user_id,
min(week(login_date)) AS first_week
FROM login
GROUP BY user_id;
+---------+------------+
| user_id | first_week |
+---------+------------+
| 2 | 1 |
| 9 | 1 |
| 10 | 0 |
| 11 | 0 |
| 12 | 0 |
| 13 | 1 |
| 14 | 3 |
| 15 | 0 |
| 16 | 1 |
| 18 | 2 |
| 20 | 3 |
| 21 | 1 |
+---------+------------+
Also read: How to Calculate Monthly Active Users (MAU) in MySQL
3. Merge the 2 tables for login_week and first_week
Next, we get login_week and first_week side by side for each user using the query below, with an INNER JOIN, to calculate retention rate in SQL.
mysql> select a.user_id,a.login_week,b.first_week as first_week from
(SELECT
user_id,
week(login_date) AS login_week
FROM login
GROUP BY user_id,week(login_date)) a,
(SELECT
user_id,
min(week(login_date)) AS first_week
FROM login
GROUP BY user_id) b
where a.user_id=b.user_id;
+---------+------------+------------+
| user_id | login_week | first_week |
+---------+------------+------------+
| 2 | 1 | 1 |
| 9 | 1 | 1 |
| 10 | 0 | 0 |
| 10 | 1 | 0 |
| 10 | 2 | 0 |
| 10 | 4 | 0 |
| 11 | 0 | 0 |
| 12 | 0 | 0 |
| 12 | 2 | 0 |
| 12 | 3 | 0 |
| 13 | 1 | 1 |
| 13 | 3 | 1 |
| 14 | 3 | 3 |
| 14 | 4 | 3 |
| 15 | 0 | 0 |
| 15 | 2 | 0 |
| 15 | 3 | 0 |
| 15 | 4 | 0 |
| 16 | 1 | 1 |
| 16 | 3 | 1 |
| 16 | 4 | 1 |
| 18 | 2 | 2 |
| 18 | 4 | 2 |
| 20 | 3 | 3 |
| 21 | 1 | 1 |
| 21 | 3 | 1 |
+---------+------------+------------+
4. Calculate Week number
From here on, it is easy to calculate retention rate in SQL. Next, we calculate the difference between login_week and first_week to calculate week_number (number of week)
mysql> select a.user_id,a.login_week,b.first_week as first_week,
a.login_week-first_week as week_number from
(SELECT
user_id,
week(login_date) AS login_week
FROM login
GROUP BY user_id,week(login_date)) a,
(SELECT
user_id,
min(week(login_date)) AS first_week
FROM login
GROUP BY user_id) b
where a.user_id=b.user_id;
+---------+------------+------------+-------------+
| user_id | login_week | first_week | week_number |
+---------+------------+------------+-------------+
| 2 | 1 | 1 | 0 |
| 9 | 1 | 1 | 0 |
| 10 | 0 | 0 | 0 |
| 10 | 1 | 0 | 1 |
| 10 | 2 | 0 | 2 |
| 10 | 4 | 0 | 4 |
| 11 | 0 | 0 | 0 |
| 12 | 0 | 0 | 0 |
| 12 | 2 | 0 | 2 |
| 12 | 3 | 0 | 3 |
| 13 | 1 | 1 | 0 |
| 13 | 3 | 1 | 2 |
| 14 | 3 | 3 | 0 |
| 14 | 4 | 3 | 1 |
| 15 | 0 | 0 | 0 |
| 15 | 2 | 0 | 2 |
| 15 | 3 | 0 | 3 |
| 15 | 4 | 0 | 4 |
| 16 | 1 | 1 | 0 |
| 16 | 3 | 1 | 2 |
| 16 | 4 | 1 | 3 |
| 18 | 2 | 2 | 0 |
| 18 | 4 | 2 | 2 |
| 20 | 3 | 3 | 0 |
| 21 | 1 | 1 | 0 |
| 21 | 3 | 1 | 2 |
+---------+------------+------------+-------------+
Also read: How to Calculate Daily Active Users (DAU) in MySQL
5. Pivot the result
Finally, we need to pivot the result, to calculate retention rate in SQL, and generate cohort table. In our pivot table, we will have one row for each first_week value, and one column for each week_number containing the number of users who have back after ‘n’ weeks to use your product/service. For this, we use the following query.
mysql> select first_week,
SUM(CASE WHEN week_number = 0 THEN 1 ELSE 0 END) AS week_0,
SUM(CASE WHEN week_number = 1 THEN 1 ELSE 0 END) AS week_1,
SUM(CASE WHEN week_number = 2 THEN 1 ELSE 0 END) AS week_2,
SUM(CASE WHEN week_number = 3 THEN 1 ELSE 0 END) AS week_3,
SUM(CASE WHEN week_number = 4 THEN 1 ELSE 0 END) AS week_4,
SUM(CASE WHEN week_number = 5 THEN 1 ELSE 0 END) AS week_5,
SUM(CASE WHEN week_number = 6 THEN 1 ELSE 0 END) AS week_6,
SUM(CASE WHEN week_number = 7 THEN 1 ELSE 0 END) AS week_7,
SUM(CASE WHEN week_number = 8 THEN 1 ELSE 0 END) AS week_8,
SUM(CASE WHEN week_number = 9 THEN 1 ELSE 0 END) AS week_9
from (
select a.user_id,a.login_week,b.first_week as first_week,a.login_week-first_week as week_number from (SELECT
user_id,
week(login_date) AS login_week
FROM login
GROUP BY user_id,week(login_date)) a,(SELECT
user_id,
min(week(login_date)) AS first_week
FROM login
GROUP BY user_id) b
where a.user_id=b.user_id
) as with_week_number
group by first_week
order by first_week;
+------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| first_week | week_0 | week_1 | week_2 | week_3 | week_4 | week_5 | week_6 | week_7 | week_8 | week_9 |
+------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
| 0 | 4 | 1 | 3 | 2 | 2 | 0 | 0 | 0 | 0 | 0 |
| 1 | 5 | 0 | 3 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
| 2 | 1 | 0 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
| 3 | 2 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
+------------+--------+--------+--------+--------+--------+--------+--------+--------+--------+--------+
Now we know how to calculate retention rate in SQL. You can also the above queries to calculate retention rate in MySQL, PostgreSQL.
Finally, you can use a data visualization tool to plot the above retention cohort analysis in a table. Here’s a cohort retention table created using Ubiq.
By the way, if you want to create pivot tables, charts & dashboards from MySQL database, you can try Ubiq. We offer a 14-day free trial.
Sreeram Sreenivasan is the Founder of Ubiq. He has helped many Fortune 500 companies in the areas of BI & software development.