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. 最速で理解したい人のためのIT用語集

関連記事

  1. Google BigQuery

    Google Apps Scriptを使ってスプレッドシートからBigQueryのテーブルを更新する…

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

  2. Adobe Analytics

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

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

  3. Google Cloud Platform

    Google Compute Engine のLinuxVMにVNC接続する

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

  4. Adobe Analytics

    Adobe AnalyticsとGoogle Analyticsの違い② サンプリングの有無

    こんにちは。CEOの原田です。AAとGAの違いの2段目にいきます。…

  5. Google Analytics

    【UAとは違う!?】GA4で変更された”イベント”について

    はじめにこんにちは、エクスチュアの岩川です。以前、こ…

  6. Google Cloud Platform

    Looker: LookerbotをGoogle Cloud Platformで動かす

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

最近の記事

  1. Snowflakeや最新データ基盤が広義のマーケティングにも…
  2. 回帰分析はかく語りき Part3 ロジスティック回帰
  3. GCSへのSnowflake Open Catalogによる…
  4. VPC Service Controlsで「NO_MATCH…
  5. モダンデータスタックなワークフローオーケストレーションツール…
  1. IT用語集

    シームレス(Seamless)って何?
  2. ブログ

    ダッシュボードとは
  3. Adobe Analytics

    Adobe AnalyticsとGoogle Analyticsの違い③ カスタ…
  4. IT用語集

    インプリ(Imple)って何?
  5. Adobe Analytics

    Adobe AnaltyicsとGoogle Analytics の「生Webビ…
PAGE TOP