인프런 커뮤니티 질문&답변

안녕하세요. 연습문제 2번을 다음과 같이 풀어보았습니다.

Q: 리텐션 연습문제: Retain User를 New, Current, Resurrected, Dormant User로 나누는 쿼리를 작성하여라. 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높은지, 찾아보아라.

주차별, 전체 유저에 대한 코호트 분석 그래프를 보면, 다음과 같습니다.

• 문제 정의

2022년 12월 19일 이후로 리텐션이 떨어지고 있는 문제점이 발생했다. 해당 원인이 무엇일까?

• 가설 설정

1) 고객의 유입량이 감소하고 있다.

-> 2022년 12월 19일 이전과 이후의 주차별 신규 가입자 수를 비교한다.

2) 리텐션의 변화가 특정 유저 그룹에 의해 영향을 받는가?

-> 신규유저는 어떤 시점을 기준으로 분류할 것인가?

주차별 신규 가입자 수 시각화

2022년 12월 19일을 월요일로 하는 주차부터 신규 가입자 수가 감소하는 추세이다. 전반적으로 신규가입자 수는 증가하는 추세였으나, 12월 19일을 기점으로 꺾이는 현상을 보인다. 따라서, 신규 가입자 수가 리텐션에 영향을 준다는 것을 확인할 수 있다.

기존의 추세와 반하게, 떨어지는 추세를 보이기 때문에 다음과 같이 기존 유저와 신규 유저를 정의하겠다.

1) 기존 유저

=> 2022년 10월 03일 ~ 2022년 12월 19일 이전

2) 신규 유저

=> 2022년 12월 19일 이후

복귀유저와 이탈 유저를 판단하는 기준이 필요하다. 이를 위해, 첫 로그인 이후, 두번째로 로그인을 하기까지의 걸리는 시간을 4분위 수로 검증해서, 중위값을 기준으로 기존유저와 복귀유저를 구분해보기로 했다.

로그인 판단 기준 = click_login 이라는 이벤트가 발생했을 경우

1. 자동 로그인 여부 판단

SELECT
    COUNT(*)
FROM advanced.app_logs
CROSS JOIN UNNEST(event_params) AS ep
WHERE ep.key = "firebase_screen" AND ep.value.string_value = 'welcome' AND user_id IS NOT NULL

자동 로그인이 된다면, click_login 이벤트가 발생하지 않을 것이다. 하지만, COUNT(*)가 0이기 때문에, 해당 앱은 자동 로그인이 되지 않음을 알 수 있다.

즉, 사용자가 앱에 접속하면, 가입을 했더라도, 무조건 로그인을 해야한다. 따라서, 사용자가 앱에 접속 시, 로그인 이벤트가 무조건 발생한다.

2.첫번째 로그인 후, 다음 로그인하기까지 걸린 시간을 4분위 수로 계산

WITH base AS (SELECT event_date,
                     DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')           AS event_timestamp,
                     user_pseudo_id,
                     event_name,
                     ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS login_rn
              FROM advanced.app_logs
              WHERE event_name = 'click_login' AND event_date >= '2022-10-03'
              QUALIFY login_rn < 3)
, second_event_data AS (SELECT event_date,
                               event_timestamp,
                               user_pseudo_id,
                               event_name,
                               login_rn,
                               IF(second_event_date IS NULL, '2023-01-20', second_event_date) AS second_event_date
                        FROM (SELECT *,
                                     LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS second_event_date
                              FROM base))


SELECT
    APPROX_QUANTILES(date_diff_day, 100)[OFFSET(25)] AS percentile_25,
    APPROX_QUANTILES(date_diff_day, 100)[OFFSET(50)] AS percentile_50,
    APPROX_QUANTILES(date_diff_day, 100)[OFFSET(75)] AS percentile_75,
    MAX(date_diff_day) AS percentile_100,
    AVG(date_diff_day) AS average
FROM (SELECT DATE_DIFF(second_event_date, event_date, DAY) AS date_diff_day
      FROM second_event_data)

유저별로 로그인을 한 시점을 ROW_NUMBER()로 카운트했다. 또한, second_event_date(다음 로그인 시점)이 NULL인 경우는 이탈 유저이다. 그런데, 4분위 수를 계산하려면, 해당 값을 채워야했다. 2099-12-31로 하려고 했으나, 너무 큰 값을 채워버리면, 4분위 수 검증이 정확하지 않게 되기 때문에 (mid와 avg값이 오른쪽으로 치우쳐버리는 현상이 발생할 수 있다.) second_event_date의 max값을 구해서, 2023-01-20을 채워주었다.

Approx_quantiles 함수는 백분위로 나눌 경우, 각 근사치의 최대값을 나타내는 함수이다. 중위값에 해당하는 값들 중 가장 큰 값은 39일이다.

