본문 바로가기
SQL

[BigQuery] 웹사이트에서의 행동을 파악하는 데이터 추출하기

by 권미정 2022. 5. 20.

<데이터 분석을 위한 SQL 레시피> 6장 14강 3번까지를 실습한 내용입니다.

 

 

이번에는 웹사이트 전체의 특징과 경향을 찾기 위한 리포트와 SQL을 소개하고자 한다.

 

#샘플 데이터

이번 실습에서는 접근 로그 테이블을 샘플 데이터로 한다.

access_log 테이블을 생성하자.

DROP TABLE IF EXISTS expanded-lock-349311.lec14.access_log;
CREATE TABLE expanded-lock-349311.lec14.access_log(
    stamp         string(255)
  , short_session string(255)
  , long_session  string(255)
  , url           string
  , referrer      string
);

INSERT INTO expanded-lock-349311.lec14.access_log
VALUES
    ('2016-10-01 12:00:00', '0CVKaz', '1CwlSX', 'http://www.example.com/?utm_source=google&utm_medium=search'       , 'http://www.google.co.jp/xxx'      )
  , ('2016-10-01 13:00:00', '0CVKaz', '1CwlSX', 'http://www.example.com/detail?id=1'                                , ''                                 )
  , ('2016-10-01 13:00:00', '1QceiB', '3JMO2k', 'http://www.example.com/list/cd'                                    , ''                                 )
  , ('2016-10-01 14:00:00', '1QceiB', '3JMO2k', 'http://www.example.com/detail?id=1'                                , 'http://search.google.co.jp/xxx'   )
  , ('2016-10-01 15:00:00', '1hI43A', '6SN6DD', 'http://www.example.com/list/newly'                                 , ''                                 )
  , ('2016-10-01 16:00:00', '1hI43A', '6SN6DD', 'http://www.example.com/list/cd'                                    , 'http://www.example.com/list/newly')
  , ('2016-10-01 17:00:00', '2bGs3i', '1CwlSX', 'http://www.example.com/'                                           , ''                                 )
  , ('2016-10-01 18:00:00', '2is8PX', '7Dn99b', 'http://www.example.com/detail?id=2'                                , 'https://twitter.com/xxx'          )
  , ('2016-10-02 12:00:00', '2mmGwD', 'EFnoNR', 'http://www.example.com/'                                           , ''                                 )
  , ('2016-10-02 13:00:00', '2mmGwD', 'EFnoNR', 'http://www.example.com/list/cd'                                    , 'http://search.google.co.jp/xxx'   )
  , ('2016-10-02 14:00:00', '3CEHe1', 'FGkTe9', 'http://www.example.com/list/dvd'                                   , ''                                 )
  , ('2016-10-02 15:00:00', '3Gv8vO', '1CwlSX', 'http://www.example.com/detail?id=2'                                , ''                                 )
  , ('2016-10-02 16:00:00', '3cv4gm', 'KBlKgT', 'http://www.example.com/list/newly'                                 , 'http://search.yahoo.co.jp/xxx'    )
  , ('2016-10-02 17:00:00', '3cv4gm', 'KBlKgT', 'http://www.example.com/'                                           , 'https://www.facebook.com/xxx'     )
  , ('2016-10-02 18:00:00', '690mvB', 'FGkTe9', 'http://www.example.com/list/dvd?utm_source=yahoo&utm_medium=search', 'http://www.yahoo.co.jp/xxx'       )
  , ('2016-10-03 12:00:00', '6oABhM', '3JMO2k', 'http://www.example.com/detail?id=3'                                , 'http://search.yahoo.co.jp/xxx'    )
  , ('2016-10-03 13:00:00', '7jjxQX', 'KKTw9P', 'http://www.example.com/?utm_source=mynavi&utm_medium=affiliate'    , 'http://www.mynavi.jp/xxx'         )
  , ('2016-10-03 14:00:00', 'AAuoEU', '6SN6DD', 'http://www.example.com/list/dvd'                                   , 'https://www.facebook.com/xxx'     )
  , ('2016-10-03 15:00:00', 'AAuoEU', '6SN6DD', 'http://www.example.com/list/newly'                                 , ''                                 )
;

 

access_log

 

1. 날짜별 방문자 수/방문 횟수/페이지 뷰 집계하기 COUNT 함수, COUNT(DISTINCT~)

 

'1회 방문당 페이지 뷰'를 날짜별로 집계하고 데이터를 추출하는 SQL을 작성해 보자.

 

1. 날짜 추출하기: substr 함수

