GA 360 Suite

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に足してしまってた誤りを修正しました。

同名のカラム

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

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

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

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

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

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

関連記事

  1. Adobe Dynamic Tag Manager

    Tag Manager: Adobe DTM で Google Analytics (gtag.js…

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

  2. Adobe Analytics

    Adobe Analytics: DatafeedのログからフォールアウトレポートをBigQuery…

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

  3. Google Cloud Platform

    Google Compute Engine: 一定時間経過したらタスクを強制終了する

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

  4. Adobe Analytics

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

    こんにちは、エクスチュアCTOの権です。もはやWeb解析だけの時代…

  5. Google Analytics

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

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

  6. Adobe Analytics

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

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

最近の記事

  1. AdobeAnalyticsでReactNativeアプリを…
  2. Adobe Analytics: BigQuery+Look…
  3. Looker: エンジニアがBIで分析ダッシュボードを作る
  4. Adobe Analytics: DWHレポートをAWSのS…
  5. Adobe Analytics: レガシーs_code.js…
  1. Google BigQuery

    Tableau×BigQueryをコスパ良く使う方法
  2. Google BigQuery

    Google Apps Scriptを使ってスプレッドシートからBigQuery…
  3. Adobe Analytics

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

    Mouseflow:計測対象からの除外
  5. Adobe Analytics

    Adobe Analytics: RSIDを間違えてしまったデータを正しいRSに…
PAGE TOP