[컬럼 만들기] 회원가입 각 단계 전환율 구하기 - SQL Challenge 세션 과제2

과제 2. 회원가입 전환율 단계별 분석

---

목표: 유저의 계정 생성부터 회원가입 완료까지의 각 단계별 전환율을 24시간 이내에 완료된 경우로 계산.

• 계정 생성 후 이메일 입력 화면 진입률.
• 이메일 입력 화면 진입 후 개인 정보 입력 화면 진입률.
• 개인 정보 입력 화면 진입 후 회원가입 완료 전환율.
• 계정 생성 후 회원가입 완료 전환율.

결과: 각 단계별 전환율을 백분율로 산출.

• 과제 설명
• 유저의 계정 생성 (create_user) 후 이메일 입력 화면 진입 (enter_email)률, 이메일 입력 화면 진입 (enter_email) 후 개인 정보 입력 화면(enter_info) 진입률, 개인 정보 입력 화면(enter_info) 진입 후 회원가입 완료 전환율(complete_signup), 유저의 계정 생성 후 회원가입 완료 전환율을 각각 구해주세요. 소숫점 둘자짜리까지 출력하며, 모든 단계는 처음 유저가 계정을 생성한 후 하루 안에 완료되었어야 합니다. (백분률로 구해주세요)

 

Mode에 있는 tutorial 데이터를 활용한 과제를 받았다. 

Mode는 PostgreSQL 문법을 따르고 있어서 특히 날짜 함수에 관한 부분이 까다로워서

csv 데이터를 추출해서 DBeaver로 가져가 MySQL로 해결했다. 

 

<내 쿼리>

① WITH절

WITH 절을 활용해 event_name에 혼재하던 값 'create_user', 'enter_email', 'enter_info', 'complete_signup'을 user_id 별로 나누어 각각의 event가 일어난 시간을 값으로 가지는 컬럼으로 생성해준다. 

 

WITH절 조회 결과

 

- user_id 별로 한 행에 합치기 위해 집계 함수를 활용해준다. 

- 전환율을 살피기 위함으로 각각의 이벤트 중 가장 먼저 일어난 값을 구하는 MIN함수를 적용해준다.

  (사실상 id 별로 한 번 밖에 일어날 수 없는 이벤트이므로 MAX를 적용했을 때도 같은 값이 나오는 것 같다.)

- 문자열이었던 'occurred_at' 컬럼을 CAST 함수를 활용해 DATETIME으로 변환해준다.

 

② 전환율 구하기 - 계정 생성 후 이메일 입력 화면 진입률

 ROUND(SUM(CASE WHEN occ_at_enter_email IS NOT NULL AND occ_at_enter_email<=occ_at_create_user + INTERVAL 1 DAY
                         THEN 1 ELSE 0 END)*100.0/COUNT(CASE WHEN occ_at_create_user IS NOT NULL THEN 1 END)                                                             
             , 2) AS email_conversion_rate,

- 분자 CASE WHEN: 이메일 입력 화면 진입한 유저이자('occ_at_enter_email IS NOT NULL' AND) 계정 생성 후 이메일 입력 화면 진입시까지 걸린 시간이 1 DAY 이내인 유저를 '1'로 이외를 0으로

- SUM: CASE WHEN 조건을 만족하는 유저들의 합

- 분모 CASE WHEN: 계정 생성을 한 유저들의 수

- *100.0: 정수들의 백분율을 구하기 위해선 100.0을 곱해주어야 함

- ROUND: 소수점 둘 째자리까지

 

같은 원리로 나머지 전환율을 구하는 쿼리도 작성해줬다.

그랬을때 각 단계의 전환율은 다음과 같이 나왔다. 

 

아직 튜터님께서 답을 공유해주지 않으셔서 맞을지는 모르겠지만 

정말 많은 시간을 들였는데 제발 맞았으면...