해당 프로젝트는 solvesql 의 playground 에서 제공되는 데이터리안 블로그 ga 로그(2022년 1월) 를 활용했습니다.
분석 목적
데이터리안 블로그에 연결되어 있는 GA 에는 사용자들의 로그 활동이 쌓여 있습니다. 퍼널 분석을 통해 사용자들이 어떤 국가, 매체, 소스, 경로로 유입되는지 파악하는 것이 프로젝트의 목적입니다.
데이터리안의 플랫폼에서 퍼널 분석의 목적은 무엇일까?
다음과 같이 골라볼 수 있다
1.
일단 등록하는 것( event_name = ‘user_engagement’ )
2.
신청서 작성 폼을 클릭하는 것( event_name = ‘~form_clik’ )
즉, 들어온 사용자들이 최대한 강의를 많이 수강하는 것이 목표이므로, 다음과 같은 목표와 가설을 설정해볼 수 있다
•
어떤 매체, 소스, 캠페인을 통해서 들어온 사용자들이 가장 많이 등록하거나 / 강의를 신청하는지
•
퍼널별 전환율을 계산하고 개선해야 할 구간이 어딘지
•
시간대에 따른 유입 트래픽 분포를 파악하여 추후 마케팅시 참고해보기
•
첫방문자들의 강의 신청폼 클릭까지의 전환율이 그외 방문자들에 비해 떨어지는지, 높아지는지?
•
분석 과정
select *
from ga
limit 10;
SQL
복사
event_timestamp_kst | user_pseudo_id | ga_session_id | event_name | page_title | page_location | source | medium | continent | country | device_category |
2022-01-20 07:30:28 | xSXcHvtWoA | 1642631425 | first_visit | 백문이불여일타 SQL 캠프 입문반 | /bootcamp/sql_basic/?utm_source=fb&utm_medium=cpc&utm_campaign=sql_basic&utm_content=1day_coding_test | fb | cpc | Asia | South Korea | mobile |
2022-01-20 07:30:28 | xSXcHvtWoA | 1642631425 | session_start | 백문이불여일타 SQL 캠프 입문반 | /bootcamp/sql_basic/?utm_source=fb&utm_medium=cpc&utm_campaign=sql_basic&utm_content=1day_coding_test | fb | cpc | Asia | South Korea | mobile |
2022-01-20 07:30:28 | xSXcHvtWoA | 1642631425 | page_view | 백문이불여일타 SQL 캠프 입문반 | /bootcamp/sql_basic/?utm_source=fb&utm_medium=cpc&utm_campaign=sql_basic&utm_content=1day_coding_test | fb | cpc | Asia | South Korea | mobile |
2022-01-20 07:30:32 | xSXcHvtWoA | 1642631425 | page_view | 백문이불여일타 SQL 캠프 입문반 | /bootcamp/sql_basic/?utm_source=fb&utm_medium=cpc&utm_campaign=sql_basic&utm_content=1day_coding_test | fb | cpc | Asia | South Korea | mobile |
2022-01-20 19:57:22 | KE8fB0xo3Z | 1642676241 | first_visit | 백문이불여일타 SQL 캠프 입문반 | /bootcamp/sql_basic/?utm_source=fb&utm_medium=cpc&utm_campaign=sql_basic&utm_content=1day_no_base_date | fb | cpc | Asia | South Korea | tablet |
2022-01-20 19:57:22 | KE8fB0xo3Z | 1642676241 | session_start | 백문이불여일타 SQL 캠프 입문반 | /bootcamp/sql_basic/?utm_source=fb&utm_medium=cpc&utm_campaign=sql_basic&utm_content=1day_no_base_date | fb | cpc | Asia | South Korea | tablet |
2022-01-20 19:57:22 | KE8fB0xo3Z | 1642676241 | page_view | 백문이불여일타 SQL 캠프 입문반 | /bootcamp/sql_basic/?utm_source=fb&utm_medium=cpc&utm_campaign=sql_basic&utm_content=1day_no_base_date | fb | cpc | Asia | South Korea | tablet |
2022-01-20 07:35:57 | 6wwspgVYNE | 1642631757 | first_visit | 백문이불여일타 SQL 캠프 입문반 | /bootcamp/sql_basic/?utm_source=fb&utm_medium=cpc&utm_campaign=sql_basic&utm_content=review_analyst_basic | fb | cpc | Asia | South Korea | tablet |
2022-01-20 07:35:57 | 6wwspgVYNE | 1642631757 | session_start | 백문이불여일타 SQL 캠프 입문반 | /bootcamp/sql_basic/?utm_source=fb&utm_medium=cpc&utm_campaign=sql_basic&utm_content=review_analyst_basic | fb | cpc | Asia | South Korea | tablet |
2022-01-20 07:35:57 | 6wwspgVYNE | 1642631757 | page_view | 백문이불여일타 SQL 캠프 입문반 | /bootcamp/sql_basic/?utm_source=fb&utm_medium=cpc&utm_campaign=sql_basic&utm_content=review_analyst_basic | fb | cpc | Asia | South Korea | tablet |
# 전체 데이터 개수 확인
select count(*)
from ga; -- 8239 개
SQL
복사
# 몇 일치 데이터가 있는지 확인해보자
select count(distinct date(event_timestamp_kst))
from ga; -- 9일치 데이터
SQL
복사
# 어떤 이벤트(사용자 로그 행동)가 존재하고, 각 이벤트마다 몇번의 session 활동이 이뤄졌는지 확인해보자
select event_name
,count(distinct ga_session_id)
from ga
group by event_name
order by 2 desc;
SQL
복사
event_name | count(distinct ga_session_id) |
session_start | 1614 |
page_view | 1533 |
first_visit | 1220 |
user_engagement | 688 |
scroll | 636 |
click | 197 |
SQL_advanced_form_click | 63 |
SQL_basic_form_click | 35 |
SQL_package_form_click | 19 |
webinar_banner_click | 9 |
python_basic_noti_form_click | 8 |
python_visualization_noti_form_click | 8 |
SQL_basic_1day_form_click | 7 |
→ 아무래도 처음 유입되면서 로그가 기록되는 session_start 가 가장 많다. 그다음으로 page_veiw 가 많은 것을 확인할 수 있다. 일단, 유입되고 나면 가장 먼저 로그가 쌓이는 단계가 ‘페이지를 보는 것’ 일 테니까.
•
first_visit : 처음 방문자라면 해당 로그가 기록된다.
•
user_engagement : 아마, 가입하는 단계인 것 같다. 실제 블로그 사이트로 들어가면 ‘로그인’ 페이지가 있다.
•
SQL_advanced_form_click , SQL_basic_form_click , SQL_package_form_click 은 모두 각각 해당하는 페이지에서 캠프 신청폼을 클릭할 때 기록되는 로그다.
•
webinar_banner_click : 데이터리안은 월간 세미나를 진행한다. 그리고 세미나 내용을 해당 페이지에 기록해놓는데, 해당 배너를 클릭했을 때 기록되는 로그다
•
python_basic_noti_form_click : 현재는 없지만, 이전에는 ‘파이썬 베이직 강의’ 가 존재했었다. 해당 강의 수강을 위해 신청폼을 클릭하면 기록되는 로그다.
•
python_visualization_noti_form_click : 이것도 현재는 없지만 이전에 존재했던 ‘파이썬 시각화 강의’ 신청폼 클릭시 기록되는 로그다.
•
SQL_basic_1day_form_click : 이것도 현재는 없지만 이전에 존재했던 ‘SQL basic 데일리 강의’ 신청폼 클릭시 기록되는 로그다.