[SQL] 유저 인게이지먼트 하락 원인 분석(Mode)-코호트

1. 문제

- 2014년 8월 4일 이후 WAU(주간 활성 유저)가 감소한다. 감소 원인을 파악하라

 

2. 코호트 분석

신규 유저의 감소가 아님을 확인했기 때문의 기존 유저의 감소라고 생각할 수 있습니다. 기존 유저 감소를 파악할 때 가장 효율적인 분석방법은 제품 가입 시점을 기반으로 한 코호트 분석입니다.

 

코호트 분석은 사용자를 그룹으로 분류하여 그룹의 행동과 유지율을 분석할 때 활용하는 기법입니다. 아래 차트를 보게 되면 가입기간이 10주 이상인 유저의 engagement가 감소함을 볼 수 있습니다.

5 weeks와 4 weeks를 필터링 했습니다. 가입 시점을 기준으로 가장 높으며, 점차 하락하는 모슴을 볼 수 있습니다. 이 부분은 대부분의 플랫폼에서 볼 수 있는 추이입니다.

Mode Code

SELECT DATE_TRUNC('week',z.occurred_at) AS "week",
       AVG(z.age_at_event) AS "Average age during week",
       COUNT(DISTINCT CASE WHEN z.user_age > 70 THEN z.user_id ELSE NULL END) AS "10+ weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 70 AND z.user_age >= 63 THEN z.user_id ELSE NULL END) AS "9 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 63 AND z.user_age >= 56 THEN z.user_id ELSE NULL END) AS "8 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 56 AND z.user_age >= 49 THEN z.user_id ELSE NULL END) AS "7 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 49 AND z.user_age >= 42 THEN z.user_id ELSE NULL END) AS "6 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 42 AND z.user_age >= 35 THEN z.user_id ELSE NULL END) AS "5 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 35 AND z.user_age >= 28 THEN z.user_id ELSE NULL END) AS "4 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 28 AND z.user_age >= 21 THEN z.user_id ELSE NULL END) AS "3 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 21 AND z.user_age >= 14 THEN z.user_id ELSE NULL END) AS "2 weeks",
       COUNT(DISTINCT CASE WHEN z.user_age < 14 AND z.user_age >= 7 THEN z.user_id ELSE NULL END) AS "1 week",
       COUNT(DISTINCT CASE WHEN z.user_age < 7 THEN z.user_id ELSE NULL END) AS "Less than a week"
  FROM (
        SELECT e.occurred_at,
               u.user_id,
               DATE_TRUNC('week',u.activated_at) AS activation_week,
               EXTRACT('day' FROM e.occurred_at - u.activated_at) AS age_at_event,
               EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS user_age
          FROM tutorial.yammer_users u
          JOIN tutorial.yammer_events e
            ON e.user_id = u.user_id
           AND e.event_type = 'engagement'
           AND e.event_name = 'login'
           AND e.occurred_at >= '2014-05-01'
           AND e.occurred_at < '2014-09-01'
         WHERE u.activated_at IS NOT NULL
       ) z

 GROUP BY 1
 ORDER BY 1
LIMIT 100

Mode output

Taegu Code

Mode의 코드는 위의 output과 같이 테이블 형태의 결과물을 얻기 위해 user_age 별 count를 작성하였고, 저는 week별 user_age를 7로 나눈 week2별 group by를 통해 코드를 작성하였습니다. 위의 output과 동일한 결과를 원할 경우 엑셀 피벗테이블을 통해 만들 수 있습니다.

SELECT DATE_TRUNC('week',z.occurred_at) AS "week",
      z.week2,
      COUNT(DISTINCT(z.user_id))
FROM      
(SELECT u.user_id,
        e.occurred_at,
        DATE_TRUNC('week', u.activated_at) as week,
        EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS user_age,
        CASE WHEN 
        (CAST(EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS INTEGER)/ 7)>10 
        THEN 10 
        ELSE (CAST(EXTRACT('day' FROM '2014-09-01'::TIMESTAMP - u.activated_at) AS INTEGER) / 7)
        END as week2
  FROM tutorial.yammer_users u
  JOIN tutorial.yammer_events e
  ON u.user_id = e.user_id
  AND e.event_type ='engagement'
  AND e.event_name = 'login'
  AND e.occurred_at BETWEEN '2014-05-01' AND '2014-09-01'
  WHERE u.activated_at IS NOT NULL) z
GROUP BY 1, 2

Taegu Code

엑셀 피벗테이블 plot