2024-06-12

• Today

1. SQL 코드카타 

2. 공부계획 세우기

3. 머신러닝 분류분석 특강 수강 및 복습

4. SQL 챌린지 3회차 수강

 

---

• Today I Learned

 

SQL Challenge 3회차

 

퍼널

: 소비자의 행동을 기업 관점에서 재구성한 것

: 고객이 유입되어 전환에 이르는 단계를 수치로 확인하고 분석하는 방법론

> 화면, 진입, 클릭 등으로 지표를 세운다

 

마케팅 퍼널

출처: 마켓핏랩

 

AARRR 퍼널

 

퍼널 단계 세워보기

스파르타 코딩 클럽 데이터 분석 신청 퍼널 7단계 이내로 작성

 

1. 데이터 연관단어 검색 후 광고 클릭

2. 스파르타 코딩 클럽 데이터 분석 페이지 조회

3. 상담사 연결

4. 회원가입 및 신청

5. 사전캠프 진행

6. 본캠프 수료

7. 지인 추천 공유 이벤트

 

* 좋은 분석가란?

> 어떤 지표가 좋은 지표일지 항상 고민하는 사람

 

퍼널 세워보기

 

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

: 어떻게 하면 수익을 극대화할 수 있을까?

- 회사의 서비스가 매출을 내는 구조 예상해보기

출처: 혜람튜터님

 

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

: BM 과 align 되는 지표들을 찾는다.

 

ex)회사의 KPI 가 지정되어있지 않다면?

> 팀별로 인터뷰 (ex. 회사가 잘 되기위해 어떤 것이 필요할지?)

> 팀별로 요청하는 데이터지표가 있다면 그 목적이 무엇인지?

* 지표를 세울 때 참여하는 사람들? PM, 경영진, PO, 디자이너, 개발자

 

3. 이탈률이 큰 단계를 중심으로 퍼널을 재설계할 수 있다.

 

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

 

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

: 진입률 높이기(광고 효율, 광고비 증액)

: 전환율 높이기

- 퍼널 순서 바꾸기 (ex. 1.강아지코 2.프로필 때문에 이탈율이 높았다면 1.프로필 2.강아지코 순서로 바꾸어볼 수 있다.)

- 퍼널 개수를 늘리기 (다음 퍼널로 넘어갈 수 있는 설득력을 높인다. ex. 이렇게 하면 세금환급을 더 많이 받을 수 있다.)

- 퍼널 개수 줄이기 (이탈 방어)

- 퍼널 구조 뒤집기 (최종 CTA  버튼을 맨 앞으로 가져오기)

 

 

 

과제

 

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

---

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

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

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

SELECT location
	 , device
	 , ROUND((COUNT(DISTINCT b.user_id)/ COUNT(DISTINCT a.user_id) *100) , 2) AS 'creation_signup_conversion'
	 # 전환율 = 후속 행동을 한 고객수 / 첫 번째 행동을 한 고객수 *100
FROM 
		(
		SELECT user_id, occurred_at, location, device
		FROM yammer_events
		WHERE event_name = 'create_user'
		) a				# 계정 생성한 유저 테이블
LEFT JOIN 
		(
		SELECT user_id, occurred_at
		FROM yammer_events
		WHERE event_name = 'complete_signup'
		) b				# 회원가입 완료한 유저 테이블
ON a.user_id=b.user_id		# user_id 로 join
AND timestampdiff(HOUR, b.occurred_at, a.occurred_at) <= 24		
# 첫번째 행동 후 후속 행동이 하루 안에 완료 (24시간 이내)
GROUP BY location, device

timestampdiff(HOUR, b.occurred_at, a.occurred_at) <= 24

이 조건이 WHERE 절에 들어가지 않는 이유는

WHERE 절에서는 조인한 결과테이블에서 NULL 값을 취급하지 않기 때문이다.

그렇기 때문에 join 절에서 조건을 두고 테이블을 조인해서 만들어줘야한다.

 

서브쿼리의 사용법이 익숙하지 않다..

과제 풀이를 하면서 서브쿼리에 익숙해지는 시간을 가져야겠다.

주어진 과제를 다 풀이하고 나면 조금은 더 익숙해질 수 있을 것 같다!

 

성훈튜터님과 멘토링 후에 혼자 작성해봤는데

join 절에 조건을 주는 것으로 했지만

select 절에서 case when 을 사용했던 구문도 내일 작성해봐야겠다.

---

• Next

 

1. SQL 코드카타

2. 머신러닝 라이브세션 회귀분석

3. SQL Challenge 3 회차 과제 풀이(1번과제 case when 사용해보기)

4. PYTHON 코드 작성해보기