데이터리안 SQL 데이터 분석 캠프 실전반 2기 3주차 수업에서는 퍼널 분석에 대해 배웠디.
간략한 개념 및 기억하고 싶은 내용 위주로 남겨두고자 한다.
퍼널 분석 (Funnel Analysis)
우리가 설계한 유저 경험 루트에 따라 잘 도착하고 있는지 확인해보기 위해 최초 유입부터 최종 목적지까지 단계를 나누어 살펴보는 분석 기법
- 얼마나 많은 사람들이 최종 단계까지 도착하는지, 어디에서 많이 이탈하는지 확인 가능
- 각 단계를 통과할 때마다 유저 수가 줄어들기 때문에, 아래로 갈수록 점점 좁아지는 형태의 깔대기(Funnel) 모양이 됨
- 각 단계를 넘어가는 것을 전환(Conversion)이라고 부르고 그 비율은 전환율(Conversion Rate)라고 함
여기서 최종 단계의 유저를 늘리려면 어떻게 해야할까?
다음과 같은 방법이 있을 것이다.
1. 앱 스토어 페이지 뷰를 본 사람 수를 늘린다.
2. 각 단계 사이의 전환률을 높인다.
3. 퍼널의 단계를 줄인다.
SQL로 퍼널 분석 해보기
저작권으로 인해 수업에서 배운 쿼리 내용과 데이터를 그대로 올리지 못한다.
그렇다고 다른 데이터를 활용하여 다르게 쿼리를 짜려고 하니.. 애초에 ga 데이터를 구하기 힘들다 ㅠㅠ
캐글에 뒤져봐도 없다..
그래서 아쉽지만 그냥 줄글로 정리하려고 한다!
데이터리안 웹사이트에서 퍼널 분석하기
사용 데이터 : solvesql 데이터리안 블로그 GA 로그(2022년 1월)
컬럼 정의
- event_timestamp_kst
- user_pseudo_id
- ga_session_id
- event_name
- page_location
- page_title
- source
- medium
- continent
- country
- device_category
0. 고려해야 될 조건
1) 같은 세션 내에서 일어난 전환만 카운트
2) user_pseudo_id 와 ga_session_id 컬럼을 모두 고려
- ga 테이블에서 한 명의 유저가 여러 개의 ga_session_id를 가지고 있기도 하며, 서로 다른 유저가 같은 ga_session_id를 갖고 있기도 함. 즉, user_pseudo_id와 ga_session_id는 N:N 관계임
1. 입문반 페이지를 본 세션 찾기
- page_title = '백문이불여일타 SQL 캠프 입문반'
- event_name = 'page_view'인 조건을 통해 찾을 수 있음
2. 입문반 페이지 뷰에서 스크롤까지 전환된 세션의 비율 구하기
- page_title = '백문이불여일타 SQL 캠프 입문반'
- event_name = 'scroll'인 조건을 통해 찾을 수 있음
결과는 다음 컬럼을 포함해야 함
- pv : 페이지를 본 세션 수
- scroll_after_pv : 페이지를 본 후 스크롤 한 세션 수
- pv_scroll_rate : scroll_after_pv / pv(스크롤한 세션 수를 페이지를 본 세션 수로 나눈 비율)
3. 입문반 페이지뷰, 스크롤을 거쳐 입문반 신청 버튼 클릭까지의 전환율 구하기
전환 기준
- 입문반 신청하기 버튼 클릭: event_name = “SQL_basic_form_click”
- 1 Day 신청하기 버튼 클릭: event_name = “SQL_basic_1day_form_click”
- 패키지 신청하기 버튼 클릭: event_name = “SQL_package_form_click”
페이지뷰 - 스크롤 전환율
(자세한 내용 저작권으로 인해 생략)
입문반 페이지뷰까지 들어왔지만, 페이지 스크롤을 하지 않는 유저들에 대해 분석하고 가설을 설정해보았다.
스크롤 - 신청하기 버튼 클릭 전환율
(자세한 내용 저작권으로 인해 생략)
입문반 페이지뷰까지 들어와서 스크롤하는 것까진 성공했지만, 신청하기 버튼을 클릭하지 않고 이탈하는 유저들에 대해 분석하고 가설을 설정해보았다.
참고) 유입채널 데이터는 어떻게 볼 수 있을까? - utm 파라미터
여러 채널을 통해 마케팅을 하다 보면, 어떤 채널이 가장 효과적인지 알고 싶다. 이때 사용하는 것이 utm 파라미터!
http://www.ddukbbok-kang.tistory.com?utm_source=naver&utm_medium=cpc&utm_campaign=brand_search&utm_term=query
한 번쯤 위와 같은 링크를 본 적 있을 것이다. 위의 형식이 utm 파라미터를 포함하고 있는 링크 형식이다.
tistory.com 뒤에 물음표가 있는데, 물음표 앞까지가 원래 url이다.
그 뒤에 따라오는 파라미터들이 유입 경로를 표시해주는 utm 파라미터이다.
- utm_source : 유입 채널 (e.g. youtube)
- utm_medium : 유입된 매체 (e.g. cpc, email)
- utm_campaign : 유입된 캠페인 (e.g. sql_advanced_orientation)
- utm_term : (옵션) 검색 유입의 경우 검색하고 들어온 키워드 / 유료 광고의 경우 타겟
- utm_content : (옵션) 콘텐츠
utm 파라미터를 통해 우리 웹사이트에 유입되면, ga를 통해 어떤 채널에서 어떤 매체로, 어떤 홍보 캠페인의 어떤 콘텐츠를 보고 유입되었는지 확인 가능하게 된다.
본 내용은 데이터리안 'SQL 데이터 분석 캠프 실전반' 을 수강하며 작성한 내용입니다.
SQL 데이터 분석 캠프에서 제공하는 수업 자료의 저작권은 데이터리안에게 있으며, 자료의 무단 복제 및 배포, 상업적 이용을 금지합니다.
'SQL > SQL 데이터 분석 캠프' 카테고리의 다른 글
| 2주차 - SQL로 코호트 분석 해보기(MySQL) (7) | 2022.03.27 |
|---|---|
| 2주차 - AARRR, 코호트 분석, 롤링 리텐션 (0) | 2022.03.26 |
| 1주차 - SQL로 피봇테이블 만들기, 다중컬럼 서브쿼리, 조인 (0) | 2022.03.26 |