2. 방문자 수 계산하기: 날짜별로 long_session을 distinct하고 count하여, 브라우저를 꺼도 사라지지 않는 쿠키의 유니크 수 계산(한 명의 사용자가 1일에 3회 사이트를 방문해도 1회로 집계)

3. 방문 횟수 계산하기: 날짜별로 short_session을 distinct하고 count하여, 브라우저를 껐을 때 사라지는 쿠키의 유니크 수 계산(한 명의 사용자가 1일에 3회 사이트를 방문하면 3회로 집계)

4. 페이지 뷰 계산하기: 날짜별로 접근 로그의 모든 줄 수를 count하기

5. 1인당 페이지 뷰 수 계산하기:

1.0 X 페이지 뷰 % (NUllIF 함수를 사용해, 방문자 수가 0이면 null을 반환하고, 방문자 수가 0이 아니면 방문자 수를 반환함)

select
  --날짜 추출하기
  substr(stamp,1,10) AS dt
  --방문자 수 계산하기
  ,COUNT(DISTINCT long_session) AS access_users
  --방문 횟수 계산하기
  ,COUNT(DISTINCT short_session) AS access_count
  --페이지 뷰 계산하기
  ,COUNT(*) AS page_view

  --1인당 페이지 뷰 수
  ,1.0*COUNT(*)/NULLIF(COUNT(DISTINCT long_session),0) AS pv_per_user
FROM
  expanded-lock-349311.lec14.access_log
GROUP BY
  dt
ORDER BY
  dt
;

날짜별 접근 데이터를 집계하는 쿼리

 

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

 

이번에는 용도에 따라 URL에서 필요한 정보를 추출하고 집계하는 방법을 알아보자.

 

#URL별로 집계하기

 

GROUP BY 함수를 사용해 URL별로 집계해보자.

SELECT
  url
  ,COUNT(DISTINCT short_session) AS access_count
  ,COUNT(DISTINCT long_session) AS access_users
  ,COUNT(*) AS page_view
FROM
  expanded-lock-349311.lec14.access_log
GROUP BY
  url
;

URL별로 집계하는 쿼리

 

#경로별로 집계하기

 

위처럼 URL별로 집계하면 집계의 밀도가 너무 작다. 이번에는 '/detail?id=**'을 '상세 페이지'라고 집계할 수 있도록, regexp_extract를 사용해 요청 매개변수를 생략하고 경로만으로 집계하는 쿼리를 작성해보자.

