Go to Qiita Advent Calendar 2024 Top
LoginSignup
6
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

@y-kazawa(Kakki Shoushou)in株式会社プロトソリューション

【GA4×BigQuery】個人的な躓きポイントと、GA4の数値取得時によく使いそうなクエリメモ

Last updated at Posted at 2022-12-16

はじめに

はじめまして、エンジニア歴半年のGopherくんLoverなペーペーエンジニアです。

訳あって2週連続でQiitaを書くことになったのですが、先週のスクレイピングの続きをする気力はなかったので…

また別の内容にしようと思います。
今回は、最近使用しているBigQueryについて、初めて触った際に躓いたポイントと、よく使うクエリを書き残そうと思います。

メモ的な要素が強いので参考程度にご覧いただけますと幸いです。

なお、BigQueryに関して超入門者なので、そこのところはご容赦ください。

個人的な躓きポイント

【躓きポイントその1】GA4のBigQueryのスキーマについて

まず引っかかったのがBigQuery独特のテーブル構造でした。
通常のSQLと違う部分として、テーブルの要素の中にテーブルが入れ子になっている部分があります。

（スキーマについてはこちらを参照：[GA4] BigQuery Export スキーマ）

例えば、events_paramsというフィールドでは、
image.png
上記のような形で、events_paramsの中に複数の要素が入れ子になっています。
events_paramsの中を取り出すと、例えばこちらのような形です。

events_params key string_value int_value float_value double_value
user_engagement page_location https://qiita.com/ null null null
engagement_time_msec null 35085 null null
ga_session_id null 1234567890 null null
session_engaged 1 null null null
page_referrer https://www.google.com/webhp null null null
engaged_session_event null 1 null null
firebase_conversion null 0 null null
page_title Qiita null null null

このように入れ子になっている値を取り出すときは、各行が独立した形(unnestされた状態)する必要があります。

つまり、先ほどのテーブルを…

events_params key string_value int_value float_value double_value
user_engagement page_location https://qiita.com/ null null null
user_engagement engagement_time_msec null 35085 null null
user_engagement ga_session_id null 1234567890 null null
user_engagement session_engaged 1 null null null
user_engagement page_referrer https://www.google.com/webhp null null null
user_engagement engaged_session_event null 1 null null
user_engagement firebase_conversion null 0 null null
user_engagement page_title Qiita null null null

このような感じで各行に情報が入っている状態(nullは問題ないです。)にします。
これにはunnest関数を使用します。

SELECT
  key,
  value.string_value,
  value.int_value,

FROM
  `analytics_table.events_20221208`, UNNEST(event_params)

こんな感じでevent_paramsをunnestして取り出すと…

unnest 結果.png

このような感じでkeyとvalueが取り出せます。
これができれば、あとはwhere句で指定するだけです。

【躓きポイントその2】_TABLE_SUFIXによる日付範囲の指定

ここもなかなかしっくりこなかった部分です。
GA4におけるBigQueryでは、テーブルが日付ごとに分かれています。

画像でもわかるとおり、例えば2022年の12月6日のイベントは"events_20221206"のテーブルに格納されています。
そのため、12月1日～12月10日など、一定期間で数値を取得したいときは、該当期間のテーブルを全て参照する必要があります。

そこで登場するのが_TABLE_SUFIXです。

SELECT
  key,
  value.string_value,
  value.int_value,

FROM
  `analytics_table.events_202212*`, UNNEST(event_params)

WHERE
  _TABLE_SUFFIX BETWEEN '20221201' AND '20221210'  -- 12月1日から12月10日までの日付を参照する

このように、FROM句で指定するテーブルの日付の範囲をワイルドカードで指定し、その後のWHERE句で_TABLE_SUFIXを使用して日付を指定します。
これにより、任意の範囲の日付で数値を取得することができるようになります。

GA4の数値取得時によく使いそうなクエリメモ

⓪GA4のスキーマとよく使いそうな項目

クエリをいろいろ列挙する前に、GA4のスキーマの中でよく使いそうな項目も挙げておこうと思います。

詳しいGA4のスキーマに関してはこちらを参照してください。
参照：[GA4] BigQuery Export スキーマ

この中で、個人的によく使用することになりそうだなと感じたものを以下に表にまとめておきます。

よく使用しそうなフィールド一覧
フィールド名 説明 使用ポイント
event_name イベントの名前。 page_view, session_start, search, clickなど、イベントの種類が格納されている。取りたい情報によって指定する内容が異なり、ここをカウントすることが多い。
event_params このイベントに関連付けられたパラメータを格納する繰り返しレコード。 page_titleやpage_locationなど、イベントの基本的な情報が連想配列で格納されている。keyで指定して取り出す。
event_date イベントが記録された日付（アプリの登録タイムゾーンにおける日付を YYYYMMDD 形式で示したもの）。 タイムゾーンを気にせずに日付を取得したいときに使用可能。(timestampを使う方がより精緻なイメージ)
event_timestamp 該当クライアントでイベントが記録された時刻（ミリ秒単位、UTC）。 イベント発生時刻を取得したいときに使う。日別での集計などで使用する。
device.category デバイスのカテゴリ（モバイル、タブレット、PC）。 mobile, tablet, pcのどれか。デバイスごとの数値を取得したいときに使用。
device.operating_system デバイスのオペレーティング システム。 Windows8, iosなど。デバイスのOSごとに数値を取得したいときに使用。
traffic_source.name ユーザーを最初に獲得したマーケティング キャンペーンの名前。 organic, direct, apppush, その他設定したキャンペーンの名前。流入元ごとに数値を取得したいときに使用。

ここからは、実際に数値を取った際に使用したクエリを少し一般化して書き残していこうと思います。

