SQL Challenge 세션 #3 퍼널(Funnel)분석

240528 Today I Learn

퍼널(Funnel)이란?

💡 퍼널(Funnel)이란?
소비자의 행동을 기업 관점에서 재구성한 것으로, 고객이 유입되어 전환에 이르는 단계를 수치로 확인하고 분석하는 방법론
→ 우리가 원하는 것은 고객이 퍼널의 끝까지 통과하는 것! 이를 전환(conversion)/ Key Event 라고 한다.

퍼널의 종류들

1. 마케팅 퍼널

출처 : 마켓핏랩

2.AARRR 퍼널

3. 사용자 그룹별 퍼널 비교 

출처 : 핵클의 퍼널

퍼널 단계 수립하기

👅 맛보기 예시
만약 여러분들이 스파르타 코딩 클럽의 데이터 분석가이고, 신청 마감일이 다가와 일주일 안에 데이터 분석 트랙 3기의 참가자 수를 5배 이상 증가시키고 싶다면, 어떻게 해야할까요? 

✅ 내가 쓴 답안
1. 획득 - 웹사이트 방문, 공고 조회, 커리큘럼 조회
2. 활성화 - 회원가입
3. 획득 - 참가 지원서 임시저장 
4. 유지 - 참가 지원서 제출
5. 추천 - 초대, 공유

 

1. 서비스의 BM(Business Model)을 파악한다. 

• 어떻게하면 수익을 극대화할 수 있을까?
• 우리 회사, 이 서비스가 매출을 내는 구조는 무엇일까?
• 수익과 지출의 흐름은 어떨까?

2. BM을 고려해 핵심 지표와 퍼널 스텝을 설계하기

출처 : https://brunch.co.kr/@uxdohyun/7

3. 이탈률이 큰 단계를 중심으로 퍼널을 재설계하기

4. 각 퍼널별 전환율을 SQL로 구해보기 

5. 퍼널을 최적화하는 방법 

(1) 진입율을 높이기 : 깔때기 위에 물을 많이 넣기

• 해당 화면까지의 진입률 (즉 퍼널 유입량)을 높이는 방향 
  • 광고 효율을 높인다.
  • 광고비를 증액한다.

(2) 전환율 높이기 : 탈주를 막아라..!

• 퍼널 순서 바꾸기
• 퍼널 개수 늘리기 : 개수를 늘리지만 설득력을 높인다
• 퍼널 개수 줄이기 → 이탈을 방어한다.
• 퍼널 구조 뒤집기 → 최종 CTA 버튼을 맨 앞으로 가져오기

퍼널 분석 과제

240529 Today I FINISHED

💡 전환율
전환율 = 특정 단계로 전환된 사용자 수 / 전체 사용자 수
퍼널에서 각각의 단계를 넘어가는 것을 전환(Conversion)이라고 부르고 그 비율은 전환율(Conversion rate)이라고 한다.
→ 웹사이트를 방문한 사람 중, 소정의 유도된 행위를 한 방문자의 비율

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

📌 목표: 사용자가 계정을 생성한 후 24시간 이내에 회원가입을 완료하는 전환율을 사용자 위치와 기기별로 계산.

유저의 계정 생성 (create_user)  후 회원가입 완료 (complete_signup) 전환율을 사용자 위치(location), 기기(device) 별로 구해주세요. 

💡 결과: location과 device를 기준으로 전환율을 백분율로 산출.

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

일부

WITH create_user as
	(
	SELECT location,
		   device,
		   user_id,
		   occurred_at AS occurred_at_create_user
	FROM Basic.yammer_events y1
	WHERE event_name = 'create_user'
	),
	complete_signup as
	(
	SELECT user_id, occurred_at AS occurred_at_complete_signup
	FROM Basic.yammer_events y2
	WHERE event_name = 'complete_signup')
SELECT location, device, ROUND(COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)/COUNT(*)*100.0,2) AS converted_rate_percent
FROM create_user cu LEFT JOIN complete_signup cs
	ON cu.user_id = cs.user_id
