-
[BigQuery] 웹사이트에서의 행동을 파악하는 데이터 추출하기SQL 2022. 5. 20. 16:52
<데이터 분석을 위한 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