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

[종합 느낀점]

1. 날짜 데이터 변환 함수, 날짜 데이터로 추출 하는 함수 등 문제 풀 때 사용하는 함수를 까먹어 애를 먹었습니다.

2. 테이블 조인시 첫 테이블을 잘못 선택하면, 쿼리가 길어지고 꼬인다는 것을 느꼈습니다.

3. 지표가 어떤 지표느냐에 따라 해석 방법이 달라진 점도 알게 됐습니다.

 

[문제 풀이 소요 시간]

1번 문제 : 30분

2번 문제 : 12분

3번 문제 : 12분

 

정답이면서도 확장성 있는 쿼리를 작성하다가는 영원히 못풀겠더라고요..

그래서 최대한 쿼리 작성을 마무리 짓는데 초점을 뒀습니다.. ㅠㅠ

[new_users]

위와 같은 시각화 결과가 나왔습니다.

눈에 띄는 데이터로는 두 가지가 있습니다:

1. 10월 new users 상승

상승 추정 이유:

1. 새로운 마케팅 전략 시행(시기가 추석 근처이니깐 추석을 기념해서 배달앱 쿠폰 지급?)

2. 12월 new users 하락

하락 추정 이유:

1. 연말 모임으로 인한 배달 수요 감소

(사실 감소세는 10월달에 고점을 찍은 이후 쭉 하락세이지만, 그 이유를 단정짓지는 못하겠습니다..)

 

[Current User]

위와 같은 시각화 결과가 나왔습니다.

눈에 띄는 데이터로는 세가지가 있습니다:

1. 9월 중순 이후 Current User 상승

상승 추정 이유:

1. 새로운 마케팅 전략 시행(시기가 추석 근처이니깐 추석을 기념해서 배달앱 쿠폰 지급?)

2. 12월까지 꾸준한 상승

상승 추정 이유:

1. 기존 유저의 긍정적인 경험이 이어져서 서비스에 잔류

2. 마케팅으로 유입된 신규 유저도 긍정적인 경험을 해서 서비스에 잔류

3. 12월 Current User 하락

하락 추정 이유:

1. 연말 모임으로 인한 배달 수요 감소

[Resurrected User]

'Current User' 해석과 비슷할 거 같습니다.

[Dormant User]

1. 9월 -> 10월 급격한 상승

상승 추정 이유:

1. 앞선 이유과 같음. 추석 관련 이벤트 실시.

2. 그러나 계속 상승이 이어지지 않은 점을 고민해 봐야 함.

2. 10월 -> 11월 말 유지

유지 추정 이유:

1. 마케팅으로 긍정적인 경험을 한 기존·신규 유저 잔류

2. 하지만 입소문이 나서 계속 상승하지는 않음.

3. 일부 유저만 잔류하고, 나머지 신규 유입 유저는 이벤트만 맛보고 이탈.

3. 12월 급격한 상승

상승 추정 이유:

1. 연말에 배달 수요가 감소할 것을 예측해 새로운 마케팅 전략 실시

주차별 리텐션

# 리텐션 정의
-- "우리 서비스를 이용한 사람들이 현재는 얼마큼 이용하고 있을까?"

/*
1. 무슨 데이터가 필요할까?
- 유저 
- 날짜

왜 이런 데이터가 필요할까? 이에 대한 답변:
'사람들'이니깐, '유저'를 인식할 수 있는 데이터가 필요하다.
'현재'이니깐, '시간'을 인식할 수 있는 데이터가 필요하다.

## 1. 유저별 첫 방문 주 구하기*/
with first_visit_table as (
select
 user_pseudo_id,
 date_trunc(min(event_date), week(monday)) as first_visit_date
from `advanced.app_logs`
where
 event_date between '2022-08-01' and '2022-12-31'
group by
 1
)

