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

sddyyy1004님 안녕하세요!

과제 푸느라 고생하셨습니다

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

• first_date_table에 작성해주신 부분에 대해 이야기를 하고 싶어요.

  SELECT 
    user_pseudo_id,
    MIN(event_date) OVER(PARTITION BY user_pseudo_id ORDER BY event_datetime) as first_date,
    event_date
  FROM base

• 이렇게 작성해주셨는데, user_pseudo_id가 특정 일자(event_date)에 여러번 접속을 했다면 여러 Row가 존재해요.

  • 예를 들어 a라는 유저가 2024-10-01에 4번 접속했다면, 4개의 row가 존재할거에요. base에서는 DISTINCT로 event_timestamp 기점으로 하기 때문에 더 세밀한 시간대를 사용하지요. 그러나 first_date_table에서는 여러 row가 있을 수 있어요(event_date를 쓰기에)

  • 따라서 first_date_table에서 SELECT DISTINCT user_pseudo_id ~ 해주는게 중복을 제거할 수 있어 더 좋습니다

  • 위 내용은 Daily 리텐션 구할 때도 활용했어요

• first_date_table이 중복이 존재하기 때문에, diff_week_table도 여러 row가 존재하게 됩니다. 다만 weekly_user_cnt에선 COUNT(DISTINCT를 사용하기 때문에 중복이 제거되어서 값에 이슈는 적을 수 있을거에요

  • 문제에서는 JOIN이 없어서 큰 이슈가 되지 않지만, 만약 JOIN이 많은 문제가 있었다면 데이터가 뻥튀기 되었을 수 있어요. 내가 짜는 쿼리가 중복이 제거되었나를 항상 확인해보셔요

• 1번 문제의 의도는 Weekly 리텐션 그래프를 그려보고 해석하는 것까지 포함됩니다. 해석도 해보셔요

• date_diff에 abs를 사용하셧던떼, date_diff가 week일 땐 소수점이 나오지 않았던 것 같은데 사용하신 의도가 있을까요?

2. retain user를 new+current+resurrected+dormant user 로 나누는 쿼리 작성해보세요.

• 사전 EDA에서 지난 30일 간이라고 했는데, 쿼리를 보니 헷갈린 부분이 있어요

• cte에서 first_date로 작성되어 있는데 last_date라고 해야하지 않을까요?

  • 그 밑에 last_user_30_days라고 되어있는걸 보니 first_date.date - 30일 ~ first_date.date라고 되어있어서 처음에 "첫 접속일에서 30일 전을 구하셔서 데이터를 뽑으셨네? 첫 접속일 30일 전엔 로그가 없을텐데?"라는 생각이 들었네요. 다시 보니 처음에 first_date라고 되어있는데 제 인식의 first_date가 first가 아니라서 생긴 오류더라구요.

  • 네이밍을 잘 하는게 중요해요. first_date인데 문법은 max => ?!

  • 그 이후는 다 first_date로 되어있네요. 회사였으면 이게 계속 커뮤니케이션 코스트를 증대시켰을 것 같네요

• 사전 EDA에서 단순히 쿼리만 작성하는게 아니라, 결과를 보고 간단하게 해석을 해보면 좋겠어요. 쿼리만 작성하는 것이 아니라 실제로 이 데이터를 해석하고 생각하는 것이 제일 중요하고 어려운 역량이라 이것도 해보시면 좋겠어요(여기선 안드로이드가 6배 많다만 하셔서..!)

• 세션 EDA

  • 평균 시간 36초, 세션 수 1.24개라고 하셨는데 여기서 분포도 같이 보는 것을 추천드려요. 분포가 왼쪽으로 쏠렸는지, 우측으로 쏠렸는지 등도 알아두면 정보가 더 많아져서 이 분포도 봐보셔요. 쿼리는 잘 작성하셨네요

  • 어떤 시간, 일자에 많이 발생했는가는 우리가 음식을 언제 먹는가? 생각하면 점심보다 저녁을 시켜서 반영된 것이에요. 이 데이터를 더 본다면 저라면 평일/주말 + 시간대별로 주문하는 수가 얼마나 다른지 볼 것 같네요. 다만 이 때는 꼭 세션으로 보지 않고 다른 유저 로그 데이터를 사용해도 괜찮을거에요

• TOP Event

  • 이건 user flow상 뒤에 있는게 더 적은 event가 발생하는게 당연합니다.

  • 여기서 얻어낼 수 있는 것은 무엇일까요?

• 그리고 보다보니 최근 30일만 사용하시는데 이유가 있을까요?

  • 앱을 사용할 때 리테인되냐는 초반에 경험이 쌓여서 생기는건데, 최근 데이터만 보셔서 질문드려봅니다

제품 사용 주기 파악

• 핵심 이벤트를 접속으로 하는 것도 방법인데, 음식을 주문하기 위해 쓰는 서비스이므로 이런 내용과 연계해서 핵심 이벤트를 정의하는 것도 좋습니다.

• 위 내용을 들으면 어떤 핵심 이벤트를 정의하시겠어요?

• 인프콘 2024에서 발표했던 내용도 보시면 도움이 될 것 같아요 => https://youtu.be/axZLFQjrFKM?si=r4NkqCWjkfHkcor-

• 유저가 핵심 이벤트를 처음 수행한 시점에서부터 두번째로 수행하기 까지 얼마만큼의 시간이 걸리는지 측정하세요. (제일 많은 차이는 4일) (쿼리 검토 요청)

  • 여기서 rank_1_2_date는 두번째 발생 이벤트가 기록되나요?

  • qualify에 rank_1=1만 있어서 맞나? 생각이 드네요

  • 저라면 핵심 이벤트를 발생한 로그를 하루 일자별로 구한 후에 윈도우 함수로 다음 접속 여부를 확인해서 다음 접속 여부가 없다면 제외하도록 구현할 것 같아요. 그러면 쿼리가 더 짧아질 것 같네요

• 유저 구분에서 new에 diff_week <=4라고 해주셨는데 diff_day <= 4가 아닐까 싶어요. diff_week <= 4면 diff_month=1과 동일해서 new와 current가 동일하게 나올거에요

  • 쿼리를 보면 diff_day라고 해주셨는데, 그럼 diff_day가 5, 6인 사람들의 데이터는 어디에 속할까요? 지금 쿼리에선 resurrect로 가지 않나 싶어요

  • 저는 diff_week, diff_month를 사용해야 하면 하나의 기준을 사용하는 것을 선호해요. 날짜가 어떻게 되냐에 따라 diff_week이 차이가 나고, diff_month가 차이가 나지 않는 경우도 있거든요(월이 변하는 시점)

  • current, dormant, new, resurrected에선 쿼리의 실수가 있는 것 같네요.

  • 이 케이스에서 제가 정확히 파악은 어렵고, 저는 이런 상황에 집계한 데이터에서 resurrected라고 나온 유저들을 보고 QA를 할 것 같네요. 눈으로 데이터를 보면 어? resurrected가 아니어야 하는데? 라는 사람이 있을 것 같아요.

SELECT 
  * 
FROM (
  SELECT 
    *,
    (CASE WHEN diff_day <= 4 THEN 'New' 
         WHEN diff_month = 1 THEN 'current'
         WHEN diff_month > 1 THEN 'dormant'
         ELSE 'resurrected'
         END) as user_group,
  FROM diff_table
  )
WHERE user_group = "resurrected" 

전 이렇게 확인해봤어요. 확인해보니 diff_day가 5인 값부터 22인 값도 나오네요. 즉, 현재 user_group은 MECE하게 나뉘지 않았네요. diff_month가 0일 때 나올 수 있는 값이 0~31인데 0~4만 처리하신거죠.

user_group에서 실수 : diff_day, diff_month를 다 사용하다보니 헷갈려져서 누락된 값이 resurrected로 나오고 있음

저의 추천

• COUNT를 할 때 CASE WHEN을 하지 마시고 원본 데이터에서 CASE WHEN으로 우측 컬럼 추가하시고 집계하시는 것을 추천해요. 그래야 디버깅이 편합니다

제가 유저 그룹을 구할 때는 다음과 같은 로직을 사용했었어요. 계산을 위해 주차를 사용했고, 주차냐 월이냐를 생각하는 것이 아니라 쿼리에서 어떻게 구성했는지를 보시면 되어요. dormant는 휴먼 유저라서 최근에 접속 정보가 없을거고, resurrected는 접속 정보가 없다가 (꽤 텀이 길다가) 다시 들어온 케이스라고 보시면 되어요. 아래 내용은 다른 분의 과제에서 작성해두었어요.

https://www.inflearn.com/community/questions/1357128/%EB%A6%AC%ED%85%90%EC%85%98-%EB%B6%84%EC%84%9D-%EA%B3%BC%EC%A0%9C

), weekly_user_type AS (
  # 첫 사용이 첫 주차일 경우 new
  # 1주 전에도 사용한 경우 current
  # 첫 사용한지 2주가 지났고, 이전 사용과 지금 사용이 2주가 지났으면 resurrected
  # 그 외엔 이탈로 간주해서 dormant
  SELECT
    *,
    CASE
      WHEN event_week = first_activity_week THEN 'new_user'
      WHEN DATE_DIFF(event_week, prev_activity_week, WEEK) = 1 THEN 'current_user'
      WHEN DATE_DIFF(event_week, first_activity_week, WEEK) > 2 AND DATE_DIFF(event_week, prev_activity_week, WEEK) > 2 THEN 'resurrected_user'
      ELSE 'dormant_user'
  END AS user_type
  FROM weekly_user_activity
)

