[Bigquery] Bigquery에서 GA4 연동 데이터 추출 전 알아둬야 할 사항들

Bigquery는 SQL과 비슷하지만 조금 차이가 있어 혼동이 올 수 있다!

빅쿼리에서 GA4 데이터를 추출하기 전, 꼭 알아야 할 몇 가지 사항들을 적어보겠다.

 

1. events_ & events_intraday_ 테이블

빅쿼리에서 GA4와 연동되면 그림과 같이 analytics_~ 형태로 구성되어 있는 목록을 확인할 수 있는데, events_ 와 events_intraday 테이블을 구성되어 있다.

events_ ~ 테이블은 오늘이 지난 어제까지의 정보를 담은 과거 데이터,

events_intrady_~ 테이블은 오늘의 데이터만 쌓이는 실시간 데이터

라고 보면 된다!

 

events_ : 오늘 이전 어제 자정까지의 데이터가 축적된 과거 테이블

events_intrady_  : 실시간으로 오늘의 데이터가 축적 중인 실시간 테이블

2. _TABLE_SUFFIX 를 이용한 데이터 기간 조회

SELECT *
FROM `myproject.analytics_123456789.events_20240501` 
LIMIT 1000

※ 2024년 5월 1일에 내가 연동한 GA4의 전체 데이터 중 1000개만 가져오는 코드

events_ 뒷 부분에 해당하는 숫자(20240501)은 년월일을 뜻하는데, 이러면 5월 1일 하루치 데이터만 가져오는거다!

(LIMIT을 거는 이유는 빅쿼리에서 데이터를 조회할 때 발생하는 과금 시스템 때문에.. 돈 폭탄 맞을 수 있으니 테스트할 땐 항상 걸자!)

 

그렇다면 예를 들어, 5월 1일부터 5월 30일까지의 데이터를 조회하고 싶으면 어떻게 해야 할까?

여기서 써야 하는게 바로 _TABLE_SUFFIX 다! 위 코드와 아래 코드를 대조하면서 보면, 

SELECT *
FROM `myproject.analytics_123456789.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240501' AND '20240530'
LIMIT 1000

1. events_20240501 이 있던 자리에는 *(아스테리스크, 별표)로 대체

2. WHERE 절에 _TABLE_SUFFIX 사용 후 BETWEEN  (원하는 첫 날짜) AND (원하는 마지막 날짜)

이렇게 변경하면 5월 1일부터 5월 30일까지 데이터를 전체 조회할 수 있다!

 

3. 테이블 필드의 유형과 모드 이해

우상단에 유형과 모드에 대한 정보를 살펴보자!

1. 유형
   보통 데이터가 문자형(STRING)인지 정수(INTEGER)인지 등 데이터 타입에 대한 정보가 담겨 있다.
   하지만 예외로 RECORD로 기록되어 있는 경우 자식 필드가 있는 경우 부모 필드의 유형은 'RECORD'가 된다.
2. 모드
   NULLABLE과 REPEATED로 구분된다.
   NULLABLE은 우리가 보통 생각하는 하나의 ROW에 한개씩 담겨있는 데이터
   REPEATED는 하나의 ROW에 배열(ARRAY) 형태로 여러 개 담겨있는 데이터

라고 보면 된다. 헷갈릴 수 있으니 아래 코드와 함께 결과 이미지로 함께 보자!

SELECT event_date, event_name, event_params
FROM `myproject.analytics_320354291.events_*`
WHERE _TABLE_SUFFIX BETWEEN '20240501' AND '20240501'
LIMIT 1000

event_date, event_name, event_params를 조회했는데 컬럼이 3개가 아니라 7개처럼 보이는 테이블이 나온다. 
이유는 event_params이 배열(Array) 형태로 되어있기 때문이다.
event_params의 자식컬럼을 보면,

 1. event_params.key

 2. event_params.value_string_value

 3. event_params.value_int_value

 4. event_params.value_float_value

 5. event_params.value_double_value
으로 구성되어 있는데, event_params를 조회하면 이 다섯가지가 1개의 ROW에 배열형태로 함께 표출되는 것이다.

event_date, event_name 같이 하나의 ROW에 한 개의 데이터가 있는 형식이  'NULLABLE'
event_params와 같이 1 ROW에 배열 형태로 여러 개의 데이터가 있는 형식이 'REPEATED'라고 보면 된다!

 

4. UNNEST

위 모드에서 REPEATED가 배열 구조라는건 알겠는데, 그러면 event_params의 배열 내에 있는 세션ID(ga_session_id)만 추출하려면 어떻게 해야돼? 라는 생각이 들 수 있다. 이럴 때 쓰는게 바로 UNNEST 함수를 이용해 CROSS JOIN 하는거다! 빅쿼리에서 UNNEST는 배열 형태의 자료를 행으로 반환하는 함수다.

 

UNNEST를 활용한 결과부터 보여주면, 

이런 식으로 배열로 나열되어 있던 데이터를 1ROW 1DATA 형태로 평면화 시킬 수 있다!

 

방법은 간단하다.

SELECT event_date, event_name, key, value.string_value, value.int_value, value.float_value, value.double_value
FROM `myproject.analytics_320354291.events_*`, unnest(event_params)
WHERE _TABLE_SUFFIX BETWEEN '20240501' AND '20240501'
LIMIT 1000

1. FROM 절에 UNNEST("REPEATED 구조에 해당하는 컬럼")을 ,(쉼표)와 함께 추가해준다.

(※ ,(쉼표)는 CROSS JOIN과 같다.)

2. event_params 자식컬럼명을 조회한다. ex) key, value.string_value

 

이렇게 하면 events_params같은 배열 형태의 자료에서도 원하는 데이터만 조회할 수 있다 !

 

 

 

끝 ! 오늘도 수고하셨습니다.

 

<참조 자료>

https://steadiness-193.tistory.com/251#google_vignette

https://cloud.google.com/bigquery/docs/nested-repeated?hl=ko