本記事では、BigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、各セッションにおける流入チャネル（参照元/メディア）ごとのセッション数を集計するSQLクエリについて解説をおこなう。各マーケティング施策の効果検証をおこなう上で最も基本的な集計の1つであり、利用頻度が高いSQLクエリとなる。

SQLクエリの概要

まずは、各セッションにおける流入チャネル（参照元/メディア）の値は、 event_params 内に格納されており、 source および medium というkeyに対応する値として格納されている。

また、event_params はデータ型が RECORD 、モードが REPEATED に設定されているため、値を取り出すためには UNNEST を使って配列をフラットニングする必要がある。 event_params 内のパラメータを取り出す方法としてワンライナーで覚えてしまうのが手っ取り早い。

(SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium

参考記事

UNNESTでGA4のevent_params内のパラメータを取り出すSQLクエリ

GA4データの複雑な構造に立ち向かう(BigQuery:ARRAY,STRUCT)

そして、上記で取り出した source 及び medium の組み合わせごとにセッション数をカウントする。CONCAT関数を利用してスラッシュで区切って連結することで、UAやGA4のUI表記に揃えることができる。

SQLクエリサンプル

今回のSQLサンプルクエリは、以下のようになる。

WITH prep AS (
  SELECT
    user_pseudo_id,
    (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
    `technogram-354804.analytics_291973511.events_intraday_202302*`
)

SELECT
  CONCAT(source, ' / ', medium) AS source_medium,
  COUNT(DISTINCT CONCAT(user_pseudo_id, ga_session_id)) AS session_count
FROM
  prep
GROUP BY
  source_medium
HAVING
  source_medium IS NOT NULL
ORDER BY
  session_count DESC
;

BigQueryでの集計結果

BigQueryでの集計結果は、以下のようになった。

GA4（Googleアナリティクス4）探索画面での集計結果

GA4での集計結果は、以下のようになった。

集計結果を見ると、BigQueryとGA4の探索画面で数値に大きな乖離が発生しているため、注意する必要がある。

これは、おそらくBigQueryにエクスポートされたデータにおいて source や medium といった event_params の一部が欠落してしまっている可能性が高い。

こちらに関しては公式なソースが現状では見当たらないため、何かしら情報のアップデートがあったら追記修正を行う予定である。

ChatGPT（GPT-4モデル）によるSQL解説

ChatGPTによる解説を紹介するので、学習に役立ててほしい。

このSQLクエリは、指定されたデータセットからトラフィックのソース（source）と媒体（medium）を抽出し、それぞれの組み合わせにおけるセッション数（session_count）をカウントしています。クエリは以下のステップで構成されています。

1.WITH句を使って、一時テーブルprepを作成します。このテーブルでは、user_pseudo_id（ユーザーの識別子）、ga_session_id（セッションID）、トラフィックの媒体（medium）、ソース（source）を抽出しています。これらの値は、UNNEST関数を使ってイベントパラメータの配列を展開し、WHERE句を使って特定のキー（’ga_session_id’、’medium’、’source’）に対応する値を取得しています。

2.SELECT句で、ソース（source）と媒体（medium）を連結してsource_mediumカラムを作成します。CONCAT関数を使って、ソースと媒体を’ / ‘で区切って表示します。

3.セッション数（session_count）をカウントするために、COUNT関数とDISTINCT句を使って、ユニークなuser_pseudo_idとga_session_idの組み合わせをカウントします。これにより、各ソース・媒体組み合わせにおけるセッション数を計算できます。

4.GROUP BY句を使って、結果をsource_mediumでグループ化します。

5.HAVING句を使って、source_mediumがNULLでないレコードに限定しています。これにより、不明なトラフィック情報を除外して結果を表示できます。

6.最後に、ORDER BY句を使って、結果をセッション数（session_count）の降順で並べて表示します。

このクエリを実行することで、ウェブサイトへのトラフィックがどのソース・媒体の組み合わせから来ているのかを把握できます。これは、ウェブサイトの運営者がマーケティング戦略を評価し、効果的なソース・媒体に資源を集中させることに役立ちます。また、この情報を使って、トラフィックの質や量に影響を与える要因を分析し、ウェブサイトのパフォーマンスを最適化することができます。

今回の記事では、各セッションにおける流入チャネル（参照元/メディア）ごとのセッション数を集計するSQLクエリについて解説をおこなった。各マーケティング施策の効果検証をおこなう上で最も基本的な集計となるので、自身での集計に活用してほしい。