Dashboard Report - DAU/MAU

Data & reporting
, ,
1

This is an SQL version of the Dashboard Report for DAU/MAU.

This report provides a detailed analysis of the Daily Active Users (DAU) to Monthly Active Users (MAU) ratio over a specific date range. The DAU/MAU ratio is a key performance indicator (KPI) that measures user engagement by comparing the number of unique active users per day to the number of unique active users over a 30-day period.

-- [params]
-- date :start_date = 2023-01-01
-- date :end_date = 2024-01-01

WITH date_series AS (
  SELECT generate_series(:start_date::date, :end_date::date, '1 day'::interval) AS date
),

DAU AS (
  SELECT date(date) AS day, COUNT(DISTINCT user_id) AS dau_count
  FROM user_visits
  JOIN date_series ON date(user_visits.visited_at) = date_series.date
  GROUP BY date
),
MAU AS (
  SELECT date_series.date, COUNT(DISTINCT user_visits.user_id) AS mau_count
  FROM user_visits
  JOIN date_series ON date(user_visits.visited_at) >= date_series.date - INTERVAL '30 days'
  AND date(user_visits.visited_at) <= date_series.date 
  GROUP BY date_series.date
)
SELECT
  DAU.day,
  (DAU.dau_count::FLOAT / NULLIF(MAU.mau_count, 0)) * 100 AS "DAU/MAU",
  DAU.dau_count,
  MAU.mau_count
FROM DAU
JOIN MAU ON DAU.day = MAU.date
ORDER BY DAU.day

SQL Query Explanation

The SQL query uses Common Table Expressions (CTEs) to create two datasets: one for DAU and one for MAU. It then combines these datasets to calculate the DAU/MAU ratio for each day within the specified date range.

Parameters

The query accepts two parameters, :start_date and :end_date , allowing the user to specify the date range for the report. Both date parameters accept the date format of YYYY-MM-DD .

CTEs

  • date_series: The date_series CTE generates a series of dates from the :start_date to the :end_date , with a one-day interval between each date. This series is used to ensure that the report includes every day within the specified range, even if there is no user activity on some days.
  • DAU: The DAU CTE calculates the number of unique users who visited the site on each day (dau_count ). It does this by joining the user_visits table with the date_series CTE on the condition that the visit date matches the date in the series. The result is grouped by the date to get the count of unique users for each day.
  • MAU: The MAU CTE calculates the number of unique users who visited the site within the 30 days preceding each date in the date_series . It does this by joining the user_visits table with the date_series CTE, where the visit date is between 30 days before and up to, and including, the date in the series. The result is grouped by the date to get the count of unique users for each 30-day period preceding each date.

Final Selection

The final part of the query calculates the DAU/MAU ratio for each day by dividing the DAU count by the MAU count and multiplying by 100 to get a percentage. It also selects the counts of DAU and MAU for reference. The NULLIF function is used to avoid division by zero, which would occur if there are no MAU for a given day. The result is ordered by day to provide a chronological view of user engagement.

Example Results

day DAU/MAU dau_count mau_count
2023-11-19 15.31914893617021 360 2350
2023-11-20 23.92223161453931 566 2366
2023-11-21 23.55648535564853 563 2390
2023-11-22 23.09929372663066 556 2407
2023-11-23 19.394944053046 468 2413
… … … …
2 Likes
2

Adding a modified version of this report here that shows just the MAU (Monthly Active Users) statistics in case this might be helpful for anyone:

MAU (Monthly Active Users)

-- [params]
-- date :start_date = 2024-01-01
-- date :end_date = 2025-01-01

WITH date_series AS (
  SELECT generate_series(:start_date::date, :end_date::date, '1 month'::interval) AS month_start
),

MAU AS (
  SELECT 
    date_trunc('month', date_series.month_start) AS month,
    COUNT(DISTINCT user_visits.user_id) AS mau_count
  FROM user_visits
  JOIN date_series 
    ON date(user_visits.visited_at) >= date_series.month_start - INTERVAL '1 month'
   AND date(user_visits.visited_at) < date_series.month_start + INTERVAL '1 month'
  GROUP BY date_trunc('month', date_series.month_start)
)
SELECT 
  month::date AS "Month",
  mau_count AS "Monthly Active Users"
FROM MAU
WHERE month <= CURRENT_DATE
ORDER BY month

Example Results

Month Monthly Active Users
2024-07-01 2
2024-08-01 3
… …
3 Likes
3

Thanks, @SaraDev this is useful to us. Since we’re a small community with staff being much more active that regular users, I (well, me and ChatGPT) modified the query to be able to run regular users and staff users separately or combined. And I like to see it graphed, so modified to enable that.
I’ve recently seen how to download the database schema in this tip from @tyler.lamparter, and I upload that to ChatGPT at the beginning of these sessions; makes it easier to ask chatty to separate staff, e.g.

-- [params]
-- int :months_ago = 3
-- int :include_staff = 1


WITH staff_users AS (
  SELECT user_id FROM group_users WHERE group_id = 3
),
daily_users AS (
  SELECT 
    date_trunc('day', visited_at)::DATE AS day, 
    COUNT(DISTINCT user_id) AS dau
  FROM user_visits
  WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
  AND (
    (:include_staff = 1) -- All users
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Non-staff only
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Staff only
  )
  GROUP BY day
),
monthly_users AS (
  SELECT 
    date_trunc('month', visited_at)::DATE AS month, 
    COUNT(DISTINCT user_id) AS mau
  FROM user_visits
  WHERE visited_at >= CURRENT_DATE - INTERVAL ':months_ago months'
  AND (
    (:include_staff = 1) -- All users
    OR (:include_staff = 0 AND user_id NOT IN (SELECT user_id FROM staff_users)) -- Non-staff only
    OR (:include_staff = 2 AND user_id IN (SELECT user_id FROM staff_users)) -- Staff only
  )
  GROUP BY month
)
SELECT 
  d.day::DATE AS date,  -- ✅ X-axis for graphing
  ROUND((d.dau::numeric / NULLIF(m.mau, 0)::numeric) * 100, 1)::FLOAT AS dau_mau_ratio  -- ✅ Y-axis for graphing
FROM daily_users d
JOIN monthly_users m ON date_trunc('month', d.day) = m.month
ORDER BY date
-- 0 = Non-Staff Only, 1 = All Users, 2 = Staff Only
1 Like