[SQL Challenge] 3회차 - 퍼널분석 복습하기

 

✅  과제 1. 유저의 계정 생성 후 회원 가입 완료 전환율 (location, device별)

 

# 과제 설명

 

유저의 계정 생성 (create_user) 후 회원가입 완료 (complete_signup) 전환율을 사용자 위치(location), 기기(device) 별로 구해주세요. 소숫점 둘째자리까지 출력하며, 처음 유저가 계정을 생성한 후 하루 안에 완료되었어야 합니다. (백분률로 구해주세요)

 

 

# 결과 출력 예시

 

 

 

# 정답 코드

WITH creation_user AS (
	SELECT user_id, 
		   occurred_at, 
		   location, 
		   device
	FROM yammer_event 
	WHERE event_name = 'create_user'
), completion_signup AS (
	SELECT user_id, 
		   occurred_at
	FROM yammer_event 
	WHERE event_name = 'complete_signup'
)
SELECT location, 
	   device,
	   round(count(DISTINCT s.user_id)*100.0 / count(DISTINCT c.user_id), 2) AS creation_signup_conversion
FROM creation_user c
LEFT JOIN completion_signup s
ON c.user_id = s.user_id
AND c.occurred_at <= s.occurred_at
AND timestampdiff(HOUR, c.occurred_at, s.occurred_at) <= 24
GROUP BY location, device

 

 

# 코드 설명

 

left join의 왼쪽에 위치하는 creation_user 부분에만 location과 device를 적어주었다

오른쪽에 위치하는 completion_signup에는 location과 device를 생략하고 꼭 필요한 user_id와 occurred_at만 적어주었다

 

timestampdiff(시간단위, 시작시간, 끝시간)

시간차이 구하기

 

 

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

 

# 과제 설명

 

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

 

 

# 결과 출력 예시

 

 

# 정답 코드

WITH creation_user AS (
	SELECT user_id,
		   occurred_at AS creation_time,
		   device,
		   location
	FROM yammer_event
	WHERE event_name = 'create_user'
), entering_email AS (
	SELECT user_id,
		   occurred_at AS email_time
	FROM yammer_event
	WHERE event_name = 'enter_email'
), entering_info AS (
	SELECT user_id,
		   occurred_at AS info_time
	FROM yammer_event
	WHERE event_name = 'enter_info'
), completion_signup AS (
	SELECT user_id,
		   occurred_at AS signup_time
	FROM yammer_event
	WHERE event_name = 'complete_signup'
), full_funnel AS (
	SELECT cu.user_id,
		   ee.user_id AS ee_id,
		   ei.user_id AS ei_id,
		   cs.user_id AS cs_id,
		   creation_time,
		   email_time,
		   info_time,
		   signup_time
	FROM creation_user cu
	LEFT JOIN entering_email ee
	ON cu.user_id = ee.user_id AND cu.creation_time <= ee.email_time AND timestampdiff(HOUR, cu.creation_time, ee.email_time) <= 24
	LEFT JOIN entering_info ei
	ON cu.user_id = ei.user_id AND ee.email_time <= ei.info_time AND timestampdiff(HOUR, ee.email_time, ei.info_time) <= 24
	LEFT JOIN completion_signup cs 
	ON cu.user_id = cs.user_id AND ei.info_time <= cs.signup_time AND timestampdiff(HOUR, ei.info_time, cs.signup_time) <= 24
	AND timestampdiff(HOUR, cu.creation_time, cs.signup_time) <= 24
)
SELECT round(count(DISTINCT ee_id)*100.0 / count(DISTINCT user_id), 2) AS email_entry_rate,
	   round(count(DISTINCT ei_id)*100.0 / count(DISTINCT ee_id), 2) AS info_entry_rate,
	   round(count(DISTINCT cs_id)*100.0 / count(DISTINCT ei_id), 2) AS signup_completion_rate,
	   round(count(DISTINCT cs_id)*100.0 / count(DISTINCT user_id), 2) AS overall_signup_rate
FROM full_funnel

 

 

 

# 핵심 개념

 

from 원래 테이블 

left join 새로운 테이블1, 2, 3 ... 을 할때

on에서 원래 테이블.user_id = 새로운테이블.user_id를 진행하였다

 

