[SQL - Bigquery/빅쿼리] 웹사이트 전체의 특징/경향 찾기

웹사이트 전체의 특징과 경향을 찾기 위한 쿼리를 소개하겠다.

 

0. 샘플 데이터

테이블 이름: ivory-program-349520.data.access_log

1.  날짜별 방문자 수 / 방문 횟수 / 페이지 뷰 집계하기 : substr, distinct, nullif

웹사이트에서는 방문자 수 , 방문 횟수, 페이지 뷰 집계가 기본이다. 먼저 각각의 지표가 무엇을 집계하는지 살펴보도록 하자.

 

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

 

'1회 방문당 페이지 뷰'를 날짜별로 집계하는 쿼리를 작성해 보겠다.

 

select substr(cast(stamp as string),1,10) as dt,  -- 1. 날짜 추출
       count(distinct long_session) as access_users, -- 2. 쿠키 계산
       count(distinct short_session) as access_count, -- 3. 방문 횟수 계산
       count(*) as page_view, -- 4. 페이지 뷰 계산
       1.0 * count(*) / nullif(count(distinct long_session), 0) as pv_per_user -- 5. 1인당 페이지 뷰 수
from `ivory-program-349520.data.access_log`
group by dt
order by dt;

 

1. 날짜 추출
   • substr 함수를 이용해서 stamp를 첫 번째 자리부터 10번째 자리까지 추출해 준다.
   • *빅쿼리 substr 함수는 timestamp 형식을 지원하지 않기 때문에 cast 함수를 이용해 문자형으로 변환해 주었다.
2. 방문자 수 계산
   • 브라우저를 꺼도 사라지지 않는 쿠키의 유니크 수를 계산해 준다.
   • distinct(long_session)
3. 방문 횟수 계산
   • 브라우저를 껐을 때 사라지는 쿠키의 유니크 수를 계산해 준다.
   • distinct(short_session)
4. 페이지 뷰 계산
   • 페이지를 출력한 로그의 수
5. 1인당 페이지 뷰 수 계산
   • 1.0 * 페이지 뷰 수 / 방문자 수
   • nullif 함수를 이용하여 방문자 수가 0이라면 null로 계산

 

 

2. url 경로별로 집계 : regexp_extract 정규표현식

 

url에는 요청 매개변수가 포함되어 있다. url을 하나하나 집계하면 밀도가 너무 작기 때문에 요청 매개변수를 생략하고 경로로 집계해 보겠다.

 

with
access_log_with_path as (
  select *,
         regexp_extract(url, '//[^/]+([^?#]+)') as url_path
from `ivory-program-349520.data.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;

 

정규표현식을 이용하여 '/detail?id=**'을 '상세 페이지'라고 집계할 수 있게 요청 매개변수를 생략하고 경로만으로 집계한 결과이다.

 

 

3. URL에 의미를 부여해서 집계

 

바로 위의 경로별로 집계하는 쿼리 결과를 보면 '/detail'로 묶어 상세페이지를 한꺼번에 묶어 놓았다. 하지만 '/list/cd', '/list/newly', '/list/dvd'의 리스트 페이지는 카테고리 별로 나누어져 있다. 이를 '신상품 리스트 페이지'라고 묶어 url에 의미 부여하는 쿼리를 작성해 보겠다.

 

with access_log_with_path as (
    select *,
           regexp_extract(url, '//[^/]+([^?#]+)') as url_path
from `ivory-program-349520.data.access_log`
)
, access_log_with_split_path as ( -- 1. 첫번째요소와 두번째 요소 추출하기
    select *,
           split(url_path, '/')[SAFE_ORDINAL(2)] AS path1,
           split(url_path, '/')[SAFE_ORDINAL(3)] AS path2
    from access_log_with_path
)
, access_log_with_page_name as (
    select *, -- 2.
    	   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 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;

 

1. 경로의 첫 번째 요소와 두 번째 요소 추출하기
   • split 함수로 배열을 /(슬래시) 기준으로 분해한다.
   • SAFE_ORDINAL 속성을 이용해서 배열 인덱스를 1부터 시작하도록 지정하고, 오류 발생 시 null을 리턴하게 한다.
2. 경로를 슬래시로 분할하고, 조건에 따라 페이지 이름 붙이기

이상 전체 페이지 뷰를 최상위 페이지, 카테고리/리스트페이지, 상세 페이지로 큰 밀도로 집계할 수 있다.

 

---

<데이터 분석을 위한 SQL 레시피> 14강 부분을 참고하여 작성하였습니다.