위 내용이 3번의 문의사항의 답변이 될 것 같네요.

2) 추가적으로 사용자가 활동한 날짜의 차이에 따라 코호트를 구분하는 방법 외 코호트를 구분할 수 있는 다른 방법도 있을지 팁이 있으면 말씀주시면 참고해서 구분해보도록 하겠습니다.

• 사용자의 특정 로그(어떤 것을 검색한 사람과 아닌 사람)

• 사용 패턴별(이건 추가 EDA를 많이 해야겠지요)

• 연령대별(연령대 정보는 없으니 지금 로그에선 불가능)

• 가설을 만들고 그에 기반해 만든다고 보시면 될 것 같네요

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

• 인사이트 1~3번으로 주신 부분을 PO, PM분들이 받아보시면 아마 이 반응일거에요

  • 네, 그거 다 당연한 내용이고 그래서요?

  • Action Item이나 뾰족한 것이 필요한데, 데이터 분석의 결과로 PMF를 달성해야 합니다! 추가 분석을 해야 합니다!라는 말은 당연하게 느껴져요. PMF를 찾는 과정에서 우리가 잘 진행했는가? 또는 2-6주차에 재유입된 사용자는 왜 재유입이 되었을까? 라는 내용을 알려줘야 보는 분들이 구체적으로 어떤 것을 해야하는지 영감이 떠오를거에요

  • 이 부분 관점으로 더 생각해보시면 좋을 것 같아요.

