BigQuery UNNEST 친절한 설명

1. ARRAY 란?

Oracle 과 같이 기존에 전통적인 RDBMS 문법을 기반으로 쿼리를 배웠을 경우,
BigQuery 에서 이런 저런 데이터를 다루다 보면 간혹 낯선 풍경과 조우하는 경우가 있다.

그 중 하나가 BigQuery 의 데이터형(data type) 중 하나인 ARRAY 이다.

BigQuery에서 배열은 데이터 유형이 동일한 0개 이상의 값으로 구성된 순서가 지정된 목록을 의미합니다.

Google BigQuery Doc

별로 와닿지 않는 정의다…
풀어서 설명을 해보도록 하겠다.

일반적으로 기업에서 BigQuery 를 사용한다고 하면,
Google Analytics (이하 GA) 와 연동해서 사용하는 경우가 많으므로
GA 데이터를 샘플 삼아 설명하도록 하겠다.

GA 는 Web 에서 고객이 남기는 행동들을 Log 형태로 수집을 하고,
수집된 raw data는 JSON 형식에서 대략 다음과 같다.

[
  {
    "visitId": "123456789",
    "visitStartTime": "1622124798",
    "totals": {
      "visits": "1",
      "hits": "1",
      "pageviews": "1"
    }
  }
]

(참고) visitStartTime 값이 왜 1622124798 인지 궁금하신 분들은 관련 포스팅을 참고

JSON 문법을 잘 모르더라도,
위 내용을 아래와 같이 직관적으로 파악할 수 있을 것이다.

👨 visitId 가 123456789 이고
🕒 1622124798 UNIX time 에 들어와서
🔢 총 방문 수를 1회, hit 수는 1회, 페이지 조회 수는 1회 기록했구나.

자, 그러면 위 JSON 데이터를 쿼리가 가능하도록 table 형태로 보면 어떨까?

2. Why UNNEST?

위 데이터를 표로 만들 경우, 결과는 다음과 같을 것이다.

visitIdvisitStartTimetotals
1234567891622124798“visits”: “1”,
“hits”: “1”,
“pageviews”: “1”
table1. UNNEST 전

자, 한 번 보자.
딱 봐도 문제가 있지 않은가? (불편 +1)

table1. 불편한 부분

totals 컬럼의 값을 펼쳐서 추가적인 컬럼으로 만들고 싶은 욕구가 생긴다.
마치 R 에서의 reshape 패키지 cast 함수처럼.

visitIdvisitStartTimetotals.visitstotals.hitstotals.pageviews
1234567891622124798111
table2. UNNEST 후


table1 에서 table2 로 변경을 해주는 작업.
이것이 바로 UNNEST 함수다.

3. UNNEST Syntax

UNNEST 의 사용 문법은 다음과 같다.

--visitId 별 페이지 조회 수

SELECT visitId, t.pageviews
FROM table1, UNNEST(totals) AS t

테이블 내 값을 컬럼으로 펼치는 과정에서 CROSS JOIN 이 들어가기 때문에,
FROM table1 뒤에 쉼표(,) 가 붙는다.

UNNEST(totals) 에 대한 alias 가 필수는 아니지만,
복잡한 JOIN 이 들어간 쿼리에서 해당 속성을 특정할 수 있어 사용을 권고한다.

외 참고 링크

Google 에서 직접 GA 샘플 데이터셋을 제공하기도 하니,
실무적인 연습이 필요한 분은 아래 링크를 참고.
Google Analytics sample dataset for BigQuery

R 과 GA 연계하기 (googleAnalyticsR)

본 글은 Google Analytics 데이터 가져오기의 후속 포스팅이다.

앞서 언급된 대로 Google 측에서는
GA 데이터 연계를 목적으로 Core Reporting API 라는 인터페이스를 제공한다.

R 에서도 해당 API 를 통해 GA 데이터를 가져올 수 있는데,
그 패키지가 바로 googleAnalyticsR 패키지다.

가령, Google 측이 제공하는 Affinity 세그먼트 별로 제품 조회 수를 알고 싶은 경우,
관련된 샘플 R 코드는 다음과 같다.

코드 별 상세 설명은 주석을 참고 바란다.

# googleAnalyticsR 패키지를 설치 및 로드

install.packages("googleAnalyticsR")
library("googleAnalyticsR")

# R 과 GA를 연동. 별도 브라우저 탭이 뜨고 계정 인증을 하면 된다.

ga_auth()

