本記事では、BigQueryにエクスポートしたGA4（Googleアナリティクス4）のデータを用いて、各セッションにおける流入チャネル（参照元/メディア）ごとの総ユーザー数を集計するSQLクエリについて解説をおこなう。各マーケティング施策の効果検証をおこなう上で最も基本的な集計の1つであり、利用頻度が高いSQLクエリとなる。
SQLクエリの概要
まず、各セッションにおける流入チャネル（参照元/メディア）の値は、 event_params 内に格納されており、 source および medium というkeyを指定することで取得することができる。
また、event_params はデータ型が RECORD 、モードが REPEATED に設定されているため、値を取り出すためには UNNEST を使って配列をフラットニングする必要がある。
参考記事
UNNESTでGA4のevent_params内のパラメータを取り出すSQLクエリ
GA4データの複雑な構造に立ち向かう(BigQuery:ARRAY,STRUCT)
そして、総ユーザー数（ユニークユーザー数）の集計は、 user_pseudo_id を重複なしでカウントする。
参考記事
日次のユニークユーザー数を集計するSQLクエリ | GA4QUERY
SQLクエリサンプル
今回のSQLクエリサンプルは、以下のようになる。
SELECT
(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,
COUNT(DISTINCT user_pseudo_id) AS user_count
FROM
`technogram-354804.analytics_291973511.events_intraday_202302*`
GROUP BY
medium,
source
HAVING
medium IS NOT NULL
AND source IS NOT NULL
ORDER BY
user_count DESC
;BigQueryでの集計結果
BigQueryでの集計結果は、以下のようになった。
GA4（Googleアナリティクス4）探索画面での集計結果
GA4での集計結果は、以下のようになった。
SQLを用いたBigQueryでの集計結果と、GA4の探索画面における集計結果は概ね一致する結果となった。
ChatGPT（GPT-4モデル）によるSQL解説
ChatGPTによる解説を紹介するので、学習の参考に役立ててほしい。
このSQLクエリは、指定されたデータセットからトラフィックの媒体（medium）とソース（source）を抽出し、それぞれの組み合わせにおけるユーザー数（user_count）をカウントしています。クエリは以下のステップで構成されています。
1.SELECT句を使って、イベントパラメータ（event_params）からトラフィックの媒体（medium）とソース（source）を抽出します。これらは、UNNEST関数を使って、イベントパラメータの配列を展開し、WHERE句を使って特定のキー（’medium’と’source’）に対応する値を取得しています。
2.また、COUNT関数とDISTINCT句を使って、ユニークなユーザー数（user_count）をカウントします。これにより、各媒体・ソース組み合わせにおける訪問者数を計算できます。
3.GROUP BY句を使って、結果を媒体（medium）とソース（source）でグループ化します。
4.HAVING句を使って、媒体（medium）とソース（source）がNULLでないレコードに限定しています。これにより、不明なトラフィック情報を除外して結果を表示できます。
5.最後に、ORDER BY句を使って、結果をユーザー数（user_count）の降順で並べて表示します。
このクエリを実行することで、ウェブサイトへのトラフィックがどの媒体やソースから来ているのかを把握できます。これは、ウェブサイトの運営者がマーケティング戦略を評価し、効果的な媒体やソースに資源を集中させることに役立ちます。また、この情報を使って、トラフィックの質や量に影響を与える要因を分析し、ウェブサイトのパフォーマンスを最適化することができます。
今回の記事では、各セッションにおける流入チャネル（参照元/メディア）ごとの総ユーザー数を集計するSQLクエリについて解説をおこなった。各マーケティング施策の効果検証をおこなう上で最も基本的な集計方法となるので、地震の集計の際に参考にしてほしい。