⚡️모든 단계는 처음 유저가 계정을 생성한 후 하루 안에 완료되었어야 합니다

이 부분을 구현하기 위해서 

각각의 옆의 단계마다 24시간 이내, cu.creation_time과 cs.signup_time은 24시간 이내 조건을 걸어줄 수도 있다

 

또는

 

cu.creation_time과 각각의 단계마다 24시간 이내 조건으로도 걸어줄 수 있다

 

 

✅  과제 3. 회원가입 전환율 및 이후 행동 전환율 분석

 

유저의 계정 생성 (create_user) 후 회원가입 완료 (complete_signup) 전환율, 회원가입 완료 후 홈페이지 진입(home_page)율, 홈페이지 진입 후 메세지 좋아요 클릭률 (like_message), 회원가입 후 메세지 좋아요 클릭 전환율을 각각 구해주세요. 소숫점 둘자짜리까지 출력하며, 모든 단계는 처음 유저가 계정을 생성한 후 30분 안에 완료되었어야 합니다. (백분률로 구해주세요)

 

 

# full_funnel 출력해보기

 

# 3-3) 퍼널 분석 : 회원가입 전환율 및 이후 행동 전환율 분석
WITH creation_user AS (
	SELECT user_id,
		   occurred_at AS creation_time,
		   device,
		   location
	FROM yammer_event
	WHERE event_name = 'create_user'
), completion_signup AS (
	SELECT user_id,
		   occurred_at AS signup_time
	FROM yammer_event
	WHERE event_name = 'complete_signup'
), homepage_enter AS (
	SELECT user_id,
		   occurred_at AS homepage_time
	FROM yammer_event
	WHERE event_name = 'home_page'
), like_msg AS (
	SELECT user_id,
		   occurred_at AS like_message_time
	FROM yammer_event
	WHERE event_name = 'like_message'
), full_funnel AS (
	SELECT cu.user_id,
		   cs.user_id AS cs_id,
		   he.user_id AS he_id,
		   lm.user_id AS lm_id,
		   creation_time,
		   signup_time,
		   homepage_time,
		   like_message_time
	FROM creation_user cu
	LEFT JOIN completion_signup cs ON cu.user_id = cs.user_id 
		AND timestampdiff(MINUTE, cu.creation_time, cs.signup_time) <= 30
		AND cu.creation_time <= cs.signup_time
	LEFT JOIN homepage_enter he ON cu.user_id = he.user_id
		AND timestampdiff(MINUTE, cu.creation_time, he.homepage_time) <= 30
		AND cs.signup_time <= he.homepage_time
	LEFT JOIN like_msg lm ON cu.user_id = lm.user_id
		AND timestampdiff(MINUTE, cu.creation_time, lm.like_message_time) <= 30
		AND he.homepage_time <= lm.like_message_time
)
SELECT *
FROM full_funnel

 

 

# 실행결과

 

 

# 정답 코드

# 3-3) 퍼널 분석 : 회원가입 전환율 및 이후 행동 전환율 분석
WITH creation_user AS (
	SELECT user_id,
		   occurred_at AS creation_time,
		   device,
		   location
	FROM yammer_event
	WHERE event_name = 'create_user'
), completion_signup AS (
	SELECT user_id,
		   occurred_at AS signup_time
	FROM yammer_event
	WHERE event_name = 'complete_signup'
), homepage_enter AS (
	SELECT user_id,
		   occurred_at AS homepage_time
	FROM yammer_event
	WHERE event_name = 'home_page'
), like_msg AS (
	SELECT user_id,
		   occurred_at AS like_message_time
	FROM yammer_event
	WHERE event_name = 'like_message'
), full_funnel AS (
	SELECT cu.user_id,
		   cs.user_id AS cs_id,
		   he.user_id AS he_id,
		   lm.user_id AS lm_id,
		   creation_time,
		   signup_time,
		   homepage_time,
		   like_message_time
	FROM creation_user cu
	LEFT JOIN completion_signup cs ON cu.user_id = cs.user_id 
		AND timestampdiff(MINUTE, cu.creation_time, cs.signup_time) <= 30
		AND cu.creation_time <= cs.signup_time
	LEFT JOIN homepage_enter he ON cu.user_id = he.user_id
		AND timestampdiff(MINUTE, cu.creation_time, he.homepage_time) <= 30
		AND cs.signup_time <= he.homepage_time
	LEFT JOIN like_msg lm ON cu.user_id = lm.user_id
		AND timestampdiff(MINUTE, cu.creation_time, lm.like_message_time) <= 30
		AND he.homepage_time <= lm.like_message_time
)
SELECT round(count(DISTINCT cs_id)*100.0 / count(DISTINCT user_id), 2) AS creation_signup_rate,
	   round(count(DISTINCT he_id)*100.0 / count(DISTINCT cs_id), 2) AS signup_homepage_entry_rate,
	   round(count(DISTINCT lm_id)*100.0 / count(DISTINCT he_id), 2) AS homepage_entry_like_message_rate,
	   round(count(DISTINCT lm_id)*100.0 / count(DISTINCT cs_id), 2) AS signup_like_message_rate