WHERE occurred_at_complete_signup IS NULL 
	  OR DATEDIFF(occurred_at_complete_signup, occurred_at_create_user) <= 1
GROUP BY location, device;

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

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

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

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

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

WITH create_user AS
	(
	SELECT location,
		   device,
		   user_id,
		   MIN(occurred_at) AS occurred_at_create_user
	FROM Basic.yammer_events y1
	WHERE event_name = 'create_user'
	GROUP BY location, device, user_id
	),
	complete_signup AS
	(
	SELECT user_id, MIN(occurred_at) AS occurred_at_complete_signup
	FROM Basic.yammer_events y2
	WHERE event_name = 'complete_signup'
	GROUP BY user_id),
	enter_email AS
	(
	SELECT user_id, MIN(occurred_at) AS occurred_at_enter_email
	FROM Basic.yammer_events y3
	WHERE event_name = 'enter_email'
	GROUP BY user_id),
	enter_info AS
	(
	SELECT user_id, MIN(occurred_at) AS occurred_at_enter_info
	FROM Basic.yammer_events y4
	WHERE event_name = 'enter_info'
	GROUP BY user_id)
SELECT ROUND(COUNT(CASE WHEN occurred_at_enter_email IS NOT NULL THEN 1 ELSE NULL END)
			/COUNT(*)*100.0,2) AS create_user_to_enter_email,
	   ROUND(COUNT(CASE WHEN occurred_at_enter_info IS NOT NULL THEN 1 ELSE NULL END)
	   		/ COUNT(CASE WHEN occurred_at_enter_email IS NOT NULL THEN 1 ELSE NULL END)*100.0,2) AS enter_email_to_enter_info,
	   ROUND(COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)
	   		/ COUNT(CASE WHEN occurred_at_enter_info IS NOT NULL THEN 1 ELSE NULL END)*100.0,2) AS enter_info_to_complete_signup,
	   ROUND(COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)
	   		/COUNT(*)*100.0,2) AS create_user_to_complete_signup
FROM create_user cu 
	LEFT JOIN enter_email ee ON cu.user_id = ee.user_id
	LEFT JOIN enter_info ei ON cu.user_id = ei.user_id
	LEFT JOIN complete_signup cs ON cu.user_id = cs.user_id
WHERE occurred_at_enter_email IS NULL 
	  OR occurred_at_enter_info IS NULL
	  OR occurred_at_complete_signup IS NULL 
	  OR DATEDIFF(occurred_at_complete_signup, occurred_at_create_user) <= 1;

 

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

📌 목표: 유저가 계정을 생성한 후 30분 이내에 각 단계별 전환율을 계산.

• 계정 생성(create_user)  후 회원가입 완료(complete_signup) 전환율.
• 회원가입 완료(complete_signup) 후 홈페이지(home_page) 진입률.
• 홈페이지 진입(home_page) 후 메시지 좋아요(like_message) 클릭률.
• 회원가입 후(complete_signup) 메시지 좋아요(like_message) 클릭 전환율.

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

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

WITH create_user AS
	(
	SELECT user_id,
		   MIN(occurred_at) AS occurred_at_create_user
	FROM Basic.yammer_events y1
	WHERE event_name = 'create_user'
	GROUP BY user_id
	),
	complete_signup AS
	(
	SELECT user_id, MIN(occurred_at) AS occurred_at_complete_signup
	FROM Basic.yammer_events y2
	WHERE event_name = 'complete_signup'
	GROUP BY user_id),
	home_page AS
	(
	SELECT user_id, MIN(occurred_at) AS occurred_at_home_page
	FROM Basic.yammer_events y3
	WHERE event_name = 'home_page'
	GROUP BY user_id),
	like_message AS
	(
	SELECT user_id, MIN(occurred_at) AS occurred_at_like_message
	FROM Basic.yammer_events y4
	WHERE event_name = 'like_message'
	GROUP BY user_id
	)
