GA4でPIVOT演算子を用いて日次のイベント数をまとめて集計するSQLクエリ

本記事ではBigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、PIVOT演算子を活用して日次のイベント数をまとめて一気に集計するSQLクエリについて解説をおこなう。BigQueryにおけるピボット機能は比較的新しいものであり最初はとっつきにくいが、慣れてしまえば非常に使い勝手が良くSQLをスッキリ書くことができるので、ぜひ一度トライしてみてほしい。

SQLクエリの概要

まずPIVOT演算子を用いて、 event_name に格納されている値から集計したいものを選択して、それをカラムに展開して event_date ごとに集計をおこなう（選択したイベント名を縦持ちから横持ちに変換）。

The PIVOT operator rotates rows into columns, using aggregation. PIVOT is part of the FROM clause.

出典 : PIVOT演算子

（参考記事）

データの縦持ち/横持ちとは？ポイントと実例を紹介

今回の記事のサンプルクエリでは、集計するイベントとして以下の6つを設定している。集計するイベントの内容を変えたい場合はこの部分を適宜修正する。

page_view
session_start
first_visit
scroll
add_to_cart
purchase

今回はGoogleが用意しているGA4のサンプルデータセットを利用して集計をおこなっている。そのため、下記のSQLクエリサンプルをコピペしてそのままBigQueryで実行していただければ、同じ結果を得ることができるようになっている。

Google Merchandise Storeは Google ブランドの商品を販売するオンラインストアです。このサイトでは、Google アナリティクス 4 の標準のウェブ e コマースの実装と拡張計測機能が使用されます。BigQuery 一般公開データセットプログラムを通じて利用可能な ga4_obfuscated_sample_ecommerce データセットには、2020 年 11 月 1 日から 2021 年 1 月 31 日までの 3 か月間の難読化した BigQuery イベントエクスポートデータのサンプルが含まれています。

出典 : Google アナリティクス 4 e コマースウェブ実装向けの BigQuery サンプルデータセット

SQLクエリサンプル

今回のSQLクエリは、以下のようになる。

SELECT
  *
FROM (
  SELECT
    event_date,
    event_name
  FROM
    `bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*`
) 

PIVOT(
  COUNT(*) 
  FOR event_name 
  IN (
    'page_view',
    'session_start',
    'first_visit',
    'scroll',
    'add_to_cart',
    'purchase'
  ) 
)
ORDER BY
  event_date
;

BigQueryでの集計結果

BigQueryでの集計結果は、以下のようになった。

ChatGPT（GPT-4モデル）によるSQL解説

今回もChatGPTによる解説を付すので、学習の参考にされたい。

このSQLクエリは、PIVOTを使用して、指定したイベントの種類（’page_view’、’session_start’、’first_visit’、’scroll’、’add_to_cart’、’purchase’）ごとに日毎の出現回数を集計します。そして、その結果をイベントの種類ごとに列として表示します。

1.まず、サブクエリで指定したデータセット(bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_202012*)から、日付(event_date)とイベント名(event_name)を取得します。

2.その後、PIVOTを使用して、特定のイベントの出現回数を集計し、それらを列として表示します。ここで指定したイベントは、’page_view’、’session_start’、’first_visit’、’scroll’、’add_to_cart’、’purchase’です。

3.最終的に、結果をイベント日(event_date)で並び替えます。

クエリの結果は、各日付における各イベントの出現回数を示す表となります。各行は特定の日付を表し、各列は特定のイベントタイプの出現回数を表します。

このようなクエリは、特定のイベントの頻度を時間に対して視覚化したいときや、特定の期間における各種イベントの傾向を調べたいときに役立ちます。

今回の記事では、PIVOT演算子を活用して日次のイベント数をまとめて一気に集計するSQLクエリについて解説を行った。BigQueryにおけるピボット機能を使用する際の参考にしてほしい。