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 theuser_visits
table with thedate_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 thedate_series
. It does this by joining theuser_visits
table with thedate_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 |
… | … | … | … |