一般的なSQLに慣れてきた人がBigQuery(Legacy SQL)を使う際によくハマるポイント、
特にGoogleアナリティクス360（旧Googleアナリティクスプレミアム）が出力するログデータを扱う場合に直面する問題を中心に解説する。

Googleアナリティクス360のログデータはBigQueryの特徴的なところを嫌というほど満載している。
ということで、これを扱えれば大概の問題には対応できるようになるだろう。

なおLegacy SQLに限定した話。Standard SQLは未検証。

言語個別の仕様

ネストされたデータ形式

BigQueryでは各レコードの1個のカラムに複数の値、つまり複数行に相当するデータを入れることができる仕様になっているのが大きな特徴である。
1つのセルにスカラ値ではなくテーブルが入るイメージ。

データに親子関係があって一般的なリレーショナルデータベースでは子テーブルを作成していたようなケース（受注と受注明細、セッションとページビューなど）でも、
データの親子構造をそのまま一つのテーブルに格納することができる（非正規化された状態）。

一般的なRDBに慣れていればわざわざ自分でこのような機能を使わないが、GA360では当然のように使ってくる。
BigQueryの中では以下のような扱いになる。

type: RECORD型
mode: REPEATED

クエリでは

親エンティティ: totals
子エンティティ: totals.visits

のようにドットを付けたxxxxx.yyyyyの形式でアクセスできる。
親エンティティのtotalsがREPEATED（mode）のRECORD型（type）カラムになる。

RECORD型でもREPEATEDでなければ複数の行（1対N）を持つことができず、1対1となる→親子関係のない通常のレコードと同様にアクセスできる（カラム名のドットが付いているだけ）。RECORD型のカラム内のカラムがRECORD型になる構造（RECORD型のネスト）になってもいい。

たとえばGA360のテーブルでREPEATEDなRECORD型になるのは

customDimensions
hits
- hits.product
  - hits.product.customDimensions
  - hits.product.customMetrics
- hits.promotion
- hits.customVariables
- hits.customDimensions
- hits.customMetrics

このうち

hits.product
- hits.product.customDimensions
- hits.product.customMetrics
hits.promotion

は拡張eコマース機能なので、高々2段階のネストまでを意識すればいい。

親子を同時にSELECT

正規化されていればJOINをするが、1つのテーブルになっているので自己結合などは不要。

第1階層と1種類の第2階層（hits.***）を抽出する場合

SELECT 
  fullVisitorId, 
  visitNumber, 
  visitStartTime, 
  date, 
  hits.hitNumber, 
  hits.page.pagePath, 
  totals.visits 
FROM 
  [99999999.ga_sessions_20151010] 
LIMIT 50;

LIMIT句は親エンティティに対して適用。

第1階層と2種類の第2階層（customDimensions.*** / hits.***）を抽出する場合

これはダメ、複数の第2階層が多対多になるため。

SELECT 
  fullVisitorId, 
  visitNumber, 
  visitStartTime, 
  date, 
  customDimensions.value, 
  hits.hitNumber, 
  hits.page.pagePath, 
  totals.visits 
FROM 
  [99999999.ga_sessions_20151010] 
LIMIT 50;

これを解決するのがFLATTEN句

SELECT 
  fullVisitorId, 
  visitNumber, 
  visitStartTime, 
  date, 
  customDimensions.value, 
  hits.hitNumber, 
  hits.page.pagePath, 
  totals.visits 
FROM 
  FLATTEN([99999999.ga_sessions_20151010], customDimensions) 
LIMIT 50;

→hitsの内容がcustomDimensionsに展開される。LIMIT句はcustomDimensionsに対して適用。

SELECT 
  fullVisitorId, 
  visitNumber, 
  visitStartTime, 
  date, 
  customDimensions.value, 
  hits.hitNumber, 
  hits.page.pagePath, 
  totals.visits 
FROM 
  FLATTEN([99999999.ga_sessions_20151010], hits) 
LIMIT 50;

→customDimensionsの内容がhitsに展開される。LIMIT句はhitsに対して適用。

何をどこに展開するのか難しい。

ネストされたRECORDの集計

WITHIN修飾子を使う

たとえばネストされているhits.***のページビュー数の集計をする場合

SELECT
  fullvisitorId,
  visitId,
  CASE WHEN trafficSource.campaign = 'a001' THEN 'Remarketing' ELSE 'Broad' END AS display_type,
  EXACT_COUNT_DISTINCT(hits.page.pagePath) WITHIN RECORD AS num_unique_pages,
FROM
  [99999999.ga_sessions_20161001]
WHERE
  trafficSource.medium = 'display'

GROUP BYを使って単純集計してもいいのだが、CASE WHEN trafficSource.campaign = 'a001' THEN 'Remarketing' ELSE 'Broad' END AS display_typeのような列があるとやりにくくなる（クエリが無駄に複雑になる）。
簡単にネストされたレコードを集計して1行の結果として出すことができる。

