Doesn't show the breakdown of active users by tenure
Doesn't distinguish between different patterns of user activity
Case 1: 100 users register every month, and are active for one month only
Case 2: Only 100 users register in the first month, and no one ever registers after, but these 100 users are active every single month
Both cases' MAUs will be 100!
MAU - breakdown
Breakdown
New users joined this month
Retained users were active in the previous month, and stayed active this month
Resurrected users weren't active in the previous month, but returned to activity this month
Retention rate - overview
Retention rate: A percentage measuring how many users who were active in a previous month are still active in the current month
Formula: $\frac{Uc}{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 period
Example: $\frac{80}{100} = 0.8 = 80\%$
Retention rate - query
WITH user_activity AS (
SELECT DISTINCT
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
user_id
FROM orders)
SELECT
previous.delivr_month,
ROUND(
COUNT(DISTINCT current.user_id) :: NUMERIC /
GREATEST(COUNT(DISTINCT previous.user_id), 1),
2) AS retention
FROM user_activity AS previous
LEFT JOIN user_activity AS current
ON previous.user_id = current.user_id
AND previous.delivr_month = (current.delivr_month - INTERVAL '1 month')
GROUP BY previous.delivr_month
ORDER BY previous.delivr_month ASC
LIMIT 3;
Preparing Video For Download...
