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

**URL:** https://meta.discourse.org/t/cohort-analysis-report-monthly-user-activity/301403
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [3월 28, 2024, 7:42오후 UTC](https://meta.discourse.org/t/cohort-analysis-report-monthly-user-activity/301403 "2024-03-28T19:42:51Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)
#### Post date: [3월 28, 2024, 7:42오후 UTC](https://meta.discourse.org/t/cohort-analysis-report-monthly-user-activity/301403/1 "2024-03-28T19:42:51Z")

</div>

이것은 [Data Explorer](https://meta.discourse.org/t/32566?silent=true) 플러그인 내에서 사용할 수 있는 사용자 활동에 대한 Cohort Analysis Report(코호트 분석 보고서)의 SQL 버전입니다.

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

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

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

```sql
--[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년치 데이터를 출력합니다.