/*2. 처음 이벤트를 발생 시킨 주는?
- 무슨 데이터가 필요할까?
 1. 첫 이벤트 발생 날짜
 2. 유저
 
- 왜 이런 데이터가 필요할까? 이에 대한 답변:
'이벤트'는 주체가 있어야 발동 되는 거니깐 '유저' 데이터가 필요하다.
또 이벤트 발생이란 결과에는 '날짜'라는 개념도 포함 되어 있기 때문에, '날짜' 데이터가 필요하다.
그중에서도 '처음 이벤트를 발생한 주'의 데이터가 필요하다.

3. 유저가 이벤트를 실행한 주는?
- 무슨 데이터가 필요할까?
 1. 유저
 2. 이벤트 이름(선택)
 3. 이벤트 발생 날짜
 + 중복제거
   왜? 목적이 모든 유저의 리텐션이기 때문에. 중복 제거를 하지 않으면,
   모든 유저가 발생시킨 모든 이벤트가 보이기 때문에 가독성이 안 좋기 때문에.

- 왜 이런 데이터가 필요할까? 이에 대한 답변:
처음 이벤트를 발생시킨 날짜와 비교하기 위해서

## 2. 유저별 이벤트 발생 주 구하기*/
, user_event as (
select
 distinct
   user_pseudo_id,
   date_trunc(event_date, week(monday)) as user_event_week  
from `advanced.app_logs`
where
 event_date between '2022-08-01' and '2022-12-31'
)

/*4. '유저가 이벤트 실행한 주 - 유저가 처음 방문한 주'
- 무슨 데이터가 필요할까?
 1. 유저
 2. 첫 이벤트 발생 날짜
 3. 이벤트 발생 날짜
 4. (이벤트 발생 날짜 - 첫 이벤트 발생 날짜)

- 왜 이런 데이터가 필요할까? 이에 대한 답변:
처음 이벤트를 발생시킨 날짜와 비교하기 위해서

## 3. 유저별 이벤트 발생주와 첫 방문주 차이 구하기*/
, diff_week_table as (
select
 fvt.user_pseudo_id,
 fvt.first_visit_date,
 ue.user_event_week,
 date_diff(ue.user_event_week, fvt.first_visit_date, week(monday)) as diff_week  
from first_visit_table as fvt
left join user_event as ue
on fvt.user_pseudo_id = ue.user_pseudo_id
order by
 2, 4
)

/*5. 주차별 유저수 구하기 
- 왜 위와 같은 생각을 했는가? 이에 대한 답변:
주차수에 따른 유저수를 알아야만 첫 주차에 비해 얼마큼 유저가 이벤트를 발생시키지 않았는지 알 수 있으니깐.

- 무슨 데이터가 필요한가? 이에 대한 답변:
 1. 이벤트 발생 차이 주
 2. 중복 없는 유저 수

## 4. 주차별 유저수 구하기*/
, unique_user_cnt_table as (
select
 diff_week,
 count(distinct user_pseudo_id) as unique_user_cnt
from diff_week_table
group by
 1
order by
 1
)

/*6. 모든 행에 첫 주 유저수 적용하기
- 왜 위와 같은 생각을 했는가? 이에 대한 답변:
첫 주 유저수로 주차마다 달라지는 유저수를 나누면, 리텐션을 구할 수 있기 때문에.

- 무슨 데이터가 필요한가? 이에 대한 답변:
 1. 이벤트 발생 차이주 
 2. 주차별 중복 없는 유저수
 3. 모든 행에 적용하는 첫 주 유저수

## 5. 모든 행에 첫 주 유저수 적용하기*/
, first_visit_week_data_table as (
select
 *,
 first_value(unique_user_cnt) over(order by unique_user_cnt desc) AS first_visit_week_data
from unique_user_cnt_table
order by
 1
)

## 6. 리텐션 구하기 | 주차별 유저수 / 첫 주 유저수
select
 *,
 round(safe_divide(unique_user_cnt, first_visit_week_data), 2) as retention_rate
from first_visit_week_data_table