작성
·
14
0
WITH base AS (
SELECT
DISTINCT
user_id,
user_pseudo_id,
event_name,
DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime,
DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul")) AS event_date
FROM `advanced.app_logs`
), 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
)
)
SELECT
diff_of_week,
COUNT(DISTINCT user_pseudo_id) AS user_cnt,
ROUND(SAFE_DIVIDE((COUNT(DISTINCT user_pseudo_id)), (FIRST_VALUE(COUNT(DISTINCT user_pseudo_id)) OVER(ORDER BY diff_of_week))), 3) AS retention
FROM first_week_and_diff
GROUP BY diff_of_week
ORDER BY diff_of_week
유저 구분 기준은 제품이나 서비스에 따라 다르겠지만 applogs 데이터는 주단위로 했을때 총 24주치 데이터가 있었고, 유저별로 평균 구매 주기와 방문 횟수를 구해보았을 때 6주를 기준으로 유저를 구분하면 될 것이라 생각함.
New : 첫 방문한 신규 유저
Current : 방문 횟수 2회 이상이면서 6주 이내 재 방문 이력 있는 유저
Resurrected : 방문 횟수 2회 이상 & 직전 방문 경과 7주 이상이면서 6주 이내 재방문 이력 있는 유저
Dormant : 직전 방문 경과 7주 이상이면서 6주 이내 재방문 없는 유저
(사실 이렇게 나누는게 맞는지 모르겠지만, 나름의 논리적인 기준을 찾아보려고 노력하였음. 😫 과제 기한 연장에도 개인적인 사정으로 시간이 부족하여 깊이 탐구하지 못했고, 쿼리 작성을 완성하지 못했습니다 😩 추후에 완성하면 보완하도록 하겠습니다. )
미완성으로 추후에 완성하면 보완하겠습니다 !
WITH base AS (
SELECT
DISTINCT
user_id,
user_pseudo_id,
event_name,
DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul") AS event_datetime,
DATE(DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Seoul")) AS event_date
FROM `advanced.app_logs`
), 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
)
)
SELECT
diff_of_week,
COUNT(DISTINCT user_pseudo_id) AS user_cnt,
ROUND(SAFE_DIVIDE((COUNT(DISTINCT user_pseudo_id)), (FIRST_VALUE(COUNT(DISTINCT user_pseudo_id)) OVER(ORDER BY diff_of_week))), 3) AS retention
FROM first_week_and_diff
GROUP BY diff_of_week
ORDER BY diff_of_week
답변 1
0
안녕하세요, 인프런 AI 인턴입니다. Sunny님께서 주신 질문에 대한 답변을 드리겠습니다. 리텐션 분석을 수행하면서 생기는 다양한 문제와 SQL 쿼리 작성에 대한 고민이 담겨 있는 것 같아요.
Weekly Retention 쿼리를 작성하셨네요. 이 쿼리는 사용자의 첫 주차와 현재 주차 간의 차이를 계산하여 각 주차의 리텐션을 구하는 핵심 로직을 담고 있습니다. 이 쿼리는 DISTINCT
키워드를 사용하여 중복 사용자 ID를 제외하고 계산하는 것이 중요하며, 리텐션 계산 시 SAFE_DIVIDE
함수를 사용해 오류를 방지하는 것도 좋은 방법입니다.
하지만 FIRST_VALUE(COUNT(DISTINCT user_pseudo_id)) OVER(ORDER BY diff_of_week)
부분은 쿼리에서 오류가 발생할 수 있어요. 적절한 COUNT 함수의 사용은 필요하지만, OVER 절 안에서 집계 함수를 바로 사용하는 것은 올바르지 않습니다. 대신 COUNT의 결과를 서브 쿼리에 따로 계산한 후 사용하는 접근이 필요합니다.
유저를 New, Current, Resurrected, Dormant로 나누는 것은 복잡할 수 있지만, 각 기준을 명확히 정의해 두는 것이 중요합니다. New는 첫 방문한 유저, Current는 최근 6주 내 재방문, Resurrected는 7주 이상 경과 후 6주 내 재방문, Dormant는 7주 이상 경과하면서 재방문 없는 유저로 구분하셨습니다. 이러한 기준을 기반으로 데이터를 분류하며, 시각화 자료를 통해 각 유형의 수를 비교 분석하면 좋습니다.
마지막으로 “click_payment” 이벤트를 기준으로 Weekly Retention을 계산하고 있습니다. 이를 위해 필터를 [WHERE event_name = 'click_payment']
로 추가하는 것을 잊지 마세요.
이 주제와 유사한 질문에 대한 답변들이 인프런에 있습니다. 예를 들어, “3-7 리텐션 SQL 쿼리 작성하기 연습 (Weekly, Monthly)”나 “3-7. Weekly 리텐션 쿼리 작성 및 질문” 같은 답변들을 참고하시면 도움이 될 것입니다. 이러한 답변들은 쿼리의 가독성과 효율성, 그리고 쿼리 작성 시 주의할 점을 다루고 있습니다.
저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.