BigQueryでGA4参照元別集計の罠を回避する

分析屋

2023年9月27日 18:30

こんにちは。
分析屋の千葉です。

GA4のデータをBigqueryで処理する際、セッションの参照元/メディアのUU、SS、PVを出したい場面があると思います。
単純な作業に思っておりましたが、詰まるポイントがあったため紹介いたします。

1.誤ったデータ抽出の例

GA4のBigQuery Export スキーマには以下のような記述があり、
traffic_sourceの中に参照元情報が詰まっている、と解説されています。

引用元：https://support.google.com/analytics/answer/7029846?hl=ja

試しにbigqueryのサンプルデータセットを用いてクエリを作成します。
そのまま解釈するとクエリを作成できそうに思います。

サンプルSQL

/*テーブルからインプットする*/
with input_table as (
    select
        user_pseudo_id,
        event_name,
        (select value.string_value from unnest(event_params) where key ='page_location') as page_location,
        (select value.int_value from unnest(event_params) where key ='ga_session_id') as ga_session_id,
        (select value.string_value from unnest(event_params) where key = 'medium') as medium,
        (select value.string_value from unnest(event_params) where key = 'source') as source
    from
        bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131
)
/*集計*/
select
    medium,
    source,
    count(distinct user_pseudo_id) as uu,
    count(distinct ga_session_id) as ss,
    sum(case when event_name = 'page_view' then 1 end) as pv
from
    input_table
group by
    medium,
    source
order by
    uu desc

結果

この結果は誤りです。
サンプルテーブルとデモアカウントの探索レポートではこの結果で正誤が確認できませんが、皆様のbigqueryとga4の画面でご確認頂くと以下の点が異なっているのではないでしょうか。

・mediumとsourceがnullの、ダイレクト流入の数字がかなり多い
・各流入の数字がかなり少ない

サンプルテーブルのご紹介：

デモアカウントのご紹介：

2.テーブルを見てみる

先程の集計のデータを確認します。
サブクエリ、input_tableを改変して以下を実行します。

SQL
    select
        user_pseudo_id,
        event_timestamp,
        event_name,
        (select value.string_value from unnest(event_params) where key ='page_location') as page_location,
        (select value.int_value from unnest(event_params) where key ='ga_session_id') as ga_session_id,
        (select value.string_value from unnest(event_params) where key = 'medium') as medium,
        (select value.string_value from unnest(event_params) where key = 'source') as source
    from
        bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131
    order by
        user_pseudo_id,
        event_timestamp

結果

以上のようにmedium、sourceがnullのレコードが多数存在し、event_nameがsession_startでもnullになっております。
またsession_startではmediumとsourceがnullになります。
これは閲覧にmedium,sourceが紐づいているためで、サンプルテーブル以外の他テーブルでも同様でした。
これらが合わさって、先程のクエリではUU、SS、PVが算出できておりませんでした。

大切なのは、mediumとsourceはsession_startとfirst_sessionイベントには付与されない点です。

3.ある程度正しいクエリ

page_viewなどのイベントの情報をsession_startなどに振り分ける必要があるため、
以下のように結合を繰り返すSQLを作成することになります。

SQL
/*テーブルからインプットする*/
with input_table as (
    select
        user_pseudo_id,
        event_timestamp,
        event_name,
        (select value.string_value from unnest(event_params) where key ='page_location') as page_location,
        (select value.int_value from unnest(event_params) where key ='ga_session_id') as ga_session_id,
        (select value.string_value from unnest(event_params) where key = 'medium') as medium,
        (select value.string_value from unnest(event_params) where key = 'source') as source
    from
        bigquery-public-data.ga4_obfuscated_sample_ecommerce.events_20210131
),
/*user_pseudo_idとga_session_idに対して振り分ける*/
medium_source_table as (
    select
        user_pseudo_id,
        ga_session_id,
        max(medium) as medium,
        max(source) as source
    from
        input_table
    group by
        user_pseudo_id,
        ga_session_id
),
/*event_name自体のサブクエリを作る*/
event_table as(
    select
        user_pseudo_id,
        ga_session_id,
        event_name,
    from
        input_table
)
/*集計*/
select
    medium_source_table.medium,
    medium_source_table.source,
    count(distinct event_table.user_pseudo_id) as uu,
    count(distinct event_table.ga_session_id) as ss,
    sum(case when event_name = 'page_view' then 1 end) as pv
from
    event_table
    left join medium_source_table on event_table.user_pseudo_id = medium_source_table.user_pseudo_id and event_table.ga_session_id = medium_source_table.ga_session_id
group by
    medium_source_table.medium,
    medium_source_table.source
order by
    uu desc

結果

以上のクエリの結果はga4とかなり近い数字になります。

実際にはga4の画面上のしきい値の関係で一致しない数字になり、完全に一致はしません。
また、サイトの実装によりクエリの書き方を変えることになります。

ここまでお読みいただき、ありがとうございました！
この記事が少しでも参考になりましたら「スキ」を押していただけると幸いです！

株式会社分析屋について

弊社が作成を行いました分析レポートを、鎌倉市観光協会様HPに掲載いただきました。

ホームページはこちら。

noteでの会社紹介記事はこちら。

【データ分析で日本を豊かに】
分析屋はシステム分野・ライフサイエンス分野・マーケティング分野の知見を生かし、多種多様な分野の企業様のデータ分析のご支援をさせていただいております。「あなたの問題解決をする」をモットーに、お客様の抱える課題にあわせた解析・分析手法を用いて、問題解決へのお手伝いをいたします！
【マーケティング】
マーケティング戦略上の目的に向けて、各種のデータ統合及び加工ならびにPDCAサイクル運用全般を支援や高度なデータ分析技術により複雑な課題解決に向けての分析サービスを提供いたします。
【システム】
アプリケーション開発やデータベース構築、WEBサイト構築、運用保守業務などお客様の問題やご要望に沿ってご支援いたします。
【ライフサイエンス】
機械学習や各種アルゴリズムなどの解析アルゴリズム開発サービスを提供いたします。過去には医療系のバイタルデータを扱った解析が主でしたが、今後はそれらで培った経験・技術を工業など他の分野の企業様の問題解決にも役立てていく方針です。
【SES】
SESサービスも行っております。

この記事が参加している募集

やってみた

35,558件