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
… … … …
1 Like