GoogleAnalyticsのデータをBigQueryで分析する

社内での勉強資料としてのアウトプットになるため、細かい説明は記載しておりません。
ざっくりと理解していただく用、もしくは理解した上でコピペ用に使っていただけますと幸いです。

複数テーブルの照会

GoogleAnalyticsのデータをBigQueryへエクスポートしたときに生成されるテーブル名は、ga_sessions_yyyymmdd形式で1日ごとに生成されています。
このように類似した名前のテーブルが複数存在する場合、ワイルドカードテーブルを用いることで複数テーブルを照会することができます。

例えば、2019年2月1日から2019年2月7日までの期間でデータ抽出を行いたい場合は、以下のように記述します。

SELECT
  *
FROM
  `{プロジェクトID}.{データセット名}.ga_sessions_*`
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'

RECORD型データをフラットにする

エクスポートしたデータの中にRECORD型と呼ばれるデータ型が存在します。
ユーザーが閲覧したページデータが格納されているhits.page.pagePathカラムもRECORD型データの1つで、

fullVisitorId	hits.page.pagePath
1234567	/
	/search/shop_list/
	/shop/index/
	/shop/access/

のような形式で保存されています。
この状態だと扱いづらいため、UNNEST関数を用いてフラットにすることをオススメします。

SELECT
  fullVisitorId,
  hits.page.pagePath
FROM
  `{プロジェクトID}.{データセット名}.ga_sessions_*`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'

こうすることで、

fullVisitorId	hits.page.pagePath
1234567	/
1234567	/search/shop_list/
1234567	/shop/index/
1234567	/shop/access/

という状態に変換されます。

ユニークユーザー数、ページビュー数、ユニークセッション数

fullVisitorId：ブラウザのクッキーに付与されるユニークID。
visitId：セッションID

ユニークユーザー数はGoogleAnalytics上で「ユーザー」という指標で表現されています。
しかし、実際にはブラウザに付与しているユニークIDの数のため、厳密には「ユーザー」ではなく「ユニークブラウザ」が正しい表現となります。
今回は、fullVisitorIdにDISTINCTをかけてカウントした数値をユニークユーザー数、かけずにカウントした数値をページビュー数、visitIdにDISTINCTをかけてカウントした数値をユニークセッション数としています。

また、ページ閲覧に対する指標のため、ヒットタイプを"PAGE"としています。

SELECT
  COUNT(DISTINCT fullVisitorId) AS unique_user,
  COUNT(fullVisitorId) AS page_view,
  COUNT(DISTINCT visitId) AS unique_session
FROM
  `{プロジェクトID}.{データセット名}.ga_sessions_*`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'
  AND hits.type = 'PAGE'

ページ、イベント

SELECT
  hits.type,
  hits.page.pagePath,
  hits.eventInfo.eventCategory,
  hits.eventInfo.eventAction,
  hits.eventInfo.eventLabel
FROM
  `{プロジェクトID}.{データセット名}.{テーブル名}`, UNNEST(hits) AS hits  
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'

流入元

SELECT
  trafficSource.source AS utm_source,
  trafficSource.medium AS utm_medium,
  trafficSource.campaign AS utm_campaign
FROM
  `{プロジェクトID}.{データセット名}.{テーブル名}`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'

日、週、月

SELECT
  FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS day,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) DAY) AS saturday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 1) DAY) AS sunday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 2) DAY) AS monday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 3) DAY) AS tuesday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 4) DAY) AS wednesday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 5) DAY) AS thursday,
  DATE_SUB(CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE), INTERVAL (EXTRACT(DAYOFWEEK FROM CAST(FORMAT_TIMESTAMP('%Y-%m-%d', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS DATE)) - 6) DAY) AS friday,
  FORMAT_TIMESTAMP('%Y-%m', TIMESTAMP_SECONDS(visitStartTime), 'Asia/Tokyo') AS month
FROM
  `{プロジェクトID}.{データセット名}.{テーブル名}`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'

カスタムディメンション

hits.customDimensionsカラムにデータが保存されており、hits.page.pagePathと同様RECORD型のデータになります。
ただ、やっかいなデータ構造をしており、

fullVisitorId	hits.customDimensions
	hits.customDimensions.index	hits.customDimensions.value
1234567	1	abc
	2	def
	3	ghi

といった具合に、行列それぞれで入れ子の形式をとっています。
そのため、UNNEST(hits) AS hitsにより、

fullVisitorId	hits.customDimensions
	hits.customDimensions.index	hits.customDimensions.value
1234567	1	abc
1234567	2	def
1234567	3	ghi

とした上で、以下のようにサブクエリを用いることで、それぞれの値を抽出することができます。

SELECT
  (SELECT MAX(IF(index=1, value, null)) FROM UNNEST(hits.customDimensions)) AS custom_dimension_1,
  (SELECT MAX(IF(index=2, value, null)) FROM UNNEST(hits.customDimensions)) AS custom_dimension_2,
  (SELECT MAX(IF(index=3, value, null)) FROM UNNEST(hits.customDimensions)) AS custom_dimension_3
FROM
  `{プロジェクトID}.{データセット名}.{テーブル名}`, UNNEST(hits) AS hits
WHERE 
  _TABLE_SUFFIX BETWEEN '20190201'
  AND '20190207'