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

Sunny님의 프로필 이미지
Sunny

작성한 질문수

BigQuery(SQL) 활용편(퍼널 분석, 리텐션 분석)

[빠짝스터디 3주차 과제] 리텐션 연습문제

작성

·

49

0

1) Weekly Retention을 구하는 쿼리를 바닥부터 스스로 작성해보세요

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

2) Retain User를 New+Current+Resurrected+DormantUser로 나누는 쿼리를 작성해보세요.

  • 유저 구분 기준은 제품이나 서비스에 따라 다르겠지만 applogs 데이터는 주단위로 했을때 총 24주치 데이터가 있었고, 유저별로 평균 구매 주기와 방문 횟수를 구해보았을 때 6주를 기준으로 유저를 구분하면 될 것이라 생각함.

    • New : 첫 방문한 신규 유저

       

    • Current : 방문 횟수 2회 이상이면서 6주 이내 재 방문 이력 있는 유저

    • Resurrected : 방문 횟수 2회 이상 & 직전 방문 경과 7주 이상이면서 6주 이내 재방문 이력 있는 유저

    • Dormant : 직전 방문 경과 7주 이상이면서 6주 이내 재방문 없는 유저

      image.png

      (사실 이렇게 나누는게 맞는지 모르겠지만, 나름의 논리적인 기준을 찾아보려고 노력하였음. 😫 과제 기한 연장에도 개인적인 사정으로 시간이 부족하여 깊이 탐구하지 못했고, 쿼리 작성을 완성하지 못했습니다 😩 추후에 완성하면 보완하도록 하겠습니다. )

3) 주어진 데이터에서 어떤 사람들이 리텐션이 그나마 높을까요? 찾아보세요

  • 미완성으로 추후에 완성하면 보완하겠습니다 !

4) Core Event를 “click_payment” 라고 설정하고 Weekly Retention을 구해주세요

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 쿼리 작성

Weekly Retention 쿼리를 작성하셨네요. 이 쿼리는 사용자의 첫 주차와 현재 주차 간의 차이를 계산하여 각 주차의 리텐션을 구하는 핵심 로직을 담고 있습니다. 이 쿼리는 DISTINCT 키워드를 사용하여 중복 사용자 ID를 제외하고 계산하는 것이 중요하며, 리텐션 계산 시 SAFE_DIVIDE 함수를 사용해 오류를 방지하는 것도 좋은 방법입니다.

하지만 FIRST_VALUE(COUNT(DISTINCT user_pseudo_id)) OVER(ORDER BY diff_of_week) 부분은 쿼리에서 오류가 발생할 수 있어요. 적절한 COUNT 함수의 사용은 필요하지만, OVER 절 안에서 집계 함수를 바로 사용하는 것은 올바르지 않습니다. 대신 COUNT의 결과를 서브 쿼리에 따로 계산한 후 사용하는 접근이 필요합니다.

User 유형 분류

유저를 New, Current, Resurrected, Dormant로 나누는 것은 복잡할 수 있지만, 각 기준을 명확히 정의해 두는 것이 중요합니다. New는 첫 방문한 유저, Current는 최근 6주 내 재방문, Resurrected는 7주 이상 경과 후 6주 내 재방문, Dormant는 7주 이상 경과하면서 재방문 없는 유저로 구분하셨습니다. 이러한 기준을 기반으로 데이터를 분류하며, 시각화 자료를 통해 각 유형의 수를 비교 분석하면 좋습니다.

Core Event “click_payment” 기준 Weekly Retention

마지막으로 “click_payment” 이벤트를 기준으로 Weekly Retention을 계산하고 있습니다. 이를 위해 필터를 [WHERE event_name = 'click_payment']로 추가하는 것을 잊지 마세요.

유사 질문 및 관련 답변

이 주제와 유사한 질문에 대한 답변들이 인프런에 있습니다. 예를 들어, “3-7 리텐션 SQL 쿼리 작성하기 연습 (Weekly, Monthly)”“3-7. Weekly 리텐션 쿼리 작성 및 질문” 같은 답변들을 참고하시면 도움이 될 것입니다. 이러한 답변들은 쿼리의 가독성과 효율성, 그리고 쿼리 작성 시 주의할 점을 다루고 있습니다.

저는 질문자님의 이해를 돕고자 지식공유자님의 기존 답변을 참고하여 유사한 답변 링크를 추천해드리고 있습니다.
현재 베타 기능으로 답변이 만족스럽지 않을 수 있는 점 양해 부탁드립니다. 🙏
추가적으로 궁금한 점이 있으시면, 이어서 질문해 주세요. 곧 지식공유자께서 답변해 주실 것입니다.

Sunny님의 프로필 이미지
Sunny

작성한 질문수

질문하기