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: BigQueryにロードしたデータフィードをDataStudioで…

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

  2. Adobe Analytics

    BigQuery: Adobe Datafeed: event_listカラムの手軽な扱い方

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

  3. Google Analytics

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

    2018/4/20追記:この記事の内容は古いので、下記の新しい記事…

  4. Google Analytics

    Google Analytics: アプリSDKのclientIDを取得する

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

  5. Google Analytics

    Google Analytics StandardのデータをBigQueryで分析するための力技

    こんにちは、エクスチュアの権です。先月「Google Clou…

  6. Cloud Dataflow

    Firebase AnalyticsのデータをフラットなCSVに変換する – Googl…

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

最近の記事

  1. Adobe Analytics: Datafeedのログから…
  2. Tableau:2つの値の比較に便利な「ダンベルチャート」を…
  3. Tableau:累計ユニークカウント数を時系列グラフで表現す…
  4. Tableau×BigQueryをコスパ良く使う方法
  5. Tableau:分かりやすいLOD – INCL…
  1. Tableau

    Tableau:累計ユニークカウント数を時系列グラフで表現する
  2. Google Tag Manager

    Google Tag Manager: 離脱リンクのクリックをトリガーにする
  3. Tableau

    Tableau:Presto APIを使ったTreasure Dataへのデータ…
  4. Google BigQuery

    GCP: 今月のGCP課金額をslackに自動的に書き込む
  5. Adobe Analytics

    Adobe Analytics: VisitorAPIでSafariでもクロスド…
PAGE TOP