retention rate in sql

How to Calculate Retention Rate in SQL?

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.

retention cohort analysis pivot table

 

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.

retention cohort analysis pivot table

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.

mm

About Ubiq

Ubiq is a powerful dashboard & reporting platform. Build dashboards, charts & reports for your business in minutes. Try it for free!