WITH
access_log_with_path AS(
  --URL에서 경로 추출하기
  SELECT *
  --정규 표현식에 regexp_extract 사용하기
  ,regexp_extract(url,'//[^/]+([^?#]+)') AS url_path
  FROM expanded-lock-349311.lec14.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
;

경로별로 집계하는 쿼리

 

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

 

경로별로 집계하는 쿼리의 출력 결과를 보면, '/detail?id=**'을 '/detail'로 묶어 상세 페이지를 한꺼번에 묶어 놓았다. 하지만 '/list/cd', '/list/newly', '/list/dvd'의 리스트 페이지는 카테고리별로 나누어져 있는 것을 확인할 수 있다.

이번에는 이를 split 함수와 CASE 식을 사용해 'category_list'로 묶어 보자. 이때 '/list/newly/'는 카테고리/리스트 페이지가 아니라 'newly_list'라고 묶어서 URL에 의미를 부여해보겠다.

WITH
access_log_with_path AS(
  SELECT *
  ,regexp_extract(url,'//[^/]+([^?#]+)') AS url_path
  FROM expanded-lock-349311.lec14.access_log
)
,access_log_with_split_path AS(
  --경로의 첫 번째 요소와 두 번째 요소 추출하기
  SELECT *
  --split 함수로 배열로 분해하고 추출하기
  ,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 *
  ,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
;

URL에 의미를 부여해서 집계하는 쿼리

출력 결과를 보면, 전체 페이지 뷰가 최상위 페이지, 상세페이지, 카테고리/리스트 페이지, 신상품 리스트 페이지로 큰 밀도로 집계되었다.

 

3. 유입원별로 방문 횟수 또는 CVR 집계하기 정규 표현식, URL 함수

 

#유입원별 방문 횟수 집계하기

 

이번에 작성해 볼 SQL은 직전 페이지의 URL을 뜻하는 레퍼러(referer)가 있는 경우와 해당 도메인이 자신의 사이트가 아닌 경우라는 2가지 조건을 만족할 때, 아래와 같은 로직으로 유입원별 방문 횟수를 집계한다.

유입 경로 판정 방법 집계 방법
검색 연동 광고 URL 생성 도구로 만들어진 매개변수 URL에 utm_source, utm_medium이 있을 경우, 이러한 두 개의 문자열을 결합해서 집계
제휴 마케팅 사이트
AD 네트워크
검색 엔진 도메인 레퍼러의 도메인이 다음과 같은 검색 엔진일 때
- search.naver.com
- www.google.co.kr
소셜 미디어 레퍼러의 도메인이 다음과 같은 소셜 미디어일 때
- twitter.com
- www.facebook.com
기타 사이트 위에 언급한 도메인이 아닐 경우 other라는 이름으로 집계

 

위 로직에 따라 쿼리를 작성해 보자.

WITH
access_log_with_parse_info AS(
  --유입원 정보 추출하기
  SELECT *
  --정규 표현식 사용하기
  ,regexp_extract(url,'https?://([^/]*)') AS url_domain
  ,regexp_extract(url,'utm_source=([^&]*)') AS url_utm_source
  ,regexp_extract(url,'utm_medium=([^&]*)') AS url_utm_medium
  ,regexp_extract(referrer,'https?://([^/]*)') AS referrer_domain
FROM expanded-lock-349311.lec14.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 <> ''
      --concat 함수에 여러 매개변수 사용 가능
      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.facebook.com') THEN 'social'
    ELSE 'other'
    --ELSE referrer_domain로 변경하면 도메인별로 집계 가능
  END AS via
FROM access_log_with_parse_info
--레퍼러가 없는 경우와 우리 사이트 도메인의 경우는 제외
WHERE COALESCE(referrer_domain,'') NOT IN('',url_domain)
)
SELECT via,COUNT(1) AS access_count
FROM access_log_with_via_info
GROUP BY via
ORDER BY access_count DESC;

유입원별로 방문 횟수를 집계하는 쿼리

 

#유입원별로 CVR 집계하기

 

이번에는 위의 출력 결과에서, 각 방문에서 구매한 비율인 CVR을 집계하는 쿼리를 작성해 보자.

 

먼저 구매 로그 테이블을 생성하자.

DROP TABLE IF EXISTS expanded-lock-349311.lec14.access_log;
CREATE TABLE expanded-lock-349311.lec14.access_log(
    stamp         string(255)
  , short_session string(255)
  , long_session  string(255)
  , url           string
  , referrer      string
);

INSERT INTO expanded-lock-349311.lec14.access_log
VALUES
    ('2016-10-01 12:00:00', '0CVKaz', '1CwlSX', 'http://www.example.com/?utm_source=google&utm_medium=search'       , 'http://www.google.co.jp/xxx'      )
  , ('2016-10-01 13:00:00', '0CVKaz', '1CwlSX', 'http://www.example.com/detail?id=1'                                , ''                                 )
  , ('2016-10-01 13:00:00', '1QceiB', '3JMO2k', 'http://www.example.com/list/cd'                                    , ''                                 )
  , ('2016-10-01 14:00:00', '1QceiB', '3JMO2k', 'http://www.example.com/detail?id=1'                                , 'http://search.google.co.jp/xxx'   )
  , ('2016-10-01 15:00:00', '1hI43A', '6SN6DD', 'http://www.example.com/list/newly'                                 , ''                                 )
  , ('2016-10-01 16:00:00', '1hI43A', '6SN6DD', 'http://www.example.com/list/cd'                                    , 'http://www.example.com/list/newly')
  , ('2016-10-01 17:00:00', '2bGs3i', '1CwlSX', 'http://www.example.com/'                                           , ''                                 )
  , ('2016-10-01 18:00:00', '2is8PX', '7Dn99b', 'http://www.example.com/detail?id=2'                                , 'https://twitter.com/xxx'          )
  , ('2016-10-02 12:00:00', '2mmGwD', 'EFnoNR', 'http://www.example.com/'                                           , ''                                 )
  , ('2016-10-02 13:00:00', '2mmGwD', 'EFnoNR', 'http://www.example.com/list/cd'                                    , 'http://search.google.co.jp/xxx'   )
  , ('2016-10-02 14:00:00', '3CEHe1', 'FGkTe9', 'http://www.example.com/list/dvd'                                   , ''                                 )
  , ('2016-10-02 15:00:00', '3Gv8vO', '1CwlSX', 'http://www.example.com/detail?id=2'                                , ''                                 )
  , ('2016-10-02 16:00:00', '3cv4gm', 'KBlKgT', 'http://www.example.com/list/newly'                                 , 'http://search.yahoo.co.jp/xxx'    )
  , ('2016-10-02 17:00:00', '3cv4gm', 'KBlKgT', 'http://www.example.com/'                                           , 'https://www.facebook.com/xxx'     )
  , ('2016-10-02 18:00:00', '690mvB', 'FGkTe9', 'http://www.example.com/list/dvd?utm_source=yahoo&utm_medium=search', 'http://www.yahoo.co.jp/xxx'       )
  , ('2016-10-03 12:00:00', '6oABhM', '3JMO2k', 'http://www.example.com/detail?id=3'                                , 'http://search.yahoo.co.jp/xxx'    )
  , ('2016-10-03 13:00:00', '7jjxQX', 'KKTw9P', 'http://www.example.com/?utm_source=mynavi&utm_medium=affiliate'    , 'http://www.mynavi.jp/xxx'         )
  , ('2016-10-03 14:00:00', 'AAuoEU', '6SN6DD', 'http://www.example.com/list/dvd'                                   , 'https://www.facebook.com/xxx'     )
  , ('2016-10-03 15:00:00', 'AAuoEU', '6SN6DD', 'http://www.example.com/list/newly'                                 , ''                                 )
;

DROP TABLE IF EXISTS expanded-lock-349311.lec14.purchase_log;
CREATE TABLE expanded-lock-349311.lec14.purchase_log(
    stamp         string(255)
  , short_session string(255)
  , long_session  string(255)
  , purchase_id   integer
  , amount        integer
);

INSERT INTO expanded-lock-349311.lec14.purchase_log
VALUES
    ('2016-10-01 15:00:00', '0CVKaz', '1CwlSX', 1, 1000)
  , ('2016-10-01 16:00:00', '2is8PX', '7Dn99b', 2, 1000)
  , ('2016-10-01 20:00:00', '2is8PX', '7Dn99b', 3, 1000)
  , ('2016-10-02 14:00:00', '2is8PX', '7Dn99b', 4, 1000)
;

 

CASE 식에 date_add 함수를 사용해서 날짜와 시간을 사칙연산할 수 있다.

WITH
access_log_with_parse_info AS(
  SELECT *
  ,regexp_extract(url,'https?://([^/]*)') AS url_domain
  ,regexp_extract(url,'utm_source=([^&]*)') AS url_utm_source
  ,regexp_extract(url,'utm_medium=([^&]*)') AS url_utm_medium
  ,regexp_extract(referrer,'https?://([^/]*)') AS referrer_domain
FROM expanded-lock-349311.lec14.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.facebook.com') THEN 'social'
    ELSE 'other'
  END AS via
FROM access_log_with_parse_info
WHERE COALESCE(referrer_domain,'') NOT IN('',url_domain)
)
,access_log_with_purchase_amount AS(
SELECT
  a.log_id
  ,a.via
  ,SUM(
    CASE
    --date_add 함수 사용해서 날짜와 시간 사칙연산하기
    WHEN date(timestamp(p.stamp))
     BETWEEN date(timestamp(a.stamp))
      AND date_add(date(timestamp(a.stamp)),interval 1 day)
      THEN amount
    END
  )AS amount
FROM
  access_log_with_via_info AS a
  LEFT OUTER JOIN
    expanded-lock-349311.lec14.purchase_log AS p
    ON a.long_session=p.long_session
GROUP BY a.log_id,a.via
)
SELECT
  via
  ,COUNT(1) AS via_count
  ,COUNT(amount) AS conversions
  ,AVG(100.0*SIGN(COALESCE(amount,0))) AS cvr
  ,SUM(COALESCE(amount,0)) AS amount
  ,AVG(1.0*COALESCE(amount,0)) AS avg_amount
FROM
 access_log_with_purchase_amount
GROUP BY via
ORDER BY cvr DESC
;

유입원별로 CVR 집계하는 쿼리

 

위 쿼리의 결과를 사용하면 어떤 유입 경로에 더 신경써야 하는지 등을 판단할 수 있다.

 

'SQL' 카테고리의 다른 글

[MySQL] 프로그래머스 Lv 3. 조건에 맞는 사용자와 총 거래금액 조회하기 정답풀이  (0) 2023.08.12
[MySQL] 프로그래머스 Lv 3. 조건별로 분류하여 주문상태 출력하기 정답풀이  (0) 2023.08.12
[BigQuery] 다면적인 축을 사용해 데이터 집약하기  (0) 2022.05.06
[BigQuery] 시계열 기반으로 데이터 집계하기  (0) 2022.05.06
[Oracle] WHERE 절을 이용한 조건 검색  (0) 2022.04.18

관련글

댓글

티스토리툴바