[13] 웹사이트에서의 행동을 파악하는 데이터 추출 - 사이트 전체의 특징/경향 찾기 : 유입원/접근요일 별 방문자 수 파악

목차

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장. 웹사이트에서의 행동을 파악하는 데이터 추출하기

14강. 사이트 전체의 특징/경향 찾기
 

접근 로그 테이블 access_log

구매 로그 테이블 purchase_log

---

14-1. 날짜 별 방문자 수 / 방문 횟수 / 페이지 뷰 집계하기

COUNT(DISTINCT~)

지표 정의

방문자 수
: 브라우저를 꺼도 사라지지 않는 쿠키의 유니크 수 (1명이 1일에 3회 방문해도 1회로 집계)
방문 횟수
: 브라우저를 껐을 때 사라지는 쿠키의 유니크 수 (1명이 1일에 3회 방문하면 3회로 집계)
페이지 뷰 
: 페이지를 출력한 로그의 줄 수

 
일자 별 방문자 수, 방문 횟수, 페이지 뷰, 1번 방문 당 페이지 뷰 집계하기

SELECT 
SUBSTRING(stamp, 1, 10) AS dt,
COUNT(DISTINCT long_session) AS access_users,
COUNT(DISTINCT short_session) AS access_count,
COUNT(*) AS page_view,
1.0 * COUNT(*) / NULLIF(COUNT(DISTINCT long_session),0) AS pv_per_user
FROM access_log
GROUP BY dt
ORDER BY dt;

 
→ 사이트에 로그인 기능도 있다면, 로그인 UU와 비로그인 UU도 동시에 집계하는 것이 좋다.

---

14-2. 페이지 별 쿠키 / 방문 횟수 / 페이지 뷰 집계하기

 
URL을 이용해서 각 페이지의 방문 횟수, 페이지 뷰 집계하기
URL 별로 집계하기

SELECT
url,
COUNT(DISTINCT short_session) AS access_count,
COUNT(DISTINCT long_session) AS access_users,
COUNT(*) AS page_view
FROM access_log
GROUP BY url;

 
경로별로 집계하기
- MYSQL은 REGEXP_SUBSTR()으로 URL에서 경로 부분 추출
 
정규 표현식

