빅쿼리 GA4 페이지뷰 테이블 만들기

빅쿼리에서 테이블 만드는 내용을 정리하려고 합니다.

 

GA4 데이터를 빅쿼리랑 연동하여 데이터를 분석하면 모든 이벤트 데이터들이 들어오고 특정 이벤트들만 볼 수 있게 테이블을 따로 만들어서 분석을 해볼 수도 있습니다.

 

이렇게 특정 이벤트 별로 테이블을 만들어서 분석하면 GA4 로우테이블에서 계속해서 분석하는 것보다 쿼리 비용도 크게 절약된다고 알고 있습니다.

 

오늘은 특정페이지의 페이지뷰 이벤트를 적재하는 테이블을 만드는 과정을 정리해보았습니다. 상세페이지의 페이지뷰 이벤트를 적재하는 테이블을 만들어보겠습니다.

 

---

* 빅쿼리 테이블 만드는 쿼리(구문)

-- 빅쿼리 테이블 만드는 기본 쿼리
create or replace table `myproject.데이터세트이름.테이블이름` as
~

 

먼저 위 블록에 있는 코드 형태로 테이블을 만들 수 있으며 as 문구 뒤에 적재하고자 하는 쿼리를 구성하면 됩니다.

 

테이블 만드는 쿼리 자체는 어렵지 않고 'as' 이후에 내가 적재하고자 하는 이벤트의 쿼리를 문제없이 잘짜면 됩니다.

 

'테이블이름'은 해당 테이블의 이름을 내가 만들어주면 됩니다. 저는 상세페이지의 페이지뷰 이벤트를 모아둔 테이블을 만들어두기로 했으니 이름을 "DetailPV"로 만들어서 진행해보겠습니다.

 

그럼 간단하게 상세페이지의 페이지뷰만 모아둔 테이블을 만드는 쿼리를 공유해보겠습니다.

 

 

* 빅쿼리로 상세페이지의 페이지뷰 이벤트 테이블 만들어보기

--상세페이지뷰 테이블 만드는 쿼리 예시

create table `프로젝트이름.적재하고자하는데이터세트이름.DetailPV`as

select
  datetime(timestamp_micros(event_timestamp)) as utc_timestamp,
  --한국 시간 기준으로 변경
  datetime(timestamp_add(timestamp_micros(event_timestamp),interval 9 hour)) as real_timestamp,
  event_name,
  (select value.int_value from unnest(event_params) where key = "ga_session_id") as ga_session_id,
  (select value.string_value from unnest(event_params) where key = "page_location") as page_location,
from `프로젝트이름.GA4데이터세트이름.events_*`
where event_name = "page_view"
--마지막 where절 and 구문은 상세페이지 url에 '/detail/' 이 들어가있다는 가정 하에 만들었음
and regexp_contains((select value.string_value from unnest(event_params) where key = "page_location"), r'\/detail\/')

 

상세페이지의 URL에 ~.com/detail/~ 과 같다는 가정하에 만들어본 정규식을 활용한 쿼리입니다. 각자 회사에서 활용하실 때에는 사이트주소의 규칙에 맞추어서 만들어보시면 됩니다. 정규식을 활용하여도 되고 가능하다면 Like 구문을 활용하는 것도 방법입니다.

 

그리고 where절에 event_name을 "page_view" 조건을 같이 걸어두었기 때문에 page_view 이벤트의 데이터만 적재됩니다.

 

위와 같이 테이블을 만들면 상세페이지의 기간 내 페이지뷰수, 세션 수, 유니크 유저 수 등의 데이터를 확인해볼 수 있습니다.

 

만약 사이트에서 상세페이지 주소에 제품 마다 다른 번호가 붙어있는 URL이라면 해당 번호만 따로 추출하여 어떤 제품에서 페이지뷰가 많았는지도 체크할 수 있을 것 같네요. 이 데이터도 select 구문에 정규식을 활용한 컬럼을 만들면 가능할 것 같습니다.

 

이렇게 빅쿼리로 GA4 데이터를 분석하면 자유롭게 분석을 해볼 수 있습니다.(물론 SQL 지식을 갖추고 있어야함...ㅠ)

 

꼭 페이지뷰 이벤트가 아닌 session_start, ga4에서 기본적으로 제공하는 이벤트가 아닌 gtm으로 만든 이벤트들도 다 들어오기 때문에 다양한 테이블을 만들어서 분석해볼 수 있습니다.