日付

BigQueryの日時を表す変数はTIMESTAMP型。
インポートの都合で日あるいは時が文字列型（STRING）で格納されていることもあるが、これをTIMESTAMP型に変換するのがTIMESTAMP()関数。基本的にうまいことやってTIMESTAMP型に変換してくれる。

実行内容	戻り値
`SELECT TIMESTAMP('20151020');`	2015-10-20 00:00:00 UTC
`SELECT TIMESTAMP('2015-10-20');`	2015-10-20 00:00:00 UTC
`SELECT TIMESTAMP('2015/10/20');`	2015-10-20 00:00:00 UTC
`SELECT TIMESTAMP('2015-10-20 00:00:00');`	2015-10-20 00:00:00 UTC
`SELECT TIMESTAMP('2015-10-20 9:00:00');`	2015-10-20 09:00:00 UTC
`SELECT TIMESTAMP('2015-10-20 09:00:00');`	2015-10-20 09:00:00 UTC
`SELECT TIMESTAMP('2015-10-20 18:5:7');`	2015-10-20 18:05:07 UTC
`SELECT TIMESTAMP('2015-10-20 24:00:00');`	null
`SELECT TIMESTAMP('10/20/2015');`	null

日付の計算

実行内容	戻り値
`SELECT TIMESTAMP('2015-10-20') - TIMESTAMP('2015-10-15');`	432000000000
`SELECT DATEDIFF(TIMESTAMP('2015-10-20'), TIMESTAMP('2015-10-15'));`	5
`SELECT DATE_ADD(TIMESTAMP('2015-10-20'), 5, 'DAY');`	2015-10-25 00:00:00 UTC
`SELECT TIMESTAMP('2015-10-20') + 5 * 1000000 * 60 * 60 * 24;`	1445731200000000

日付の連番になっているテーブル群をまとめて扱う（table wildcard function）。
メニューで「ga_sessions_ (331)」と表示されているテーブルは実は「ga_sessions_20160817」「ga_sessions_20160816」…の集約

SELECT ... FROM 
  TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2016-10-01'), TIMESTAMP('2016-10-05'));`

resources exceededのエラーがGROUP EACH BYやJOIN EACHを使ったクエリで発生する。
これは大量のレコードが一つのキーに紐づくような歪んだデータによって発生する。
一部のデータでCOUNT(DISTINCT) over GROUP EACH BYを実行してみて、キーの分布を確認するといい。

よく使うクエリ

ページビュー数やセッション数のカウント
複数セッションをまたいだカスタマージャーニーを見る
コンバージョンの発生したセッションにフラグを付ける（BigQueryのログにはコンバージョンデータが含まれていないのでページビューやイベントを参照して自前で付ける必要がある）
その他特定の行動（特定階層の閲覧やイベント発生など）にフラグを付ける
- ページビュー単位で見る
- セッション単位で集計
独自の流入元分類をする
→2016年秋以降、チャネルグループ分類もログデータに出力されるようになった

これらはTODO

ハマるところ

ユニークカウントでCOUNT(DISTINCT ...)は概算。正確なユニークカウントはEXACT_COUNT_DISTINCT(...)を使う。

GAまわり

Adwordsと未連携のデータにはtrafficSource.adwordsClickInfoがないため、連携後のデータとUNIONするとエラーが発生

window関数

WITHIN節とwindow関数は同時に使えない。
複数のwindowがあるときに列名エイリアスが使えない（エイリアスを使わないオリジナルの列名でSELECT済みの場合は除く）
LAST_VALUE()関数が使い物にならない。FIRST_VALUE() OVER (ORDER BY ... DESC)で。

`JOIN`の罠

3個以上のテーブルをJOINするとき、
3個目以降のテーブルのカラムを指定する際は、
テーブル名を省略してはならない。

省略すると以下のエラーが発生する。

Field 'フィールド名' not found on either side of the JOIN

分散処理

BigQueryの処理は分割・分散処理が基本。
たとえば全レコードを1個のテーブルに収めるのではなく、分割して連番管理（日ごとにテーブルを分けるなど）したテーブルを扱うのがBigQueryの王道である。
GA360のログデータも日別のテーブルに格納されている。そのような分割されたテーブルを扱う際に発生する問題。

分散処理でたまたま先に返ってくる列の数が少ない場合

クエリ結果の列数が一致しないことがある。
というのも連番管理したテーブルもタイミングによって列が増えたり減ったりすることがある。
GA360ではAdWords連携すると、未連携時と前後で列の数が変わるため、この問題に直面する。
その他新しい仕様でカラムが追加されることもある（trafficSource.isTrueDirectなど）

（例）AdWords連携を2016-7-2に行った場合

これだとダメ

