GA 360 Suite

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

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

Twitter:@exturekwon

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

というわけで、GA360の公開パブリックデータセットを使ってフラット変換してみます。

クエリはこうなります。

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,
  geoNetwork.continent,
  geoNetwork.subContinent,
  geoNetwork.country,
  geoNetwork.region,
  geoNetwork.metro,
  geoNetwork.city,
  geoNetwork.cityId,
  geoNetwork.networkDomain,
  geoNetwork.latitude,
  geoNetwork.longitude, 
  geoNetwork.networkLocation,
  (select value from unnest(t.customDimensions) where index = 1) as customDimension1,
  (select value from unnest(t.customDimensions) where index = 2) as customDimension2,
  (select value from unnest(t.customDimensions) where index = 3) as customDimension3,
  (select value from unnest(t.customDimensions) where index = 4) as customDimension4,
  (select value from unnest(t.customDimensions) where index = 5) as customDimension5,
  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 value FROM UNNEST(hits.customDimensions) where index = 1) AS hit_customDimension1, 
  (SELECT value FROM UNNEST(hits.customDimensions) where index = 2) AS hit_customDimension2, 
  (SELECT value FROM UNNEST(hits.customDimensions) where index = 3) AS hit_customDimension3, 
  (SELECT value FROM UNNEST(hits.customDimensions) where index = 4) AS hit_customDimension4, 
  (SELECT value FROM UNNEST(hits.customDimensions) where index = 4) AS hit_customDimension5,
  (SELECT value FROM UNNEST(hits.customMetrics) where index = 1) AS hit_customMetrics1,
  (SELECT value FROM UNNEST(hits.customMetrics) where index = 2) AS hit_customMetrics2,
  (SELECT value FROM UNNEST(hits.customMetrics) where index = 3) AS hit_customMetrics3,
  (SELECT value FROM UNNEST(hits.customMetrics) where index = 4) AS hit_customMetrics4,
  (SELECT value FROM UNNEST(hits.customMetrics) where index = 5) AS hit_customMetrics5,
  hits.type
FROM
  `bigquery-public-data.google_analytics_sample.ga_sessions_*` as t, 
  UNNEST(hits) AS hits
WHERE
  _TABLE_SUFFIX between  '20170701' AND '20170731'
limit 1000

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

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

ヒットタイムスタンプ

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

同名のカラム

transactionIdとcurrencyCodeは同じ名前の別カラムが存在するので、別名をつけて抽出します。
また、customDimensionsもセッションレベルとヒットレベルで同名のカラムが存在してます。

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

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

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

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

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

ブログへの記事リクエストはこちらまで

関連記事

  1. Adobe Analytics

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

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

  2. Adobe Analytics

    BigQuery: テーブルに格納されたURL文字列をKey-ValueのSTRUCT型で抽出する

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

  3. GA 360 Suite

    Google Analytics 360: BigQueryを使ってアトリビューション分析で91日以…

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

  4. Google Cloud Platform

    GoogleNext 2019レポート:初日目

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

  5. Google BigQuery

    オープンソースBI「Metabase」の使い勝手が丁度良かった

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

  6. Adobe Analytics

    Adobe AnalyticsのDatafeedをBigQueryのColumn-based Tim…

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

最近の記事

  1. エクセルで「テーブル」を使うメリット① 「BIツールで使いや…
  2. GTMで複数のコンテナに同じ実装をしたい
  3. イベントレポート Qualtrics XM on tour …
  4. イベントレポート Qualtrics XM on tour …
  5. ObservePoint:2022年デジタル・ガバナンスレポ…
  1. Adobe Analytics

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

    イベントレポート Qualtrics XM on tour TOKYO 2022…
  3. Enterprise Data Warehouse

    爆速データウェアハウスなApache Druidを試す
  4. Amazon Web Services

    セキュリティグループ vs ネットワークACL
  5. Mouseflow

    Mouseflowで問題解決② -事例ベースで機能紹介-
PAGE TOP