퍼널 분석은 고객들이 우리가 설계한 유저 경험 루트를 따라 잘 도착하고 있는지 확인해보기 위해 최초 유입부터 최종 목적지까지 단계를 나누어서 살펴보는 분석 기법이다. 얼마나 많은 사람들이 최종 단계까지 도착하는지, 또 어디에서 많이 이탈하는지 확인할 수 있다. 각 단계를 통과할 때마다 유저 수가 줄어들게 되는데 그림으로 그려보면 아래 그림처럼 점점 좁아지는 형태의 깔대기 모양이라 퍼널 분석이라고 부른다. 각각의 단계를 넘어가는 것을 전환이라고 부르고 그 비율은 전환율이 된다.
예를 들어, 앱 서비스의 가입까지의 퍼널을 구성한다고 하면 아래 그림과 같이 그려볼 수 있다.
퍼널의 최종 단계에 도착하는 유저 수 늘리기
그러면 이제 퍼널을 보면서 퍼널 최종 단계의 유저를 늘리려면 어떻게 할 수 있을까?
1.
앱 스토어 페이지 뷰를 늘린다.
퍼널로 유입되는 고객 수를 늘리는 방법을 처음으로 생각해 볼 수 있다. 아래 테이블에서처럼 전환율이 같다고 가정하면 앱스토어 페이지 뷰를 본 사람 수를 늘리면 마지막 단계인 가입완료까지 도착하는 사람 수도 따라서 늘어나게 된다. 어찌보면 가장 쉬운 방법이다. 유로 광고 예산을 늘리거나 자극적인 문구나 이미지로 클릭을 유도해서 많이 들어와보게 할 수 있다. 들어왔다 나가는 사람도 그만큼 많겠지만 들어오는 사람을 10배 100배 늘려버리면 그 다음단계로 가는 사람들도 분명히 늘어날테니까.
2.
각 단계 사이의 전환율을 올린다.
위에서 언급한 것처럼 많이 들어와도 그만큼 많이 나가버리면 효율이 떨어진다. 아까와 같이 기존 전환율이 5%라고 해보자. 5%면 100명 중에 5명이 전환되는 것이다. 우리가 전환율을 획기적으로 끌어올려서 30%가 됐다고 해보자. 그러면 유입을 전혀 늘리지 않고도 가입완료 수를 늘릴 수 있다. 100명이 들어오면 30명이 전환되니까.
그러면 이 때 유입을 늘리면 어떻게 될까? 500명이 들어오면 150명이 전환될 것이다. 처음보다 30배가 늘었다. 처음에 입문반 참가자를 10배로 늘리는 게 가능해? 하고 생각했는가? 쉽지는 않겠지만 전환율을 끌어올리면 가능할 수 있다. 그래서 전환율을 높이는 작업이 중요한 것이다.
3.
퍼널의 단계를 줄인다.
전환율을 올리고 유입 수를 늘려도 결국은 한 단계를 거칠 때마다 이탈하는 유저 수가 생길 수 밖에 없다. 그래서 가장 효과적인 방법은 퍼널의 단계를 줄이는 것이다.
지금 위의 예시를 언뜻 보면 단계를 여기서 더 줄일 수 있을까? 싶지만, 각 단계를 또 좀 쪼개서 보면 다르게 보일 수 있다.
일반적인 가입 절차
마이현대 앱 : 여러 단계로 이루어진 일반적인 가입 예시
소셜 로그인 가입 절차
브런치 앱 : 소셜 로그인 가입 예시
지금 퍼널에서는 가입하기 한 단계로 표현되어 있지만 사실은 이메일 입력, 이름 입력, 휴대폰 인증, 아이디 중복체크 등등 여러 단계가 들어있다. 요즘 대부분의 서비스들이 구글 계정 로그인, 소셜 계정 로그인을 많이 쓰는데, 이렇게 가입 절차 자체를 최소화하는 것도 퍼널을 줄이고 전환율을 높이기 위한 장치로 이해할 수 있다.
그러면 이제 어떻게 하면 되는 걸까? 퍼널의 단계를 최소한으로 줄이고, 각 단계 사이의 전환율을 끌어올린 다음에, 유입을 늘리면 되겠구나! 이런 생각이 들 것이다. 물론 이상적으로는 더없이 좋은 시나리오다. 하지만 현실에서는 그 작업들을 순서대로 하나씩 다 하기는 어려울 것이다. 들일 수 있는 시간과 비용에는 언제나 한계가 있기 때문에, 회사에서는 비용 대비 효율을 생각해야하고 우리에게 시간이 무한정 있는 것은 아니니까 말이다. 우선순위를 정해서 가장 적은 비용으로 큰 효과를 볼 수 있는 전략을 선택해야 하겠다.
그래서 퍼널에 불필요한 단계가 들어있지는 않은지, 전환율이 너무 떨어지는 구간이 있지는 않은지, 유입량 자체가 너무 적지는 않은지 살펴보면서 가장 쉽고 빠르게 가장 큰 효과를 가져다줄 부분이 어디인지 찾는 게 우리의 과제라고 할 수 있겠다.
좋은 분석이란 무엇일까
단순히 숫자만 뽑아서 보여주는 게 아니라 의사결정에 도움이 되고 구체적인 액션 아이템이 나오는 분석이 좋은 분석일 것이다. 사실 쉬운 일은 아니다. 그런 점에서 퍼널분석이 좋은 툴이다. 퍼널을 쪼개보면 어디를 개선해야할지가 나오기 때문이다. 그러면 혼자 액션 아이템까지 찾는 건 어렵더라도 마케터, 기획자, 개발자, 다른 파트들과 이야기를 나눠보면서 해결책을 찾아갈 수 있을 것이다.
1. 유입채널 데이터는 어떻게 볼 수 있을까? : UTM 파라미터
어떤 채널이 가장 효과적인지, 적은 비용으로 많은 유저를 데리고 오는 채널이 가장 효과적일 것이다. 각 유입채널의 성과를 알아내려면 어떤 유저가 어떤 채널로 유입되었는지 데이터를 쌓아놔야할 것이다. 그 때 사용하는게 UTM 파라미터다.
가끔 위와 같이 친구한테 공유해줄려고 url을 복사했는데 말도 안되게 길어서 카톡창에 넘치는 경우..있을 것이다. 보통 광고를 통해 접속한 경우에 이런 일이 자주 일어나는데, UTM 파라미터를 사용해서 그렇다.
링크 중간에 물음표가 있는데, 물음표 앞까지가 원래 url이다. 저기까지만 입력해도 똑같은 페이지를 볼 수 있다. 그럼 그 뒤에 달린 것들은 다 무엇일까. 그게 바로 유입경로를 표시해주는 UTM 파라미터다.
UTM 파라미터에 대해 간단하게 설명을 하자면, 아래의 다섯가지 파라미터가 있다.
▪
utm_source : 유입 채널(e.g. youtube)
▪
utm_medium : 유입된 매체(e.g. cpc,cpm, email)
▪
utm_campaign : 유입된 마케팅 캠페인
▪
utm_term : (옵션) 검색 유입의 경우 검색하고 들어온 키워드/ 유료 광고의 경우 타겟
▪
utm_content : (옵션) 유입된 콘텐츠
여기서 source, medium, campaign 은 필수값이고 term, content 는 옵션으로 사용할 수 있는 값들이다. UTM 파라미터가 달린 링크를 클릭해서 웹사이트에 들어오면 로그에 저 긴 링크 그대로 남게 된다. 그렇기 때문에 기업은 어떤 채널에서 어떤 매체로 어떤 홍보 캠페인의 어떤 콘텐츠를 보고 유입되었는지 확인할 수 있게 된다.
예시를 하나 들어볼까. 예를 들어 유튜브 영상 밑 설명에 링크를 넣었다고 하자. 링크는 실제 등록 웹사이트로 이어지는 링크라고 하자. 그 때 UTM 파라미터를 붙이면 이 링크를 통해 우리 사이트에 접속한 사람이 얼만지 카운트할 수 있게 된다.
유입채널마다 UTM 파라미터를 다르게 설정하면 어떤 채널로 들어온 사람이 어떤 액션을 했는지 데이터를 수집할 수 있게 된다.
퍼널 분석 전 EDA using solvesql 데이터리안 블로그 GA 로그 (2022년 1월)
1.
ga 테이블에 어떤 데이터들이 있는지 10개의 데이터만 뽑아서 확인해보자
select *
from ga
limit 10;
SQL
복사
2.
ga 테이블에 있는 전체 데이터가 몇 개인지 확인해보자.
select count(*)
from ga;
SQL
복사
8239개의 데이터가 존재한다..
3.
몇 일치 데이터가 들어있는지 확인해보자.
select count(distinct date(event_timestamp_kst))
from ga;
SQL
복사
4.
ga 테이블에 있는 ga_session_id = 1643212217 세션의 데이터를 확인해보자. 시간 순서대로 출력해주세요.
select *
from ga
where ga_session_id = 1643212217
order by event_timestamp_kst;
SQL
복사
5.
입문반 페이지 내에서 일어난 이벤트(event_name) 수를 각각 카운트해주세요. 이벤트가 많이 일어난 순서대로 정렬해서 보여주세요.
select event_name, count(*) -- 각 이벤트 별로 개수 세주기
from ga
where page_title = "백문이불여일타 SQL 캠프 입문반" -- 입문반 페이지 내
group by event_name -- 일어난 이벤트 별로 그룹핑
order by 2 desc;
SQL
복사
event_name | count(*) |
page_view | 1279 |
session_start | 1045 |
first_visit | 908 |
user_engagement | 402 |
scroll | 337 |
click | 92 |
SQL_basic_form_click | 78 |
SQL_package_form_click | 40 |
SQL_basic_1day_form_click | 14 |
webinar_banner_click | 2 |
6.
위에서 어떤 페이지가 가장 많이 노출되고 있는지 확인했습니다. 그 페이지의 유입경로가 궁금합니다. 유입경로별로 해당 페이지를 본 세션 수를 카운트해주세요.
select source, medium, count(distinct user_pseudo_id, ga_session_id) session_count
from ga
where page_title = "백문이불여일타 SQL 캠프 입문반"
and event_name = 'page_view'
group by 1, 2
order by 3 desc;
SQL
복사
source | medium | session_count |
fb | cpc | 773 |
(direct) | (none) | 164 |
ig | profile | 103 |
brunch | footer | 54 |
open_katalk | (none) | 17 |
velog | footer | 14 |
careerly | footer | 12 |
youtube | video | 11 |
webinar | review_form | 4 |
→ facebook의 광고 클릭을 통해서 들어온 세션 수가 가장 많은 것으로 파악했다.
7.
전체 세션을 입문반 페이지를 본 세션과 안 본 세션으로 나누어 각각 카운트해주세요.
•
집합의 개념을 활용해야함
•
페이지를 안 본 세션 = 전체 세션 - 페이지를 본 세션
WITH pv AS (
SELECT user_pseudo_id
, ga_session_id
FROM ga
WHERE event_name = 'page_view' AND page_title = '백문이불여일타 SQL 캠프 입문반'
) -- 페이지 본 세션만 있는 테이블 생성
SELECT COUNT(DISTINCT ga.user_pseudo_id, ga.ga_session_id) total
, COUNT(DISTINCT ga.user_pseudo_id, ga.ga_session_id) - COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) pv_no
, COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) pv_yes
FROM ga
LEFT JOIN pv ON ga.user_pseudo_id = pv.user_pseudo_id -- 원본 ga테이블에 left join
AND ga.ga_session_id = pv.ga_session_id
SQL
복사
→ 전체 세션 중에서 페이지뷰를 본 세션이 안 본 세션에 비해 2배정도 된다.
현상 파악하기 : SQL로 퍼널의 전환율 구하기
1.
세션이란?
세션이란 지정된 기간 내에 웹사이트에서 발생한 사용자 상호작용의 집합을 말한다. 예를 들어 단일 세션에 다수의 페이지 조회, 이벤트, 소셜 상호작용, 전자상거래가 포함될 수 있다.
사용자 한 명이 여러 세션을 시작할 수 있다. 이러한 세션은 하루 동안 집중적으로 발생할 수도 있고, 며칠, 몇 주 또는 몇 개월에 걸쳐 발생할 수도 있다. 세션 하나가 완료되는 순간 새 세션이 시작될 수 있다. 세션이 종료되는 방식에는 다음과 같이 두 가지가 있다.
•
시간 기반 완료:
◦
방문자 활동이 없을 경우 30분 후
◦
자정
•
캠페인 변경:
◦
사용자가 한 캠페인을 통해 사이트를 방문했다가 떠난 후 다른 캠페인을 통해 사이트를 다시 방문하는 경우
8.
입문반 페이지 뷰에서 스크롤까지 전환된 세션의 비율 구하기
** 페이지뷰 다음에 스크롤한 세션을 찾아라. 이벤트들간의 선후관계를 고려할 때 동시에 일어난 경우도 전환으로 카운트한다.
-- 먼저, 입문반 페이지를 본 세션 테이블을 만든다
-- 입문반 페이지에서 스크롤을 한 세션 테이블을 만든다
WITH pv AS (
SELECT user_pseudo_id,
ga_session_id,
event_timestamp_kst AS pv_at
FROM ga
WHERE page_title = '백문이불여일타 SQL 캠프 입문반'
and event_name = 'page_view'
), scroll AS (
SELECT user_pseudo_id,
ga_session_id,
event_timestamp_kst AS scroll_at
FROM ga
WHERE page_title = '백문이불여일타 SQL 캠프 입문반'
and event_name = 'scroll'
)
SELECT COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv,
COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) AS scroll_after_pv,
COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv_scroll_rate -- pv에서 scroll로의 전환율
FROM pv
LEFT JOIN scroll ON pv.user_pseudo_id = scroll.user_pseudo_id
and pv.ga_session_id = scroll.ga_session_id
and pv_at <= scroll_at;
SQL
복사
입문반 페이지 뷰에서 스크롤까지의 전환율이 27% 정도다.
9.
입문반 페이지뷰, 스크롤을 거쳐 입문반 신청 버튼 클릭까지의 전환율을 얼마인가?
이제, 한단계 더 들어가서 입문반 페이지뷰, 스크롤을 거쳐 입문반 신청하기 버튼 클릭까지 한 세션 수를 구하고 전환율까지 구해보자. 위에서 짰던 쿼리를 조금만 변형하면 된다!
예를 들어, 입문반 페이지 안에 있는 입문반 신청하기, 1 day 신청하기, 패키지 신청하기 3개 중에 하나만 클릭해도 전환이라고 본다고 하자. 조건절을 작성할 때 참고한다!
•
입문반 신청하기 버튼 클릭 : event_name = ‘SQL_basic_form_click’
•
1 day 신청하기 버튼 클릭 : event_name = ‘SQL_basic_1day_form_click’
•
패키지 신청하기 버튼 클릭 : event_name = ‘SQL_package_form_click’
출력해야할 속성은 다음과 같다
pv | scroll | click | pv_scroll_rate | scroll_click_rate | pv_click_rate |
-- 입문반 페이지를 본 세션, 스크롤을 한 세션, click을 한 세션 테이블을 각각 만든다.
WITH pv AS (
SELECT user_pseudo_id,
ga_session_id,
event_timestamp_kst AS pv_at
FROM ga
WHERE page_title = '백문이불여일타 SQL 캠프 입문반'
and event_name = 'page_view'
), scroll AS (
SELECT user_pseudo_id,
ga_session_id,
event_timestamp_kst AS scroll_at
FROM ga
WHERE page_title = '백문이불여일타 SQL 캠프 입문반'
and event_name = 'scroll'
), click AS (
SELECT user_pseudo_id,
ga_session_id,
event_timestamp_kst AS click_at
FROM ga
WHERE page_title = '백문이불여일타 SQL 캠프 입문반'
AND (event_name = 'SQL_basic_form_click'
OR event_name = 'SQL_basic_1day_form_click'
OR event_name = 'SQL_package_form_click')
)
SELECT COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv,
COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) AS scroll,
COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) AS click,
COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv_scroll_rate,
COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) / COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) AS scroll_click_rate,
COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv_click_rate
FROM pv
LEFT JOIN scroll ON pv.user_pseudo_id = scroll.user_pseudo_id
AND pv.ga_session_id = scroll.ga_session_id
AND pv_at <= scroll_at
LEFT JOIN click ON scroll.user_pseudo_id = click.user_pseudo_id
AND scroll.ga_session_id = click.ga_session_id
AND scroll_at <= click_at;
SQL
복사
페이지뷰에서 스크롤로의 전환율은 27% , 스크롤에서 클릭으로의 전환율은 12%, 페이지뷰에서 클릭으로의 전환율은 3% 정도다.
결과 데이터 해석하기
입문반 페이지를 보는 단계서부터 최종 클릭까지의 단계까지 전환율은 3.35%다. 목표를 달성하려면
•
입문반 페이지뷰를 한 세션을 11350건으로 늘리거나(10배)
•
각 단계 사이의 전환율을 끌어올려서 최종 전환율 33.5% 로 올리면 될 것이다.
좀 더 구체적으로 단계를 살펴보자.
페이지 뷰 - 스크롤 전환율
페이지뷰를 한 세션 중 27.22%가 스크롤을 한다. 그렇다면 나머지 72.78%는 뭘 하는걸까?
1.
세션을 종료한다.
a.
애초에 관심없는 유저들에게 광고 노출이 많이 되고 있다
b.
광고 문구와 페이지 내용이 일치하지 않는다.
→ Action Item : 광고 문구나 외부로 노출되고 있는 메세지를 점검한다.
c.
처음 들어갔을 때 보이는 페이지 내용이 아래로 더 내려볼만큼 충분히 흥미를 끌지 못한다.
→ Action Item : 이미지나 동영상을 추가해보는 등 구성을 변경해본다.
→ Action Item : 매력적인 페이지 타이틀을 고민해본다.
2.
다른 메뉴를 눌러본다.
a.
유저들이 다른 메뉴가 더 궁금한 걸 수도 있다.
→ Action Item : 그 메뉴를 본 이후의 행동을 확인해본다.
→ Action Item : 다른 메뉴를 클릭한 유저들이 신청하기 버튼 클릭으로 더 많이 전환된다면 그 페이지로 광고 랜딩 페이지를 바꿔본다. 혹은 그 내용을 입문반 페이지에 추가한다.
스크롤 - 신청하기 버튼 클릭 전환율
스크롤한 세션 중 클릭으로 전환된 비율은 12.3%다. 나머지 87.7%는 스크롤은 하지만 이탈한다. 내용을 읽었지만 설득되지 않은 것이다. 여기서 얼마나 읽고 이탈하는 것인지 궁금할 것이다.
이탈한 이유는 이렇게 생각해볼 수 있다.
1.
스크롤만 슥슥하고 바로 나간다
→ 페이지에 눈길을 끄는 문구가 없었다
→ Action Item : 페이지 방문자들이 반응할 만한 문구를 찾아본다
2.
천천히 스크롤하며 읽었지만 나간다
→ 페이지 내용에 설득력이 부족하다
→ Action Item : 페이지 내용을 개선한다
추가적으로 신청하기 버튼이 있는데까지 스크롤해볼 사람 수 자체가 적었다면
→ Action Item : 스크롤하지 않아도 신청하기 버튼이 보이도록 만든다. (퍼널에서 스크롤 단계를 없애는 것)
Q1. 스크롤 속도도 알 수 있는가? 천천히 읽었는지, 빠르게 읽었는지 어떻게 알 수 있는가?
GA에서 페이지뷰 이벤트와 스크롤 이벤트를 기록해주고 있으니까 각각의 이벤트가 찍힌 시간의 차이를 계산해서 얼마나 빨리 스크롤했는지 확인할 수 있다. 기본 스크롤 이벤트는 페이지의 90% 지점까지 스크롤했을 때 찍히는데, 스크롤 이벤트를 커스텀해서 만들 수도 있다. 20% 지점, 50% 지점까지 봤을 때 찍히는 스크롤 이벤트를 만들어서 데이터를 쌓으면 좀 더 디테일한 데이터를 볼 수도 있다.
유입 늘리기는 어떻게 할 수 있는가?
현업에서 유입 퍼널을 볼 때는 자주 유입채널별로 사용자를 나누어서 확인한다. 유입채널별로 쪼개 보면 유입뿐만 아니라 위에서 살펴본 퍼널 중간 단계에서도 또 다른 인사이트를 발견할 수도 있다. 전체를 한번에 보는 것보다 데이터를 쪼개서 볼 때 더많은 인사이트를 얻을 수 있다.
실제로 유입채널별로 사용자들이 다른 행동을 보일 때가 많다. 전환율에서 생각보다 큰 차이가 나기도 한다.
유입 채널별 전환율 구하기
UTM 파라미터를 사용해서 유입채널 데이터를 쌓아놨다. ga 테이블에서 source , medium 컬럼을 보면 유입채널을 알 수 있다. 이 데이터를 활용해서 유입채널별로 전환율을 구해주자.
‘백문이불여일타 SQL 캠프 입문반’ 페이지를 본 사람들의 행동 로그를 분석하여 입문반 신청하기 버튼 클릭을 최대화하는 방향으로 퍼널 최적화를 하고 싶다고 하자. 유입 채널별로 퍼널을 계산하여 어떤 채널에서 유입이 많이 발생했는지, 어떤 채널로 들어온 유저들의 전환율이 좋은지 등을 분석할 수 있다.
유입 채널 별로 입문반 페이지를 본 이후에 스크롤을 내려본 세션은 얼마나 되는지, 또 스크롤을 내려 입문반 신청하기 버튼을 클릭한 세션은 얼마나 되는지, 퍼널의 현황을 파악할 수 있는 데이터를 추출하자. 동일 세션 내에서도 이벤트들 간의 선후 관계를 고려하자. 이벤트들 간의 선후 관계를 고려할 때 동시에 일어난 경우도 전환으로 카운트하자.
** 참고 : source 칼럼은 유저를 획득한 트래픽 소스, medium 컬럼은 유저를 획득한 트래픽 매체 값을 담고 있다. 한 유저의 같은 세션 내에서 발생한 이벤트(event_name) 들은 모두 같은 source와 medium값을 가진다.
ga 테이블에 있는 전체 기간의 데이터를 사용하며, 결과는 아래의 컬럼들을 포함한다. click_after_scroll 컬럼을 기준으로 내림차순으로 정렬하고, click_after_scroll 값이 같다면 pv 컬럼을 기준으로 내림차순으로 정렬한다.
•
source
•
medium
•
pv
•
scroll_after_pv
•
click_after_scroll
•
pv_scroll_rate : scroll_after_pv / pv
•
pv_click_rate : click_after_scroll / pv
•
scroll_click_rate : click_after_scroll / scroll_after_pv
source | medium | pv | scroll_after_pv | click_after_scroll | pv_scroll_rate | pv_click_rate | scroll_click_rate |
WITH pv AS (
SELECT source,
medium,
user_pseudo_id,
ga_session_id,
event_timestamp_kst AS pv_at
FROM ga
WHERE page_title = '백문이불여일타 SQL 캠프 입문반'
and event_name = 'page_view'
), scroll AS (
SELECT user_pseudo_id,
ga_session_id,
event_timestamp_kst AS scroll_at
FROM ga
WHERE page_title = '백문이불여일타 SQL 캠프 입문반'
and event_name = 'scroll'
), click AS (
SELECT user_pseudo_id,
ga_session_id,
event_timestamp_kst AS click_at
FROM ga
WHERE page_title = '백문이불여일타 SQL 캠프 입문반'
AND (event_name = 'SQL_basic_form_click'
OR event_name = 'SQL_basic_1day_form_click'
OR event_name = 'SQL_package_form_click')
)
SELECT source,
medium,
COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv,
COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) AS scroll_after_pv,
COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) AS click_after_scroll,
COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv_scroll_rate,
COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) / COUNT(DISTINCT scroll.user_pseudo_id, scroll.ga_session_id) AS scroll_click_rate,
COUNT(DISTINCT click.user_pseudo_id, click.ga_session_id) / COUNT(DISTINCT pv.user_pseudo_id, pv.ga_session_id) AS pv_click_rate
FROM pv
LEFT JOIN scroll ON pv.user_pseudo_id = scroll.user_pseudo_id
AND pv.ga_session_id = scroll.ga_session_id
AND pv_at <= scroll_at
LEFT JOIN click ON scroll.user_pseudo_id = click.user_pseudo_id
AND scroll.ga_session_id = click.ga_session_id
AND scroll_at <= click_at
GROUP BY medium, source
ORDER BY click_after_scroll DESC, pv DESC;
SQL
복사
해석하기
•
source = (direct) , medium = (none) : 다이렉트로 홈페이지로 유입된 경우
•
source = fb, medium = cpc(cost per click) : 페이스북의 유료광고를 통해 유입된 경우
•
source = brunch, medium = footer : 브런치 페이지의 링크를 통해 유입된 경우
•
source = ig, medium = profile : 인스타그램의 프로필을 통해 유입된 경우
•
source = open_katalk, medium = (none) : 오픈카카오톡을 통해 유입된 경우
•
source = velog, medium = footer : velog라는 플랫폼의 링크를 통해 유입된 경우
•
source = youtube, medium = video : 유튜브의 링크를 통해 유입된 경우
…..
Q1. 페이지뷰가 가장 많은 채널
페이스북 cpc가 가장 페이지뷰가 높다. Q2. 페이지뷰에서 스크롤로 전환율이 가장 높은 채널 / 낮은 채널
가장 높은 채널은 brunch, 가장 낮은 채널은 페이스북 cpc다.Q3. 스크롤에서 클릭으로 전환율이 가장 높은 채널 / 낮은 채널
가장 높은 채널은 유튜브와 오픈 카톡방이고 가장 낮은 채널은 웨비나와 페이스북이다.Q4. 페이지뷰에서 클릭으로 전환율이 가장 높은 채널 / 낮은 채널
가장 높은 채널은 유튜브이고, 가장 낮은 채널은 페이스북과 웨비나다. Q5. 클릭 전환수가 가장 높은 채널
(direct) , (none) 이 클릭 전환수가 가장 높다. 이를 제외하면 페이스북 cpc와 브런치가 높다고 나온다. 채널 간의 단순 비교를 통해 해석하기는 어렵다. 채널마다의 특성을 고려해서 해석해줘야 한다.
채널의 특성에 따라 나눠보기
1.
유료 /무료 채널로 나눠보기
•
유료 : fb / cpc , ig / profile
•
무료 : brunch, velog, youtube, open_katalk
2.
무료 채널을 운영 리소스에 따라 나눠보기
•
open_katalk , careerly( 시간 얼마 안걸림 )
: 링크나 간단한 문구를 업로드하는 채널
•
brunch, velog (시간 좀 듬)
: 긴 글을 올리는 채널
•
youtube (시간 많이 듬)
: 영상을 올리는 채널
채널의 특성을 반영해 데이터 해석하기
•
fb/ cpc는 전환율이 가장 낮지만(1.03%) 클릭 전환수는 가장 많음(8건)
•
youtube는 전환율이 가장 높지만(18.18%) 클릭 전환수는 적음(2건)
•
open_katalk는 전환율이 높지만(17.65%) 클릭 전환수는 적음(3건)
•
brunch는 전환율이 높으면서(14.81%) 클릭 전환수도 많음(8건)
youtube와 open_katalk은 비슷하게 높은 전환율을 보이지만 클릭 전환수 자체는 낮은 채널이다.채널 관리에 드는 리소스를 고려하면 open_katalk이 youtube에 비해 훨씬 효과적인 채널이라고 할 수 있다. 전환율과 전환수까지 고려할 때 brunch가 가장 좋은 채널이지만, 긴 글을 써야하기 때문에(채널 특성상) 퀄리티를 유지하면서 콘텐츠를 더 많이 발행하려면 부담이 늘어난다는 단점이 있다 단기간에 클릭 전환수를 키우기 위해서는 유료 마케팅 위주(fb / cpc)로 유입을 늘리고 장기적으로는 전환율이 잘 나오는 무료 채널을 통한 유입량을 늘려가면 좋을 것이라고 생각할 수 있다.