방문한 첫째 날짜를 기준으로 D-리텐션 구하기
/* 1. 특정 날짜 이후로 참여한 활성 유저 정보, 접속일자 가져오기 */
select date as attend_date
,user_id
from user_attend_log
where date >= '20230828' -- 2023/08/28 일 이후로, 접속한 로그 정보
SQL
복사
→ 위의 결과를 attend_log 라고 할 때, 데이터프레임으로 변환
# 유저별로 첫 방문일자를 계산
attend_log = pd.DataFrame(attend_log)
min_attend = attend_log.groupby('user_id').min().attend_date.reset_index()
min_attend.columns = ['user_id','first_date']
Python
복사
# 기존의 attend_log 와 min_attend left join
# 첫 방문일자와 이후 접속일자간의 일자 차이 계산
attend_log = pd.merge(attend_log, min_attend, how='left', on='user_id')
attend_log[['attend_date','first_date']] = attend_log[['attend_date','first_date']].apply(pd.to_datetime) # 로그일자를 날짜타입으로 변경
attend_log['datediff'] = (attend_log['attend_date'] - attend_log['first_date']).dt.days
Python
복사
# 특정 이벤트에 참여한 유저들의 재방문율을 보고 싶을 경우
# ex) 파티 참여 유저 리스트 : party_user_list
party_attend_retention = attend_log[attend_log['user_id'].isin(party_user_list)]
# 첫 방문일자와 이후 방문 차이를 기준으로 그룹핑
party_attend_retention = party_attend_retention.groupby(['first_date','datediff'])['user_id'].count().reset_index()
# 기존의 0 ~ N 일차 방문 데이터프레임과 0일차 방문만 있는 데이터프레임 left join
party_attend_retention = pd.merge(party_attend_retention, party_attend_retention[party_attend_retention['datediff']==0, how='left', on='first_date')[['first_date','datediff_x','user_id_x','user_id_y']]
# 0일차 방문한 총 유저수로 방문비율 구하기
party_attend_retention['retention_ratio'] = round(party_attend_retention['user_id_x'] / party_attend_retention['user_id_y'],3)
# 마지막으로 리텐션 그래프에 필요한 필드만 추출
party_attend_retention_df = party_attend_retention[['first_date','datediff_x','user_id_x','retention_ratio']]
# 컬럼 이름 재지정
party_attend_retention_df.columns = ['first_date', 'datediff', 'user_cnt', 'retention_rate']
Python
복사
# 특정 이벤트에 참여하지 않은 유저들의 재방문율을 보고 싶을 경우
# -> 이벤트 미참여 유저 리스트를 뽑은 뒤에 위와 같은 방식으로 진행!
Python
복사