SELECT ROUND(COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)
	   		/COUNT(*)*100.0,2) AS create_user_to_complete_signup,
	   ROUND(COUNT(CASE WHEN occurred_at_home_page IS NOT NULL THEN 1 ELSE NULL END)
	   		/COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)*100.0,2) AS complete_signup_to_homepage,
	   ROUND(COUNT(CASE WHEN occurred_at_like_message IS NOT NULL THEN 1 ELSE NULL END)
	   		/COUNT(CASE WHEN occurred_at_home_page IS NOT NULL THEN 1 ELSE NULL END)*100.0, 2) AS homepage_to_like_message,
	   ROUND(COUNT(CASE WHEN occurred_at_like_message IS NOT NULL THEN 1 ELSE NULL END)
	   		/COUNT(CASE WHEN occurred_at_complete_signup IS NOT NULL THEN 1 ELSE NULL END)*100.0,2) AS complete_signup_to_like_message
FROM create_user cu 
	LEFT JOIN complete_signup cs ON cu.user_id = cs.user_id
	LEFT JOIN home_page hp ON cu.user_id = hp.user_id
	LEFT JOIN like_message lm ON cu.user_id = lm.user_id
WHERE occurred_at_complete_signup IS NULL
	  OR occurred_at_home_page IS NULL
	  OR occurred_at_like_message IS NULL
	  OR TIMESTAMPDIFF(HOUR, occurred_at_create_user, occurred_at_like_message) <= 0.5;

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

📌 목표: 유저가 계정을 생성한 후 30분 이내에 각 단계별 전환율을 계산.

• 계정 생성 (create_user) 후 이메일 입력 화면 진입(enter_email)까지.
• 이메일 입력 화면 진입(enter_email) 후 개인 정보 입력 화면 진입(enter_info)까지.
• 개인 정보 입력 화면 진입(enter_info) 후 회원가입 완료(complete_signup)까지.
• 계정 생성 (create_user) 후 회원가입 완료(complete_signup)까지.

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

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

WITH create_user AS
	(
	SELECT location,
		   device,
		   user_id,
		   MIN(occurred_at) AS occurred_at_create_user
	FROM Basic.yammer_events y1
	WHERE event_name = 'create_user'
	GROUP BY location, device, user_id
	),
	complete_signup AS
	(
	SELECT user_id, MIN(occurred_at) AS occurred_at_complete_signup
	FROM Basic.yammer_events y2
	WHERE event_name = 'complete_signup'
	GROUP BY user_id),
	enter_email AS
	(
	SELECT user_id, MIN(occurred_at) AS occurred_at_enter_email
	FROM Basic.yammer_events y3
	WHERE event_name = 'enter_email'
	GROUP BY user_id),
	enter_info AS
	(
	SELECT user_id, MIN(occurred_at) AS occurred_at_enter_info
	FROM Basic.yammer_events y4
	WHERE event_name = 'enter_info'
	GROUP BY user_id)
SELECT ROUND(AVG(TIMESTAMPDIFF(SECOND, occurred_at_create_user,occurred_at_enter_email)),2) AS create_user_to_enter_email_AVG,
	   ROUND(AVG(TIMESTAMPDIFF(SECOND, occurred_at_enter_email,occurred_at_enter_info)),2) AS enter_email_to_enter_info_AVG,
	   ROUND(AVG(TIMESTAMPDIFF(SECOND, occurred_at_enter_info,occurred_at_complete_signup)),2)enter_info_to_complete_signup_AVG,
	   ROUND(AVG(TIMESTAMPDIFF(SECOND, occurred_at_create_user,occurred_at_complete_signup)),2)create_user_to_complete_signup_AVG
FROM create_user cu 
	LEFT JOIN enter_email ee ON cu.user_id = ee.user_id
	LEFT JOIN enter_info ei ON cu.user_id = ei.user_id
	LEFT JOIN complete_signup cs ON cu.user_id = cs.user_id
WHERE occurred_at_enter_email IS NULL 
	  OR occurred_at_enter_info IS NULL
	  OR occurred_at_complete_signup IS NULL 
	  OR DATEDIFF(occurred_at_complete_signup, occurred_at_create_user) <= 1;

Funnels with SQL: The good, the bad and the ugly way