1. Retention
The MAU KPI is central, but it doesn't show the whole picture. For example, it doesn't tell you how many users are new and how many were already active. The retention rate tells you exactly that.
2. MAU - pitfalls
MAU doesn't show the breakdown of active users by tenure (or length of stay), just their overall count.
Also, MAU doesn't distinguish between different patterns of user activity. Take two cases. In the first case, 100 users register every month, and are active for one month only. In the second case, only 100 users register in the first month, and no one ever registers after, but these 100 users are active every single month.
In both cases, MAU will be 100 every month. It doesn't distinguish these two very different patterns of user activity.
3. MAU - breakdown
Active users can be broken down into three types.
New users joined the app this month. You already have the new users count via the registrations KPI.
Retained users are old users who were active in the previous month, and stayed active this month.
Resurrected users are old users who weren't active in the previous month, but returned to activity this month.
The retention rate calculates the percentage of users who were retained from the last month into this one. Let's focus on that, since the count of resurrected users can be inferred once you've counted both new and retained users.
4. Retention rate - overview
So what is the retention rate, exactly? The MoM retention rate is a percentage measuring how many users who are active in a previous month are still active in the current month.
The retention rate formula is UC over UP, where UC is the count of distinct users who were active in both the current and previous months, and UP is the count of distinct users who were active in the previous month.
Here's an example. Last month, Delivr had 100 active users. Of these 100 users, 80 are still using the app this month. UP is 100 and UC is 80, so the retention rate is 80/100 = 0.8, so Delivr's retention rate for this month is 80%.
5. Retention rate - query
The retention rate query is complex, so let's break it down.
The user activities CTE stores a list of months and the users that were active in these months. Simply select the distinct order dates, truncated to the month, and the user IDs. You now have a list of each month in which a user is active.
Next, in the final query, select the previous month. Then, count the distinct active users in the current month, and divide them by the count of distinct active users in the current month. Cast the numerator to NUMERIC and round the results for a cleaner output.
GREATEST is used here to avoid dividing by zero, in case the previous month has zero active users. In case it does, it defaults to 1.
Finally, select from the user_activity CTE and alias it as previous. Then, left join on that same CTE aliased as current. Join it on user ID and the previous month being equal to the current month minus an interval of one month. This way, you "peek into" the future and see whether a user stayed in the month after the previous month.
6. Retention rate - result
From this query's results, you can see that 70% of Delivr's users in June stayed in July, and so on.
7. Retention
Calculating the retention rate is crucial to judge your userbase's loyalty to your service. Practice writing retention rate queries in the following exercises.