이번에는, 해당 범위에 속하는 값들이 몇개가 있는지 확인해보겠다.

SELECT
    COUNTIF(date_diff_day <= 19) AS count_up_to_25,
    COUNTIF(date_diff_day > 19 AND date_diff_day <= 39) AS count_25_to_50,
    COUNTIF(date_diff_day > 39 AND date_diff_day <= 66) AS count_50_to_75,
    COUNTIF(date_diff_day > 66 AND date_diff_day <= 109) AS count_75_to_100
FROM temp

데이터가 1분위, 2분위에 몰려있다. 따라서, 40일 을 기준점으로 삼아야겠다.

2022년 12월 19일 이후 ~ (신규유저)

2022년 10월 3일 이후 ~ 2022년 12월 19일 이전, 10월 3일 이후부터 다음 접속 시간이 40일 이전인 경우 (기존유저)

2022년 10월 3일 이후로부터 40일 이후로 접속한 유저(복귀유저)

2022년 10월 3일 이후로부터 40일 이후로도 접속 이력이 없는 경우 (이탈 유저)

3. 다음 접속일까지의 걸린 일 수 기반, 유저 분류

-- 유저별, 첫번째 로그인 후, 다음 로그인하기 걸린 시간을 4분위 수로 계산
-- 25% | 50% | 75% | 100%
-- 로그인_이벤트: click_login
-- event_date, event_timestamp, event_name,

-- 중간테이블
-- 유저별 로그인을 한 시점을 ROW_NUMBER()로 카운트하기
-- 유저별 첫번째 로그인을 한 시점 구하기 MIN(event_date) OVER()

-- second_event_date가 NULL인 경우는 이탈 유저임.
-- 그런데, 4분위 수를 계산하려면, MAX(second_event_date) 값을 구하고, NULL을 잠시 해당 값으로 채워놔야함.
-- 그 이유는 너무 큰 값으로 채워버리면, 4분위 수 검증이 정확하지 않기 때문임 (mid값과 avg값이 오른쪽으로 치워쳐버리는 현상이 발생할 수 있음) max_event_date: 2023-01-20

WITH base AS
    (SELECT event_date,
            DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')           AS event_timestamp,
            user_pseudo_id,
            event_name,
            ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS login_rn
     FROM advanced.app_logs
     WHERE event_name = 'click_login'
       AND event_date > '2022-10-03'
     QUALIFY login_rn < 3)
   , second_event_data AS
    (SELECT DISTINCT event_date,
                     event_timestamp,
                     user_pseudo_id,
                     event_name,
                     login_rn,
                     IF(second_event_date IS NULL, '2099-01-20', second_event_date) AS second_event_date
     FROM (SELECT *,
                  LEAD(event_date) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS second_event_date
           FROM base))
-- 접속 데이터 기반, 신규, 기존, 복귀, 이탈 구분
   , user_type_data AS
    (SELECT event_date,
            second_event_date,
            user_pseudo_id,
            DATE_DIFF(second_event_date, event_date, DAY) AS comeback_day,
            CASE
                WHEN event_date >= DATE('2022-12-19') THEN '신규 유저'
                WHEN event_date >= DATE('2022-10-03') AND
                     second_event_date <= DATE_ADD(event_date, INTERVAL 40 DAY)
                    THEN '기존 유저'
                WHEN event_date >= DATE('2022-10-03') AND
                     second_event_date > DATE_ADD(event_date, INTERVAL 40 DAY) AND
                     second_event_date != DATE('2099-01-20')
                    THEN '복귀 유저'
                WHEN event_date >= DATE('2022-10-03') AND second_event_date = DATE('2099-01-20')
                    THEN '이탈 유저'
                ELSE NULL -- 예외 처리
                END                                       AS user_type
     FROM second_event_data)

4. 각 유저 타입별 리텐션 계산

기존 유저

-- 일자별 리텐션 계산 (기존 유저)
   , analysis_current AS
    (SELECT diff_of_day,
            COUNT(DISTINCT user_pseudo_id) AS user_cnt
     FROM (SELECT user_pseudo_id,
                  MIN(event_date) OVER (PARTITION BY user_pseudo_id) AS first_day,
                  event_date,
                  comeback_day                                       as diff_of_day
           FROM user_type_data
           WHERE user_type = '기존 유저'
           ORDER BY event_date, second_event_date)
     GROUP BY 1)
SELECT diff_of_day,
       user_cnt,
       first_user_cnt,
       ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 3) AS retention_day_rate
FROM (SELECT diff_of_day,
             user_cnt,
             FIRST_VALUE(user_cnt) OVER (ORDER BY diff_of_day) AS first_user_cnt
      FROM analysis_current)

복귀 유저

신규 유저

이탈 유저

리텐션이 평평하게 안나오네요... 뭔가 단단히 잘못된것 같습니다..