# GA ID 입력을 위해 해당 목록 조회. Property가 여러 개로 나눠져 있는 경우 유의해서 입력해야 한다.

ga_accounts <- ga_account_list()
View(ga_accounts)

# GA ID 입력
ga_id <- 12345678

# View ID 입력. View ID 는 GA 화면에서 Admin > View > View Settings 에서 조회 가능하다.
view_id <- 98765432

#스키마: https://ga-dev-tools.appspot.com/dimensions-metrics-explorer/

#데이터 호출

Affinity_PV <- google_analytics (view_id,
                                      date_range = c("2020-01-01","2021-05-31"),
                                      metrics = c("ga:pageviews"),
                                      dimensions = c("ga:productDetailViews","ga:interestAffinityCategory"),
                                      anti_sample = TRUE,
                                      max = -1 )

Google Analytics 데이터 가져오기 (Data Export)

Google Analytics 로부터 데이터를 가져오는, 추출하는 방법은 총 4가지가 있다.

1. UI 다운로드

웹(https://analytics.google.com/)에서 GA에 접속해
Export 를 클릭해 원하는 파일 유형 (PDF, Google Sheets, Excel, CSV)으로 로컬에 다운로드 한다.

※ 제한사항 : 한 번에 최대 5,000 행까지만 다운로드 가능하다.

2. Unsampled Report (GA 360 Only)

웹(https://analytics.google.com/)에서 GA에 접속해
Export > Unsampled Report 를 클릭해 CSV 형태로 로컬에 다운로드 한다.

샘플링되지 않은 보고서를 생성할 때는 Google 서버 측에 상당한 처리 부하가 발생하므로
GA의 엔터프라이즈 버전인 GA 360에서만,
그 중에서도 권한 부여된 계정에 한해서만 가능하다.

※ 제한사항 : 한 번에 최대 3,000,000 행까지만 다운로드 가능하다. 기간이 1년 이상일 경우, Google 에서는 오류 방지를 위해 분할 추출을 권장한다.

3. BigQuery API

BigQuery 를 사용한다는 가정 하에, BigQuery 를 Google Analytics 와 연계해
GA 데이터를 BigQuery로 추출할 수 있는 기능이다.

이 때, 주의할 점은 모든 GA 데이터를 가져올 수 없다는 것이다.
가령 성별, 연령대 또는 관심사 등의 데이터는 추출이 불가능하다.

가져올 수 있는 데이터는 오직 BigQuery API 스키마에서 정의된 속성에 한해서만 가능하며
해당 스키마는 아래 링크를 통해 확인 가능하다 :
BigQuery Export API 스키마 링크

※ 제한사항 : Export 가능한 속성이 제한된다.

4. Core Reporting API (★추천)

Core Reporting API 는 Google 측에서 GA 데이터를 타 시스템과 연계할 수 있도록
제공하는 API 방식의 인터페이스다.

목적 자체가 호환성에 있기 때문에,
Java, Python, PHP, JavaScript 기반의 시스템과 연계 가능하며 가이드까지 제공한다.

Core Rerpoting API 를 통해 다음 작업이 가능하다 :

  • GA 데이터 기반으로 커스텀 대시보드를 제작
  • 리포팅 업무 자동화
  • 타 비즈니스 어플리케이션과 GA 데이터 연계

Core Reporting API 방식을 적극 추천하는 이유는,
단연 접근 가능한 속성과 디멘션이 가장 포괄적이기 때문이다.

앞서 살펴본 3번 BigQuery Export API 에서 지원하지 않는 속성들을
(ex.연령대, 성별, 관심사 등) 대부분 제공하며,
Custom 속성/디멘션까지 조회 가능하다.

해당 스키마는 아래 링크를 통해 확인 가능하다 (+Expand All 클릭) :
Core Reporting API 스키마 링크

단, 고객 식별자 값과 같은 개인민감정보(PII)들은 조회가 불가하니 이 점 알아두자.
(필자의 경우, hashed 값도 추출 불가했는데 가능한 경우 댓글 바람)

※ 제한사항 : API Request 최대 2,000 건으로 제한

↑ 다음 포스팅에서는 Core Reporting API 를 활용해 R 로 GA 데이터를 가져오는 방법(googleAnalyticsR 패키지)에 대해 구체적으로 다루어보도록 하겠다.


Reference
https://medium.com/@larsgundersen/how-to-get-the-google-analytics-360-core-reporting-api-to-return-unsampled-data-e83ce87fc176