FROM full_funnel

 

 

# 정답 코드 실행결과

 

 

 

 

✅  과제 4. 일별로 회원 가입 프로세스의 각 단계 완료 평균 시간

 

# 문제

 

일별로 회원 가입 프로세스의 각 단계(계정 생성부터 이메일 입력, 개인 정보 입력, 가입 완료까지)를 완료하는 데 걸린 평균 시간을 구해주세요.

 

유저의 계정 생성 (create_user) 후 이메일 입력 화면 진입 (enter_email) 까지 걸린 시간, 이메일 입력 화면 진입 (enter_email) 후 개인 정보 입력 화면(enter_info) 진입까지 걸린 시간, 개인 정보 입력 화면(enter_info) 진입 후 회원가입 완료 전환까지 걸린 시간(complete_signup), 유저의 계정 생성 후 회원가입 완료까지 걸린 시간의 평균을 각각 구해주세요.

 

소숫점 둘자짜리까지 출력하며, 모든 단계는 처음 유저가 계정을 생성한 후 하루 안에 완료되었어야 합니다. (백분률로 구해주세요)

 

 

# 문제 실행결과

 

 

 

# 정답 코드

# 3-4) 퍼널 분석 : 일별로 회원 가입 프로세스의 각 단계 완료 평균 시간
WITH creation_user AS (
    SELECT user_id 
        , occurred_at AS creation_time
        , device
        , location
    FROM yammer_event 
    WHERE event_name = 'create_user'
), entering_email AS (
    SELECT user_id
         , occurred_at AS email_time
    FROM yammer_event
    WHERE event_name = 'enter_email'
), entering_info AS (
    SELECT user_id
         , occurred_at AS info_time
    FROM yammer_event
    WHERE event_name = 'enter_info'
), completion_signup AS (
    SELECT user_id
         , occurred_at AS signup_time 
    FROM yammer_event
    WHERE event_name = 'complete_signup'
)
SELECT date_format(cu.creation_time, '%Y-%m-%d') AS creation_date,
	   round(avg(timestampdiff(SECOND, cu.creation_time, ee.email_time)),2) AS email_entry_time,
	   round(avg(timestampdiff(SECOND, ee.email_time, ei.info_time)),2) AS info_entry_time,
	   round(avg(timestampdiff(SECOND, ei.info_time, cs.signup_time)),2) AS signup_completion_time,
	   round(avg(timestampdiff(SECOND, cu.creation_time, cs.signup_time)),2) AS creation_signup_time
FROM creation_user cu
LEFT JOIN entering_email ee ON cu.user_id = ee.user_id
	AND timestampdiff(HOUR, cu.creation_time, ee.email_time) <= 24
	AND cu.creation_time <= ee.email_time
LEFT JOIN entering_info ei ON cu.user_id = ei.user_id
	AND timestampdiff(HOUR, cu.creation_time, ei.info_time) <= 24
	AND ee.email_time <= ei.info_time
LEFT JOIN completion_signup cs ON cu.user_id = cs.user_id 
	AND timestampdiff(HOUR, cu.creation_time, cs.signup_time) <= 24
	AND ei.info_time <= cs.signup_time
GROUP BY date_format(cu.creation_time, '%Y-%m-%d')

 

 

 

# 정답 코드 실행 결과

 

 

# 핵심 개념

 

round

avg(집계함수)

timestampdiff를 한번에 사용하였다