안녕하세요 :)

앞 글에서 빅쿼리에 있는 독특한 문법이라 할 수 있는

Array / Struct / Unnest 에 대해서 알아보았었는데요,

<이전글>

1. 빅쿼리의 STUCT 이란?

http://googleanalytics360.com/board/view.php?bo_table=googleanalytics&wr_id=95&page=

2. 빅쿼리의 ARRAY & UNNEST이란? 

http://googleanalytics360.com/board/view.php?bo_table=googleanalytics&wr_id=94&page=

이번 글에서는 해당 문법들을 실제 GA4 데이터에서 어떤식으로 활용하는지 알아보겠습니다.

<목차>

1. Record 의 데이터 조회하기

2. Record + Repeated 데이터 조회하기

3. Record + Repeated 데이터 안에 또 다른 Record 가 있는 경우의 데이터 조회하기

[빅쿼리의 데이터 유형]

우선 GA4 데이터로 가기 전에, 빅쿼리의 데이터 유형들을 봐볼게요.

도움말에서 확인할 수있는 빅쿼리의 스키마 및 데이터 유형은 다음과 같습니다.

뭔가 알아야하는 항목들이 여러개 있어보이지만

정말 자주 쓰이는 것들은 아마

STRING (문자형) , INT64 (정수형) , DATE 날짜 정도 일 것 같아요.

여기서 추가로 저희는 STRUCT (RECORD) 과 ARRAY (REPEATED) 도 알아야겠죠?

아래 이미지는 빅쿼리에 GA4를 연동했을 때 수집되는 데이터들의 스키마입니다.

우측의 유형과 모드에 집중해볼게요.

RECORD 면 Struct

REPEATED 면 Array

이 두 가지는 꼭 기억해주세요!

[ROCORD 데이터를 가져오는 방법]

한번 저희가 device 라는 정보를 빅쿼리를 통해서 조회해보겠습니다.

SQL 에서 정보를 가져올 때 가장 기본적인게

SELECT '항목' FROM '데이터 소스' 형식이죠,

device 를 아래와 같이 select 하게 되면 어떻게 될까요?

SELECT device FROM `데이터 소스'

분명 select 된 것은 device 하나인데

갑자기 열이 엄청 많이 조회되는데요,

이처럼 여러 열이 출력된 이유는 유형이 'Record' 인 항목이 Struct 이기 때문이에요.

(struct 은 테이블 안에 또 다른 테이블, 열의 형태로 존재하는 것)

이렇게 device 왼쪽의 세모를 눌러서 펼쳐보게되면

숨겨있던 이름들이 나오게 됩니다.

만약 'device' struct 안에 있는 'category' 정보와 'operating_system' 만

가져오고 싶다면 어떻게 할 수 있을까요? 

바로 '.' 을 이용하는 것 입니다.

이렇게 device.category '.' 을 활용했더니 정상적으로 원하는 컬럼을 select 해올 수 있죠.

SELECT device.category,

             device.operating_system

FROM `데이터소스'

[Record 와 Repeated 가 같이 있는 경우]

위에서는 Record 만 있는 경우였는데,

실제로 ga4 에서 더 많이 쓰는 값들은 Record 와 Repeated 가 같이 있어요.

items 로 한번 같이 알아보겠습니다.

우선 items 라는 값들은 어떤 구조일지 생각해볼게요.

한 회사에서 실제로 어떤 상품들이 팔리고 있는지 확인하려면

상품의 이름 ,상품의 가격 , 상품의 옵션 등 각 상품에 대한 정보가 필요할것이고,

사람들은 1개만 구매하기보다 여러 상품을 구매하기 때문에

그 여러 상품들에 대해서 수집을 할 수있어야겠죠?

엑셀로 표현해보면 살짝 이런 느낌이에요.

그럼 이전 글에서 설명할 때,

ARRAY 는 엑셀에서 '병합셀' 과 같다고 표현했는데

지금 딱 그 형식인게 보이실까요?

스크립트로 표현하면 이렇게 ITEMS 라는 ARRAY 안에 아이템 정보들을 여러개 넣을 수 있게 됩니다.

ITEMS 가 REPEATED 였으니 ARRAY 는 이렇게 쓰인걸 알았고,

RECORD 도 있었는데...

그럼 ITEMS 에서 STRUCT 은 어디에 쓰였을까요?

아까 위에서 device 와 동일하게 , 아래와 같이 items 를 select 해보면

