仪表盘报告 - DAU/MAU

数据与报告
, ,
1

这是 DAU/MAU 仪表板报告的 SQL 版本。

本报告提供了对特定日期范围内日活跃用户 (DAU) 与月活跃用户 (MAU) 比率的详细分析。DAU/MAU 比率是一个关键绩效指标 (KPI)，通过将每日独立活跃用户数与 30 天内的独立活跃用户数进行比较来衡量用户参与度。

-- [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 查询说明

SQL 查询使用公共表表达式 (CTE) 来创建两个数据集：一个用于 DAU，一个用于 MAU。然后，它将这些数据集合并起来，以计算指定日期范围内每天的 DAU/MAU 比率。

参数

查询接受两个参数 :start_date 和 :end_date，允许用户指定报告的日期范围。两个日期参数都接受 YYYY-MM-DD 格式的日期。

CTE

  • date_series：date_series CTE 生成从 :start_date 到 :end_date 的日期序列，每个日期之间间隔一天。此序列用于确保报告包含指定范围内的每一天，即使某些日期没有用户活动。
  • DAU：DAU CTE 计算每天访问网站的独立用户数量 (dau_count)。它通过将 user_visits 表与 date_series CTE 连接起来实现，连接条件是访问日期与序列中的日期匹配。结果按日期分组，以获取每天独立用户的计数。
  • MAU：MAU CTE 计算在 date_series 中每个日期之前的 30 天内访问网站的独立用户数量。它通过将 user_visits 表与 date_series CTE 连接起来实现，其中访问日期介于序列日期之前的 30 天到（包括）序列日期之间。结果按日期分组，以获取每个 30 天期间的独立用户计数。

最终选择

查询的最后一部分通过将 DAU 计数除以 MAU 计数并乘以 100 来计算每天的 DAU/MAU 比率，以获得百分比。它还选择 DAU 和 MAU 的计数作为参考。NULLIF 函数用于避免除以零的情况，如果某天没有 MAU，则会出现这种情况。结果按日期排序，以提供用户参与度的时间顺序视图。

示例结果

day DAU/MAU dau_count mau_count
2023-11-19 15.3191489 360 2350
2023-11-20 23.9222316 566 2366
2023-11-21 23.5564854 563 2390
2023-11-22 23.0992937 556 2407
2023-11-23 19.3949441 468 2413
… … … …
2 个赞
2

在此处添加此报告的修改版本，其中仅显示 MAU（月活跃用户） 统计信息，以供参考：

MAU（月活跃用户）

-- [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 "月份",
  mau_count AS "月活跃用户数"
FROM MAU
WHERE month <= CURRENT_DATE
ORDER BY month

示例结果

月份 月活跃用户数
2024-07-01 2
2024-08-01 3
… …
3 个赞
3

谢谢，@SaraDev，这对我们很有帮助。由于我们是一个小型社区，工作人员比普通用户活跃得多，我（以及 ChatGPT）修改了查询，以便能够分别或合并运行普通用户和工作人员用户。而且我喜欢看到它被图形化，所以进行了修改以启用它。
我最近在 @tyler.lamparter 的这个技巧中看到了如何下载数据库模式，并在这些会话开始时将其上传到 ChatGPT；这使得更容易要求 chatty 分开 staff，例如。

-- [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 个赞