社内での勉強資料としてのアウトプットになるため、細かい説明は記載しておりません。
ざっくりと理解していただく用、もしくは理解した上でコピペ用に使っていただけますと幸いです。
複数テーブルの照会
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'