💡 입구 페이지 = 사이트에 방문했을 때 처음 접근한 페이지 접근 분석 도구에 따라서는 랜딩 페이지라고도 부름. 출구 페이지 = 마지막 접근한 페이지, 이탈한 페이지 접근 분석 도구에 따라서는 이탈 페이지라고도 부름.
코드15-1. 각 세션의 입구 페이지와 출구 페이지 경로를 추출하는 쿼리
WITH
activity_log_with_landing_exit AS(
select
session
,path
,stamp
,first_value(path)
over(
partition by session
order by stamp asc
rows between unbounded preceding
and unbounded following
) as landing
,last_value(path)
over(
partition by session
order by stamp asc
rows between unbounded preceding
and unbounded following
) as exit
from activity_log
)
select *
from
activity_log_with_landing_exit
코드 15-1 실행 결과
코드15-2. 각 세션의 입구 페이지와 출구 페이지를 기반으로 방문 횟수를 추출하는 쿼리
with activity_log_with_landing_exit as(
with activity_log_with_landing_exit as(
select
session
,path
,stamp
,first_value(path)
over(
partition by session
order by stamp asc
rows between unbounded preceding
and unbounded following
) as landing
,last_value(path)
over(
partition by session
order by stamp asc
rows between unbounded preceding
and unbounded following
) as exit
from activity_log
)
select *
from
activity_log_with_landing_exit
)
, landing_count as(
--입구 페이지의 방문 횟수 집계하기
select
landing as path
,count(distinct session) as count
from activity_log_with_landing_exit
group by landing
)
, exit_count as(
--출구 페이지의 방문 횟수 집계하기
select
exit as path
,count(distinct session) as count
from
activity_log_with_landing_exit
group by exit
)
--입구 페이지와 출구 페이지 방문 횟수 결과를 한꺼번에 출력하기
select 'landing' as type, * from landing_count
union all
select 'exit' as type, * from exit_count
코드15-3. 세션별 입구 페이지와 출구 페이지의 조합을 집계하는 쿼리
with activity_log_with_landing_exit as(
WITH
activity_log_with_landing_exit AS(
select
session
,path
,stamp
,first_value(path)
over(
partition by session
order by stamp asc
rows between unbounded preceding
and unbounded following
) as landing
,last_value(path)
over(
partition by session
order by stamp asc
rows between unbounded preceding
and unbounded following
) as exit
from activity_log
)
select *
from
activity_log_with_landing_exit
)
select
landing
,exit
,count(distinct session) as count
from
activity_log_with_landing_exit
group by
landing, exit
코드 15-3 실행 결과
코드15-4. 경로별 이탈률을 집계하는 쿼리
with activity_log_with_exit_flag as(
select *
--출구 페이지 판정
, case
when row_number() over(partition by session order by stamp desc) = 1 then 1
else 0
end as is_exit
from activity_log
)
select path
,sum(is_exit) as exit_count
,count(1) as page_view
,avg(100.0 * is_exit) as exit_ratio
from activity_log_with_exit_flag
group by path
코드 15-4 실행 결과
코드15-5. 경로들의 직귀율을 집계하는 쿼리
with
activity_log_with_landing_bounce_flag as (
select *
--입구 페이지 판정
, case
when row_number() over(partition by session order by stamp asc) = 1 then 1
else 0
end as is_landing
--직귀 판정
, case
when count(1) over(partition by session) = 1 then 1
else 0
end as is_bounce
from activity_log
)
select
path
,sum(is_bounce) as bounce_count
,sum(is_landing) as landing_count
,avg(100.0 * case when is_landing = 1 then is_bounce end) as bounce_ratio
from activity_log_with_landing_bounce_flag
group by path
코드 15-4 실행 결과
3절. 성과로 이어지는 페이지 파악하기
방문 횟수를 아무리 늘려도, 성과와 관계 없는 페이지로만 사용자가 유도된다면, 성과가 발생하지 않음
성과와 직결되는 페이지로 유도해야 웹사이트 전체의 CVR을 향상시킬 수 있음
여러 가지 검색 기능을 제공할 때, 성과에 이르는 비율이 적은 검색 기능이 있다면 위치를 옮기거나, 삭제하는 등의 방안 검토
코드15-6. 컨버전 페이지보다 이전 접근에 플래그를 추가하는 쿼리
WITH
activity_log_with_conversion_flag AS (
SELECT
SESSION
, stamp
, PATH
-- 성과를 발생시키는 컨버전 페이지의 이전 접근에 플래그 추가하기
, sign(SUM(CASE WHEN PATH = '/complete' THEN 1 ELSE 0 END)
OVER(PARTITION BY SESSION ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
AS has_conversion
FROM
activity_log
)
SELECT *
FROM
activity_log_with_conversion_flag
ORDER BY
SESSION, stamp
;
코드 15-6 실행 결과
OVER 절: 집계 함수의 작업 범위를 지정.
형식
집계 함수 OVER (PARTITION BY 컬럼1, 컬럼2, ... 컬럼N ORDER BY 정렬컬럼 [ASC 또는 DESC] ROWS 범위)
ROWS 범위는 윈도우 함수의 범위를 지정하는데 사용. 범위에는 UNBOUNDED PRECEDING, CURRENT ROW, UNBOUNDED FOLLOWING 등의 키워드가 사용.
PARTITION BY: OVER 절과 함께 사용되며, 집계 함수 또는 창 함수의 작업 범위를 그룹화할 기준을 제공
형식
SELECT 집계 함수 또는 창 함수
OVER (PARTITION BY 컬럼1, 컬럼2, ... 컬럼N)
FROM 테이블명
여기서 "컬럼1, 컬럼2, ... 컬럼N"은 그룹화할 컬럼들의 목록. 이 컬럼들에 따라 결과 집합이 분할되며, 각 그룹에 대해 집계 함수 또는 창 함수가 실행.
PARTITION BY 구문을 사용하면 결과 집합을 여러 그룹으로 나눌 수 있으며, 각 그룹에 대해 개별적인 계산을 수행할 수 있다.
코드15-7. 경로들의 방문 횟수와 구성 수를 집계하는 쿼리
WITH
activity_log_with_conversion_flag AS (
SELECT
SESSION
, stamp
, PATH
-- 성과를 발생시키는 컨버전 페이지의 이전 접근에 플래그 추가하기
, sign(SUM(CASE WHEN PATH = '/complete' THEN 1 ELSE 0 END)
OVER(PARTITION BY SESSION ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
AS has_conversion
FROM
activity_log
)
SELECT
path
-- 방문 횟수
, COUNT(DISTINCT session) AS sessions
-- 성과 수
, SUM(has_conversion) AS conversions
-- 성과 수 / 방문 횟수
, 1.0 * SUM(has_conversion) / COUNT(DISTINCT session) AS cvr
FROM
activity_log_with_conversion_flag
-- 경로별로 집약
GROUP BY path
;
방문 횟수와 구성 수를 집계하여 CVR을 계산
코드 15-7 실행 결과
4절. 페이지 가치 산출하기
성과로 이어지는 페이지로 사용자를 유도하는 것이 웹사이트 매출 향상에 도움이 될 수 있음
사이트 맵을 변경해서 해당 페이지를 경유하게 만들거나 해당 페이지의 콘텐츠와 광고를 다른페이지에 활용하는 것이 좋음
페이지의 가치를 할당하는 방법
마지막 페이지에 할당하기
직접적인 효과가 있다고 판단할 수 있는 페이지에 성과를 모두 할당
매출에 직접적으로 기여하는 페이지 판단 가능
첫 페이지에 할당하기
성과로 이어지는 계기가 되었던 첫 페이지에 성과를 모두 할당
이를 활용하면 매출에 간접적으로 기여하는 페이지 판단 가능
광고또는 검색 엔진등의 외부 유입에서, 어떤 페이지가 가치가 높은지 판단 가능
균등하게 분산하기
성과에 이르기까지 거쳤던 모든 페이지에 성과를 균등하게 할당
어떤 페이지를 경유했을 때, 사용자가 성과에 이르는지 판단 가능
적은 페이지를 거쳐 성과로 연결된 경우와 반복적으로 방문하는 페이지의 경우
페이지의 가치가 높게 측정됨
성과 지점에서 가까운 페이지에 더 높게 할당하기
마지막 페이지에 가까울수록 높은 가치 할당
성과 지점에서 먼 페이지에 더 높게 할당하기
첫 페이지에 가까울수록 높은 가치 할당
코드15-8. 페이지 가치 할당을 계산하는 쿼리
WITH
activity_log_with_session_conversion_flag AS (
SELECT
SESSION
, stamp
, PATH
-- 성과를 발생시키는 컨버전 페이지의 이전 접근에 플래그 추가하기
, sign(SUM(CASE WHEN PATH = '/complete' THEN 1 ELSE 0 END)
OVER(PARTITION BY SESSION ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
AS has_conversion
FROM
activity_log
)
, activity_log_with_conversion_assign AS (
SELECT
session
, stamp
, path
-- 성과에 이르기까지 접근 로그를 오름차순 정렬
, ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC) AS asc_order
-- 성과에 이르기까지 접근 로그를 내림차순으로 순번
, ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC) AS desc_order
-- 성과에 이르기까지 접근 수 세기
, COUNT(1) OVER(PARTITION BY session) AS page_count
-- 1. 성과에 이르기까지 접근 로그에 균등한 가치 부여
, 1000.0 / COUNT(1) OVER(PARTITION BY session) AS fair_assign
-- 2. 성과에 이르기까지 접근 로그의 첫 페이지 가치 부여
, CASE
WHEN ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC) = 1
THEN 1000.0
ELSE 0.0
END AS first_assign
-- 3. 성과에 이르기까지 접근 로그의 마지막 페이지 가치 부여
, CASE
WHEN ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC) = 1
THEN 1000.0
ELSE 0.0
END AS last_assign
-- 4. 성과에 이르기까지 접근 로그의 성과 지점에서 가까운 페이지 높은 가치 부여
, 1000.0
* ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC)
-- 순번 합계로 나누기( N*(N+1)/2 )
/ ( COUNT(1) OVER(PARTITION BY session)
* (COUNT(1) OVER(PARTITION BY session) + 1) / 2)
AS decrease_assign
-- 5. 성과에 이르기까지의 접근 로그의 성과 지점에서 먼 페이지에 높은 가치 부여
, 1000.0
* ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC)
-- 순번 합계로 나누기( N*(N+1)/2 )
/ ( COUNT(1) OVER(PARTITION BY session)
* (COUNT(1) OVER(PARTITION BY session) + 1) / 2)
AS increase_assign
FROM activity_log_with_session_conversion_flag
WHERE
-- conversion으로 이어지는 세션 로그만 추출
has_conversion = 1
-- 입력, 확인, 완료 페이지 제외하기
AND path NOT IN ('/input', '/confirm', '/complete')
)
SELECT
session
, asc_order
, path
, fair_assign AS fair_a
, first_assign AS first_a
, last_assign AS last_a
, decrease_assign AS dec_a
, increase_assign AS inc_a
FROM
activity_log_with_conversion_assign
ORDER BY
session, asc_order;
SELECT구문 내부에서 한 conversion에 1,000이라는 가치 할당 이후, 하나의 로그별 가치를 윈도 함수로 집계
코드 15-8 실행 결과
출력 결과 확인시,
한 conversion에 1,000의 가치가 부여됨
fair_assign, 세션의 수로 나눈 평균값이 할당
first_assign, 처음 접근에만 가치 할당
last_assign, 마지막 접근에만 가치 할당
코드15-9. 경로별 페이지 가치 합계를 구하는 쿼리
WITH
activity_log_with_session_conversion_flag AS (
SELECT
SESSION
, stamp
, PATH
-- 성과를 발생시키는 컨버전 페이지의 이전 접근에 플래그 추가하기
, sign(SUM(CASE WHEN PATH = '/complete' THEN 1 ELSE 0 END)
OVER(PARTITION BY SESSION ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
AS has_conversion
FROM
activity_log
)
, activity_log_with_conversion_assign AS (
SELECT
session
, stamp
, path
-- 성과에 이르기까지 접근 로그를 오름차순 정렬
, ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC) AS asc_order
-- 성과에 이르기까지 접근 로그를 내림차순으로 순번
, ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC) AS desc_order
-- 성과에 이르기까지 접근 수 세기
, COUNT(1) OVER(PARTITION BY session) AS page_count
-- 1. 성과에 이르기까지 접근 로그에 균등한 가치 부여
, 1000.0 / COUNT(1) OVER(PARTITION BY session) AS fair_assign
-- 2. 성과에 이르기까지 접근 로그의 첫 페이지 가치 부여
, CASE
WHEN ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC) = 1
THEN 1000.0
ELSE 0.0
END AS first_assign
-- 3. 성과에 이르기까지 접근 로그의 마지막 페이지 가치 부여
, CASE
WHEN ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC) = 1
THEN 1000.0
ELSE 0.0
END AS last_assign
-- 4. 성과에 이르기까지 접근 로그의 성과 지점에서 가까운 페이지 높은 가치 부여
, 1000.0
* ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC)
-- 순번 합계로 나누기( N*(N+1)/2 )
/ ( COUNT(1) OVER(PARTITION BY session)
* (COUNT(1) OVER(PARTITION BY session) + 1) / 2)
AS decrease_assign
-- 5. 성과에 이르기까지의 접근 로그의 성과 지점에서 먼 페이지에 높은 가치 부여
, 1000.0
* ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC)
-- 순번 합계로 나누기( N*(N+1)/2 )
/ ( COUNT(1) OVER(PARTITION BY session)
* (COUNT(1) OVER(PARTITION BY session) + 1) / 2)
AS increase_assign
FROM activity_log_with_session_conversion_flag
WHERE
-- conversion으로 이어지는 세션 로그만 추출
has_conversion = 1
-- 입력, 확인, 완료 페이지 제외하기
AND path NOT IN ('/input', '/confirm', '/complete')
)
, page_total_values AS (
-- 페이지 가치 합계 계산
SELECT
path
, SUM(fair_assign) AS fair_assign
, SUM(first_assign) AS first_assign
, SUM(last_assign) AS last_assign
FROM
activity_log_with_conversion_assign
GROUP BY
path
)
SELECT * FROM page_total_values;
코드 15-9 실행 결과
코드15-10. 경로들의 평균 페이지 가치를 구하는 쿼리
WITH
activity_log_with_session_conversion_flag AS (
SELECT
SESSION
, stamp
, PATH
-- 성과를 발생시키는 컨버전 페이지의 이전 접근에 플래그 추가하기
, sign(SUM(CASE WHEN PATH = '/complete' THEN 1 ELSE 0 END)
OVER(PARTITION BY SESSION ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
AS has_conversion
FROM
activity_log
)
, activity_log_with_conversion_assign AS (
SELECT
session
, stamp
, path
-- 성과에 이르기까지 접근 로그를 오름차순 정렬
, ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC) AS asc_order
-- 성과에 이르기까지 접근 로그를 내림차순으로 순번
, ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC) AS desc_order
-- 성과에 이르기까지 접근 수 세기
, COUNT(1) OVER(PARTITION BY session) AS page_count
-- 1. 성과에 이르기까지 접근 로그에 균등한 가치 부여
, 1000.0 / COUNT(1) OVER(PARTITION BY session) AS fair_assign
-- 2. 성과에 이르기까지 접근 로그의 첫 페이지 가치 부여
, CASE
WHEN ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC) = 1
THEN 1000.0
ELSE 0.0
END AS first_assign
-- 3. 성과에 이르기까지 접근 로그의 마지막 페이지 가치 부여
, CASE
WHEN ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC) = 1
THEN 1000.0
ELSE 0.0
END AS last_assign
-- 4. 성과에 이르기까지 접근 로그의 성과 지점에서 가까운 페이지 높은 가치 부여
, 1000.0
* ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC)
-- 순번 합계로 나누기( N*(N+1)/2 )
/ ( COUNT(1) OVER(PARTITION BY session)
* (COUNT(1) OVER(PARTITION BY session) + 1) / 2)
AS decrease_assign
-- 5. 성과에 이르기까지의 접근 로그의 성과 지점에서 먼 페이지에 높은 가치 부여
, 1000.0
* ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC)
-- 순번 합계로 나누기( N*(N+1)/2 )
/ ( COUNT(1) OVER(PARTITION BY session)
* (COUNT(1) OVER(PARTITION BY session) + 1) / 2)
AS increase_assign
FROM activity_log_with_session_conversion_flag
WHERE
-- conversion으로 이어지는 세션 로그만 추출
has_conversion = 1
-- 입력, 확인, 완료 페이지 제외하기
AND path NOT IN ('/input', '/confirm', '/complete')
)
, page_total_values AS (
-- 페이지 가치 합계 계산
SELECT
path
, SUM(fair_assign) AS sum_fair
, SUM(first_assign) AS sum_first
, SUM(last_assign) AS sum_last
, SUM(decrease_assign) AS sum_dec
, SUM(increase_assign) AS sum_inc
FROM
activity_log_with_conversion_assign
GROUP BY
path
)
, page_total_cnt AS (
-- 페이지 뷰 계산
SELECT
path
, COUNT(1) AS access_cnt -- 페이지 뷰
-- 방문 횟수로 나누고 싶은 경우, 다음과 같은 코드 작성
--, COUNT(DISTINCT session) AS access_cnt
FROM
activity_log
GROUP BY
path
)
SELECT
-- 한 번의 방문에 따른 페이지 가치 계산
s.path
, s.access_cnt
, v.sum_fair / s.access_cnt AS avg_fair
, v.sum_first / s.access_cnt AS avg_first
, v.sum_last / s.access_cnt AS avg_last
, v.sum_dec / s.access_cnt AS avg_dec
, v.sum_inc / s.access_cnt AS avg_inc
FROM
page_total_cnt AS s
JOIN
page_total_values As v
ON s.path = v.path
ORDER BY
s.access_cnt DESC;
WITH
activity_log_with_session_click_conversion_flag AS (
SELECT
SESSION
, stamp
, PATH
, search_type
-- 상세 페이지 이전 접근에 플래그 추가하기
, sign(sum(CASE WHEN PATH = '/detail' THEN 1 ELSE 0 END)
OVER(PARTITION BY SESSION ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
AS has_session_click --즉 detail이면 click했다는 것!
-- 성과를 발생시키는 페이지의 이전 접근에 플래그 추가하기
, sign(sum(CASE WHEN PATH = '/complete' THEN 1 ELSE 0 END)
OVER(PARTITION BY SESSION ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
AS has_session_conversion -- 즉 complete 이면 구매(여기서는 직무지원)했다는 것!
FROM
activity_log
)
SELECT
SESSION
, stamp
, PATH
, search_type
, has_session_click AS click
, has_session_conversion AS cnv
FROM
activity_log_with_session_click_conversion_flag
ORDER BY
SESSION, stamp
;
WITH
activity_log_with_session_click_conversion_flag AS (
SELECT
SESSION
, stamp
, PATH
, search_type
-- 상세 페이지 이전 접근에 플래그 추가하기
, sign(sum(CASE WHEN PATH = '/detail' THEN 1 ELSE 0 END)
OVER(PARTITION BY SESSION ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
--UNBOUNDED PRECEDING : 윈도우의 시작 위치로 첫번째 ROW를 넣어줄 때
AS has_session_click --즉 detail이면 click했다는 것!
-- 성과를 발생시키는 페이지의 이전 접근에 플래그 추가하기
, sign(sum(CASE WHEN PATH = '/complete' THEN 1 ELSE 0 END)
OVER(PARTITION BY SESSION ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW))
AS has_session_conversion -- 즉 complete 이면 구매(여기서는 직무지원)했다는 것!
FROM
activity_log
)
SELECT
search_type
, count(1) AS count
, sum(has_session_click) AS detail -- 총 클릭 횟수
, avg(has_session_click) AS ctr -- 0과 1이니, 평균이면 클릭율 == CTR
, sum(CASE WHEN has_session_click = 1 THEN has_session_conversion END) AS CONVERSION -- complete 횟수
, avg(CASE WHEN has_session_click = 1 THEN has_session_conversion END) AS cvr -- CVR
FROM
activity_log_with_session_click_conversion_flag
WHERE
-- 검색 로그만 추출하기
PATH = '/search_list'
-- 검색 조건으로 집약하기
GROUP BY
search_type -- 검색 조건으로 그룹바이
ORDER BY
count DESC
;
WITH
activity_log_with_session_click_conversion_flag AS (
SELECT
SESSION
, stamp
, PATH
, search_type
-- 상세 페이지의 직전 접근에 플래그 추가하기
, CASE
WHEN lag(path) over(PARTITION BY SESSION ORDER BY stamp desc) = '/detail'
THEN 1
ELSE 0
END AS has_session_click
, sign(
sum(CASE WHEN PATH = '/complete' THEN 1 ELSE 0 END)
OVER(PARTITION BY SESSION ORDER BY stamp DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
) AS has_session_conversion
FROM
activity_log
)
SELECT
SESSION
, stamp
, PATH
, search_type
, has_session_click AS click
, has_session_conversion AS cnv
FROM
activity_log_with_session_click_conversion_flag
ORDER BY
SESSION, stamp
;
💡 TIP 검색 결과가 너무 적게 나오지 않도록 검색 조건을 설정하는 것이 중요!
6 폴아웃 리포트를 사용해 사용자 회유를 가시화하기
💡 SQL : LAG 함수, FIRST_VALUE 함수 분석 : 폴아웃
💡 폴아웃 리포트 : 웹사이트가 사용자를 어떻게 유도하는지를 대략적으로 파악하고 싶을 때 유용한 리포트 - 성과로 이어진 사용자 - 성과로 이어지지 못한 사용
코드15-14. 폴아웃 단계 순서를 접근 로그와 결합하는 쿼리
WITH
mst_fallout_step AS (
-- 폴아웃 단계와 경로의 마스터 테이블
SELECT 1 AS step, '/' AS path
UNION ALL SELECT 2 AS step, '/search_list' AS path
UNION ALL SELECT 3 AS step, '/detail' AS path
UNION ALL SELECT 4 AS step, '/input' AS path
UNION ALL SELECT 5 AS step, '/complete' AS path
)
, activity_log_with_fallout_step AS (
SELECT
l.session
, m.step
, m.PATH
-- 첫 접근과 마지막 접근 시간 구하기
, MAX(l.stamp) AS max_stamp
, MIN(l.stamp) AS min_stamp
FROM
mst_fallout_step AS m
JOIN
activity_log AS l
ON m.path = l.path
GROUP BY
-- 세션별로 단계 순서와 경로를 사용해 집악하기
l.SESSION, m.step, m.path
)
, activity_log_with_mod_fallout_step AS (
SELECT
session
, step
, PATH
, max_stamp
-- 직전 단계에서의 첫 접근 시간 구하기
, LAG(min_stamp)
OVER(PARTITION BY SESSION ORDER BY step)
AS lag_min_stamp
-- 세션에서의 단계 순서 최소값 구하기
, MIN(step) over(PARTITION BY SESSION) AS min_step
-- 해당 단계에 도달할 때까지 걸린 단계 수 누계
, COUNT(1)
OVER(PARTITION BY SESSION ORDER BY step
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS cum_count
FROM
activity_log_with_fallout_step
)
SELECT *
FROM activity_log_with_mod_fallout_step
ORDER BY SESSION, step
;
코드 15-14 실행 결과
결과 과정
session에 step=1인 URL이 있는 경우
step=1 URL(현재 예제의 경우 최상위 페이지’/’)에 접근하지 않은 경우, 폴아웃 리포트 대상에서 제외
현재 step이 해당 step에 도달할 때까지의 누계 step 수와 같은지 확인
step=2 페이지(/search_list)를 스킵하고, step=3 페이지(detail)에 직접 접근한 로그 등을 제외
바로 전의 atep에 처음 접근한 시각이 현재 step의 최종 접근 시각보다 이전인지 확인
step=3 페이지에 접근한 후에 step=2로 돌아간 경우
step=3에 접근했던 로그를 제외
step=2에 처음 접근한 시각이 step=3의 최종 접근 시각보다 이전인지 확인
step=1의 경우 앞에 페이지가 따로 존재하지 않으므로외
코드15-15. 폴아웃 리포트에 필요한 로그를 압축하는 쿼리
WITH
mst_fallout_step AS (
-- [코드 15-14] 참고하기
)
, activity_log_with_fallout_step AS (
-- [코드 15-14] 참고하기
)
, activity_log_with_mod_fallout_step AS (
-- [코드 15-14] 참고하기
)
, fallout_log AS (
-- 폴아웃 리포트에 사용할 로그만 추출하기
SELECT
session
, step
, PATH
FROM
activity_log_with_mod_fallout_step
WHERE
-- 세션에서 단계 순서가 1인지 확인하기
min_step = 1
-- 현재 단계 순서가 해당 단계의 도달할 때까지의 누계 단계 수와 같은지 확인하기
AND step = cum_count
-- 직전 단계의 첫 접근 시간이
-- NULL 또는 현재 시간의 최종 접근 시간보다 이전인지 확인하기
AND (lag_min_stamp IS NULL
OR max_stamp >= lag_min_stamp)
)
SELECT *
FROM fallout_log
ORDER BY SESSION, step
;
코드 15-15 실행 결과
코드15-16. 폴아웃 리포트를 출력하는 쿼리
WITH
mst_fallout_step AS (
-- [코드 15-14] 참고하기
)
, activity_log_with_fallout_step AS (
-- [코드 15-14] 참고하기
)
, activity_log_with_mod_fallout_step AS (
-- [코드 15-14] 참고하기
)
, fallout_log AS (
-- [코드 15-14] 참고하기
)
SELECT
step
, path
, COUNT(1) AS count
-- <단계 순서> = 1인 URL부터의 이동률
-- NULL값은 제외하고 COUNT
, 100.0 * COUNT(1)
/ FIRST_VALUE(COUNT(1))
OVER(ORDER BY step ASC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
AS first_trans_rate
-- 직전 단계까지의 이동률
, 100.0 * COUNT(1)
/ LAG(COUNT(1)) OVER(ORDER BY step ASC)
AS step_trans_rate
FROM fallout_log
GROUP BY
step, path
ORDER BY
step
;
WITH
activity_log_with_lead_path AS (
SELECT
SESSION
, stamp
, PATH AS path0
-- 곧바로 접근한 경로 추출하기
, LEAD(PATH, 1) over(PARTITION BY SESSION ORDER BY stamp ASC) AS path1
-- => path를 고를 건데, stamp 기준으로 오름차순한 session 기준으로 1칸 이전 행
, lead(PATH, 2) OVER(PARTITION BY SESSION ORDER BY stamp ASC) AS path2
-- => path를 고를 건데, stamp 기준으로 오름차순한 session 기준으로 2칸 이전 행
FROM
activity_log
)
, raw_user_flow AS(
SELECT
path0
-- 시작 지점 경로로의 접근 수
, sum(count(1)) OVER() AS count0
-- 곧바로 접근한 경로(존재하지 않는 경우 문자열 NULL)
, COALESCE(path1, 'null') AS path1 --coalesce: null이 아닌 첫 인자 반환
-- 곧바로 접근한 경로로의 접근 수
, sum(count(1)) over(PARTITION BY path0, path1) AS count1
-- 이어서 접근한 경로(존재하지 않는 경우 문자열로 'NULL' 지정)
, COALESCE(path2, 'NULL') AS path2
-- 이어서 접근한 경로로의 접근 수
, count(1) AS count2
FROM
activity_log_with_lead_path
WHERE
-- 상세 페이지를 시작 지점으로 두기
path0 = '/detail'
GROUP BY
path0, path1, path2
)
SELECT
path0
, count0
, path1
, count1
, 100.0*count1/count0 AS rate1
, path2,
, count2,
, 100.0*count2/count1 AS rate2
FROM
raw_user_flow
ORDER BY
count1 DESC, count2 DESC
;
코드 15-18. 바로 위 레코드와 같은 값은 출력하지 않게 데이터 가공하기
리포트 도구 등, 고도화된 프로그램의 타 기능을 활용하면 불필요한 중복 데이터를 제거할 수 있다. 하지만 우선 SQL문을 통해 그러한 편집을 해보자.
-- 디버깅 필요
WITH
activity_log_with_lead_path AS (
SELECT
SESSION
, stamp
, PATH AS path0
-- 곧바로 접근한 경로 추출하기
, LEAD(PATH, 1) over(PARTITION BY SESSION ORDER BY stamp ASC) AS path1
-- => path를 고를 건데, stamp 기준으로 오름차순한 session 기준으로 1칸 이전 행
, lead(PATH, 2) OVER(PARTITION BY SESSION ORDER BY stamp ASC) AS path2
-- => path를 고를 건데, stamp 기준으로 오름차순한 session 기준으로 2칸 이전 행
FROM
activity_log
)
, raw_user_flow AS(
SELECT
path0
-- 시작 지점 경로로의 접근 수
, sum(count(1)) OVER() AS count0
-- 곧바로 접근한 경로(존재하지 않는 경우 문자열 NULL)
, COALESCE(path1, 'null') AS path1
-- 곧바로 접근한 경로로의 접근 수
, sum(count(1)) over(PARTITION BY path0, path1) AS count1
-- 이어서 접근한 경로(존재하지 않는 경우 문자열로 'NULL' 지정)
, COALESCE(path2, 'NULL') AS path2
-- 이어서 접근한 경로로의 접근 수
, count(1) AS count2
FROM
activity_log_with_lead_path
WHERE
-- 상세 페이지를 시작 지점으로 두기
path0 = '/detail'
GROUP BY
path0, path1, path2
)
SELECT
CASE
WHEN
COALESCE(
-- 바로 위의 레코드가 가진 path0 추출하기(존재하지 않는 경우 NOT FOUND로 지정)
LAG(path0)
OVER(ORDER BY count1 DESC, count2 DESC)
, 'NOT FOUND') <> path0
THEN path0
END AS path0
, CASE
WHEN
COALESCE(
lag(path0)
OVER(ORDER BY count1 DESC, count2 DESC)
,'NOT FOUND') <> path0
THEN count0
END AS count0
, CASE
WHEN
COALESCE(
-- 바로 위의 레코드가 가진 여러 값을 추출할 수 있게 문자열 결합 후 추출하기
LAG(path0 || path1)
OVER(ORDER BY count1 DESC, count2 DESC)
, 'NOT FOUND') <> (path0 || path1)
THEN path1
END AS page1
, CASE
WHEN
COALESCE(
LAG(path0 || path1)
OVER(ORDER BY count1 DESC, count2 DESC)
,'NOT FOUND') <> (path0 || path1)
THEN 100.0 * count1/coount0
END AS rate1
, CASE
WHEN(
COALESCE(
LAG(path0 || path1 || path2)
OVER(ORDER BY count1 DESC, count2 DESC)
, 'NOT_FOUND') <> (path0 || path1 || path2)
THEN page2
END AS page2
, CASE
WHEN
coalesce(
LAG(path0 || path1 || path2)
OVER(ORDER BY count1 DESC , count2 DESC)
, 'NOT_FOUND') <> (path0 || path1 || path2)
THEN count2
END AS count2
, CASE WHEN
COALESCE(
LAG(path0 || path1 || path2 )
OVER( ORDER BY count1 DESC, count2 desc)
, 'NOT_FOUND') <> (path0 || path1 || path2)
THEN 100.0*count2/count1
END AS rate2
FROM
raw_user_flow
ORDER BY
count1 DESC
, count2 DESC
;
이전 페이지 집계하기
/detail 페이지를 시작 지점으로, 이전 흐름 두 단계까지를 집계한 것.
코드 15-19. /detail 페이지 이전의 사용자 흐름을 집계하는 쿼리
WITH
activity_log_with_lag_path AS (
SELECT
session
, stamp
, path AS path0
-- 바로 전에 접근한 경로 추출하기(존재하지 않는 경우 문자열 'NULL'로 지정)
, COALESCE(LAG(path, 1) OVER(PARTITION BY session ORDER BY stamp ASC), 'NULL') AS path1
-- 그 전에 접근한 페이지 추출하기(존재하지 않는 경우 문자열 'NULL'로 지정)
, COALESCE(LAG(path, 2) OVER(PARTITION BY session ORDER BY stamp ASC), 'NULL') AS path2
FROM
activity_log
)
, raw_user_flow AS (
SELECT
path0
-- 시작 지점 경로로의 접근 수
, SUM(COUNT(1)) OVER() AS count0
, path1
-- 바로 전의 경로로의 접근 수
, SUM(COUNT(1)) OVER(PARTITION BY path0, path1) AS count1
, path2
-- 그 전에 접근한 경로로의 접근 수
, COUNT(1) AS count2
FROM
activity_log_with_lag_path
WHERE
-- 상세 페이지를 시작 지점으로 두기
path0 = '/detail'
GROUP BY
path0, path1, path2
)
SELECT
path2
, count2
, 100.0 * count2 / count1 AS rate2
, path1
, count1
, 100.0 * count1 / count0 AS rate1
, path0
, count0
FROM
raw_user_flow
ORDER BY
count1 DESC
, count2 DESC
;
결과
8절. 페이지 완독률 집계하기
완독률
페이지를 끝까지 읽었는지 비율로 나타낸 것
이를 집계하여 사용자에게 가치를 제대로 전달했는지 확인 가능
직귀율, 이탈률, 페이지 뷰를 확인하더라도 사용자가 페이지 끝까지 조회했는지는 확인이 어려움
따라서 페이지 내용에 만족하면서 이탈했는지, 만족하지 못하여 중간 이탈을 했는지 확인이 어려움
완독률을 집계하려면 페이지 조회 로그가 필요.
이런 로그를 만들기 위해서는 자바스크립트를 사용하여, 어디까지 읽었는지를 전송할 수 있는 시스템이 구축되어야 함
해당 페이지의 길이를 판단한 뒤에, 30%, 60% 조회시 정보 전송 후 로그 기입
코드 15-20. 완독률을 집계하는 쿼리
SELECT
url
, action
, COUNT(1) AS count
, 100.0
* COUNT(1)
/ SUM(CASE WHEN action='view' THEN COUNT(1) ELSE 0 END)
OVER(PARTITION BY url)
AS action_per_view
FROM read_log
GROUP BY
url, action
ORDER BY
url, count DESC
;
결과
EC 사이트(전자상거래)에서는 상품 상세 정보아래에 추천 상품을 출력하는 모듈
뉴스 사이트에서는 기사 마지막에 관련 기사를 출력하는 모듈이 설치되어 있는 경우가 많음
9절. 사용자 행동 전체를 시각화 하기
각각의 항목에서 배웠던 내용과 접근 분석 도구에서 출력해주는 리포트는 어디까지나 정보의 ‘점’에 불과
점과 점을 이어 선을 만들어가며, 전체적인 흐름을 파악할 수 있어야 함
조감도를 사용해 사용자 행동 시각화 하기
어떤 보고서로 상황을 파악하면 좋을지 일반적으로 서비스 전체를 파악할 수 있는 조감도를 만드는 것이 좋음
조감도를 작성하면 사이트와 조직의 전체적인 이미지를 파악할 수 있으며 어디에 문제가 있는지, 어떤 부분을 개선할 지, 팀을 개선할 수 있는 지 등을 파악 가능
서비스 형태에 맞게 조감도 작성하기
서비스 전체를 파악하는 조감도는 웹사이트 이외의 경우에도 활용할 수 있음
어떻게 조감도를 표현할지는 서비스마다 다르나 누가 보아도 쉽고, 확실하게 이해할 수 있는 조감도가 좋은 조감도
