Google AnalyticsのデータをBigQueryで集計・分析するときのテクニック集

BigQuery Google Analytics

先週の投稿で、Google AnalyticsのサンプルデータをBigQueryでクエリできるようにしました。

BigQueryを有効化してGoogle Analyticsのサンプルデータにクエリできるようにする - け日記

今回はBigQueryを使ってGoogle Analytics (GA)のデータを集計・分析するとに役立ったテクニックを紹介します。
ここではbigquery-public-dataデータセットのgoogle_analytics_sampleテーブルを使います。

スキーマ定義

GAのデータをBigQueryにインポートすると、以下のスキーマでテーブルが生成されます。項目の意味を確認したい時に、都度都度参照します。

support.google.com

かなりの項目数ですので、適宜タブ補完も活用しましょう。

Standard SQLを使う

BigQueryはLegacy SQLとStandard SQLの2つが利用でき、デフォルトではLegacy SQLです。

Standard SQLはSQL 2011と互換性があり、この後説明するWITH句やSELECT内のサブクエリなど、Standard SQLでしかできないこともあります。そのため、これから作るクエリについてはStandard SQLで書くことをおすすめします。以降のテクニックもStandard SQLを前提としています。
2つの違いについては、こちらの投稿でまとめられています。

tech.starttoday-tech.com

画面から Show Options > SQL Dialect で"Use Legacy SQL"のチェックを外すことで無効化されます。

また、SQLの1行目で#standardSQLと記述すると、Standard SQLで解釈・実行されます。SQLをコピーして共有したりすることもあるかと思いますので、おすすめはこちらの方法です。

#standardSQL

SELECT
  SUM(totals.transactions) AS transactions,
  SUM(totals.transactionRevenue) / 1000000 AS revenue
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_20170801`

パーティションを指定する

GAのデータは日付ごとにパーティションされており、ga_sessions_YYYYMMDDという名前になっています。
そのため、最初に日付を範囲指定して1テーブルにして取り出す必要があります(実質的にはUNION)。

例えば、2017/7/3〜2017/7/30の4週間分を取り出すには、以下のようにクエリします。FROM句のテーブル末尾*でワイルドカード指定できるようにし、_TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'とすることで指定の日付でフィルタリングしています。

#standardSQL

# 2017/7/3〜2017/7/30のGAのデータ
SELECT *
FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
WHERE _TABLE_SUFFIX BETWEEN '20170703' AND '20170730'

WITH句でクエリをまとめる

Standard SQLの大きな利点の1つが、WITH句を使ってクエリを再利用できることです。
Legacy SQLでは、FROM句やJOIN句で別々に記述する、あるいは、ビューを作る、といったことをしなければならず、非常に読みづらいクエリになりがちでした。
GAのデータはデバイス毎やランディングページ毎、リファラ毎で別々に集計することがよくあるため、WITH句を使って1箇所で定義してそれに名前をつけてあげることで、クエリの見通しがとても良くなります(よく使う定型のクエリについては、ビューを検討した方が良いかもしれません)。

例えば、上でパーティション指定して取得した行データから、PCとスマートデバイス(SD)のセッションを抽出し、最後に日毎・デバイス毎のセッション数を集計するクエリは以下のようになります。
row_dataという名前で複数のクエリから使っていること、別のWITHクエリ(pc_sessionとsd_session)内からでも定義済みのrow_dataでクエリできることがポイントです。

#standardSQL

WITH row_data AS (
  SELECT *
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  WHERE _TABLE_SUFFIX BETWEEN '20170703' AND '20170730'
),
pc_session AS (
  SELECT date, fullVisitorId, visitId
  FROM row_data
  WHERE device.deviceCategory = 'desktop'
  GROUP BY date, fullVisitorId, visitId 
),
sd_session AS (
  SELECT date, fullVisitorId, visitId
  FROM row_data
  WHERE device.deviceCategory <> 'desktop'
  GROUP BY date, fullVisitorId, visitId 
)
SELECT date, 'PC', COUNT(*) FROM pc_session GROUP BY date
UNION ALL
SELECT date, 'SD', COUNT(*) FROM sd_session GROUP BY date
ORDER BY 1,2

RECORD型をUNNEST関数で展開する

GAのデータを集計する上でやっかいなのが、hitsやcustomDimensionsなどの、繰り返し(repeated)のRECORD型です。

例えば、ランディングページでの滞在時間を集計したいとします。
ランディングページでの滞在時間は、hits.hitNumber=2のときのhits.time(つまりセッション内の2ページ目までの滞在時間)を取得する必要があります。

以下のようにhits.Numberやhits.timeで直接アクセスすることはできません。

#standardSQL

WITH row_data AS (
  SELECT *
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  WHERE _TABLE_SUFFIX BETWEEN '20170703' AND '20170730'
)
SELECT date, AVG(hits.time) / 1000
FROM row_data
WHERE row_data.hits.hitNumber = 2
GROUP BY date
ORDER BY 1

こういうときは、以下のようにUNNEST関数でRECORD型を展開します。こうするとRECORD型中の複数の要素が展開され、1要素1行になります (ちょうどINNER JOINで自己結合したようになります)。

#standardSQL

WITH row_data AS (
  SELECT *
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  WHERE _TABLE_SUFFIX BETWEEN '20170703' AND '20170730'
)
SELECT date, AVG(hits.time) / 1000
FROM row_data, UNNEST(row_data.hits) AS hits 
WHERE hits.hitNumber = 2
GROUP BY date
ORDER BY 1

試しに、以下のクエリでセッションごとのhitsからhitNumberとtimeを抽出すると、1行1セッションだったデータが、複数行に分かれていることがわかります。

#standardSQL

WITH row_data AS (
  SELECT *
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  WHERE _TABLE_SUFFIX BETWEEN '20170703' AND '20170730'
)
SELECT date, fullVisitorId, visitId, hits.hitNumber, hits.time
FROM row_data, UNNEST(row_data.hits) AS hits

SELECT句のサブクエリで集計する

Standard SQLではSELECT句内でサブクエリができます。

例えば、セッション内である商品(hits.product.productSKU='GGOEGBPB021199')が閲覧されたかどうかを出力するクエリは以下のようになります。
SELECT内でサブクエリを発行していますが、サブクエリ内でもUNNESTが使えますので、そこでproductを展開し、productSKUが一致するかどうかを判定しています (こんなふうにCASE WHENで横持ちにして集計するのもあるあるですね)。

#standardSQL

WITH row_data AS (
  SELECT *
  FROM `bigquery-public-data.google_analytics_sample.ga_sessions_*`
  WHERE _TABLE_SUFFIX BETWEEN '20170703' AND '20170730'
)
SELECT date, fullVisitorId, visitId, 
  MAX((SELECT COUNT(DISTINCT productSKU) FROM UNNEST(hits.product)))
FROM row_data, UNNEST(row_data.hits) AS hits 
GROUP BY date, fullVisitorId, visitId
ORDER BY 1, 2, 3

おわりに

自分がGAのデータをBigQueryで集計・分析する際に役立ったテクニックをまとめました。