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. GA 360 Suite

    GoogleDataStudio:GoogleAnalyticsのカスタムチャネルグループをCASE…

    こんにちは。エクスチュアの渡部です。DataStudioの小ネ…

  2. Azure

    Google Cloud StorageとAzure Blob Storage間でファイルを並列コピ…

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

  3. Adobe Analytics

    Adobe Analytics: DatafeedをGoogle BigQueryにロード(2019…

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

  4. Adobe Analytics

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

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

  5. Google Cloud Platform

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

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

  6. Google Analytics

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

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

最近の記事

  1. System Managerを利用したVPC内Redshif…
  2. Amazon Redshift ビルド168まとめ
  3. Tableauのテスト自動化を実現する Wiiisdom O…
  4. Databricksが買収した8080Labのbambool…
  5. databricksのnotebookを使ってみよう その2…
  1. Mouseflow

    【Tips】mouseflowを使ってたった5分でコンバージョン率を上げる方法
  2. Adobe Analytics

    Adobe Analytics + Google BigQueryでよく使うSQ…
  3. ヒートマップ

    スクロール・ヒートマップ
  4. ヒートマップ

    ユーザビリティの検証で気を付けたいこと、やってはいけないこと④
  5. ObservePoint

    ObservePointでサイト内のタグが全部まるっとお見通しだ!
PAGE TOP