GA4：「“購入前”に訪れたページ」の集計（SQLクエリ）

BigQueryでGA4データを解析：「purchase発生前に訪れたページ」の集計

今回ご紹介するSQLは、Googleが用意してくれている全て無料の環境で、実際に実行して頂くことができます。

Googleが用意してくれている、全て無料の環境の利用方法は下記のURLをご覧ください。クレジットカードの登録も一切不要です。

https://nakata.click/ga4/SQL_free_Study.pdf

どんな集計結果が得られるか？

下記の例の場合だと、「ページ閲覧A」の集計結果は2セッションになるように

集計します。

Aさんのセッション（1回目）：「セッション開始」 → 「ページ閲覧A」 →「ページ閲覧B」 →「ページ閲覧A」 →「ページ閲覧C」 → 「purchase発生」

同じAさんのセッション（2回目）：「セッション開始」 → 「ページ閲覧A」 →「ページ閲覧P」 →「ページ閲覧Q」 → 「ページ閲覧A」 →「purchase発生」

具体的には、下記のような集計結果が得られます。

スプレッドシートでもご覧いただけます。

必要とされる知識

SQLの基本知識
BigQueryの基本操作方法
GA4のイベントトラッキングの基本

クエリの目的

purchaseイベントの前にどのページをユーザーが訪れたのかを知ることで、ユーザーの購入経路や最も影響力のあるコンテンツを特定することができます。

ただし、上記の実行結果にあるように、セッション数ランキングの上位には、purchaseの直前に必須のページが固まってランクインすることになります。

クエリの与件とクエリ

集計期間

2020年11月01日～2020年11月10日（上記のGoogleが用意した無料環境では、今年や昨年のデータは入っていないため）

与件

purchaseイベントが発生する前に閲覧したpage_locationのセッション数を集計。

それぞれのページを通過したセッション数上位20位を降順で出力。

SQLクエリ

上記のGoogleが用意した無料環境にて、無料のGA4サンプルデータを使用して実行します。
この記事で解説しているクエリは、下記のGitHubのリンク先にて、ご覧いただけます。

https://github.com/nakata1967/ga4sql/blob/main/view_before_purchase.sql

上記GitHubに置いてあるクエリの解説

SQLの書き方は一つではありませんので、一例としてご利用ください。

今回のクエリでは、3つのCTEを利用しています。CTE （Common Table Expression）とは、一時的な名前付きの結果セットを作成するシンプルなSQL文のことを指します。このCTEは、その後のSELECT、DELETE、INSERT、またはUPDATE文で使用することができます。

PurchaseSequence CTE
- 何をしているのか: まず、各ユーザーの各セッションでのpurchaseイベントのタイムスタンプを取得します。これは、後にpurchaseの前にどのページを訪れたかを特定するための基盤となります。
- UNNEST関数の利用: GA4のデータは、複数のイベントパラメータを持つことがあり、これらのパラメータは配列の形で格納されています。UNNEST関数は、この配列を「フラット化」するためのもので、それにより各イベントパラメータを独立した行として扱うことができます。
- このセクションの目的: この部分のクエリの主な目的は、各セッションでの購入イベントの発生時刻を知ることです。
ViewsBeforePurchase CTE
- 何をしているのか: 次に、各ユーザーがpurchaseイベントの前にどのページを訪れたかをリストアップします。
- ページのURLの取得: ここでもUNNEST関数を使用して、イベントパラメータからページのURL（page_location）とセッションIDを抽出します。page_locationは、ユーザーが訪れた具体的なウェブページのURLを示しています。
- このセクションのポイント: 各ユーザーがpurchaseイベントの前にどのページをいつ訪れたかの情報を集めることが目的です。
FilteredViews CTE
- 何をしているのか: ここでは、先程の2つのCTEのデータを組み合わせて、purchaseイベントの前に訪れたページの情報を絞り込みます。
- JOIN操作: SQLにおけるJOINは、2つのテーブルのデータを特定のキー（この場合はユーザーIDとセッションID）に基づいて結合する操作です。これにより、ユーザーがpurchase前に訪れたページと、実際のpurchaseイベントの情報を1つのテーブルに結合することができます。
- このセクションのポイント: purchaseの前にどのページを訪れたのか、その情報を正確に特定することが目的です。
最終的なSELECT文
- 何をしているのか: 最後に、先程絞り込んだデータから、各ページのユニークなセッション数を集計します。
- ユニークなセッションとは: 同じユーザーが何度も同じページを訪れる場合が考えられますが、このクエリではそのような重複を排除して、各ページを訪れたユニークなセッション数のみをカウントしています。
- このセクションのポイント: ユーザーがpurchaseの前に訪れたページのランキングを出力することが目的です。

ご質問は、弊社が運営するGA4の無料コミュニティへ、お気軽にどうぞ。リンクは下記にあります。

https://discord.gg/NJUF5Ktv

最後まで読んで頂きまして、ありがとうございました。