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. Adobe Analytics

    Adobe Analytics: データフィードをGoogle Compute EngineのLin…

    こんにちは、CTOの権です。もはやWeb解析だけの時代は終わり、様…

  2. Adobe Analytics

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

    こんにちは、CTOの権です。前回のブログの続きです。今回は…

  3. Google BigQuery

    Tableau : BigQueryでLOD計算が使えない場合の対処法

    こんにちは、エクスチュアの渡部です。Tableau×BigQu…

  4. Google Cloud Platform

    Google Compute Engine のLinuxVMにVNC接続する

    こんにちは、エクスチュアCTOの権です。今回は、GCEのLin…

  5. Adobe Analytics

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

    こんにちは、CTOの権です。前回のブログで、BigQueryに…

  6. Google Analytics

    Google Tag Manager上でGoogle AnalyticsのclientIDを取得する…

    こんにちは、CTOの権です。今日はGoogle Tag Manag…

最近の記事

  1. Adobe AnalyticsからDWHレポートをGoogl…
  2. Pardotの初期セットアップをする① DNSレコード設定と…
  3. Tag Manager: Adobe DTM で Googl…
  4. BigQuery: Google Analytics 360…
  5. Adobe Target: at.jsの「チラつき」を手っ取…
  1. Adobe Analytics

    Adobe Analytics: データフィードをGoogle Compute …
  2. Adobe Analytics

    Adobe AnalyticsからDWHレポートをGoogle Cloud St…
  3. Adobe Analytics

    Adobe Analytics: データフィードをGoogle BigQuery…
  4. Adobe Dynamic Tag Manager

    Tag Manager: Adobe DTM で Google Analytic…
  5. Adobe Analytics

    Adobe Analytics: Mobile SDK 4.x でアプリ計測する…
PAGE TOP