同期群分析报告：月度用户活动（帖子与解决方案）

数据与报告
,
1

这是用户活动队列分析报告的 SQL 版本，可在 Data Explorer 插件中使用。

:discourse: 此报告需要启用 Discourse Solved 插件。

此队列分析报告通过跟踪自指定开始日期以来注册的用户的活动来提供用户参与度方面的见解。它特别关注那些在注册后的每个月内，发帖数和提供的解决方案数达到或超过特定阈值的用户。

此报告对于希望了解其社区在随着时间的推移留住和吸引新用户方面的有效性的管理员来说非常有价值，特别是与发帖和提供主题解决方案相关的内容。此报告还有助于评估社区健康状况和识别社区增长策略的有效性。

注册后每月活跃用户（含最低发帖数 + 解决方案参数）

--[params]
-- date :start_date = 2023-01-01
-- int :min_posts_per_month = 1
-- int :min_solutions_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 
),
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
),
solutions_counts AS (
    SELECT
        p.user_id,
        COUNT(p.user_id) as solutions_count,
        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 discourse_solved_solved_topics dsst
    INNER JOIN posts p ON p.id = dsst.answer_post_id
    JOIN topics t ON t.id = p.topic_id
    JOIN users u ON p.user_id = u.id
    WHERE p.created_at >= u.created_at
    GROUP BY months_after_registration, cohort, p.user_id
    HAVING COUNT(p.user_id) >= :min_solutions_per_month
),
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
        sc.cohort,
        sc.months_after_registration,
        COUNT(DISTINCT ac.user_id) AS active_users
    FROM solutions_counts sc
    FULL JOIN activity_counts ac ON sc.user_id = ac.user_id
    AND sc.months_after_registration = ac.months_after_registration
    AND sc.cohort = ac.cohort
    GROUP BY sc.cohort, sc.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 "Joined In", -- Include the year in the Joined In column
    users_signed_up AS "Users Signed Up",
    MAX(CASE WHEN months_after_registration = 0 THEN active_users END) AS "Month 1",
    MAX(CASE WHEN months_after_registration = 1 THEN active_users END) AS "Month 2",
    MAX(CASE WHEN months_after_registration = 2 THEN active_users END) AS "Month 3",
    MAX(CASE WHEN months_after_registration = 3 THEN active_users END) AS "Month 4",
    MAX(CASE WHEN months_after_registration = 4 THEN active_users END) AS "Month 5",
    MAX(CASE WHEN months_after_registration = 5 THEN active_users END) AS "Month 6",
    MAX(CASE WHEN months_after_registration = 6 THEN active_users END) AS "Month 7",
    MAX(CASE WHEN months_after_registration = 7 THEN active_users END) AS "Month 8",
    MAX(CASE WHEN months_after_registration = 8 THEN active_users END) AS "Month 9",
    MAX(CASE WHEN months_after_registration = 9 THEN active_users END) AS "Month 10",
    MAX(CASE WHEN months_after_registration = 10 THEN active_users END) AS "Month 11",
    MAX(CASE WHEN months_after_registration = 11 THEN active_users END) AS "Month 12"
FROM final_counts
GROUP BY cohort, users_signed_up
ORDER BY cohort

SQL 查询说明

参数

此报告使用三个参数：

  • start_date：开始跟踪新用户注册的日期。
  • min_posts_per_month：用户每月必须发帖的数量才能被视为活跃用户。
  • min_solutions_per_month：用户每月必须提供的解决方案（已接受的答案）的数量才能被视为活跃用户。

用户必须同时满足 min_posts_per_month 和 min_solutions_per_month 的要求，才能在该月被视为活跃用户。

CTE

提供的 SQL 查询使用多个通用表表达式 (CTE) 来分解计算注册后每月活跃用户数的流程，该计算基于最低发帖数和解决方案标准。以下是每个 CTE 的说明：

user_cohorts

此 CTE 根据用户注册月份识别用户队列。对于每个用户，它计算其注册月份（cohort）并计算在同一月份注册的总用户数。这有助于理解每个队列的初始规模。

posts_activity

此 CTE 跟踪用户注册后发帖方面的活动。对于每个帖子，它计算自用户注册日期以来经过了多少个月（months_after_registration），并按用户的注册队列对这些帖子进行分组。这用于跟踪用户随着时间的推移在发布内容方面的活跃度。

solutions_counts

此 CTE 专注于计算用户提供的解决方案（已接受的答案）的数量。它筛选标记为解决方案的帖子，并为每个用户计数，确保只考虑用户注册后发布的帖子。它还计算每个解决方案自用户注册以来经过了多少个月。只有满足或超过指定每月最低解决方案数量的用户才会被计入此计数。

activity_counts

此 CTE 按用户注册后每月发帖的数量汇总每个用户的帖子数。它按用户的注册队列和注册以来的月数对用户进行分组，然后计算发帖数量。只有满足或超过指定每月最低发帖数量的用户才会被计入此计数。

active_users

此 CTE 结合了 solutions_counts 和 activity_counts 的数据，以识别活跃用户——即同时满足发帖和解决方案标准的那些用户。它根据为发帖和解决方案设定的标准，按用户的注册队列和注册以来的月数对活跃用户进行计数。

cohorts_series

此 CTE 生成一个从 0 到 11 的数字序列，代表注册后的月数。这用于确保最终报告包含最多 12 个月的数据，即使某些月份没有活跃用户。

cohorts

此 CTE 聚合 user_cohorts 的数据，以获取每个队列中注册的总用户数。它确保最终报告包含每个队列的总注册用户数。

cross_join

此 CTE 在 cohorts CTE 和 cohorts_series CTE 之间执行交叉连接。这确保每个队列在注册后的每个月都有代表，从而便于在最后一步计算每个月的活跃用户数。

final_counts

此 CTE 结合了所有先前的 CTE，以计算每个队列在注册后每个月的活跃用户数的最终计数。它使用左连接将 active_users CTE 中的活跃用户与 cross_join CTE 中生成的队列和月份进行匹配。它确保每个队列-月份对都有一个活跃用户计数，如果没有活跃用户，则默认为 0。

最终 SELECT

查询中的最终 SELECT 语句根据用户的注册月份和年份将用户分组到队列中，然后计算注册后最多一年内每个月的活跃用户数。这是通过转换和条件聚合的组合完成的，它将数据透视为一种格式，其中每一行对应一个队列，每一列代表注册后每个月的活跃用户数，从“第 1 个月”到“第 12 个月”。

结果

报告输出一个包含以下列的表：

  • Joined In：队列的月份和年份（用户注册时间）。
  • Users Signed Up：该队列中注册的总用户数。
  • Month 1 到 Month 12：注册后每个月的活跃用户数，最多 12 个月。

示例结果

Joined In Users Signed Up Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8 Month 9 Month 10 Month 11 Month 12
Jan 2023 50 40 8 4 3 3 3 4 3 2 1 1 4
Feb 2023 63 40 7 5 3 2 2 7 2 2 2 1 1
… … … … … … … … … … … … … …

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

6 个赞