인프런 커뮤니티 질문&답변
작성한 질문수
[인프런 빅쿼리 빠짝스터디 3주차] 리텐션 연습문제
작성
·
110
·
수정됨
0
1번 과제
#weekly retention
WITH base AS(
SELECT
DISTINCT
user_id,
event_name,
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
user_pseudo_id
FROM advanced.app_logs
WHERE event_date BETWEEN '2022-08-01' AND '2022-11-03'
)
, dates AS(
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
)
, first_week_and_diff AS (
SELECT *,
DATE_DIFF(event_week, first_week, week) AS diff_of_week
FROM dates
)
, 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
)
, first_week_user_count AS (
SELECT diff_of_week,
user_cnt,
FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_week_user_cnt
FROM user_counts
WHERE diff_of_week = 0
)
SELECT *, SAFE_DIVIDE(user_cnt, first_week_user_cnt) AS retain_rate
FROM first_week_user_count
2번 과제
New+Current+Resurrected+Dormant User로 나누기
일단 1주일만에 리텐션이 6.8%대이니까, 신규유저의 기준을 7일로 두겠습니다.
리텐션이 반으로 떨어지는 3% 미만이 8주부터, 1% 미만이 12주부터여서 임의로 구간을 세팅하겠습니다.
이에 따라 미접속 8주차부터는 휴면 유저로 보겠습니다.
따라서 8주 뒤 다시 들어온 유저는 복귀 유저로 보겠습니다.
# 신규 유저, 기존 유저, 이탈 유저, 복귀 유저로 나눈 쿼리
# 첫 event 후 7일 안 지난 유저
# event 후 7일 지났고, 마지막 접속으로부터 8주가 안 지난 유저
# event 후 7일이 지났고, 마지막 접속으로부터 8주가 지난 유저
# 첫 event 후 7일이 지났고, 마지막 접속으로부터 8주가 지났다가, 다시 event가 발생한 유저
WITH base AS(
SELECT
DISTINCT
user_id,
event_name,
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
user_pseudo_id
FROM advanced.app_logs
WHERE event_date BETWEEN '2022-08-01' AND '2023-08-31'
)
, dates AS (
SELECT MIN(event_date) AS first_date, MAX(event_date) AS last_date
FROM base
GROUP BY user_pseudo_id
)
SELECT
event_date,
COUNT(DISTINCT CASE WHEN DATE_DIFF(event_date, first_date, DAY) < 8 THEN user_pseudo_id ELSE NULL END) AS new_user,
COUNT(DISTINCT CASE WHEN DATE_DIFF(event_date, first_date, DAY) >= 8 AND DATE_DIFF(event_date, last_date, DAY) < 56
THEN user_pseudo_id ELSE NULL END) AS current_user,
COUNT(DISTINCT CASE WHEN DATE_DIFF(event_date, first_date, DAY) >= 8 AND DATE_DIFF(event_date, last_date, DAY) >= 56
THEN user_pseudo_id ELSE NULL END) AS resurrected_user,
COUNT(DISTINCT CASE WHEN DATE_DIFF(event_date, first_date, DAY) >= 8 END) AS dormant_user
FROM dates
GROUP BY event_date
2번 과제는 이렇게까진 짜보았으나 실패했습니다..
짜보다보니 last_date가 절대로 event_date보다 +가 나올 수가 없더군요...
그래서 그냥 일반 날짜 컬럼을 붙일 수 있나...? 어떻게 해야하지? 하고
이미 짜둔 쿼리에 매몰돼서 구하질 못했네요 ㅎㅎ
혹시 이렇게 짜는 방향에 대해서 조언해주실 점이 있으시면 부탁드리겠습니다..
3번 과제
아래와 같이 퍼널 나눴다.
획득
screen_view, click_login
활성화
click_banner, click_food_category, click_restaurant, click_food,
view_recommend_extra_food, click_recommend_extra_food
click_recommend_food, click_restaurant_nearby
click_search, request_search
수익화 관련
click_cart
click_payment
하지만 각 행동한 유저의 수를 구해보니 아래와 같았다.
recommend 관련 행동을 한 유저가 오히려 더 적었다.
그래서 각 이벤트별로 유저 수를 구해보았고
#event가 click_cart일 때 weekly retention
WITH base AS(
SELECT
DISTINCT
user_id,
event_name,
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
user_pseudo_id
FROM advanced.app_logs
WHERE event_date BETWEEN '2022-08-01' AND '2023-08-31'
AND user_pseudo_id IN (SELECT user_pseudo_id FROM advanced.app_logs WHERE event_name = 'click_cart')
)
, dates AS(
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
)
, first_week_and_diff AS (
SELECT *,
DATE_DIFF(event_week, first_week, week) AS diff_of_week
FROM dates
)
, 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
)
, first_week_user_count AS (
SELECT diff_of_week,
user_cnt,
FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_week_user_cnt
FROM user_counts
)
SELECT *, SAFE_DIVIDE(user_cnt, first_week_user_cnt) AS retain_rate
FROM first_week_user_count
4번 과제처럼 core event를 각각 설정해보았다.
4번 과제의 결과인 click_payment 했던 유저의 리텐션이 역시 제일 좋았었다.
반면 cart를 클릭한 유저의 리텐션은 비교적 조오금 낮았고
반대로 click_search, click_recommend_food, click_restaurant_nearby를 한 유저의 리텐션이 구매자 리텐션과 유사했다.
그래서 기본 행동들에서, 고객이 더 관심 기울여하는 행동인 검색, 추천 서비스 클릭하기 등의 행동을 유도하면 어떨까.. 싶었다! 사실 10% 이상으로 다 비슷하긴 하지만 약간의 차이라도 보이는 것들을 좀 추려보았다.
4번 과제
# click_payment event를 경험한 유저들의 weekly retention
WITH base AS(
SELECT
DISTINCT
user_id,
event_name,
DATETIME(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_datetime,
DATE(TIMESTAMP_MICROS(event_timestamp), 'Asia/Seoul') AS event_date,
user_pseudo_id
FROM advanced.app_logs
WHERE event_date BETWEEN '2022-08-01' AND '2023-08-31'
AND user_pseudo_id IN (SELECT user_pseudo_id FROM advanced.app_logs WHERE event_name = 'click_payment')
)
, dates AS(
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
)
, first_week_and_diff AS (
SELECT *,
DATE_DIFF(event_week, first_week, week) AS diff_of_week
FROM dates
)
, 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
)
, first_week_user_count AS (
SELECT diff_of_week,
user_cnt,
FIRST_VALUE(user_cnt) OVER(ORDER BY diff_of_week) AS first_week_user_cnt
FROM user_counts
)
SELECT *, SAFE_DIVIDE(user_cnt, first_week_user_cnt) AS retain_rate
FROM first_week_user_count
답변 1
0
안녕하세요, 인프런 AI 인턴입니다. Min Choi님이 질문하신 리텐션 연습 문제에 대해 유사한 기존 답변을 찾아보았습니다. 리텐션 분석과 관련된 유사한 문제 해결 방법을 참고하여 문제를 푸는 데 도움이 되는 정보들을 제공하려고 합니다.
리텐션 분석 시 흔히 고려해야 하는 항목이 몇 가지 있는데, 이를 기반으로 작성하신 쿼리에 대해 몇 가지 피드백을 드리겠습니다:
주차별 리텐션 계산의 개념: 리텐션 분석에서는 사용자가 첫째 주부터 몇 주 동안 다시 방문하는지를 측정합니다. 이때,
user_pseudo_id가 주차 내에 중복되더라도 한 번으로 간주되어야 하며, DISTINCT 키워드를 사용해 이를 관리하고 있습니다.유저 구분 기준:
New, Current, Resurrected, Dormant등을 구분하기 위해서는 사용자의 활동 이력 데이터를 적절히 분리하고 해석해야 합니다. Min Choi님이 세운 기준점들은 현실적인 유저 행동 분석에 유용할 수 있습니다.코어 이벤트 기반 분석: 특정 ‘코어 이벤트’를 기준으로 리텐션을 살펴보는 것은 주요 사용자 행동 패턴을 이해하는 데 도움이 됩니다. 이는 고객이 핵심 행동을 유도할 필요성이나 이벤트 후의 유지율을 높이는 전략 구상을 위해 유용합니다.
다양한 케이스의 고려: Min Choi님이 제안한 세그먼트 별 리텐션 등은 비즈니스에서 중요한 지표가 될 수 있습니다. 여기서 각 이벤트 전환 단계의 유저 수를 분석해 그 원인을 심도 있게 파악할 수 있겠습니다.
관련한 더 깊은 논의를 위해, 이전에 다뤄진 답변 중에서 리텐션 관련 문제 해결법을 추천드립니다. 해당 답변에서는 다양한 데이터 분석 기법과 SQL 쿼리 예시들을 통해 리텐션 분석의 기본적인 흐름과 복잡한 상황에 대한 대처법을 설명하고 있습니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.