[SQL] 사이트 내의 사용자 행동 파악하기

데이터 분석을 위한 SQL 레시피 p.343 ~ p.371

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

-- 입구페이지
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

• order by를 지정한 경우의 윈도 함수 파티션은 디폴트로 첫 행부터 현재 행까지임
• ROWS에 대한 조건값이 있을 때와 없을 때 비교

(좌) 있을 때 (우)없을 때

 

돌려보기 :http://sqlfiddle.com/#!17/562a22/1

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

• 이탈률 : 마지막으로 조회한 페이지가 해당 페이지인 비율 → 출구수/페이지뷰
• 직귀율 : 특정 페이지만 조회하고 곧바로 이탈한 비율 → 직귀 수 /입구수 or 직귀수/방문횟수

-- 이탈인지 아닌지 판단하는 쿼리
CASE 
	WHEN ROW_NUMBER () OVER(PARTITION BY session ORDER BY stamp DESC)=1 then 1
	ELSE 0
	END AS is_exit

--직귀인지 아닌지 판단하는 쿼리
CASE 
	WHEN COUNT(1) OVER(PARTITION BY session) = 1 THEN 1
else 0
END AS is_bounce

 

이탈률 돌려보기 : http://sqlfiddle.com/#!17/2cfcd/2

직귀율 돌려보기 : http://sqlfiddle.com/#!17/2cfcd/7

• count(1)과 count(*)의 차이는 ? 없음
• count(*)과 count(아무컬럼)의 차이는? 있음
  • COUNT(컬럼) - NULL 값이 들어간 행은 카운트하지 않습니다.
  • COUNT() - NULL 값에 상관없이 모든 행을 카운트합니다.*
  출처 : https://hue9010.github.io/db/select_count/

3. 성과로 이어지는 페이지 파악하기

성과를 발생시키는 컨버전 페이지의 이전 접근에 플래그 추가하기

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

 

• SIGN이 왜 붙을까? 하나의 session 안에 complete페이지에 여러번 접근하면 sum값이니 1 이상의 숫자가 나올 수 있기 때문에 플래그를 추가하기 위해선 sign을 써야합니다!

플래그 추가 쿼리 돌려보기 : http://sqlfiddle.com/#!17/2cfcd/15

4. 페이지 가치 산출하기

• 성과를 수치화하고, 이 성과를 달성하기까지 방문한 페이지에 가치를 할당하기
• 더 자세한 설명은 아래 링크 참고
• ㄴ https://analyticsmarketing.co.kr/digital-analytics/google-analytics/1680/

<쿼리 뜯어보기>

 

-- 성과페이지에 도달하기까지 거쳐온 페이지 개수 카운트
COUNT(1) OVER(PARTITION BY session) AS page_count

--성과에 이르기까지 접근 로그에 균등한 가치 부여하기
1000.0 / COUNT(1) OVER(PARTITION BY session) AS fair_assign

-- 첫 페이지에 가치 부여하기
CASE WHEN ROW_NUMBER() OVER (PARTITION BY session ORDER BY stamp ASC) = 1 THEN 1000 ELSE 0.0

-- 마지막 페이지에 가치 부여하기
CASE WHEN ROW_NUMBER() OVER (PARTITION BY session ORDER BY stamp DESC) = 1 THEN 1000 ELSE 0.

-- 성과에 이르기까지 가까운 페이지에 높은 가치 부여하기
1000 * ROW_NUMBER() OVER(PARTITION BY session ORDER BY stamp ASC) / ((count(1) OVER(PARTITION BY session) * (count(1) over(PARTITION BY session) +1) /2)

-- 컨버전으로 이어지는 세션 로그만 추출하기 
WHERE has_conversion = 1

•  

5. 검색 조건들의 사용자 행동 가시화하기

상세페이지와 성과페이지 이전 접근 페이지에 모두 플래그 추가한다면,

-- 상세페이지 이전 접근 페이지에 모두 플래그 추가
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

-- 성과 페이지는 path = /complete이외 동일

클릭 플래그를 직전 페이지에 한정한다면,

 

-- 상세페이지 이전 접근 페이지에만 플래그 추가
CASE 
	WHEN LAG(path) OVER(PARTITION BY session ORDER BY stamp desc) = '/detail'
	THNE 1
	ELSE 0
	END AS has_session_click

-- 성과 페이지는 path = /complete이외 동일

쿼리 돌려보기 : http://sqlfiddle.com/#!17/2cfcd/19