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解析ツールのデータを様々なビッグデータ分析ツールを使って分析する業務を行っております。
お問い合わせはこちらからどうぞ。

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

Adobe Target: at.jsの「チラつき」を手っ取り早く回避する前のページ

Tag Manager: Adobe DTM で Google Analytics (gtag.js)を配信する次のページ

ピックアップ記事

  1. 最速で理解したい人のためのIT用語集

関連記事

  1. Google Analytics

    初めてのGoogle Analytics分析

    こんにちは、インターン生の松本です。今回は初めてのGoogle An…

  2. Google Cloud Platform

    Server-side GTMのAppEngine設定をカスタマイズする

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

  3. Cortex

    BigQueryとSnowflakeのLLM関数を比較してみた

    はじめにこんにちは、石原です。生成 AI と大規模言…

  4. Google Cloud Platform

    GCSへのSnowflake Open Catalogによるデータレイクハウス導入チュートリアル

    はじめにこんにちは、エクスチュアの石原です。今回は1…

  5. Adobe Analytics

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

    ※2019年9月4日追記この記事は情報が古いので、新しい記事を書き…

  6. Google Analytics

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

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

カテゴリ
最近の記事
  1. TableauとSnowflakeを接続する方法
  2. 【dbts25】Snowflake×PostgreSQLのニ…
  3. TROCCO dbt連携編
  4. KARTEの「フレックスエディタ(β)」登場!ノーコードでこ…
  5. dbt Projects on Snowflake使ってみた…
  1. Data Clean Room

    Snowflake の新しいData Clean Roomの見どころを解説
  2. Google BigQuery

    【BigQuery】TABLESAMPLE SYSTEMを日本一詳しく解説する
  3. Sansan

    Sansanで名刺を取り込んだらSlackで社内共有する(Zapier利用)
  4. IT用語集

    ファーストパーティー、セカンドパーティー、サードパーティデータって何?
  5. IT用語集

    OS(Operating System)って何?
PAGE TOP