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. Cloud Dataflow

    データアナリスト/データエンジニア向けNext19まとめ

    こんにちは、エクスチュア渡部です。2019/4/9-4/11に行わ…

  2. Adobe Analytics

    Adobe AnalyticsとGoogle Analyticsの違い① セグメントのコンテナの違い…

    こんにちは。CEOの原田です。今回は良く話題に出ながらも情報がまと…

  3. Google BigQuery

    OWOX BI: GoogleSheetsアドオンでBigQueryをお手軽ビジュアライズ

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

  4. Google BigQuery

    Tableau : BigQueryでLOD計算が使えない場合の対処法

    こんにちは、エクスチュアの渡部です。Tableau×BigQu…

  5. Adobe Analytics

    続・SafariのITP2.x対策として別サーバー使ってクッキーを永続化する

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

  6. Adobe Analytics

    Adobe Analytics: BigQueryにロードしたデータフィードをDataStudioで…

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

最近の記事

  1. KARTE「オフラインデータをオンライン接客に活用する」
  2. ELB (ALB・NLB・CLB) をサクッと学ぶ
  3. Direct Connect vs Site to Site…
  4. パブリックサブネット vs プライベートサブネット
  5. セキュリティグループ vs ネットワークACL
  1. Adobe Analytics

    Adobe AEP SDKでReactNativeアプリを計測する (Andro…
  2. Adobe Analytics

    Adobe Analytics: データフィードをGoogle BigQuery…
  3. Amazon Web Services

    IAM (Identity and Access Management) を理解…
  4. Adobe Analytics

    Adobe Analytics: BigQuery+Lookerでアトリビューシ…
  5. GA 360 Suite

    Google Analytics 360: BigQueryを使ってアトリビュー…
PAGE TOP