17
15

More than 5 years have passed since last update.

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

Last updated at Posted at 2019-02-22

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

複数テーブルの照会

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'
17
15
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
17
15