인프런 커뮤니티 질문&답변
작성한 질문수
[빠짝스터디 3주차 과제] 리텐션 과제
작성
·
52
·
수정됨
0
# 1) Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해보세요
WITH base AS (
SELECT
DISTINCT
user_id,
event_name,
DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
user_pseudo_id
FROM advanced.app_logs
WHERE
event_date BETWEEN '2022-08-01' AND '2023-08-03'
), first_diff_of_week AS (
SELECT
*,
DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
FROM (
SELECT
DISTINCT
user_pseudo_id,
DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
FROM base
)
), user_counts AS (
SELECT
diff_of_week,
COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_diff_of_week
GROUP BY
diff_of_week
)
SELECT
diff_of_week,
user_cnt,
ROUND(SAFE_DIVIDE(user_cnt, first_cnt), 3) AS retention_rate
FROM (
SELECT
*,
FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_cnt
FROM user_counts
)
ORDER BY
diff_of_week# 2) Retain User를 New + Current + Resurrected + Dormant User로 나누는 쿼리를 작성해보세요.
# 신규유저(New) : 제품을 처음 사용하는 유저
# 기존유저(Current) : 제품을 지속적으로 사용하는 유저
# 복귀유저(Resurrected) : 과거에 사용 -> 비활성 -> 다시 제품을 사용한 유저
# 휴면유저(Dormant) : 일정 기간 제품을 사용하지 않은 비활성화 사용자
WITH base AS (
SELECT
DISTINCT
user_id,
DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week,
user_pseudo_id
FROM advanced.app_logs
WHERE
event_date BETWEEN '2022-08-01' AND '2023-08-03'
), user_activity AS (
SELECT
user_pseudo_id,
event_week,
LAG(event_week, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_week) AS prev_week,
MIN(event_week) OVER(PARTITION BY user_pseudo_id) AS first_week
FROM base
), user_classification AS (
SELECT
user_pseudo_id,
event_week,
CASE
WHEN event_week = first_week THEN 'New'
WHEN prev_week IS NOT NULL AND DATE_DIFF(event_week, prev_week, WEEK) = 1 THEN 'Current'
WHEN prev_week IS NOT NULL AND DATE_DIFF(event_week, prev_week, WEEK) > 1 THEN 'Resurrected'
ELSE 'Dormant'
END AS user_type
FROM user_activity
)
SELECT
event_week,
user_type,
COUNT(DISTINCT user_pseudo_id) AS user_count
FROM user_classification
GROUP BY ALL
ORDER BY
event_week,
user_type# 3) 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높을까요?
WITH base AS (
SELECT
DISTINCT
user_id,
DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
DATE_TRUNC(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul'), WEEK(MONDAY)) AS event_week,
user_pseudo_id
FROM advanced.app_logs
WHERE
event_date BETWEEN '2022-08-01' AND '2023-08-03'
), user_activity AS (
SELECT
user_pseudo_id,
event_week,
LAG(event_week, 1) OVER(PARTITION BY user_pseudo_id ORDER BY event_week) AS prev_week,
MIN(event_week) OVER(PARTITION BY user_pseudo_id) AS first_week
FROM base
), user_classification AS (
SELECT
user_pseudo_id,
event_week,
CASE
WHEN event_week = first_week THEN 'New'
WHEN prev_week IS NOT NULL AND DATE_DIFF(event_week, prev_week, WEEK) = 1 THEN 'Current'
WHEN prev_week IS NOT NULL AND DATE_DIFF(event_week, prev_week, WEEK) > 1 THEN 'Resurrected'
ELSE 'Dormant'
END AS user_type
FROM user_activity
), user_counts AS (
SELECT
event_week,
user_type,
COUNT(DISTINCT user_pseudo_id) AS user_count
FROM user_classification
GROUP BY ALL
ORDER BY
event_week,
user_type
)
SELECT
event_week,
DATE_DIFF(event_week, first_week, WEEK) AS weeks_after_first_week,
user_type,
user_count,
cohort_users,
ROUND(SAFE_DIVIDE(user_count, cohort_users), 3) AS retention_rate
FROM (
SELECT
DATE(event_week) AS event_week,
DATE(MIN(event_week) OVER()) AS first_week,
user_type,
user_count,
MAX(CASE WHEN user_type = 'New' THEN user_count END) OVER(PARTITION BY DATE(event_week)) AS cohort_users
FROM user_counts
ORDER BY
event_week,
CASE user_type
WHEN 'New' THEN 1
WHEN 'Current' THEN 2
WHEN 'Resurrected' THEN 3
WHEN 'Dormant' THEN 4
END
)
2022-08-01 ~ 2023-08-03 기간으로 설정하여, 개인적인 의견으로 Retain User를 쪼개 보았습니다.
배달어플은 유저들의 사용 간격이 짧다고 생각하기 때문에, 주의 주기로 리텐션으로 설정했습니다. 저는
바로 전 주에 사용했고, 그 다음주에 사용했다면 기존유저(Current)로 설정했습니다.
2주 이상 텀을 두고 다시 사용했다면 복귀유저(Resurrected)로 설정했습니다.
처음 들어가고 사용하지 않았다면 휴면유저(Dormant)로 설정했습니다.
주차마다 '신규/기존/복귀/휴면' 유저를 새로 계산하는 쿼리입니다.
데이터를 살펴보면,
1) 신규유저(New) : 제품을 처음 사용하는 유저
출시(2022-08-01을 출시기간으로 생각하면)로부터 현재(2023-01-16 => 0803으로 설정했지만 0116까지가 끝입니다.)까지의 신규유저 수의 변화를 살펴보겠습니다.
런칭부터 10주까지의 신규유저의 수는 주춤한 경우도 있지만 평균 4~500명 정도 증가했습니다.
하지만, 11주에 4000명 대에서 3100명으로 900명이 급감한 이후로 계속 하락세를 타고 24주차 현재 신규 가입자의 수는 500명 남짓하고 있습니다.
11주차부터의 마케팅 효과는 빛을 보지 못하고 있는 것 같습니다.
코호트 리텐션 시각화를 보자면,
2) 기존유저(Current) : 제품을 지속적으로 사용하는 유저(바로 전 주와 이번 주)
출시로부터 현재까지 줄곧 주차마다의 신규유저 대비 기존유저의 비율이 증가하고 있습니다.
특히, 24주차에 가입한 신규유저 대비 기존유저의 비율은 76%나 차지하고 있습니다.
시간이 흐름에 따라 점차 어플이 안정화되면서 신규유저들이 만족을 느끼는 비율이 늘어가고 있다고 보입니다.
3) 복귀유저(Resurrected) : 과거에 사용 -> 비활성화 -> 다시 제품을 사용한 유저(2주 후 복귀)
복귀유저는 12주차를 분기로 13주차부터는 신규가입자보다 복귀유저의 수가 많아지기 시작했습니다.
24주차에는 신규유저 대비 복귀유저는 6배가 많습니다.
복귀유저가 기존유저로 안정되는 현황을 추출할 수 있는 쿼리는 조금 더 연구해 보겠습니다.
4) 휴면유저(Dormant) : 일정기간 제품을 사용하지 않은 비활성화 사용자
신규유저 대비 휴면유저는 꾸준히 늘어가고 있습니다(휴면유저가 누적됨).
24주차 신규가입자의 5.7배가 휴면유저 입니다.
# 4) Core Event를 'click_payment'라고 설정하고 Weekly Retention을 구해주세요
WITH base AS (
SELECT
DISTINCT
user_id,
DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul')) AS event_date,
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
user_pseudo_id
FROM advanced.app_logs
WHERE
event_date BETWEEN '2022-08-01' AND '2023-08-03' AND
event_name = 'click_payment'
), first_week_and_diff AS (
SELECT
*,
DATE_DIFF(event_week, first_week, WEEK) AS diff_of_week
FROM (
SELECT
DISTINCT
user_pseudo_id,
DATE_TRUNC(MIN(event_date) OVER(PARTITION BY user_pseudo_id), WEEK(MONDAY)) AS first_week,
DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week
FROM base
)
), user_counts AS (
SELECT
diff_of_week,
COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM first_week_and_diff
GROUP BY
diff_of_week
ORDER BY
diff_of_week
)
SELECT
diff_of_week,
user_cnt,
ROUND(SAFE_DIVIDE(user_cnt, cohort_users), 3) AS retention_rate
FROM (
SELECT
*,
FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS cohort_users
FROM user_counts
)
ORDER BY
diff_of_week
답변 1
0
안녕하세요, 인프런 AI 인턴입니다. song님의 질문에 대한 도움이 될 수 있는 이전 답변 및 유사한 사례를 제공하도록 하겠습니다.
1. Weekly Retention 쿼리
song님께서 작성하신 Weekly Retention 쿼리는 기본적으로 사용자의 첫 번째 주 사용 기록을 기준으로 그 이후 주마다의 사용자 수를 비교하여 리텐션을 계산하였습니다. 유사한 방법으로 리텐션을 분석하는 예시는 이전에 3-7. 리텐션 SQL 쿼리 작성하기 : Weekly Retention에서 확인할 수 있습니다. 기존 답변에서는 고급 전략을 추천하여 데이터의 중복을 최소화하고, 최적화된 쿼리 구조를 갖추도록 지원하고 있습니다.
2. 사용자 유형 분류 쿼리
사용자 분류 쿼리는 일반적으로 사용자의 활동 주기에 기반하여 분류됩니다. 이는 곧 사용자들의 행동을 기준으로 선택된 조건에 따라 ‘신규(New)’, ‘현재(Current)’, ‘복귀(Resurrected)’, ‘휴면(Dormant)‘으로 구분합니다. song님께서 작성한 쿼리는 이러한 기준을 잘 반영하고 있으며, 특히 ‘Prev_week’를 이용한 조건식이 적절히 사용된 것 같습니다. 반면, 유사한 사례로 리텐션 과제_연습문제 2번에서 추가적으로 복귀 유저를 구분할 수 있는 구체적인 접근법이 설명되어 있으니 참조하시기 바랍니다.
3. 전체 리텐션 분석과 시각화
song님이 제공하신 코드를 사용하여, 리텐션 분석 결과에서 사용자의 구체적인 행동 패턴을 도출하였고 시각화를 통해 인사이트를 얻으려는 노력은 굉장히 유익합니다. 그러나 각 유형의 유저 수를 계산 후, 시각화된 데이터가 실제 영향력을 충분히 반영하고 있는지 점검해보는 것도 좋습니다. 특히 저희 코호트 분석에서 누락되거나 과대평가된 데이터를 식별하는 방법에 대한 조언이 제공된 리텐션 연습 문제를 참고해 보시길 권장드립니다.
4. Core Event(‘click_payment’)를 기준으로 한 Weekly Retention
song님께서 Core Event를 ‘click_payment’로 설정하고 리텐션을 분석한 부분에 대해선, 관련된 접근법이 이전의 리텐션 과제_연습문제 2번에서 ‘click_payment’ 이벤트를 중심으로 수익에 관련한 코호트 분석을 다루고 있습니다. 이와 같이 경제적 이벤트 중심의 분석은 사용자 유지 전략을 강화하는데 도움이 됩니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다. 현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏 추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.