Query for WAU/MAU

With DAU/MAU I was also curious about a weekly equivalent. Below is a query for data explorer that will give you the weekly active user count divided by the monthly active user count. Unfortunately it doesn’t have a history mechanic, so if you wanted to see how this metric varied on a weekly basis you would need to run the script once per week via api or manually and store the values somewhere.

WITH weekly_active_users_count AS(
  SELECT CAST(COUNT(DISTINCT u.id) AS NUMERIC)AS count
  FROM users u
  WHERE age(u.last_seen_at) < interval '7 days'
), monthly_active_users_count AS (
  SELECT CAST(COUNT(DISTINCT u.id) AS NUMERIC) AS count
  FROM users u
  WHERE age(u.last_seen_at) < interval '31 days'
)
SELECT 
  ROUND(weekly_active_users_count.count / monthly_active_users_count.count,2)*100  
  AS weekly_monthly_percentage
FROM weekly_active_users_count,monthly_active_users_count

For reference, my DAU/MAU rate is around the 20% mark, and WAU/MAU rate is around 54%. I feel the WAU/MAU rate is more suitable for a community that is tailored more towards intermittent usefulness rather than engagement.

3 Likes