BigQuery: Google Analytics 360のネストされたデータをフラット変換するSQL

ホーム
GA 360 Suite, Google Analytics, Google BigQuery, Google Cloud Platform
BigQuery: Google Analytics 360のネストされたデータをフラット変換するSQL

2017.12.13

BigQuery: Google Analytics 360のネストされたデータをフラット変換するSQL

こんにちは、エクスチュアCTOの権です。

今回はGA360のBigQueryエクスポートデータについてです。
GA360データをBigQueryからサードパーティDWHに入れようとしたりする時は、hitsデータなど「入れ子」になっているJSON形式のデータを、フラットな1行に変換してやると扱いやすくなります。

というわけで、お馴染みのLondonCycleHelmetのサンプルテーブルを変換してみます。

#standardSQL
SELECT
  visitorId,
  fullVisitorId,
  visitNumber,
  visitId,
  visitStartTime,
  PARSE_DATE('%Y%m%d', date) AS hit_date,
  trafficSource.referralPath,
  trafficSource.campaign,
  trafficSource.source,
  trafficSource.medium,
  trafficSource.keyword,
  trafficSource.adContent,
  device.browser,
  device.browserVersion,
  device.operatingSystem,
  device.operatingSystemVersion,
  device.isMobile,
  device.flashVersion,
  device.javaEnabled,
  device.language,
  device.screenColors,
  device.screenResolution,
  hits.hitNumber,
  hits.time,
  TIMESTAMP_SECONDS(visitStartTime + CAST((hits.time / 1000) AS int64)) AS hit_timestamp,
  hits.hour,
  hits.minute,
  hits.isSecure,
  hits.isInteraction,
  hits.referer,
  hits.page.pagePath,
  hits.page.hostname,
  hits.page.pageTitle,
  hits.page.searchKeyword,
  hits.page.searchCategory,
  hits.transaction.transactionId AS hit_transactionId,
  hits.transaction.transactionRevenue,
  hits.transaction.transactionTax,
  hits.transaction.transactionShipping,
  hits.transaction.affiliation,
  hits.transaction.currencyCode AS hit_transaction_currencyCode,
  hits.transaction.localTransactionRevenue,
  hits.transaction.localTransactionTax,
  hits.transaction.localTransactionShipping,
  hits.item.transactionId,
  hits.item.productName,
  hits.item.productCategory,
  hits.item.productSku,
  hits.item.itemQuantity,
  hits.item.itemRevenue,
  hits.item.currencyCode,
  hits.item.localItemRevenue,
  hits.contentInfo.contentDescription,
  hits.appInfo.name,
  hits.appInfo.version,
  hits.appInfo.id,
  hits.appInfo.installerId,
  hits.exceptionInfo.description,
  hits.exceptionInfo.isFatal,
  hits.eventInfo.eventCategory,
  hits.eventInfo.eventAction,
  hits.eventInfo.eventLabel,
  hits.eventInfo.eventValue,
  (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension1, 
  (SELECT MAX(IF(index=2, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension2, 
  (SELECT MAX(IF(index=3, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension3, 
  (SELECT MAX(IF(index=4, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension4, 
  (SELECT MAX(IF(index=5, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension5, 
  (SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metrics1, 
  (SELECT MAX(IF(index=2, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metrics2, 
  (SELECT MAX(IF(index=3, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metrics3,
  (SELECT MAX(IF(index=4, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metrics4,
  (SELECT MAX(IF(index=5, value, NULL)) FROM UNNEST(hits.customMetrics)) AS metrics5,
  hits.type
FROM
  `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`,
  UNNEST(hits) AS hits

このクエリを実行すると、ヒット毎に一行ずつフラットになった結果が返ります。

hitsというネストされたデータをunnestして一行ずつに展開するとこうなります。

ヒットタイムスタンプ

また、ヒット毎のタイムスタンプはhits.timeカラムに、セッション開始からの経過時間がミリ秒で格納されるので、この「ミリ秒」を「秒」に直した上でvisitStartTimeに足せばヒットタイムスタンプになります。
ついでにタイムスタンプを扱いやすくするためにtimestamp_seconds関数で数値→タイムスタンプに変換しておきます。

※2018年11月26日訂正 hts.timeをミリ秒のままvisitStartTimeに足してしまってた誤りを修正しました。