Analytics Patterns · Distinct user-days · days in month · ratios
53. DAU/MAU stickiness
Concept: Distinct user-days · days in month · ratiosshow
Stickiness is average daily active users divided by monthly active users. Average DAU is distinct (user, day) pairs divided by the days in the month, so quiet days count as zero; MAU is distinct users in the month. (m + INTERVAL '1 month')::date - m gives the number of days in month m.
Example (different data):
SELECT to_char(played_on, 'YYYY-MM') AS month, COUNT(DISTINCT player_id) AS monthly_players FROM game_plays GROUP BY 1;
Your task
Investors asked for DAU/MAU, the standard measure of how habit-forming an app is.
From app_opens, for each month return month (YYYY-MM), avg_dau (distinct user-days divided by the number of days in that month, rounded to 2 decimals), mau (distinct users that month) and stickiness_pct (100 * user-days / (days in month * mau), rounded to 1 decimal). Oldest first.
Starting Postgres in your browser…
Checking your access…