해결된 질문
24.07.24 01:19 작성
·
173
0
안녕하세요. 연습문제 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 이라는 이벤트가 발생했을 경우
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이기 때문에, 해당 앱은 자동 로그인이 되지 않음을 알 수 있다.
즉, 사용자가 앱에 접속하면, 가입을 했더라도, 무조건 로그인을 해야한다. 따라서, 사용자가 앱에 접속 시, 로그인 이벤트가 무조건 발생한다.
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일 이후로도 접속 이력이 없는 경우 (이탈 유저)
-- 유저별, 첫번째 로그인 후, 다음 로그인하기 걸린 시간을 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)
-- 일자별 리텐션 계산 (기존 유저)
, 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)
리텐션이 평평하게 안나오네요... 뭔가 단단히 잘못된것 같습니다..
답변 4
0
2024. 07. 30. 22:08
# 첫 사용이 활동 주차일 경우 new
# 1주 전에도 사용한 경우 current
# 첫 사용한지 2주가 지났고, 이전 사용과 지금 사용이 2주가 지났으면 resurrected
# 그 외엔 이탈로 간주해서 dormant
이런 조건으로 만들어보면 되어요! 이 부분은 추후에 이야기 또 나누어요.
0
2024. 07. 25. 17:39
안녕하세요!자동 로그인에 대해 검증해서 항상 로그인을 해야한다는 것을 캐치한 부분도 잘하셨어요. 이런 고민을 가지고 데이터가 진짜 맞는지 확인해보는 과정이 필요해요.
다만 더 쉽게 한다면, 로그인 여부 상관없이 앱 로그에 데이터가 저장되고 있다면, 활동했다라고 볼 수 있어요. event_date와 user_pseudo_id를 기준으로 DISTINCT를 하면 이 유저가 언제 활동했는지 알 수 있지요
SELECT
DISTINCT
event_date,
user_pseudo_id
FROM advanced.app_logs
WHERE event_date BETWEEN "2022-08-01" AND "2022-12-01"
ORDER BY user_pseudo_id, event_date
위 쿼리를 실행해보면 일자별로 유저가 언제 활동했는지 알 수 있게 됩니다. 또한 다음 로그인까지 걸리는 시간을 사용할 수도 있지만, 위 쿼리를 실행한 후에 event_date를 순서대로 정렬해서 각 차이를 구해보면 되어요. 그러면 다시 로그인하기까지 걸리는 시간을 확인할 수 있지요.
지금 쿼리에선 user_type을 WHERE 조건에 설정하고 쿼리를 구하시는데, user_type을 SELECT 절에 넣고 4개의 타입에 대한 데이터를 한번에 구해볼 수 있을거에요.
리텐션을 구하기 전에 주차별로 New, Current, Dormant, Resurrected User가 몇 명인지를 구해보는 쿼리를 작성한다면 어떻게 작성해야 할까요?
이걸 작성하시면 리텐션 작성이 더 수월해질 것 같아 질문드려요!
2024. 07. 30. 19:59
먼저, 리텐션을 구하기 전에, 주차별로 New, Current, Dormant, Resurrected User가 몇 명인지 구하는 쿼리를 작성해보고, 리텐션을 구해보았습니다.
로그인 여부 상관없이, 카일스쿨
선생님 말씀처럼, 앱 로그에 데이터가 저장되고 있다면, 활동했다.(Active)하다라고 볼 수 있습니다.
new_user를 구분하는 기준이 필요한데, LAG()
로 최근 로그인 시간이 없다면, 신규 유저라고 구분을 지으려 했으나, 해당 데이터는 모든 유저가 최근 로그인 시간이 있기 때문에, 다음과 같이 나누었습니다.
전체 데이터 기간에 대하여,
신규유저: "2022-08-01" ~ "2022-10-01" 에 해당하는 유저
복귀유저: 사용자가 14일 이상 활동하지 않다가, 다시 활동한 경우
휴면 유저: 현재 이벤트 발생 시점에서, 14일 이상 활동하지 않는 경우
기존 유저: 위의 조건에 해당하지 않는 모든 사용자
WITH all_user_activity AS (
SELECT
user_pseudo_id,
MIN(event_date) AS first_activity_date
FROM
advanced.app_logs
GROUP BY
user_pseudo_id
),
filtered_user_activity AS (
SELECT DISTINCT
event_date,
user_pseudo_id,
LAG(event_date, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS last_login,
LEAD(event_date, 1) OVER (PARTITION BY user_pseudo_id ORDER BY event_date) AS since_end_login
FROM
advanced.app_logs
),
base AS (
SELECT
*,
DATE_DIFF(event_date, last_login, DAY) AS comback_day,
DATE_DIFF(since_end_login, event_date, DAY) AS since_end_date
FROM
filtered_user_activity
),
user_type_classification AS (
SELECT
a.*,
CASE
WHEN b.first_activity_date BETWEEN '2022-08-01' AND '2022-10-01' THEN 'new_user'
WHEN comback_day > 14 THEN 'resurrected_user'
WHEN since_end_date > 14 THEN 'dormant_user'
ELSE 'current_user'
END AS user_type
FROM
base a
JOIN
all_user_activity b ON a.user_pseudo_id = b.user_pseudo_id
)
SELECT
user_type,
COUNT(*) AS count
FROM
user_type_classification
GROUP BY
user_type
ORDER BY
count DESC;
이후에, Weekly Retention 쿼리를 작성했습니다. 특징점은 user_type에 new_user .. 등이 String으로 들어가 있어, MAX(CASE(WHEN))문을 사용해서, 새로운 컬럼으로 뽑고, retention_ratio를 작성했습니다.
NULL값이 있어서, 값이 비면, 그래프가 끊겨서 나와서 LAST_VALUE( IGNORE NULLS)를 사용해서 NULL값이 있으면 그 전의 값을 가져와서 채우도록 구성하였습니다.
first_date_and_diff AS (
SELECT
DISTINCT
user_pseudo_id,
user_type,
DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id, user_type), WEEK(MONDAY)) AS first_week,
DATE_TRUNC(event_date, WEEK(MONDAY)) AS event_week,
DATE_DIFF(DATE_TRUNC(event_date, WEEK(MONDAY)), DATE_TRUNC(MIN(event_date) OVER (PARTITION BY user_pseudo_id, user_type), WEEK(MONDAY)), WEEK) AS diff_of_week
FROM
user_type_classification
),
analysis AS (
SELECT
diff_of_week,
user_type,
user_cnt,
FIRST_VALUE(user_cnt) OVER (PARTITION BY user_type ORDER BY diff_of_week) AS first_user_cnt
FROM (
SELECT
diff_of_week,
user_type,
COUNT(DISTINCT user_pseudo_id) AS user_cnt
FROM
first_date_and_diff
GROUP BY
diff_of_week, user_type
)
),
retention_by_type AS (
SELECT
diff_of_week,
MAX(CASE WHEN user_type = 'current_user' THEN ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 3) END) AS retention_week_rate_current_user,
MAX(CASE WHEN user_type = 'dormant_user' THEN ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 3) END) AS retention_week_rate_dormant_user,
MAX(CASE WHEN user_type = 'resurrected_user' THEN ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 3) END) AS retention_week_rate_resurrected_user,
MAX(CASE WHEN user_type = 'new_user' THEN ROUND(SAFE_DIVIDE(user_cnt, first_user_cnt), 3) END) AS retention_week_rate_new_user
FROM
analysis
GROUP BY
diff_of_week
)
SELECT
diff_of_week,
LAST_VALUE(retention_week_rate_current_user IGNORE NULLS) OVER (ORDER BY diff_of_week ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS retention_week_rate_current_user,
LAST_VALUE(retention_week_rate_dormant_user IGNORE NULLS) OVER (ORDER BY diff_of_week ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS retention_week_rate_dormant_user,
LAST_VALUE(retention_week_rate_new_user IGNORE NULLS) OVER (ORDER BY diff_of_week ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS retention_week_rate_new_user,
LAST_VALUE(retention_week_rate_resurrected_user IGNORE NULLS) OVER (ORDER BY diff_of_week ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS retention_week_rate_resurrected_user
FROM
retention_by_type
ORDER BY
diff_of_week;
2024. 07. 30. 20:17
그래프는 다음과 같습니다.
신규 유저의 경우 1주차 때, 급격한 리텐션 감소가 있습니다. 사용자의 초기 경험이 유저들에게 매력적이지 못한 듯 보입니다.
2주차부터 리텐션이 상승 추세를 보입니다. 15주차까지 리텐션이 상승 추세를 보이기 때문에, 2주차 ~ 15주차까지 남아있는 유저의 행동을 분석해볼 필요가 있어 보입니다.
휴면 유저가 2주차에 소폭 상승하는 경향을 보이나, 주차가 지날수록 리텐션이 0에 가까워지고 있습니다. 다시 해당 컨텐츠에 참여했으나, 변화한 점이 없어서 실망하고, 서비스를 떠나는 듯 보입니다.
기존 유저 또한 2주차부터 조금씩 이탈하는 추세를 보입니다. 하지만 이탈하는 추세의 기울기가 작아서, 3주차 ~ 10주차까지를 평평하다고 봐도 될 것 같습니다. 해당 주차에 속한 고객층이 어떤 콘텐츠에 만족을 느끼는지 분석이 필요해보입니다.
2주차 이후 소폭 증가하다가 3주차 ~ 10주차 사이에 유저가 이탈하고 있습니다. 10주차 이후로는 평평한 구간을 보이는데, 10주차 이후의 복귀유저 고객층이, 어떤 이유로 서비스를 계속 사용하고 있는지 분석이 필요해보입니다.
0
2024. 07. 24. 12:04
오 문제 푸느라 고생하셨어요. 이 문제가 처음엔 어려울 수 있지만, 한번 익혀두면 회사에서 잘 사용할 수 있는 부분이에요. 제가 오후에 계속 일정이 있어서, 내일 중(7/25)으로 답변 드릴게요!
일단 바로 말씀드릴 수 있는 부분은 위에 유저 수를 파악하는 부분은 주차별로 해주셨는데, 아래는 데일리 리텐션이네요. 요것도 기준 맞추면 좋을거에요. 이건 추후 답변 드릴 때 또 말씀드릴게요!
0
안녕하세요, 인프런 AI 인턴입니다. minsubrother님의 질문에 대한 직접적인 답변을 제공하기 위해, 유사한 질문 및 해결 방법을 찾아본 결과 아래의 내용을 찾아냈습니다.
질문에 대한 해결 방법은 이미 제시된 4-8. 지표 정의 연습 문제 풀이 답변 올려볼게요!에서 찾을 수 있습니다. 이 답변은 다양한 사례와 함께 데이터 분석 과정을 통해 지표를 정의하고, 결과를 분석하는 방법을 포함하고 있습니다. 특히, 어떻게 데이터를 분리하고, 집계하여 인사이트를 도출할 수 있는지에 대한 깊이 있는 토론이 포함되어 있습니다.
minsubrother님께서 진행하신 리텐션 분석 및 오류를 수정하거나 더 나은 결과를 도출하는데 있어, 이 답변이 해결책의 일부가 될 수 있을 것입니다. 특히, 유저 분류와 리텐션 계산을 위한 SQL 쿼리 예시, 그리고 이를 통해 얻은 결과의 해석 방법에 대한 섹션이 유익할 것입니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.
2024. 07. 25. 17:41
제가 입력한 내용과 다르게 기록이 되네요.. 흐음 엔터 등이 깨지네요. 이거 수정되면 다시 보기 좋게 댓글 달아드릴게요인프런에 오류 제보해서 수정해주셔서 엔터 반영해서 작성했어요!