SQLでGA4のWebデータを自在に解析する：GA4＋BigQuery ウェブ分析基盤の活用入門（3）（1/2 ページ）

今回は、GA4とBigQueryを組み合わせてSQLによるWeb分析を行う方法を解説します。特定のイベントをカウントする方法や閲覧ページの1つ前と1つ後を取得する方法およびアクティブユーザーを取得するSQLを紹介します。これらのSQLについて詳しく解説し、ライブラリの活用方法を紹介します。これらの手法を活用することで、GA4の標準UIのみではできない詳細な分析を行いましょう。なお今回のSQLでは、前回の記事で紹介したサンプルデータを使用しています。

[西潤史郎（著）／山田祥寛（監修），WINGSプロジェクト]

連載：GA4＋BigQuery ウェブ分析基盤の活用入門

特定のイベントをカウントするSQL

　シンプルな例からはじめます。GA4からエクスポートしたデータから、BigQueryで特定のイベントをカウントするためのSQLクエリを以下に示します。例として、イベントのうちpage_viewイベントをカウントします。

SELECT
  COUNT(*) AS event_count
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
WHERE
  event_name = 'page_view' -- (1)

リスト1　ページビューイベント数カウント

　実行結果は図1のようになります。

図1　ページビューイベント数カウント

　このSQLクエリは、bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131というGA4のサンプルデータセットから、page_viewというイベント名のイベントの総数を取得するものです。このクエリでは（1）のWHERE句により、イベント名（event_name）がpage_viewであるレコードのみを取得しています。

　実行すると、page_viewイベントの総数（event_count）が取得できます。このクエリは、特定のイベント（この場合はページビュー）の発生回数を調べる際に役立ちます。

　実際の現場では必要な結果を得るために、SQLをカスタマイズしながら実行と確認を繰り返すのが一般的です。以下では、上のクエリを少し変えて、「全イベントをイベントごとにカウントするクエリ」に変更してみます。

SELECT
  event_name,
  COUNT(*) AS event_count
FROM
  `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131`
GROUP BY
  event_name -- (1)

リスト2　全イベント数カウント

　このSQLクエリは、bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131というGA4のサンプルデータセットからイベント名とそのイベントの発生回数を取得するものです。

　（1）では、結果をグループ化するために使用するカラムを指定します。このクエリでは、イベント名（event_name）ごとに結果をグループ化しています。実行すると、イベント名ごとに発生したイベントの総数（event_count）が取得できます。

図2　イベント数の取得

　このクエリは、イベント一覧や発生回数を調べる際に役立ちます。例えばイベントとしてpage_viewの他に、session_start（セッション開始）やfirst_visit（ユーザーの初回訪問）といったイベントがあることが分かります。これらのイベント発生回数を利用して、例えば以下のように別の指標を算出できるようになります。

• セッション当たりのページビュー数：page_viewのイベント数／session_startのイベント数
• 新規ユーザー比率：first_visitのイベント数／session_startのイベント数

　先ほどのクエリからの変更は1カ所ですが、結果テーブルの内容は変わりました。このように、データ分析ではSQLをカスタマイズしながら求める結果を得ることになります。

【補足】BigQueryでクエリを実行する際に便利な機能

　BigQueryでクエリを実行する際に便利な機能を2つ紹介します。まず、クエリを入力するとエディタ右上に処理データ量が表示されます。例えば、月に1TBまでの無料枠内でクエリを実行したい場合など、これを実行時の目安として参考にしてください。

図3　利用データ量表示

　また、エディタ右上のキーボードのアイコンをクリックすると、ショートカットを参照できます。例えば、［クエリを実行］やSQLを整形する［クエリを書式設定］は頻繁に実行するため、覚えておくと便利です。

図4　ショートカット表示

閲覧ページの1つ前と1つ後を取得