[빅쿼리 - SQL]를 사용하여 URL에서 요소 추출하기(host, split 함수)

분석 형장에서는 서비스 런칭 때 로그 조건과 분석 요건을 제대로 검토하지 못하고,

일단 최소한의 요건으로 레퍼러와 페이지 URL을 저장해 두는 경우가 있다.

그리고 이후에 저장한 URL을 기반으로 요소들을 추출하는데,

이번 글에서 샘플 데이터를 활용하여 URL에서 요소들을 추출 하는 법을 알아보겠다.

 

샘플 데이터

로그 데이터

1. 레퍼러로 어떤 웹 페이지를 거쳐왔는지 판별하기

어떤 웹페이지를 거쳐 넘어왔는지 판별 할때는 레퍼러를 집계한다. 하지만, 이를 위 샘플 데이터처럼 페이지 단위로 집계하면 밀도가 너무 작아 복잡해지기 쉽다.

따라서 '호스트'단위로 집계하는 것이 일반적인데, 구글 빅쿼리에는 URL에서 '호스트 이름의 패턴'을 추출하는 함수를 사용할 수 있다.

 

net.host 함수

이처럼 net.host 함수를 사용하여 URL 에서 호스트를 추출 할 수 있다.

host 추출결과

2. URL을 배열로 분해하기

1번에서는 URL의 내용을 생략하여 호스트를 추출하는 방법을 배웠다면, 

2번에서는 URL를 분해하여 페이지 계층을 자세하게 알아보는 방법을 알아보겠다.

이 방법을 통해 접속자가 어떤 경로로 접근했는지 더 자세히 알 수 있다.  

 

URL 분해법

갑자기 많은 함수, 이상한 문자가 나와서 당황했을 수 있다. 찬찬히 살펴보겠다.

가장 안쪽의 함수인 regexp_extract 함수는 '패턴에 맞는 데이터를 추출하는 함수'이다.

regexp_extract('문자열','정규식 패턴')

여기서 '정규식'이란 일정한 '패턴'을 가진 '문자열'의 '집합'을 표현하기 위해 사용하는 '형식 언어'이다.

(사람 이름 아니다)

여기서는 URL 문자열을 패턴화 했다고 보면 되는데,

정규식은 추후에 단독 내용으로 다룰 예정이니 지금은 아래 사진을 보고 이해만 하고 넘어간다.

---

regexp_extract

regexp_extract만 적용한 결과

---

다음은 split 함수.

split 함수는 이름 그대로 문자열을 나누는 함수이다. 

split('문자열',구분자)로 나타낼 수 있으며, 이번 예제의 1번은 split('/video/detail?id=001',/) 와 같다.

따라서 << ' ', video, detail?=001 >> 라는 3가지 문자로 로 나누어진다.

이 세 문자에 접근하는 방법 중 하나가 [SAFE_ORDINAL()]이다. 

SAFE_ORDINAL()은 ' ' 를 1번, video를 2번, detail?=001을 3번으로 접근하는 방법이고,

또 다른 접근 방법인 [OFFSET()]은

' ' 를 0번, video를 1번, detail?=001을 2번으로 접근하는 방법이다,

 

모든 함수를 적용한 진짜 결과는

URL 분해 결과

 

지금까지 로그데이터 샘플을 활용하여 URL에서 정보를 추출하는 방법을 알아보았다.

다음 글에서는 로그 데이터의 시간 열(stamp)의 요소를 추출하는 방법을 알아보겠다.

 

 

---

[데이터 분석을 위한 SQL 레시피] 책을 참고하여 기재한 내용입니다.