最近BigQueryを使い始めました。
興味があるものの、使うまでに時間がかかっていたのは、生データ以外の魅力として「それGA4でもよくない？」と感じる部分が多かったからです。
あとはXを見ていると、「BigQueryも使えないくせにGA4なんかのぬるいデータ見てるんじゃねーよ」といったBigQuery奉行が結構いらっしゃって、とっても怖かったからです。下手な発信したら噛みつかれそう。
目次
ユーザーのページ遷移を追うことの難しさ
この辺、みなさまも悩まれる部分ではないでしょうか。
UAの時代からしっくりこなかったり、GA4の経路データ探索もイマイチ。
「なんとなく想像していたものじゃない」って感じですよね。
これ、僕のセンスの無さでしょうかねｗ
タグマネージャーを使って、力技で数値を取ったり等々さまざまなサイト様も見受けられますが…。
もう少しカジュアルにこの動きを見たいなぁと思ってます。
そこで出会ったSQL
株式会社HAPPY ANALYTICS様の運営する、Google Analytics 4 ガイド – アクセス解析ツール「Google Analytics 4」の実装・設定・活用のための情報サイトという非常にありがたいサイトを見つけます。
見つけると言うより、失礼な表現ですが、BigQuery×GA4のSQLや活用を調べていて、何かとわかりやすいと感じる・ヒットするサイトがハピアナさんのサイトしか無かった…。
さすが小川さん・ハピアナさん…。
参考にしたSQL：指定ページの１つ次に見たページを取得
まず、参考にさせていただいたSQLを掲載しておきます。
with predata as (
select
user_pseudo_id, --ユーザーのCookieIDを取得
(select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'ga_session_id') as session_id,--ユーザーのセッションIDを取得
(select value.string_value from unnest(event_params) where key = 'page_location') as page,--ページのURLを取得
event_timestamp --イベントの発生時間を取得
from
`ha-ga4.analytics_227084301.events_*` -- データの選択範囲。ここでは全期間とし、whereの部分で日付を指定する
where
_table_suffix between '20220201' and '20220205' -- 日付の指定
and event_name = 'page_view'), -- イベント名を指定
prep_nextpage as (
select
user_pseudo_id, --ユーザーのCookieIDを取得
session_id, --ユーザーのセッションIDを取得
page, --ページのURLを取得
lead(page,1) over (partition by user_pseudo_id,session_id order by event_timestamp) as next_page, --イベント時間昇順に並びかえた後に、leadを使ってpage列の1つ先の値を取得してnext_pageに格納
event_timestamp --イベントの発生時間を取得
from
predata--上記のデータをpredataのクエリ結果から取得
)
select
page, --URLを取得
ifnull(next_page,'(exit)') as next_page, --次のページを取得
count(distinct concat(user_pseudo_id,"-", session_id)) as count --CookieIDとセッションIDをつなげユニーク数をカウントする
from
prep_nextpage --prep_nextpageのクエリ結果から取得
where
page='https://happyanalytics.co.jp/' --起点となるURLを指定
group by
page,next_page --ページと次ページでグルーピング
order by
count desc --カウントの降順で並び替え指定したページの次ページのURLを追うSQLです。
結果例はこちら
遷移数と、遷移先のページが表示されます。
正直、ここまではよくあるデータかなとは思います。
今回のお題となるSQL：指定ページの次に見たページを10先まで取得
先ほど、よくあるデータと表現したのは、1つ先・後ろはUA時代にもなんとなく見れたんですよね。
生データ叩いて出しているので、同じ1つ前後でも精度が違うことは理解しているんですが、BigQuery使っている感が無いんです。
そこで、「1つ先とかではなく、最後まで追えないの？」という意向のもと、
「10ページ先までの遷移」SQLを組んでみました。
with predata as (
select
user_pseudo_id, --ユーザーのCookieIDを取得
(select value.int_value from unnest(event_params) where event_name = 'page_view' and key = 'ga_session_id') as session_id, --ユーザーのセッションIDを取得
(select value.string_value from unnest(event_params) where key = 'page_location') as page, --ページのURLを取得
event_timestamp --イベントの発生時間を取得
from
`sample-project1234.analytics_123456789.events_202410*` -- データの選択範囲
where
event_name = 'page_view' -- イベント名を指定
),
prep_nextpages as (
select
user_pseudo_id, --ユーザーのCookieIDを取得
session_id, --ユーザーのセッションIDを取得
page, --ページのURLを取得
lead(page, 1) over (partition by user_pseudo_id, session_id order by event_timestamp) as next_page_1, -- 1つ先のページ
lead(page, 2) over (partition by user_pseudo_id, session_id order by event_timestamp) as next_page_2, -- 2つ先のページ
lead(page, 3) over (partition by user_pseudo_id, session_id order by event_timestamp) as next_page_3, -- 3つ先のページ
lead(page, 4) over (partition by user_pseudo_id, session_id order by event_timestamp) as next_page_4, -- 4つ先のページ
lead(page, 5) over (partition by user_pseudo_id, session_id order by event_timestamp) as next_page_5, -- 5つ先のページ
lead(page, 6) over (partition by user_pseudo_id, session_id order by event_timestamp) as next_page_6, -- 6つ先のページ
lead(page, 7) over (partition by user_pseudo_id, session_id order by event_timestamp) as next_page_7, -- 7つ先のページ
lead(page, 8) over (partition by user_pseudo_id, session_id order by event_timestamp) as next_page_8, -- 8つ先のページ
lead(page, 9) over (partition by user_pseudo_id, session_id order by event_timestamp) as next_page_9, -- 9つ先のページ
lead(page, 10) over (partition by user_pseudo_id, session_id order by event_timestamp) as next_page_10, -- 10つ先のページ
event_timestamp --イベントの発生時間を取得
from
predata --上記のデータをpredataのクエリ結果から取得
)
select
page, --URLを取得
ifnull(next_page_1, '(exit)') as next_page_1, --次のページを取得 (1つ先)
ifnull(next_page_2, '(exit)') as next_page_2, --次のページを取得 (2つ先)
ifnull(next_page_3, '(exit)') as next_page_3, --次のページを取得 (3つ先)
ifnull(next_page_4, '(exit)') as next_page_4, --次のページを取得 (4つ先)
ifnull(next_page_5, '(exit)') as next_page_5, --次のページを取得 (5つ先)
ifnull(next_page_6, '(exit)') as next_page_6, --次のページを取得 (6つ先)
ifnull(next_page_7, '(exit)') as next_page_7, --次のページを取得 (7つ先)
ifnull(next_page_8, '(exit)') as next_page_8, --次のページを取得 (8つ先)
ifnull(next_page_9, '(exit)') as next_page_9, --次のページを取得 (9つ先)
ifnull(next_page_10, '(exit)') as next_page_10, --次のページを取得 (10つ先)
count(distinct concat(user_pseudo_id, "-", session_id)) as count --CookieIDとセッションIDをつなげユニーク数をカウントする
from
prep_nextpages --prep_nextpagesのクエリ結果から取得
where
page = 'https://sample.com' --起点となるURLを指定
group by
page, next_page_1, next_page_2, next_page_3, next_page_4, next_page_5, next_page_6, next_page_7, next_page_8, next_page_9, next_page_10 -- ページと次ページでグルーピング
order by
count desc --カウントの降順で並び替え活用される際は、
`sample-project1234.analytics_123456789.events_202410*`をご自身のデータのもの
'https://sample.com'を軸・起点としたいURL
に合わせてくださいね。
LEAD関数の使用
蛇足ですが、既存コードの流れを踏襲し、コードをシンプルに拡張していきます。
lead(page, 1), lead(page, 2), lead(page, 3), lead(page, 4)と順に使うことで、1つ先から4つ先のページURLを取得しています。partition by user_pseudo_id, session_id order by event_timestampにより、同一ユーザーのセッション内でのページ遷移を追跡します。
BigQueryではこの「user_pseudo_id」と呼ばれる、ユーザーの識別Cookieを軸に触れる部分が面白いですね。
グルーピングとカウント
最終的にpage, next_page_1, next_page_2, next_page_3, next_page_4でグルーピングして、各ページ遷移パターンの数を集計します。
出口（exit）の扱い
ifnull(next_page_x, '(exit)')を使って、次のページが存在しない場合は'(exit)'と表示しています。
※ちなみに、私はSQLのプロエンジニアではありません。ある程度理解していますが、
既存コードを細かく解析し、AI等と情報を整理した上で理解・組み直しをおこなっております。ご了承ください。
結果はこちら
キャプチャでは5ページ先までしか取れていませんが、csvでデータを見たところ、MAX10まで取れているデータもちらほらありました。
なぜ、10個先にしたのか？
理由は2つありまして、
- BigQuery叩く容量が大きくなる怖さ
- 矛盾するがあまり先まで見ても参考にしづらい
解説していきます。
BigQuery叩く容量が大きくなる怖さ
BigQueryは従量課金なので、縦横無尽にSQL叩きまくるとあっという間にお金がかかります。
もちろん、データ量等で左右されるのですが…。
テスト的に10個でどんなもんか？の感覚を試したい意図もありました。
矛盾するがあまり先まで見ても参考にしづらい
ページ遷移は見れれば見れる分だけ面白いのですが、いつまでも終わらない遷移を見てもキリがないんですよねｗ
それこそ、離脱までの傾向とCVまでの傾向や数が一定数追えれば十分です。
感じたこと・まとめ
既に似たようなことをやられていたり、もう少し丁寧な仕組みを組んでいる方もいらっしゃると思います。
SQLの条件次第で、本当に色々なデータが整理できそうだなと感激です。
正直、BigQueryを舐めてました。すみません。
あとは、今後課金がしっかり発生するタイミングで、費用対効果といいますか、どこまでお金をかけて価値のあるデータ整理ができるかが重要ですね。
自分で特定のテーブルを組んで、そこにPythonを掛け合わせてクラスター分析を行う、なんて学習も進めておりますが、なかなか難しい。私のリテラシーでは、SEOベースのマーケに活用できるか怪しいのですが、この辺は別途記事にしようと思っております。
▼202411〜：追加でBigQuery記事更新しました。
【BigQuery×GA4】広告アカウント無しで線形アトリビューション分析を行う
