코호트 분석 보고서 - 월별 사용자 활동

커뮤니티 구축 데이터 및 보고
1

이것은 Data Explorer 플러그인 내에서 사용할 수 있는 사용자 활동에 대한 Cohort Analysis Report(코호트 분석 보고서)의 SQL 버전입니다.

Cohort Analysis Report는 관리자가 시간에 따른 사용자 참여도에 대한 통찰력을 얻을 수 있도록 설계되었습니다. 사용자를 가입 월(코호트)별로 그룹화하여 활동성을 분석함으로써, 이 보고서는 최소 게시 활동 기준을 충족하는 등록 후 매월 활성 사용자의 수를 추적합니다.

이 보고서는 사용자 유지율, 참여 추세, 커뮤니티 건강 상태 평가, 그리고 커뮤니티 성장 전략의 효과성 파악을 위한 가치 있는 자료가 될 수 있습니다.

Cohort Analysis Report - 월별 활성 사용자 수

--[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 "Joined In", -- Joined In 열에 연도 포함
    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: 사용자가 해당 월에 활성으로 간주되기 위해 작성해야 하는 최소 게시 수입니다.

CTE

Cohort Analysis Report는 데이터 분석을 위해 조직화하고 처리하기 위해 여러 개의 공통 테이블 표현식(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 사이에 교차 조인(Cross Join)을 수행하여 코호트와 등록 후 경과 월의 모든 가능한 조합의 그리드를 생성합니다. 이를 통해 각 코호트의 각 월에 대한 행이 최종 보고서에 포함되도록 하여 월별 활성 사용자 수 계산이 가능해집니다.

8. final_counts

이 CTE는 cross_join과 active_users의 데이터를 결합하여 등록 후 각 월별 코호트별 최종 활성 사용자 수를 계산합니다. 일부 조합에 활성 사용자가 없더라도 모든 코호트와 월의 조합이 포함되도록 왼쪽 조인(Left Join)을 사용합니다.

  • 주요 작업:
    • COALESCE(au.active_users, 0) AS active_users: 활동이 없는 조합의 경우 빈 값으로 두지 않고 활성 사용자 수가 0으로 표시되도록 보장합니다.

CTE 외부의 최종 SELECT 문은 이 데이터를 서식 지정하여 표시하며, 각 코호트의 가입자 수와 등록 후 각 월별 활성 사용자 수를 보여줍니다.

결과

이 보고서는 다음 열을 가진 테이블을 생성합니다:

  • Joined In: 코호트가 생성된 월과 연도로, 사용자가 가입한 시점을 나타냅니다.
  • Users Signed Up: 해당 코호트에 가입한 총 사용자 수입니다.
  • Month 1 to Month 12: 각 열은 가입 후 12개월까지 각 후속 월별 코호트의 활성 사용자 수를 나타냅니다. 활성 사용자는 min_posts_per_month 매개변수로 지정된 최소 게시물 수 이상을 작성한 사람으로 정의됩니다.

결과 예시

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 120 40 8 4 3 3 3 4 3 2 1 1 4
Feb 2023 119 40 7 5 3 2 2 7 2 2 2 1 1
… … … … … … … … … … … … … …

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

3개의 좋아요
Sharing retention data (Google Analytics cohort analysis)