• // : 연속된 두 개의 슬래시를 찾고
• [^/]+ : 슬래시가 아닌 하나의 문자에 일치, +는 하나 이상의 문자가 연속됨을 의미
• (/[^?#]*) : 첫 번째 슬래시를 찾고(경로 부분이 시작되는 곳), 해쉬(#)가 나오기 전까지 모든 문자에 일치, *는 0개 이상의 문자가 연속되는 것을 의미 (물음표는 문자가 존재할 수도, 존재하지 않을 수도 있음을 의미)

WITH access_log_with_path AS(
SELECT *,
    REGEXP_SUBSTR(url, '//[^/]+(/[^?#]*)') AS url_path
FROM access_log)
SELECT
url_path,
COUNT(DISTINCT short_session) AS access_count,
COUNT(DISTINCT long_session) AS access_users,
COUNT(*) AS page_view
FROM access_log_with_path
GROUP BY url_path;

www~com 부분이 왜 나오는지 모르겠다 ...

---

URL에 의미를 부여해서 집계하기

• URL에 list/cd, list/dvd 등 리스트 페이지는 카테고리 별로 나누어져 있는데, 이를 카테고리/리스트 페이지로 묶어보자
• /list/newly/는 카테고리/리스트가 아니라, 신상품 리스트 페이지에 묶어 url에 의미 부여
• mysql에는 split, split_part 함수가 없어서, SUBSTRING_INDEX()를 사용해야 한다.
• substring_index()함수는 문자열을 구분자 기준으로 분할하고, 지정한 구분자 위치까지의 부분 문자열을 반환

SUBSTRING_INDEX (str, delim, count) [mysql]
- str: 원본 문자열
- delim: 구분자 문자열
- count: 반환될 부분 문자열에 포함될 delim의 발생 횟수

• 양의 count는 문자열 시작~구분자까지의 문자열 반환
• 음의 count는 문자열 끝~구분자까지의 문자열 반환

    SUBSTRING_INDEX(url_path, '/', 4) AS path1,
    SUBSTRING_INDEX(url_path, '/', 5) AS path2

 
access_log_with_parth
: path1과 path2 추출하기

WITH access_log_with_path AS(
SELECT *,
    REGEXP_SUBSTR(url, '//[^/]+(/[^?#]*)') AS url_path
FROM access_log),
access_log_with_split_path AS (
  SELECT *,
    SUBSTRING_INDEX(SUBSTRING_INDEX(url_path, '/', 4), '/', -1) AS path1,
    SUBSTRING_INDEX(SUBSTRING_INDEX(url_path, '/', 5), '/', -1) AS path2
  FROM access_log_with_path
)
SELECT * FROM access_log_with_split_path;

acess_log_with_split_path

access_log_with_page_name
: page_name 지정하기

•  path2가 list인 경우, newly가 아닐 때에는 category_llist로 name으로 지정
• path2가 list이고, newly인 경우 newly_list로 name으로 지정
• path1이 list가 아니라면, url_path 전체를 name으로 지정

-- access_log_with_split_path까지 동일
,
access_log_with_page_name AS(
SELECT *,
CASE WHEN path1 = 'list' THEN
CASE WHEN path2 = 'newly' THEN 'newly_list'
ELSE 'category_list' END
ELSE url_path
END AS page_name
FROM access_log_with_split_path)
SELECT * FROM access_log_with_page_name;

acess_log_with_page_name

SELECT
page_name,
COUNT(DISTINCT short_session) AS access_count,
COUNT(DISTINCT long_session) AS access_users,
COUNT(*) AS page_view
FROM access_log_with_page_name
GROUP BY page_name
ORDER BY page_name;

---

14-3. 유입원별로 방문 횟수 또는 CVR 집계하기

정규 표현식, URL함수, 유입원, URL 생성 도구, CVR
 

유입원 판정 - 레퍼러 도메인과 랜딩 페이지를 사용한 판정
 
1. access_log_with_pase_info
: REGEXP_SUBSTR 함수로 url과 referrer에서 도메인과 UTM 파라미터(utm_source, utm_medium) 추출
 
2. access_log_with_via_info
- ROW_NUMBER() 윈도 함수로 각 로그 항목에 대한 순차적인 ID(log_id) 생성
- url_utm_source와 url_utm_medium이 비어있지 않은 경우, 두 값을 하이픈('-')으로 연결해서 via컬럼 생성
- referrer_domain이 특정 도메인(yahoo, google)에 속하는 경우, via를 search로 설정
- referrer_domain이 특정 도메인(twitter, facebook)에 속하는 경우, via를 social로 설정
- 그 외 via를 other로 설정
- where절에서 1. referrer_domain이 비어있지 않고, 2. url_domain과 같지 않은경우(외부 도메인인경우) 필터링

WITH access_log_with_parse_info AS (
SELECT
*,
REGEXP_SUBSTR(url, 'https?://([^/?&]*)') AS url_domain,
REGEXP_SUBSTR(url, 'utm_source=([^&]*)') AS url_utm_source,
REGEXP_SUBSTR(url, 'utm_medium=([^&]*)') AS url_utm_medium,
REGEXP_SUBSTR(referrer, 'https?://([^/?&]*)') AS referrer_domain
FROM access_log
),
access_log_with_via_info AS(
SELECT *,
ROW_NUMBER() OVER(ORDER BY stamp) AS log_id,
CASE WHEN url_utm_source <> '' AND url_utm_medium <> ''
THEN CONCAT(url_utm_source, '-', url_utm_medium)
WHEN referrer_domain IN ('search.yahoo.co.jp', 'www.google.co.jp') THEN 'search'
WHEN referrer_domain IN ('twitter.com', 'www.faceboo.com') THEN 'social'
ELSE 'other' END AS via
FROM access_log_with_parse_info
WHERE COALESCE(referrer_domain, '') NOT IN ('', url_domain))
SELECT via, COUNT(*) AS access_COUNT
from access_log_with_via_info
GROUP BY via
ORDER BY access_count DESC;

유입원 별 방문횟수, via가 이상해 ~.. regexp_substr 문제인가

---

방문 별 구매한 비율(cvr)구하기

 
access_log_with_purchase_amount
:access_log_with_via_info와 purchase_log를 결합해서 log_id 와 via 그룹 별로 구매 금액을 합산한다.

- 조건은 구매(p.stamp)가 사용자의 마지막 action(a.stamp)이후 1일 이내(dateadd(a.stamp, 1 day)) 발생했을 때

-- access_log_with_via_info까지 동일
,
access_log_with_purchase_amount AS(
SELECT
a.log_id,
a.via,
SUM(CASE WHEN p.stamp >= a.stamp AND p.stamp < DATE_ADD(a.stamp, INTERVAL 1 DAY) THEN p.amount
END) AS amount
FROM access_log_with_via_info AS a
LEFT OUTER JOIN purchase_log AS p
ON a.long_session = p.long_session
GROUP BY a.log_id, a.via)
SELECT * FROM access_log_with_purchase_amount;

임시 테이블 access_log_with_purchase_amount

SELECT문
: sign과 coalesce함수로 금액이 해당 기간의 구매 금액이 0보다 큰 경우의 cvr을 100을 곱해서 백분율로 표시

SELECT
via,
COUNT(*) AS via_count,
COUNT(amount) AS conversions,
AVG(100.0 * SIGN(COALESCE(amount, 0))) AS cvr,
SUM(COALESCE(amount, 0)) AS avg_amount
FROM access_log_with_purchase_amount
GROUP BY via
ORDER BY cvr DESC;

각 방문에서 구매한 비율(CVR)

---

14-4. 접근 요일, 시간대 파악하기

날짜 함수, 문자열 함수, 요일, 시간대

요일/시간대 별 페이지뷰 리포트 만들기

결과 테이블

Steps

: 각 로그 항목이 자정 이후 특정 30분 간격에 속하도록 하고, 이 간격이 시작하는 시간 index_time을 구해서 index_time 별 요일 별 로그 카운트 집계하기
1. 24시간에서 추출하고자 하는 단위를 결정 (10분 간격, 15분 간격, 30분 간격)
2. 접근한 시간을 해당 단위로 집계하고, 요일과 함께 방문자 수를 집계
※ 시간대를 구하는 부분은 시간을 00:00:00 이후 얼마나 지났는지 초로 환산하고, 이를 기반으로 숫자를 자른 뒤, 다시 시간 표기로 바꾸는 방법 사용
 
<요일번호 추출 - mysql>
: DAYOFWEEK()로 주어진 날짜의 요일 번호 추출, 일요일(1)~토요일(7)
[postgreSQL]
DATEPART('dow', timestamp)
[mysql]
(DAYOFWEEK(timestamp) -1) AS dow -- postgre처럼 동일하게 일요일을 0으로 시작하고 싶다면, 1을 빼주기
 

table access_log

1. access_log_with_dow
: 각 로그의 타임스탬프에서 요일을 계산하고(dow), 하루 중 경과된 전체 초(whole_seconds)를 계산하고, 30분 간격(interval_seconds)를 설정

WITH access_log_with_dow AS(
SELECT stamp,
-- 일요일(0)부터 토요일(6)까지의 요일 번호 추출
(DAYOFWEEK(stamp)-1) AS dow,
-- 00:00:00부터의 경과 시간 초 단위로 계산
SUBSTRING(stamp, 12, 2) * 60 * 60
+ SUBSTRING(stamp, 15, 2) * 60 
+ SUBSTRING(stamp, 18, 2) AS whole_seconds,
-- 시간 간격 정하기 (여기에서는 30분[1800초]로 지정)
30 * 60 AS interval_seconds
FROM access_log)
SELECT * FROM access_log_with_dow;

임시테이블 access_log_with_dow

2. access_log_with_floor_seconds
: 계산된 whole seconds를 30분 간격(interval_seconds)로 나누고, 그 결과를 다시 간격으로 곱해서 하루 중 특정 30분 간격의 시작 시간을 초로 나타내는 floor_seconds를 계산

-- access_log_with_dow까지 동일
,
access_log_with_floor_seconds AS (
  SELECT
    stamp,
    dow,
    -- 00:00:00부터의 경과 시간을 interval_seconds로 나누고, 그 결과에 interval_seconds를 곱하여 floor_seconds 계산
    FLOOR(whole_seconds / interval_seconds) * interval_seconds AS floor_seconds
  FROM access_log_with_dow
)
SELECT * FROM access_log_with_floor_seconds;

임시테이블 access_log_with_floor_seconds

3. access_log_with_index
: floor_seconds를 시간 형식으로 변환해서 index_time을 생성
 

- 전체 초를 시간으로(floor seconds / 60*60), 남은 초를 분으로(floor_seconds%60*60), 남은 초를 초로(floor_seconds % 60) 변환
- LPAD()함수로 각 시간 단위를 2자리 문자열로 포맷팅 (ex. 숫자가 1자리라면, 왼쪽에 0을 추가해서 01로)
- mysql에서는 floor함수의 결과를 문자열로 직접 변환하기 때문에 cast to string할 필요 없음
- 그리고 변환 시 as string이 아닌 CAST(...AS CHAR) 사용 

-- access_log_with_floor_seconds까지 동일
, 
access_log_with_index AS (
  SELECT
    stamp,
    dow,
    -- 초를 시간 형식으로 다시 변환
    CONCAT(
      LPAD(FLOOR(floor_seconds / (60 * 60)), 2, '0'), ':',
      LPAD(FLOOR((floor_seconds % (60 * 60)) / 60), 2, '0'), ':',
      LPAD(FLOOR(floor_seconds % 60), 2, '0')
    ) AS index_time
  FROM access_log_with_floor_seconds
)
SELECT * FROM access_log_with_index;

임시테이블 access_log_with_index

SELECT문
: index_time을 기준으로 데이터를 그룹화하고, 각 요일 별로 로그가 몇 번 기록되었는지 count 

SELECT 
index_time,
COUNT(CASE dow WHEN 0 THEN 1 END) AS sun,
COUNT(CASE dow WHEN 1 THEN 1 END) AS mon,
COUNT(CASE dow WHEN 2 THEN 1 END) AS tue,
COUNT(CASE dow WHEN 3 THEN 1 END) AS wed,
COUNT(CASE dow WHEN 4 THEN 1 END) AS thu,
COUNT(CASE dow WHEN 5 THEN 1 END) AS fri,
COUNT(CASE dow WHEN 6 THEN 1 END) AS sat
FROM access_log_with_index
GROUP BY index_time;

 
→ 하루 중 특정 시간대에 어떤 요일에 가장 많은 활동이 있었는지 분석할 수 있다.
→ 사용자의 방문이 많은 시간대에 캠페인을 실시
→ 사용자의 방문이 적은 시간대에 타임 세일 혹은 아이템 획득율 상승 등의 이벤트 검토 가능