GA4のBigQueryでUA指標を再現する
こんにちは！
分析屋の千葉です。
UAからGA4への完全移行になった中、bigqueryへのエクスポートスキーマを利用し、SQLを組まれている方も多いのではないでしょうか。
UA時代の直帰率とページの価値はわかりやすい指標であったものの、数字を見るだけでもbigqueryでは大変です…。
そこで直帰率とページの価値を求めるクエリを2つ紹介いたします。
1. ページごとの直帰率
直帰率はセッションを開始したにもかかわらず、ページ遷移や何か計測に関するイベントをせずに帰ったセッションの割合です。ファーストビューを重視したページに重用された指標でありましたがなくなってしまったようです。
with ga4_table as(
select
event_params[safe_offset((select index from unnest(event_params) with offset index where key = 'ga_session_id'))].value.string_value as ga_session_id,
event_name
from
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210101`
),
pre_bounce_table as(
select
ga_session_id,
countif(event_name = 'page_view') as pv_by_session
from
ga4_table
group by
ga_session_id
),
bounce_table as(
select
sum(if(pv_by_session = 1,1,0)) as bounce
from
pre_bounce_table
),
ss_table as(
select
count(distinct ga_session_id) as ss
from
ga4_table
)
select
bounce,
ss,
safe_divide(bounce,ss) as bounce_rate
from
ss_table,bounce_table早速かなり長いクエリとなってしまいましたが…。
pre_bounce_tableでga_session_idごとのpv数を集計しています。
bounce_tableにてsum(if(pv_by_session = 1,1,0))で直帰数を集計します。sumの中のifは、ga_session_idに対してpvが1のみ場合、直帰した場合は直帰と判定して1を返します。
その後ss_tableで固有のga_session_idの数を集計し、割り算をしています。
なお上記クエリではssとbounce_rateが0になります。これはデータセット内でga_session_idがnullになっているためです。
サイトのイベント設定によるとは思いますが、全体的にga4の直帰率はUAに比べて低く出る気がしております。
2. ページの価値
ページの価値とは…
そのページを経由した売上(収益) ÷ ユニークのPV数 になります。
ページタイトルごとにページの価値をSQLで再現すると以下になります。
ページの価値はECサイトやECサイトの販促用オウンドメディアを運用している方は多く見た指標だったのではないでしょうか。
こちらのクエリも直帰率と同様に値が0になります。
with revenue_table as(
select
event_params[safe_offset((select index from unnest(event_params) with offset index where key = 'ga_session_id'))].value.string_value as ga_session_id,
sum(ecommerce.purchase_revenue) as revenue
from
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210101`
group by
1
order by
2 desc
),
page_table as (
select distinct
event_params[safe_offset((select index from unnest(event_params) with offset index where key = 'ga_session_id'))].value.string_value as ga_session_id,
event_params[safe_offset((select index from unnest(event_params) with offset index where key = 'page_title'))].value.string_value as page_title
from
`bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210101`
where
event_name = 'page_view'
),
distinct_table as(
select distinct
page_table.page_title,
page_table.ga_session_id,
revenue_table.revenue
from
page_table
left join revenue_table on page_table.ga_session_id = revenue_table.ga_session_id
)
select
page_title,
count(distinct ga_session_id) as unique_pv,
sum(revenue) as revenue,
sum(revenue)/count(distinct ga_session_id) as page_price
from
distinct_table
group by
page_titleまずrevenue_tableでga_sesion_idごとの収益を集計しています。
その後page_tableでga_session_idとpage_titleの一覧を取得し、distinct_tableでpage_title,ga_session_idごとのrevenueを振り分けています。
つまりdistinct_tableでそのページを経由した結果の売上を各ページに振り分けております。
その後集計し、ページの価値を表示しております。
今回はleft joinでga_session_idに収益を振り分けておりますが、ウィンドウ関数を利用しても同様の結果が得られます。
個人的にこちらもUAに比べてページの価値が大きく出る傾向がございます。私も原因を細かく理解できておりませんが、UAに比べて今のブラウザ事情に適応した結果、セッションが外れにくくなったということだと予想しております。
まとめ・所感
GA4に移行したことでマーケター視点では数字の分析がより難しいものになり、エンジニアに似た視点が必要になってしまいました。
さらにGA4とUAでデータ取得の仕組みが違うため、従来とはぶれた数字が出てくることも多いです。
数字を完全に合わせることは多くの場合で難しく、数字は不可解です。
マーケターとしてはデータ構造を理解していくと同時に、
データ抽出担当はよりマーケター的視点で説明していくことが重要になっていきそうです。
ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！
株式会社分析屋について
弊社が作成を行いました分析レポートを、鎌倉市観光協会様HPに掲載いただきました。
ホームページはこちら。
noteでの会社紹介記事はこちら。
【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。 「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。