[14] 사이트 내의 사용자 행동 파악하기(1) - landing/exit 페이지 파악하기, 이탈률/직귀율 계산하기, 성과로 이어지는 페이지 파악하기, 페이지 가치 산출하기

목차

3. 데이터 가공을 위한 SQL
 3-1. 하나의 값 조작하기 (5강)
 3-2. 여러 개의 값에 대한 조작 (6강)
  3-3. 하나의 테이블에 대한 조작 (7강)
3-4. 여러 개의 테이블 조작하기 (8강)
4. 매출을 파악하기 위한 데이터 추출 
  4-1. 시계열 기반으로 데이터 집계하기 (9강)
4-2. 다면적인 축을 사용해 데이터 집약하기 (10강)
5. 사용자를 파악하기 위한 데이터 추출 
  5-1. 사용자 전체의 특징과 경향 찾기 (11강)
  5-2. 시계열에 따른 사용자 전체의 상태 변화 찾기 (12강)
  5-3. 시계열에 따른 사용자의 개별적인 행동 분석하기 (13강)
6. 웹사이트에서 행동을 파악하는 데이터 추출하기
  6-1. 사이트 전체의 특징/경향 찾기
  6-2. 사이트 내의 사용자 행동 파악하기
  6-3. 입력 양식 최적화하기
7. 데이터 활용의 정밀도를 높이는 분석 기술
  7-1. 데이터를 조합해서 새로운 데이터 만들기
  7-2. 이상값 검출하기
  7-3. 데이터 중복 검출하기
  7-4. 여러 개의 데이터셋 비교하기
8. 데이터를 무기로 삼기 위한 분석 기술
  8-1. 검색 기능 평가하기
  8-2. 데이터 마이닝
  8-3. 추천
  8-4. 점수 계산하기
9. 지식을 행동으로 옮기기
  9-1. 데이터 활용의 현장

---

※ Mysql 기준
 
6장. 웹사이트에서의 행동을 파악하는 데이터 추출하기

15강. 사이트 내의 사용자 행동 파악하기

 

웹사이트에서의 특징적인 지표 (방문자 수, 방문 횟수, 직귀율, 이탈률) 리포트를 작성하는 SQL 쿼리 소개

 

table activity_log with search_type

Create할 때 참고

