BigQueryでのGA360データ利用

やりたいこと

1. GA360と連携されたBigQuery（以下BQ）でカスタムディメンションの集計
2. 対象テーブルを動的にする

（平日のみ実行。月曜は金土日を対象、それ以外の平日は前日を対象として抽出）

前提

このエントリで説明しないこと。

1. GA360とBQの連携の仕方
2. カスタムディメンション設定
3. BQは標準SQLに設定

さらにそもそもってレベルだと、

1. GCPアカウントの用意
2. プロジェクト作成済みであること
3. プロジェクトでBQを有効にしていること

。。。とか他にもあるかもしれないけど、BQ使えるのが前提ってことです。

まずカスタムディメンション集計

LondonCycleHelmetのサンプルテーブルを使います。
ネストされたデータであるカスタムディメンションをフラットにして、インデックス１と２の組み合わせをセッション集計したいとき。

SELECT 
   PARSE_DATE('%Y%m%d', date) AS date
  ,(SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension1
  ,(SELECT MAX(IF(index=2, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension2
  ,COUNT(DISTINCT CONCAT(fullVisitorId, SAFE_CAST(visitNumber AS STRING))) as sessions
FROM 
  `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_20130910`,  
   UNNEST(hits) AS hits
GROUP BY 1, 2, 3

カスタムディメンションはhitsをUNNESTしたうえで、SELECTでもサブクエリ書くんですね。
BQ使ってなかったから知らんかった。

さらにテーブルを動的に変える場合

このサンプルデータは2013年9月10日テーブルしかないけど、GA360連携していれば日々、日次テーブルが追加されていく。
例えば、クエリ実行の曜日によって対象テーブルを動的に変えたいときなどがある。

• 平日のみ実行
• 月曜は金土日、それ以外は前日を対象にしたい

※下記のクエリはテーブルが1日分しかないので動きません

SELECT 
   PARSE_DATE('%Y%m%d', date) AS date
  ,(SELECT MAX(IF(index=1, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension1
  ,(SELECT MAX(IF(index=2, value, NULL)) FROM UNNEST(hits.customDimensions)) AS dimension2
  ,COUNT(DISTINCT CONCAT(fullVisitorId, SAFE_CAST(visitNumber AS STRING))) as sessions
FROM 
  `google.com:analytics-bigquery.LondonCycleHelmet.ga_sessions_*`,  
   UNNEST(hits) AS hits
WHERE
   _TABLE_SUFFIX BETWEEN 
   REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL IF(EXTRACT(DAYOFWEEK FROM CURRENT_DATE)=2, 3, 1) day) AS string), '-', '')
   AND REPLACE(CAST(DATE_SUB(CURRENT_DATE, INTERVAL 1 day) AS string), '-', '')
GROUP BY 1, 2, 3
ORDER BY 1

FROMで参照するテーブルの日付を*にして、WHEREでその条件を書く。

参考サイト

エクスチュアのグレートなテックブログ。
エクスチュアCTOの権さん、しゅごい…
https://ex-ture.com/blog/2017/12/13/unnest-ga360-bq-data-with-standardsql/
ex-ture.com