GA 360 Suite

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

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

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

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

#standardSQL
SELECT
  visitorId,
  visitNumber, 
  visitId,
  visitStartTime,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(SAFE_CAST(visitStartTime AS INT64)), 'Asia/Tokyo') AS visitStartTime_format, 
  CONCAT(SUBSTR(date,0,4),'-',SUBSTR(date,5,2),'-',SUBSTR(date,7,2)) 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,
  visitStartTime + hits.time as hit_timestamp,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S', TIMESTAMP_SECONDS(SAFE_CAST(visitStartTime + hits.time /1000 AS INT64)), 'Asia/Tokyo') AS hit_timestamp_format, 
  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=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,
  hits.type,
  fullVisitorId
FROM `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`, UNNEST(hits) as hits
LIMIT 100

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

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

ヒットタイムスタンプ

また、ヒット毎のタイムスタンプはhits.timeカラムに、セッション開始からの経過時間がミリ秒で格納されるので、これをvisitStartTimeに足せばヒットタイムスタンプになります。
ついでにタイムスタンプを読みやすくするためにformat_timestamp関数で変換したカラムも追加しました。

同名のカラム

transactionIdとcurrencyCodeは同じ名前の別カラムが存在するので、別名をつけて抽出します。

カスタムディメンションとカスタム指標

あとメンドクサイのはカスタムディメンションとカスタム指標ですね。
元のデータでは、インデックス別に縦方向に格納されてます。

hits.customDimensions/hits.customMetricsをunnestした上で、各ディメンション/指標のインデックス番号別にmax関数で値を抽出してます。
それらを別々のカラムとして横方向に展開して行きます。
こうする事で、一つのヒット=1行=カスタムディメンションもカスタム指標も横方向に1行となります。

このようにフラットな一行に変換すれば、CSVファイルに書きだしたり、サードパーティDWHとの連携がしやすくなります。
今回はGoogle Anallytics 360のBigQueryエクスポートデータをフラット変換する方法についてでした。

弊社では各Web解析ツールのデータを様々なビッグデータ分析ツールを使って分析する業務を行っております。
お問い合わせはこちらからどうぞ。

関連記事

  1. Firebase Analytics

    Firebase AnalyticsのデータをフラットなCSVに変換するETL処理

    こんにちは、エクスチュアCTOの権です。今日はFirebase A…

  2. Firebase Analytics

    Firebase Analyticsの新しいBigQueryスキーマを試す

    こんにちは、エクチュアの権泳東(コン・ヨンドン)です。「お名前なん…

  3. Adobe Analytics

    Adobe Analytics: BigQueryにロードしたデータフィードをDataStudioで…

    こんにちは、エクスチュアCTOの権です。前回のブログで、Big…

  4. Google Cloud Platform

    Google Compute EngineのUbuntu VMにスワップ領域を作成する

    こんにちは、エクスチュアの権泳東(コン・ヨンドン)です。今回は…

  5. Adobe Analytics

    Adobe Analytics: データフィードをGoogle BigQueryのテーブルにロードす…

    こんにちは、エクスチュアCTOの権です。前回のブログの続きです…

  6. Adobe Analytics

    Adobe AnalyticsからDWHレポートをGoogle Cloud Storageにアップロ…

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

最近の記事

  1. Google Compute EngineのUbuntu V…
  2. Firebase Analyticsの新しいBigQuery…
  3. Adobe Cloud Platform Auditor (…
  4. Google Analytics Standardのデータを…
  5. Google Tag Manager上でGoogle Ana…
  1. Google Tag Manager

    Google Tag Manager: 離脱リンクのクリックをトリガーにする
  2. Adobe Analytics

    Adobe Analytics: ランドスケイプの「企業ログ解析データベースAP…
  3. Adobe Analytics

    Adobe Analytics: データフィードをBigQueryで集計する
  4. Tableau

    Tableau Viz紹介 : 10.4新機能を使ってダッシュボードを作ってみた…
  5. GA 360 Suite

    BigQuery: Google Analytics 360のネストされたデータ…
PAGE TOP