队列分析报告 - 月度用户活动

数据与报告
1

这是一个用户活动队列分析报告的 SQL 版本，供 Data Explorer 插件使用。

队列分析报告旨在为管理员提供用户随时间推移的参与度见解。通过分析按注册月份（队列）分组的用户活动，本报告会跟踪注册后每个月满足最低发帖活动标准的活跃用户数量。

本报告可以成为了解用户留存、参与趋势、评估社区健康状况以及识别社区增长策略有效性的宝贵资源。

队列分析报告 - 每月活跃用户

--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_per_month = 1

WITH user_cohorts AS (
    SELECT
        id AS user_id,
        DATE_TRUNC('month', created_at) AS cohort,
        COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS users_signed_up
    FROM users
    WHERE created_at >= :start_date -- 使用 start_date 参数过滤用户
),
posts_activity AS (
    SELECT
        p.user_id,
        EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at)) AS months_after_registration,
        DATE_TRUNC('month', u.created_at) AS cohort
    FROM posts p
    JOIN users u ON p.user_id = u.id
    WHERE p.created_at >= u.created_at
),
activity_counts AS (
    SELECT
        cohort,
        months_after_registration,
        COUNT(user_id) AS posts_count,
        user_id
    FROM posts_activity
    GROUP BY cohort, months_after_registration, user_id
    HAVING COUNT(user_id) >= :min_posts_per_month -- 按每月的最低发帖数过滤用户
),
active_users AS (
    SELECT
        cohort,
        months_after_registration,
        COUNT(DISTINCT user_id) AS active_users
    FROM activity_counts
    GROUP BY cohort, months_after_registration
),
cohorts_series AS (
    SELECT generate_series AS months_after_registration
    FROM generate_series(0, 11)
),
cohorts AS (
    SELECT
        cohort,
        MAX(users_signed_up) AS users_signed_up -- 聚合以获取每个队列的总注册用户数
    FROM user_cohorts
    GROUP BY cohort
),
cross_join AS (
    SELECT
        c.cohort,
        c.users_signed_up,
        cs.months_after_registration
    FROM cohorts c
    CROSS JOIN cohorts_series cs
),
final_counts AS (
    SELECT
        cj.cohort,
        cj.users_signed_up,
        cj.months_after_registration,
        COALESCE(au.active_users, 0) AS active_users
    FROM cross_join cj
    LEFT JOIN active_users au ON au.cohort = cj.cohort AND au.months_after_registration = cj.months_after_registration
)
SELECT
    TO_CHAR(cohort, 'Mon YYYY') AS "加入月份", -- 在“加入月份”列中包含年份
    users_signed_up AS "注册用户数",
    MAX(CASE WHEN months_after_registration = 0 THEN active_users END) AS "第1个月",
    MAX(CASE WHEN months_after_registration = 1 THEN active_users END) AS "第2个月",
    MAX(CASE WHEN months_after_registration = 2 THEN active_users END) AS "第3个月",
    MAX(CASE WHEN months_after_registration = 3 THEN active_users END) AS "第4个月",
    MAX(CASE WHEN months_after_registration = 4 THEN active_users END) AS "第5个月",
    MAX(CASE WHEN months_after_registration = 5 THEN active_users END) AS "第6个月",
    MAX(CASE WHEN months_after_registration = 6 THEN active_users END) AS "第7个月",
    MAX(CASE WHEN months_after_registration = 7 THEN active_users END) AS "第8个月",
    MAX(CASE WHEN months_after_registration = 8 THEN active_users END) AS "第9个月",
    MAX(CASE WHEN months_after_registration = 9 THEN active_users END) AS "第10个月",
    MAX(CASE WHEN months_after_registration = 10 THEN active_users END) AS "第11个月",
    MAX(CASE WHEN months_after_registration = 11 THEN active_users END) AS "第12个月"
FROM final_counts
GROUP BY cohort, users_signed_up
ORDER BY cohort

SQL 查询说明

该报告通过根据用户加入的月份将用户分群来运行。然后，它会跟踪这些队列，以了解每个月有多少用户保持活跃，这基于每月的最低发帖数。

参数

此报告有两个参数：

  • start_date：用户被纳入队列分析的起始日期。在此日期之后加入的用户将被包含在报告中。
  • min_posts_per_month：用户在一个月内必须发帖的最低数量才能在该月被视为活跃用户。

CTE

