GA4データを目的別に分析する方法をコード付きで解説！

前回の記事でSQLの基本、UNNESTや_TABLE_SUFFIXなどのGA4データへのSQLの頻出句を解説しました。
次は一歩進んで、GA4データを具体的な目的を持って分析してみましょう。

本記事では「GA4データ分析を始める」と題して、下記の全3回の内、
第3回目のGA4データを目的別に分析する方法について解説します。
連載一覧
第1回 GA4とBigQueryを連携するメリットと連携方法をわかりやすく解説！
第2回【実例つき】GA4データへのSQLの基本について
第3回 GA4データを目的別に分析する方法をコード付きで解説！

LP毎のセッション数の抽出

アクセス解析においてよく分析されるLP（ランディングページ）毎の指標を抽出します。
下記のSQLはGA4データから、LP毎のセッション数を抽出するSQLです。

SELECT
landingPageLocation,
landingPageTitle,
COUNT(*) AS sessions
FROM (
SELECT
(SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'page_location') AS landingPageLocation,
(SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'page_title') AS landingPageTitle,
FROM
analytics_XXX.events_*
WHERE
_TABLE_SUFFIX BETWEEN '20240101'
AND '20240103'
AND event_name = 'session_start' )
GROUP BY
landingPageLocation,
landingPageTitle

解説：
FROM句の中はサブクエリとなっています。サブクエリとはSQLの中にSQLが存在する入れ子構造を指します。
上記のコードのサブクエリの中では、event_name = ‘session_start’の行を抽出しており、session_startイベントはセッションの起点を指すので、これでLPが抽出できます。
次にGROUP BYでlandingPageLocation, landingPageTitle毎にデータ行数をカウントすることで、セッション数を出します。

このようにGA4データから指標を抽出するには、イベントの数（データ行数）をカウントして出すことが基本となります。

ECサイトにおける商品の収益の抽出

ECサイトの場合、GA4データから商品の収益のデータも抽出可能です。
下記のSQLはGA4データから、商品の収益や数量を抽出するSQLです。

SELECT
CAST(CONCAT(LEFT(event_date,4),'-',RIGHT(LEFT(event_date,6),2),'-',RIGHT(event_date,2)) AS date) AS date
ecommerce.transaction_id,
item_category,
item_id,
item_name,
quantity,
item_revenue
FROM
analytics_XXX.events_*, UNNEST(items)
WHERE
_TABLE_SUFFIX BETWEEN '20240101'
AND '20240103'
AND event_name = 'purchase'

解説：
FROM句でanalytics_XXX.events_*, UNNEST(items) と書いているのは、itemsが配列構造のためです。
event_paramsがkey – value形式の配列のため、SELECT句中でのUNNESTが可能なことに対して、itemsの場合このようにFROM句中でUNNESTする必要があります。

WHERE句でevent_name = ‘purchase’とすることで、購入イベントを抽出しています。

一度の購入イベントで複数商品が購入される可能性があることから、このようにitemsはUNNESTが必須な項目となっています。

カスタマージャーニーの追跡

次に個々のユーザーがサイト上でどのような行動をとっているか、分析してみましょう。
GA4データでユーザーを識別するIDは、デフォルトで付番されるuser_pseudo_idとGA4プロパティ画面上で設定が必要なuser_idの２種類があります。

user_pseudo_id：デバイス、ブラウザ毎に一意なID
user_id：ログイン時のIDなどを代入するユーザー毎に一意なID

クロスデバイストラッキングや、自社で管理しているIDでユーザーを追跡したい場合はuser_idを使用します。
下記のSQLはGA4データから、特定ユーザーの行動を抽出するSQLです。

SELECT
CAST(CONCAT(LEFT(event_date,4),'-',RIGHT(LEFT(event_date,6),2),'-',RIGHT(event_date,2)) AS date) AS date,
event_timestamp,
event_name,
(SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'page_location') AS pageLocation,
(SELECT value.string_value FROM UNNEST(event_params) AS params WHERE params.key = 'page_title') AS pageTitle
FROM
analytics_XXX.events_*
WHERE
_TABLE_SUFFIX BETWEEN '20240101'
AND '20240103'
AND user_id = 'XXX'
ORDER BY
date ASC,
event_timestamp ASC

解説：
WHERE句のuser_id = ‘XXX’で、抽出するユーザーを指定します。
ORDER BY句ではdate ASC, event_timestamp ASCとすることで、日付、発生時刻順になるように並び替えています。
このSQLにより例えば下記のような結果が得られます。

session_start　　　　ページA
page_view　　　　　ページB
user_engagement　 ページB
page_view　　　　　ページC
purchase　　 　　　ページC

JOIN句で自社データとの統合

JOIN句を用いることで、テーブル同士の結合が可能です。
例えば下記のSQLではGA4データから抽出したカスタマージャーニーに自社の会員マスタを結合して、会員マスタのデータを統合しています。

解説：
JOINにはINNER JOIN（内部結合）とLEFT JOIN（左外部結合）があり、INNER JOINは結合キーが一致するデータ行のみ結果に残します。LEFT JOINは結合キーの一致に関わらず、左側のテーブルのデータ行は全て残します。
上記のコードの場合はLEFT JOINなので、GA4データは全て残して、会員マスタからは結合キーが一致するデータのみ統合します。

分析結果をBIツールで可視化する

SQLの結果をテーブルとして保存すると、BIツールで可視化することができます。
Looker StudioやTableauなど有名BIツールは標準でBigQueryのコネクターをサポートしているため、
BIツール上で、BigQueryのテーブルを選択するだけでBIツールとのデータ連携ができます。

データを可視化することで、分析結果のシェア、示唆の抽出、モニタリングといったデータ活用が行えるので、
BIツールとの連携を試してみましょう。

BIツールでの可視化イメージ　出典：電算システム

まとめ

「GA4データ分析を始める」の第3回として、GA4データを目的別に分析する方法について解説しました。
基本的なSQLを学習すれば、テーブルから目的に沿ってデータを抽出することが簡単に行えるようになります。
複雑な分析を行うには、更に数ステップの処理が必要なSQLを書いたり、Pythonなどのコードでデータモデリングが必要になりますが、この記事の内容だけでもGA4データの探索には困りません。

第1〜第3回の記事をご覧いただくことで、GA4データの分析・活用を始められるようになっているので、参考にしていただけたら幸いです。
連載一覧
第1回 GA4とBigQueryを連携するメリットと連携方法をわかりやすく解説！
第2回【実例つき】GA4データへのSQLの基本について
第3回 GA4データを目的別に分析する方法をコード付きで解説！

KUIXではただ導入・開発するだけでなく、導入後の利活用を実現することに着眼した、データレイク・DWH・データマート・BIツールの選定・導入からレポート作成、運用、啓蒙・展開までトータルのコンサルテーションなどを行っています。
「データマート/データレイク/DWH/BIツールを導入したい」
「すでに導入しているがデータ活用、展開が進んでいない」など
お困りの方は、ぜひお気軽にご連絡ください！お問い合わせはこちらから