4번은 위에 쿼리 피드백 드린 것에 모두 포함이 되어서 통합해서 보시면 될거에요. 이벤트를 필터링하는 관점의 차이만 존재해요.

정리해서 말씀드리면

• 쿼리에서 네이밍을 잘 표현하기

• 그래프를 그려보고, 분포도 그려보고 데이터를 더 많이 해석하기(지금은 쿼리를 작성하는 것에 집중을 하신 느낌이 살짝 들어요)

• 구체적인 Action Item을 생각하기 위해 가설을 만들고 그 가설 기반으로 분석해보기

• user_group 나누는 과정에서 다시 시도해보셔요. 데이터의 누락이 발생해서 생긴 이슈라 데이터 QA를 해보는 것을 고민해보셔요(입문편 데이터 결과 검증을 수강하지 않으셨더라구요. 이 부분을 보시면서 어떻게 값을 맞출 수 있는지 공부해보셔요. 이 값이 왜 그럴까요?라는 관점에서 스스로 분석해보는 과정이 필요해요)

• 쿼리는 UNNEST도 잘 사용하시는 것 같아서, 중복 제거하거나 몇가지 말씀드린 부분만 인지하시면 될 것 같아요

안녕하세요! 과제를 자세히 봐야해서 약간의 시간이 걸릴 것 같네요..!

다음주 화요일 이내까지 완료해볼게요. 공유해주셔서 감사합니다! 🙏

안녕하세요, 인프런 AI 인턴입니다. sddyyy1004님!

리텐션 분석 과제를 완료하셔서 제출하셨군요. 링크를 통해 제공한 자료를 검토하며, 과제의 쿼리 검토 및 문의사항에 대한 도움을 드릴 수 있도록 노력하겠습니다. 관련하여 유사한 질문과 답변이 있는 게시물들을 찾아보았으니 참고해 보시면 좋을 것 같습니다.

1. 리텐션 과제 작성: 이 게시물에서는 리텐션 쿼리에 대한 일반적인 피드백과 해결 방법이 제시되어 있습니다. 특히 쿼리에서 주의할 점과 효율성을 높이는 방법에 대한 조언이 포함되어 있습니다.

2. 리텐션 분석 과제: 리텐션 분석을 진행하면서 발생할 수 있는 문제와 이 과제를 해결하는 방법에 대한 자세한 논의가 있습니다. 쿼리 작성의 논리와 데이터를 어떻게 분석해야 하는지에 대한 실용적인 이유를 찾을 수 있습니다.

3. 리텐션 과제_연습문제 2번: 리텐션 분석이 처음이신 분께 도움이 될 수 있는, 단계별 접근 방법과 이를 구현하는 SQL 예제에 대한 설명이 출처와 함께 제공됩니다.

sddyyy1004님의 과제를 검토하며, 각 쿼리와 과정이 이상이 없는지 확인한 후 피드백을 드리겠습니다. 또한, 직접적으로 해결하지 못한 부분이나 더 명확하게 이해하고 싶은 부분이 있다면, 질문을 더 해주시면 다른 학습자분들의 경험을 기반으로 더 많은 예시를 제공해드리도록 하겠습니다.

제가 제공한 자료가 충분하지 않더라도, 지속적으로 여러분의 학습 경험을 개선해 나가는 데에 도움이 되고자 합니다.

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