빅쿼리에서 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