队列分析报告使用多个公共表表达式 (CTE) 来组织和处理数据以供分析。每个 CTE 在整个查询中都起着特定作用，在前一个 CTE 的基础上进行构建，最终生成报告。以下是每个 CTE 如何运行的细分：

1. user_cohorts
此 CTE 根据用户加入的月份来识别队列。对于每个用户，它通过将 created_at 时间戳截断到月份来计算他们所属的队列。它还计算每个队列中注册的用户数量。

  • 关键操作：
    • DATE_TRUNC('month', created_at) AS cohort：将 created_at 时间戳截断到月份粒度，有效地按用户注册月份对用户进行分组。
    • COUNT(id) OVER (PARTITION BY DATE_TRUNC('month', created_at))：计算每个队列中的用户数量。

2. posts_activity
此 CTE 跟踪用户相对于其注册日期的发帖活动。它连接 posts 和 users 表，将每个帖子与发帖用户关联起来，并计算每个帖子发布时距离用户注册已过去多少个月。

  • 关键操作：
    • EXTRACT(YEAR FROM AGE(p.created_at, u.created_at)) * 12 + EXTRACT(MONTH FROM AGE(p.created_at, u.created_at))：计算每个帖子发布时距离用户注册已过去多少个月。
    • DATE_TRUNC('month', u.created_at) AS cohort：根据用户的注册月份识别用户的队列。

3. activity_counts
此 CTE 汇总 posts_activity 中的发帖活动，以计算每个用户在注册后每个月发帖的数量。它会过滤这些计数，只包括满足 min_posts_per_month 参数指定的最低发帖活动的用户。

  • 关键操作：
    • GROUP BY cohort, months_after_registration, user_id：按队列、注册后月份和用户 ID 对数据进行分组，为计算发帖数做准备。
    • HAVING COUNT(user_id) >= :min_posts_per_month：过滤分组数据，只包括每月至少发帖数量达到最低要求的用户。

4. active_users
此 CTE 进一步汇总 activity_counts 中的数据，以计算每个队列在注册后每个月的独立活跃用户数量。

  • 关键操作：
    • COUNT(DISTINCT user_id) AS active_users：计算每个队列在注册后每个月的唯一活跃用户数量。

5. cohorts_series
此 CTE 生成一个从 0 到 11 的整数序列，代表注册后的月份。此序列用于确保最终报告为每个队列包含最多 12 个月的数据，即使某些月份没有活动数据。

  • 关键操作：
    • generate_series(0, 11)：生成一个从 0 到 11 的整数序列。

6. cohorts
此 CTE 汇总 user_cohorts 中的数据，以获取每个队列的总注册用户数。

  • 关键操作：
    • MAX(users_signed_up) AS users_signed_up：聚合每个队列的总注册用户数。

7. cross_join
此 CTE 在 cohorts 和 cohorts_series 之间执行交叉连接，以创建队列和注册后月份的所有可能组合的网格。这确保了最终报告为每个队列的每个月都包含行，从而便于计算每月的活跃用户数。

8. final_counts
此 CTE 将 cross_join 和 active_users 中的数据结合起来，计算每个队列在注册后每个月的最终活跃用户数。它使用左连接来确保包含所有队列和月份的组合，即使某些组合没有活跃用户。

  • 关键操作：
    • COALESCE(au.active_users, 0) AS active_users：确保报告显示没有活动的组合的活跃用户数为 0，而不是留空。

CTE 之外的最终 SELECT 语句然后格式化并呈现此数据，显示每个队列在注册后每个月的注册用户数和活跃用户数。

结果

该报告生成一个包含以下列的表：

  • 加入月份：队列创建的月份和年份，表示这些用户何时注册。
  • 注册用户数：在该队列中注册的总用户数。
  • 第 1 个月到第 12 个月：这些列中的每一列代表队列在加入后的每个后续月份（最多 12 个月）的活跃用户数。活跃用户被定义为已发帖数量至少达到 min_posts_per_month 参数指定数量的用户。

示例结果

加入月份 注册用户数 第1个月 第2个月 第3个月 第4个月 第5个月 第6个月 第7个月 第8个月 第9个月 第10个月 第11个月 第12个月
2023 年 1 月 120 40 8 4 3 3 3 4 3 2 1 1 4
2023 年 2 月 119 40 7 5 3 2 2 7 2 2 2 1 1
… … … … … … … … … … … … … …

报告的完整结果将输出 start_date 之后一年的数据。

3 个赞