Retention rate
Bob's requested your help again now that you're done with Carol's MAU monitor. His meeting with potential investors is fast approaching, and he wants to wrap up his pitch deck. You've already helped him with the registrations running total by month and MAU line charts; the investors, Bob says, would be convinced that Delivr is growing both in new users and in MAUs.
However, Bob wants to show that Delivr not only attracts new users but also retains existing users. Send him a table of MoM retention rates so that he can highlight Delivr's high user loyalty.
This is a part of the course
“Analyzing Business Data in SQL”
Exercise instructions
- Select the month column from
user_monthly_activity, and calculate the MoM user retention rates. - Join
user_monthly_activityto itself on the user ID and the month, pushed forward one month.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
WITH user_monthly_activity AS (
SELECT DISTINCT
DATE_TRUNC('month', order_date) :: DATE AS delivr_month,
user_id
FROM orders)
SELECT
-- Calculate the MoM retention rates
previous.delivr_month,
ROUND(
___ /
___,
2) AS retention_rate
FROM user_monthly_activity AS previous
LEFT JOIN user_monthly_activity AS current
-- Fill in the user and month join conditions
ON ___
AND ___
GROUP BY previous.delivr_month
ORDER BY previous.delivr_month ASC;This exercise is part of the course
Analyzing Business Data in SQL
Learn to write SQL queries to calculate key metrics that businesses use to measure performance.
Financial KPIs like profit are important, but they don't speak to user activity and engagement. In this chapter, you'll learn how to calculate the registrations and active users KPIs, and use window functions to calculate the user growth and retention rates.
Exercise 1: Registrations and active usersExercise 2: Registrations by monthExercise 3: Monthly active users (MAU)Exercise 4: Window functionsExercise 5: Registrations running totalExercise 6: MAU monitor (I)Exercise 7: GrowthExercise 8: MAU monitor (II)Exercise 9: MAU monitor (III)Exercise 10: Order growth rateExercise 11: RetentionExercise 12: New, retained, and resurrected usersExercise 13: Retention rateWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.