코호트 분석 보고서 - 게시물 및 솔루션별 월간 사용자 활동

커뮤니티 구축 데이터 및 보고
,
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 간의 교차 조인(cross join)을 수행합니다. 이를 통해 가입 후 각 월에 대해 모든 코호트가 표현되도록 하여, 최종 단계에서 각 월별 활성 사용자를 계산하는 것을 용이하게 합니다.

final_counts

이 CTE는 이전의 모든 CTE를 결합하여 각 코호트별로 가입 후 월별 최종 활성 사용자 수를 계산합니다. active_users CTE의 활성 사용자를 cross_join CTE에서 생성된 코호트와 월과 일치시키기 위해 왼쪽 조인(LEFT JOIN)을 사용합니다. 활성 사용자가 없는 경우 0으로 기본값을 설정하여 각 코호트-월 쌍에 활성 사용자 수가 포함되도록 보장합니다.

최종 SELECT

쿼리의 최종 SELECT 문은 사용자의 가입 월과 연도를 기준으로 코호트로 그룹화한 후, 가입 후 최대 1년 동안의 월별 활성 사용자 수를 계산합니다. 이는 데이터 변환과 조건부 집계의 조합을 통해 수행되며, 데이터를 각 행이 코호트에 해당하고 각 열이 가입 후 월별 활성 사용자 수를 나타내는 형식(“Month 1”부터 “Month 12”까지)으로 피벗(pivot)합니다.

결과

보고서는 다음 열을 가진 표를 출력합니다:

  • Joined In: 코호트의 월과 연도(사용자가 가입한 시점).
  • Users Signed Up: 해당 코호트에 가입한 사용자의 총 수.
  • Month 1 to Month 12: 가입 후 최대 12개월까지의 월별 활성 사용자 수.

결과 예시

가입 월 가입 사용자 수 1개월 2개월 3개월 4개월 5개월 6개월 7개월 8개월 9개월 10개월 11개월 12개월
2023년 1월 50 40 8 4 3 3 3 4 3 2 1 1 4
2023년 2월 63 40 7 5 3 2 2 7 2 2 2 1 1
… … … … … … … … … … … … … …

보고서의 전체 결과는 start_date 이후 1년 치 데이터를 출력합니다.

6개의 좋아요