빅쿼리에서 REGEXP_EXTRACT 함수로 URL 요소 추출하기

데이터를 분석하면서 정규식의 요소를 외우기는 힘들지만 대신 알고있으면 분석하고 전처리하는데 매우 유용한 것이 바로 정규식입니다. 

 

오늘은 url과 같은 컬럼을 정규식을 활용해서 특정 요소를 추출하는 과정을 정리해보려고 합니다. 요 정규식을 알고있으면 GTM에서 불필요한 이벤트를 굳이 추가할 필요가 없을 것 같기도 합니다.

 

REGEXP_EXTRACT  함수 

regexp_extract(page_location, r'정규식입력')

 

url과 같은 데이터를 전처리하기 위해서는 위 함수에서 첫 번째 value에는 url과 관련된 컬럼을 넣어주시고 두 번째 요소에는 정규식을 입력해주면 됩니다.

 

그리고 정규식입력하는 value에서 ()를 사용하면 캡쳐링 그룹이라고해서 ()사이에 있는 값을 추출합니다.

 

제 블로그의 GA4 데이터를 예시로 한 번 알아보도록 하겠습니다.

 

URL에서 특정 요소 추출하기

select
 page_location,
 regexp_extract(page_location, r'entry\/.*') as test
from blog_data

 

두번째 요소에서 내가 추출하고자 하는 정규식을 입력하면 해당 정규식과 일치하는 값들이 나오게 됩니다. 여기에 캡쳐링(괄호)를 같이 사용하면 정규식안에서도 세부적으로 요소를 추출할 수 있습니다.

 

저는 entry이후의 블로그 제목만 추출하고 싶어 캡쳐링을 통해 블로그 제목만 가져오도록 하겠습니다.

 

select
 page_location,
 regexp_extract(page_location, r'entry\/.*') as test,
 regexp_extract(page_location, r'entry\/(.*)') as test2 --entry/이후의 블로그제목 부분에 () 캡쳐링 사용
from blog_data

 

캡쳐링을 사용해서 블로그 제목값만 불러올 수 있습니다. 이러한 함수는 이커머스 페이지의 경우 상세페이지에 제품의 실제 코드(숫자 등)를 url에 포함하고 있어 regexp_extract 함수를 통해 해당 코드를 추출한 후 실제 DB에 조인해서 분석해볼 수도 있습니다.

 

아래 빅쿼리 공식 문서에서도 확인할 수 있으니 같이 참고해주세요!

String functions  |  BigQuery  |  Google Cloud