①記事ごとにページビュー・セッション数を取得

SELECT
  (select value.string_value from unnest(event_params) where key = 'page_location') as page_location,
  countif(event_name = 'page_view') as page_view,
  countif(event_name = 'session_start') as session

FROM
  `analytics_table.events_*`  -- 自身が参照したいテーブルを選択

WHERE
  _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'  -- 日付指定
AND
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE 'https://qiita.com/'  -- 取得したいページのURLを指定(ディレクトリごとに取得することも可能)

GROUP BY 
  page_location  -- URLごとに数値をまとめる

ORDER BY
  page_view desc  -- ページビュー数で降順

LIMIT 100

記事ごとに情報を取りたい場合に使用しました。
page_locationを指定し、数値を取得した記事を抜粋し、event_nameの"page_view"と"session_start"をページビュー数・セッション数として取得しています。

出力結果(数値はデタラメです)
行 page_location page_view session
1 https://qiita.com/ 146723 14230
2 https://qiita.com/y-kazawa 1456 1398
3 https://qiita.com/y-kazawa/items/01b2983606996c8987dc 237 220

②日別でページビュー・セッション数を取得

SELECT
  date(timestamp_micros(event_timestamp),""Asia/Tokyo"") as event_date,  -- DATE関数でタイムスタンプを日別に集計
  countif(event_name = 'page_view') as page_view,
  countif(event_name = 'session_start') as session

FROM
  `analytics_table.events_*`  -- 自身が参照したいテーブルを選択

WHERE
  _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'  -- 日付指定
AND
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE 'https://qiita.com/'  -- 特定のページから取得したい場合はURLを指定(ディレクトリごとに取得することも可能)

GROUP BY 
  event_date  -- 日付ごとに数値をまとめる

ORDER BY
  page_view desc  -- ページビュー数で降順

LIMIT 100

①同様page_locationを指定すれば特定ページの日別の数値が参照できます。
event_timestampに入っている情報をdate関数で日付化し、event_dateとして集計しています。

出力結果(数値はデタラメです)
行 event_date page_view session
1 2022-12-09 36497 29569
2 2022-12-10 30796 23580
3 2022-12-11 30245 23368

③流入元ごとにセッション数・アクセス数を取得

SELECT
  (select value.string_value from unnest(event_params) where key = 'page_title') as page_title,
  (select value.string_value from unnest(event_params) where key = 'page_location') as page_location,
  traffic_source.name as source_name,
  countif(event_name = 'page_view') as page_view,
  countif(event_name = 'session_start') as session

FROM
  `analytics_table.events_*`  -- 自身が参照したいテーブルを選択

WHERE
  _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'  -- 日付指定
AND
  (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') LIKE 'https://qiita.com/'  -- 特定のページから取得したい場合はURLを指定(ディレクトリごとに取得することも可能)

GROUP BY 
  page_title,          -- タイトルごとに数値をまとめる
  page_location,       -- URLごとに数値をまとめる
  traffic_source.name  -- 流入元ごとに数値をまとめる

ORDER BY
  page_view desc  -- ページビュー数で降順

LIMIT 100

流入元ごとのアクセス数を取得するために使用しました。

流入元に関する情報は"traffic_source"というフィールドに格納されているので、
event_paramsからタイトル、URLを取得し、流入元をtraffic_sourceから取得しています。

出力結果(数値はデタラメです)
行 page_title page_location source_name page_view session
1 Qiita https://qiita.com/ (organic) 146723 14230
2 Qiita https://qiita.com/ (direct) 1456 1398
3 マイページ｜Qiita https://qiita.com/y-kazawa/ apppush 600 489
4 マイページ｜Qiita https://qiita.com/y-kazawa/ (direct) 237 220

④デバイス(デバイス / OS)ごとにセッション数・アクセス数を取得

SELECT
  (select value.string_value from unnest(event_params) where key = 'page_title') as page_title,
  (select value.string_value from unnest(event_params) where key = 'page_location') as page_location,
  -- device.category,          -- デバイスだけ取得すればよいのであればこちらを使用
  -- device.operating_system,  -- OSだけ取得すればよいのであればこちらを使用
  concat(device.category, ""/"", device.operating_system) as device_source,  -- デバイスとOSを結び付けて表示
  countif(event_name = 'session_start') as number_of_sessions,
  countif(event_name = 'page_view') as page_view

FROM
  `analytics_table.events_*`  -- 自身が参照したいテーブルを選択

WHERE
  _TABLE_SUFFIX BETWEEN 'YYYYMMDD' AND 'YYYYMMDD'  -- 日付指定
AND
  (select value.string_value from unnest(event_params) where key = 'page_location') like 'https://qiita.com/'  -- 特定のページから取得したい場合はURLを指定(ディレクトリごとに取得することも可能)


GROUP BY 
  page_title,          -- タイトルごとに数値をまとめる
  page_location,       -- URLごとに数値をまとめる
  device_source        -- デバイスごとにまとめる

ORDER BY
  page_view desc  -- ページビュー数で降順

LIMIT 100

deviceに格納されるデバイスの情報を用いてデバイスごとに数値を取得するときに使用しました。
device.categoryが良く使うかと思いますが、device.operating_systemなどを用いてOSごとに取得することも可能です。

まとめ

今回は初めて触ったBigQueryについて書いてみました。
今回はページビュー数やセッション数など、アクセスに絞った内容がメインでしたが、今後は回遊率やコンバージョンなど、より後ろの工程の数値も取得できるよう勉強していきたいなという気持ちです。

メモ的な要素が若干強かったかもですが、参考にあれば幸いです。
ここまでお読みいただきありがとうございました！

6
5
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
Sign upLogin
6
5

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?