CREATE TABLE activity_log(
    stamp        varchar(255),
    session      varchar(255),
    action       varchar(255),
    `option`     varchar(255),  -- 백틱(`)을 사용하여 예약어를 이스케이프하기
    path         varchar(255),
    search_type  varchar(255)
);

 

15-1. 입구 페이지와 출구 페이지 파악하기

FIRST_VALUE 함수, LAST_VALUE 함수, 입구 페이지, 출구 페이지

 

- 입구 페이지는 사이트에 방문했을 때 처음 접근한 페이지로, 랜딩 페이지라고도 부름

- 출구 페이지는 마지막으로 접근한 페이지로, 이탈한 페이지를 나타냄

 

type(landing/exit)에 따른 경로와 입구(출구) 수를 집계하기

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' -- MYSQL에서는 option, exit과 같은 단어는 예약어이기 때문에, alias로 사용시 backticks를 붙여줘야 함
FROM activity_log)
SELECT * FROM activity_log_with_landing_exit;

→ Session 별로 stamp 의 path 중 FIRST_VALUE를 landing으로 추가, LAST_VALUE를 exit으로 추가한 모습

 

추출한 각 세션의 입구 페이지와 출구 페이지의 URL을 기반으로 유니크한 세션의 수를 집계하여 방문 횟수 구하기

-- 앞 쿼리 동일
,
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,
path,
count
FROM landing_count
UNION ALL
SELECT 'exit' AS type,
path,
count
FROM exit_count;

LANDING/EXIT 별 경로 별 COUNT 집계

→ 마지막에 UNION ALL 할 때 >SELECT 'landing' AS type, * FROM ~ 은 실행되지 않음

→ 이유는 UNION(ALL)사용 시 테이블의 컬럼명과 컬럼수가 동일해야 하는데, AS type으로 새로운 컬럼을 추가해주었기 때문에, SELECT 절에 양쪽의 구조가 동일하다는 사실을 표시해주어야 함

 

세션 별 입구 페이지와 출구 페이지의 조합 집계하기

-- activity_log_with_landing_exit 까지 동일
SELECT
landing,
'exit',
COUNT(DISTINCT session) AS count
FROM activity_log_with_landing_exit
GROUP BY landing, 'exit';

→ 웹사이트 담당자는 최상위 페이지부터 사이트 설계를 시작하지만, 최상위 페이지부터 조회를 시작하는 사용자는 거의 없다. 

→ 상세 페이지부터 조회를 시작하는 사용자가 많은 사이트도 존재하기에, 사용자가 어디에 유입되는지 입구 페이지를 파악하면 사이트 설계 시 도움이 될 것

---

15-2. 이탈률과 직귀율 계산하기

ROW_NUMBER 함수, COUNT 윈도 함수, AVG(CASE~END)

 

책에서의 이탈률 = 종료율

책에서의 직귀율 = 이탈율(아무것도 안하고 나갔을 때)

앞서 구한 출구 페이지를 사용해서 이탈률을 계산하고, 문제가되는 페이지 찾아내기

- 직귀율은 '특정 페이지만 조회하고 곧바로 이탈한 비율'- 단순하게 이탈률이 높은 페이지는 나쁘다고 할 수 없다. 사용자가 만족해서 이탈하는 페이지(ex.구매 완료 페이지)는 당연히 이탈률이 높아야 하기 때문에 문제가 되지 않음

 

이탈률 리포트(경로 별 출구 수, 페이지 뷰, 이탈률 집계) 생성

- 경로별 이탈률 집계하기

: CASE문으로 각 세션의 마지막 활동(가장 최근의 stamp)에 대해 is_exit 플래그를 1로 설정하여 집계

WITH activity_log_with_exit_flag AS(
SELECT *,
CASE WHEN
ROW_NUMBER() OVER(PARTITION BY session
ORDER BY stamp DESC) = 1 THEN 1 END AS is_exit
FROM activity_log)
SELECT 
path,
SUM(is_exit) AS exit_count,
COUNT(*) AS page_view,
AVG(100.0 * COALESCE(is_exit, 0)) AS exit_ratio
FROM activity_log_with_exit_flag
GROUP BY path;

 

- 직귀율 집계하기

직귀율 = <직귀 수> / <입구 수> 혹은 <방문 횟수>

→ 순수하게 랜딩 페이지에서 다른 페이지로 이동하는지를 평가하기 위해 전자의 식 사용(직귀수 / 입구 수)

 

* 직귀율이 높은 원인

• 연관 기사 또는 상품으로 사용자를 이동시키는 모듈이 않아서
• 페이지 자체의 콘텐츠에 사용자가 만족하지 않는 경우
• 이동이 복잡해서 다음 단계로 이동하지 못하는 경우

직귀율 리포트(경로 별 직귀 수, 입구 수, 직귀율) 작성하기

- is_landing 플래그와 is_bounce(세션 당 count수가 1=직귀) 플래그를 설정

path가 랜딩 페이지인 경우 is_bounce에 해당하는, 즉 사이트에 들어와서 첫 페이지만 보고 바로 나간 비율(직귀율) 계산

 

※ ROW_NUMBER()

1. 첫 번째와 맨 끝 값을 찾아내고 싶을 때 

2. 중복된 값을 제거하고 싶을 때 (조인 후 넘버링)

※ 직귀 판정 식

-- 직귀 판정
CASE WHEN 
COUNT(*) OVER(PARTITION BY session) = 1 THEN 1 ELSE 0 END AS is_bounce

WITH activity_log_with_landing_bounce_flag AS(
SELECT
*,
CASE WHEN 
-- 입구 페이지 판정 플래그 is_landing
ROW_NUMBER() OVER(PARTITION BY session
ORDER BY stamp ASC) = 1 THEN 1 ELSE 0 END AS is_landing,
-- 직귀 판정(세션 별 카운트 수가 1번인 경우) 플래그 is_bounce
CASE WHEN 
COUNT(*) 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-3. 성과로 이어지는 페이지 파악하기

SIGN 함수, SUM 윈도 함수, CVR

 

성과와 직결되는 페이지를 파악하고 유도하여 웹사이트 전체의 CVR을 향상시킬 수 있다.

1. 페이지 또는 경로에 대한 방문 횟수

2. 방문이 성과로 연결되는지 집계하기

*보충 자료

https://noelee.tistory.com/217

 

1. activity_log_with_conversion_flag 

: 컨버전 페이지보다 이전 접근에 플래그 추가하기

목적

: 세션별 전환 여부 판단

▼

세션별로 complete까지 갔는지 여부 체크하기

▼

 세션이 complete를 포함하고 있을 경우 플래그를 추가하고, 세션의 sum이 1이 넘는지 확인하기

▼

sum(case when path = '/complete'~) 를 사용해서 각 세션에서 경로가 complete인 이벤트가 발생했는지 합산하기

& over(partition by~ orderby~)로 세션별로, stamp가 최신인 순서대로, old부터 현재까지로 윈도우를 정의

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;

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-4. 페이지 가치 산출하기

ROW_NUMBER(), 페이지 평가

 

• 특정 페이지를 경유한 사용자가 더 높은 성과로 이어진다면, 사이트 맵을 변경해서 해당 페이지를 경유하게 만들거나, 해당 페이지의 콘텐츠와 광고를 다른 페이지에도 적용하는 것이 좋다.
• 앞서 사용한 방법에 추가로 '금액' 개념을 사용해서 성과를 고려하는 "페이지 가치" 지표를 사용하면 더 자세하게 페이지를 분석할 수 있다. 

페이지 가치 집계 준비하기

1-1. 성과(무엇을 평가해야 할지) 정의하기

1-2. 어떻게 해당 성과를 수치화할 수 있는지 정의하기

▼

2. 페이지 가치 할당하는 방법 정의하기

▼

3. 페이지 가치 집계하기 

---

1. 성과 수치화하기

• 예를 들어 성과로 자료 청구, 견적 의뢰 신청이 이루어졌을 때라고 정의했다면, 해당 페이지로 이동하는 버튼을 클릭했는지 그 여부로 성과를 수치화할 수 있음
  • 이러한 설정을 기반으로 자료 청구, 견적 의뢰를 할 때마다 1이라는 점수를 부여하는 방법
  • 자료를 청구했을 때의 매출을 점수로 부여하는 방법

※ 매출 금액 등의 구체적인 정보를 산출할 수 없다면, 임시로 1CV의 가치를 1,000으로 설정하는 방법이 있다. 

---

2. 페이지 가치를 할당하는 방법 정하기

: 페이지 가치로 "어떤 판단을 내리고 싶은지"에 따라 페이지에 가치를 할당하는 로직이 달라진다.

5가지 방법

1. 마지막 페이지에 할당하기

: 직접적인 효과가 있다고 판단할 수 있는 페이지에 성과를 모두 할당

→ 목적: 매출에 직접적으로 기여하는 페이지 판단하기

 

2. 첫 페이지에 할당하기

: 성과로 이어지는 계기가 되었던 첫 번째 페이지에 성과를 모두 할당

→  목적: 매출에 간접적으로 기여하는 페이지 판단

(ex. 광고/검색 엔진 등의 외부 유입 中 가치가 높은 페이지 판단)

 

3. 균등하게 분산하기

: 성과에 이르기까지 거쳤던 모든 페이지에 성과를 균등하게 할당

→ 목적: 어떤 페이지를 '경유'했을 때 사용자가 성과에 이르는지 판단

(ex. 적은 페이지를 거쳐 성과로 이어지거나, 반복적으로 방문하는 페이지의 가치가 높게 측정됨)

 

4. 성과 지점에서 가까운 페이지에 더 높게 할당하기

: 마지막 페이지에 가까울수록 높은 가치 할당

 

5. 성과 지점에서 먼 페이지에 더 높게 할당하기

: 첫 페이지에 가까울수록 높은 가치를 할당

---

3. 페이지 가치 집계하기

특정한 계급 수로 히스토그램 만들기

a. 성과

: "신청 입력 양식을 제출하고 완료 화면이 출력된 경우" 

b. 페이지 가치 할당 방법

: 성과 수치를 1,000으로 계산하여 경유헀던 페이지에 균등하게 할당(3번 방법)

c. 조건

: 페이지 평가 시 입력,확인,완료 페이지를 포함하면 집계가 제대로 이루어지지 않기에 집계에서 제외

 

table activity_log

 

※ 성과에 이르기까지의 로그 정렬

ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC) AS asc_order

 

※ 균등 가치 부여

1000.0 / COUNT(*) OVER(PARTITION BY session) AS fair_assign,

 

※ 성과에 이르기까지의 접근 로그 성과 시점에서 가까운 페이지에 높은 가치 부여

- 순번을 오름차순으로 부여해서, 세션의 시작 부분에 방문한 페이지가 낮은 순번을 받게 됨

- 나누는 순번 합계는 전체 페이지 순번의 합 (n*(n+1)/2)

1000.0 * ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC)
-- 순번 합계로 나누기 (N*(N+1)/2)
/ ( COUNT(*) OVER(PARTITION BY session)*( COUNT(*) OVER(PARTITION BY session)+1)/2)

 

※ 조건

-- 컨버전으로 이어지는 세션 로그만 필터링 / 입력, 확인, 완료 페이지 제외
WHERE has_conversion=1 
AND path NOT IN ('/input', '/confirm', '/complete'))

 

전체 쿼리

1. /complete 경로를 방문한 경우를 확인하고 플래그를 설정하는 과정까지 앞과 동일
2. has_conversion 플래그가 1인 로그만 대상으로(전환된) 세션 내 페이지 방문 순서를 ASC, DESC으로 각각 순번 부여
3. 각 세션의 총 페이지 방문 수 page_count 계산
4. decrease_assign은 세션의 시작에서 멀어질 수록(asc로 정렬하여 세션의 시작에서 row_number이 낮음) 가치를 감소시키고, increase는 세션의 시작부터 가까워질수록(desc로 정렬하여 세션의 시작에서 row_number이 큼) 가치를 증가시키는 방식으로 1000을 할당한다.

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),
activity_log_with_conversion_assigning AS(
SELECT
session,
stamp,
path,
-- 성과에 이르기까지의 접근 로그 정렬(ASC)
ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC) AS asc_order,
-- 성과에 이르기까지의 접근 로그에 순번 붙이기(DESC)
ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC) AS desc_order,
-- 성과에 이르기까지 접근 수 세기
COUNT(*) OVER(PARTITION BY session) AS page_count,
-- 1. 성과에 이르기까지의 접근 로그에 균등한 가치 부여하기
1000.0 / COUNT(*) 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(*) OVER(PARTITION BY session)*( COUNT(*) OVER(PARTITION BY session)+1)/2)
AS decrease_assign,
-- 5. 먼 페이지에 높은 가치 부여
1000.0 * ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp DESC)
/ ( COUNT(*) OVER(PARTITION BY session)*( COUNT(*) OVER(PARTITION BY session)+1)/2)
AS increase_assign
FROM activity_log_with_conversion_flag
-- 컨버전으로 이어지는 세션 로그만 필터링 / 입력, 확인, 완료 페이지 제외
WHERE 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_assigning
ORDER BY session, asc_order;

 

이어서 페이지 가치의 합계를 경로별로 집계하기

-- 앞의 쿼리와 동일
,
page_total_values AS(
SELECT
path,
SUM(fair_assign) AS fair_assign,
SUM(first_assign) AS first_assign,
SUM(last_assign) AS last_assign,
SUM(decrease_assign) AS dec_assign,
SUM(increase_assign) AS inc_assign
FROM activity_log_with_conversion_assigning
GROUP BY path)
select * from page_total_values;

경로별로 페이지 가치 합계 확인

 

→ 페이지 뷰가 많은 페이지에서의 페이지 가치가 높게 판정되는 경향이 있다는 한계점이 있다.

▼

페이지 뷰가 적으면서도 높은 페이지 가치를 가진 페이지 찾기

= <페이지 가치> / <페이지 방문횟수> 혹은 <페이지 뷰>

-- 앞 쿼리까지 동일
,
page_total_cnt AS(
SELECT
path,
COUNT(*) AS access_cnt -- 페이지 뷰, 방문횟수로 나눌 경우 count distinct session
FROM activity_log
GROUP BY path)
-- 한 번의 방문에 따른 페이지 가치 계산
SELECT 
s.path,
s.access_cnt,
round(v.fair_assign / s.access_cnt,2) AS avg_fair,
round(v.first_assign / s.access_cnt,2) AS avg_first,
round(v.last_assign / s.access_cnt,2) AS avg_last,
round(v.dec_assign / s.access_cnt,2) AS avg_dec,
round(v.inc_assign / s.access_cnt,2) AS avg_asc
FROM page_total_cnt AS s
JOIN
page_total_values AS v
ON s.path = v.path
ORDER BY s.access_cnt DESC;

페이지 뷰로 나눈 페이지 가치

페이지 가치만 구했을 때와 비교(여기에서는 페이지 뷰가 비슷해서 페이지 가치 비교 결과도 유사)

 

→ 검색 페이지(/search_list)가 상세 페이지(/detail)보다 전반적으로 높은 페이지 가치를 갖고 있다고(성과로 이어진다고) 해석할 수 있다.

---