FROM 
  (SELECT * FROM TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2016-5-31'), TIMESTAMP('2016-7-1'))), 
  (SELECT * FROM TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2016-7-2'), TIMESTAMP('2016-7-3')))

これならOK

FROM 
  (SELECT *, trafficSource.adwordsClickInfo.adNetworkType AS adNetworkType FROM TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2016-7-2'), TIMESTAMP('2016-12-31'))), 
  (SELECT *, STRING(NULL) AS adNetworkType FROM TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2015-12-1'), TIMESTAMP('2016-7-1')))

ただし、（内部的に）先に返ってくるクエリ結果の列数のほうが多い場合はエラーが発生しない。
分散処理のタイミングに依存する。

FLATTEN節と連番テーブル

FLATTEN節はTABLE_DATE_RANGE()などを使った連番テーブルに対して適用できない。一度連番テーブル全体をSELECTした結果のサブクエリに対してFLATENを適用する。

これはOK

SELECT
  fullVisitorId,
  customDimensions.index,
  customDimensions.value,
  visitId,
  totals.hits,
  hits.type,
  hits.page.pagePath
FROM
  FLATTEN([99999999.ga_sessions_20151214], customDimensions )
WHERE
  (customDimensions.index = 1
    OR customDimensions.index IS NULL)
  AND hits.type = 'PAGE'

これはNG

SELECT
  fullVisitorId,
  customDimensions.index,
  customDimensions.value,
  visitId,
  totals.hits,
  hits.type,
  hits.page.pagePath
FROM
  FLATTEN(TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2015-12-14'), TIMESTAMP('2015-12-15')), customDimensions )
WHERE
  (customDimensions.index = 1
    OR customDimensions.index IS NULL)
  AND hits.type = 'PAGE'

こうする必要がある

SELECT
  *
FROM
  FLATTEN( (
    SELECT
      fullVisitorId,
      customDimensions.index,
      customDimensions.value,
      visitId,
      totals.hits,
      hits.type,
      hits.page.pagePath
    FROM
      TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2015-12-14'), TIMESTAMP('2015-12-15')) ), customDimensions )
WHERE
  (customDimensions.index = 1
    OR customDimensions.index IS NULL)
  AND hits.type = 'PAGE'

分割できない処理で扱うデータサイズが大きくなる場合

分割できない処理で扱うデータサイズが大きくなると「Resources exceeded during query execution.」というエラーが発生する。たとえば

SELECT
  visitStartTime,
  fullVisitorId,
  GAID,
  visitId,
  visitNumber,
  device.deviceCategory,
  totals.hits,
  hits.hitNumber,
  timeSec,
  hits.page.pagePath
FROM
  FLATTEN( (
    SELECT
      visitStartTime,
      fullVisitorId,
      customDimensions.index,
      customDimensions.value AS GAID,
      visitId,
      visitNumber,
      device.deviceCategory,
      totals.hits,
      hits.type,
      hits.hitNumber,
      hits.time / 1000 AS timeSec,
      hits.page.pagePath
    FROM
      TABLE_DATE_RANGE([99999999.ga_sessions_], TIMESTAMP('2016-1-1'), TIMESTAMP('2016-1-31')) ), customDimensions )
WHERE
  (customDimensions.index = 1
    OR customDimensions.index IS NULL)
  AND hits.type = 'PAGE'
ORDER BY
  fullVisitorId,
  visitId,
  visitNumber,
  hits.hitNumber;

その他限界

GROUP BYしすぎると

Maximum number of keys in GROUP BY clause is 64, query has 65 GROUP BY keys.

DISTINCTなレコードを抽出するためにはGROUP BYが必要だが、列が多くなるとこのエラーが発生する。

データ分析の記事一覧

marketechlabo

Rにおける代表的な一般化線形モデル（GLM）の実装ライブラリまとめ

https://www.marketechlabo.com/r-glm-libraries/

marketechlabo

BigQueryでGA4/Firebaseのログを使って機械学習 | marketechlabo

https://www.marketechlabo.com/bqml-ga4-firebase-log/

marketechlabo

Google BigQueryでお手軽機械学習（BQML） | marketechlabo

https://www.marketechlabo.com/bqml-intro/

marketechlabo

BigQueryで列可変のテーブルからcos類似度を計算する | marketechlabo

https://www.marketechlabo.com/bigquery-calculate-cos-similarity/

marketechlabo

Google ColaboratoryでRを使う

https://www.marketechlabo.com/google-colaboratory-with-r/

言語個別の仕様

ネストされたデータ形式

親子を同時にSELECT

ネストされたRECORDの集計

日付

よく使うクエリ

ハマるところ

GAまわり

window関数

JOINの罠

分散処理

分散処理でたまたま先に返ってくる列の数が少ない場合

FLATTEN節と連番テーブル

分割できない処理で扱うデータサイズが大きくなる場合

その他限界

GROUP BYしすぎると

`JOIN`の罠