SELECT items FROM `데이터 소스'

아까 DEVICE 처럼 너무 많은 열들이 SELECT 되었죠,

이를 통해 ITEMS 도 STRUCT 구조로 이루어져 있다는 것도 확인할 수 있습니다.

그럼 과연 ITEMS 도 아까 DEVICE 예시처럼 '.' 을 사용하면 원하는 열만 가져올 수 있을까요?

SELECT items.item_name,

             items.price

FROM `데이터 소스'

'.' 을 동일하게 썼는데 위의 쿼리는 실행되지 않고, 다음과 같은 오류가 확인됩니다.

Cannot access field item_name on a value with type ARRAY> at [1:14]

지금 이 형식의 데이터 구조에서는 item_name 값에 접근이 불가하다고 뜨죠,

array 로 묶여 있기 떄문이에요.

하지만 저희는 array 안에 있는 값을 가져올 unnest 를 활용하면 된다는걸 알고있죠!

이렇게 from 절에서 items 를 unnest 해주신 후 select 절에서 items 안에 있는 열들을 가져올 수 있습니다.

(두개의 테이블이 ',' 로 이어지게 되면 Inner join)

SELECT items.item_name,

             items.price 

FROM `ga 데이터 소스` , unnest(items) items

[Array 안의 Struct 안에 또 다른 Struct 이 있을 때]

위에서

1.STRUCT 구조일때

2.STRUCT 구조와 ARRAY 구조가 함께 있을 때를 알아보았는데요,

이 경우도 한번 볼까요?

지금 'event_params' 라는 필드는 array 와 struct 구조가 함께 존재하는 것이죠,

근데 하위 단위로 내려가니 key , value 값이 있고,

value값은 또 다시 Record,

Struct 구조가 안에 또 있는 형식이에요.

이것도 왜 이런 구조가 나오는지 생각해볼게요.

예를 들어서 유저 A 와 B 가 이벤트들을 발생시킬 때,

함께 수집하고 싶은 값들이 있을 있어요.

page_view 라는 이벤트라 하면

어떤 페이지를 조회한건지 (page_location)

그 페이지를 구분하는 컨텐츠 카테고리가 있는지 (page_content)

이전 페이지는 어디인지 (page_referrer)

몇 번째 세션인지 (session_number)

세션 id 는 무엇인지... (ga_session_id)

등등 여러 정보를 수집하고 싶겠죠

그러면 위에서 items 와 같이 각 정보들은 page_view 라는 한 이벤트에서 발생한 것이니

array 형식으로 하나로 묶여있으면 관리하기 편하게 될거에요.

이때 수집한 값 (page_location ,page_content...) 들은 'key' 라는 이름으로 정리되고,

그 key 에 대응하는 값들은 value 가 됩니다.

근데, 문제가 있어요.

지금 key , value 라는 값들을 struct 과 array 로 넣어줬는데

여기 있는 value 값들은 데이터 유형이 다 같아야 오류가 발생하지 않습니다.

그래서 value 를 string , int 열로 다시 한번 더 나눠서 관리하기 위해

array > struct >struct 으로 한번 더 쪼개진거죠.

최종적으로 여기 event_params 의 값들을 빅쿼리에서 조회할 때는 다음과 같이 많이 씁니다.

SELECT (select value.string_value from unnest(event_params) where key = 'page_location') page_location,

             (select value.int_value from unnest(event_params) where key = 'ga_session_id') ga_session_id

FROM `'데이터 소스'

위의 식을 하나씩 풀어서 생각해보면 

저희가 쓰는 select * from '데이터 소스' 이 기본 형에서

event_params 의 array 를 unnest 로 풀어주고,

struct 구조인 value.string_value / value.int_value 를 select 해 준 다음

where 절에서 그 key 값들 중 원하는 것을 가져오는 거에요.

그러면 실행 결과는 아래처럼 볼 수 있게 됩니다. 

여기까지 오늘은 array, struct, unnest 를 활용해서

ga4 데이터를 빅쿼리에서 조회하는 방법에 대해서 알아보았습니다.

원하는 데이터를 맘껏 조회하는 그날까지!

같이 공부해요~~

[참고 구글 공식 도움말]

-https://cloud.google.com/bigquery/docs/best-practices-performance-nested?hl=ko

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

-https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types

소통하면 좋은 부분 있다면 언제든 댓글 남겨주세요

감사합니다 :)