GA4×BigQueryで流入元(参照元・メディア)別のコンバージョンを集計する方法
「どの流入元から来た人が、お問い合わせにつながっているか」は、広告やSEOの予算を考えるうえで欠かせない数字です。GA4×BigQueryの分析でも、最もよく作られる集計の1つです。
ただし、GA4のエクスポートデータには流入元の情報が複数の場所に入っていて、どれを使うかで数字が変わります。この記事では、セッションの流入元ごとのセッション数・コンバージョン数・CVRを集計するSQLと、流入元の列の使い分けを解説します。
参考記事
GA4×BigQueryのevent_paramsをUNNESTで取り出す方法【初心者向けSQL】
GA4×BigQueryでユーザー数・セッション数を集計する方法【GA4の画面と合わない理由も解説】
SQL WITH句(CTE)の使い方【長いクエリを読みやすく組み立てる】
※この記事の実行結果は、GA4のエクスポートと同じ構造で作ったサンプルデータ(架空のサイト、2026年3月1日〜7日)で集計したものです。コンバージョンは、お問い合わせ完了の generate_lead イベントとしています。
流入元の情報はどこに入っているか
| 場所 | 内容 | 使いどころ |
|---|---|---|
traffic_source |
そのユーザーが最初にサイトに来たときの流入元 | 新規ユーザーの獲得元の分析 |
event_params の source・medium・campaign |
そのイベントが起きたときの流入元(セッションの最初のイベントなどに付く) | セッションの流入元の分析 |
collected_traffic_source |
イベントと一緒に集められたURLのパラメータ(utm_source など)の値 | 広告のパラメータの確認 |
session_traffic_source_last_click |
GA4の画面の「セッションの参照元 / メディア」に近い、セッション単位の流入元 | GA4の画面に近い数字を出したいとき(2024年以降のデータ) |
ここで特に注意したいのが traffic_source です。名前から「そのイベントの流入元」だと思われがちですが、実際にはユーザーが最初に来たときの流入元で、2回目以降の訪問でも同じ値のままです。セッションごとの流入元を集計するときに使うと、数字がずれてしまいます。
この記事では、event_params の source・medium からセッションの流入元を作る方法を紹介します。
セッションの流入元を作って、コンバージョンを数える
考え方は次のとおりです。
- イベントから、
user_pseudo_id・ga_session_id・source・mediumを取り出す - セッションごとにまとめて、流入元(source・medium)と、コンバージョンがあったかどうかを1行にする
- 流入元ごとに、セッション数とコンバージョン数を集計する
WITH ev AS (
SELECT
user_pseudo_id
, event_name
, (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 = 'source') AS source
, (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'medium') AS medium
FROM
`プロジェクトID.analytics_123456789.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
),
sessions AS (
SELECT
user_pseudo_id
, ga_session_id
, IFNULL(MAX(source), '(direct)') AS source
, IFNULL(MAX(medium), '(none)') AS medium
, MAX(IF(event_name = 'generate_lead', 1, 0)) AS converted
FROM
ev
GROUP BY
user_pseudo_id
, ga_session_id
)
SELECT
source
, medium
, COUNT(*) AS sessions
, SUM(converted) AS conversions
, ROUND(SUM(converted) / COUNT(*) * 100, 2) AS cvr
FROM
sessions
GROUP BY
source
, medium
ORDER BY
sessions DESC
実行結果
| source | medium | sessions | conversions | cvr |
|---|---|---|---|---|
| organic | 217 | 5 | 2.3 | |
| (direct) | (none) | 128 | 5 | 3.91 |
| cpc | 75 | 3 | 4 | |
| social | 64 | 1 | 1.56 | |
| yahoo | organic | 49 | 0 | 0 |
| newsletter | 45 | 1 | 2.22 |
ポイントは2つ目の sessions です。
MAX(source):セッションの中で source が入っているイベントの値を取り出します(ほかのイベントはNULLなので、MAXで1つにまとまります)IFNULL(…, '(direct)'):source が1つも入っていないセッションは、直接の訪問(ブックマークやURLの直接入力など)とみなしますMAX(IF(event_name = 'generate_lead', 1, 0)):セッションの中に1回でもコンバージョンがあれば1にします。こうすると、1セッションで2回送信しても、コンバージョンは1回と数えられます
この結果から、自然検索(google / organic)はセッション数が最も多い一方、CVRは有料検索(google / cpc、4.0%)のほうが高いことがわかります。
※サンプルデータのため、コンバージョンの件数は少なくなっています。実際の分析では、件数が少ない流入元のCVRは、たまたまの影響を大きく受けるので注意しましょう。
独自のチャネル分類にまとめる
参照元・メディアは種類が多くなりがちなので、CASE式でチャネルにまとめると、全体像がつかみやすくなります。上のSQLの最後のSELECTを、次のように書き換えます。
SELECT
CASE
WHEN source = '(direct)' AND medium IN ('(none)', '(not set)') THEN 'Direct'
WHEN medium = 'organic' THEN 'Organic Search'
WHEN REGEXP_CONTAINS(medium, r'^(cpc|ppc|paid)') THEN 'Paid Search'
WHEN medium = 'social' THEN 'Social'
WHEN medium = 'email' THEN 'Email'
ELSE 'Other'
END AS channel
, COUNT(*) AS sessions
, SUM(converted) AS conversions
, ROUND(SUM(converted) / COUNT(*) * 100, 2) AS cvr
FROM
sessions
GROUP BY
channel
ORDER BY
sessions DESC
実行結果
| channel | sessions | conversions | cvr |
|---|---|---|---|
| Organic Search | 266 | 5 | 1.88 |
| Direct | 128 | 5 | 3.91 |
| Paid Search | 75 | 3 | 4 |
| Social | 64 | 1 | 1.56 |
| 45 | 1 | 2.22 |
GA4の「デフォルト チャネル グループ」と同じ分け方にしたい場合は、GA4のヘルプにある定義に合わせて条件を増やしていきます。自社の広告の運用に合わせて、「指名検索広告」「一般検索広告」を分けるといった独自の分類にできるのが、BigQueryで集計するメリットです。
キャンペーン別に集計する
広告のURLに付けた utm_campaign の値は、event_params の campaign に入ります。同じ考え方で、キャンペーン別のセッション数とコンバージョン数を出せます。
-- sessions の段階で、campaign も取り出しておく
-- , MAX((SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'campaign')) AS campaign
SELECT
campaign
, COUNT(*) AS sessions
, SUM(converted) AS conversions
FROM
sessions
WHERE
campaign IS NOT NULL
GROUP BY
campaign
ORDER BY
sessions DESC
実行結果
| campaign | sessions | conversions |
|---|---|---|
| spring_sale | 139 | 4 |
| march_news | 45 | 1 |
新規ユーザーの獲得元を集計する
ユーザーが最初に来た流入元を見たい場合は、traffic_source を使います。first_visit イベントで数えると、新規ユーザーの獲得元がわかります。
SELECT
traffic_source.source
, traffic_source.medium
, COUNT(DISTINCT user_pseudo_id) AS new_users
FROM
`プロジェクトID.analytics_123456789.events_*`
WHERE
_TABLE_SUFFIX BETWEEN '20260301' AND '20260307'
AND event_name = 'first_visit'
GROUP BY
1, 2
ORDER BY
new_users DESC
実行結果
| source | medium | new_users |
|---|---|---|
| organic | 156 | |
| (direct) | (none) | 86 |
| cpc | 50 | |
| social | 45 | |
| yahoo | organic | 34 |
| newsletter | 33 |
「セッションの流入元」と「ユーザーの最初の流入元」は、目的に合わせて使い分けましょう。たとえば、メールマガジンは新規ユーザーを連れてくる力は小さくても、既存のユーザーを呼び戻してコンバージョンさせる役割を担っていることがあります。
GA4の画面と数字が合わない理由
この記事の方法で集計した数字は、GA4の画面の「セッションの参照元 / メディア」と完全には一致しません。
- 直接の訪問の扱い:GA4の画面では、直接の訪問(direct)のセッションは、過去の直接以外の流入元を引き継いで表示されることがあります(ラストクリックで、直接の訪問よりもほかの流入元を優先する考え方)。この記事のSQLでは、セッションの中にsourceがなければそのまま(direct)にしています
- セッションが日付をまたぐ場合:日付の範囲の端で、セッションの途中から集計されることがあります
- 推定値・同意モード:GA4の画面の数字には、推定やモデリングが含まれることがあります
GA4の画面に近い数字を出したい場合は、2024年以降のデータであれば、セッション単位の流入元が入った session_traffic_source_last_click の列を使う方法もあります。どの定義で集計しているかをレポートに注記し、社内で使う定義を決めておくことが大切です。
まとめ
traffic_sourceはユーザーが最初に来たときの流入元。セッションの流入元ではない- セッションの流入元は、event_params の source・medium をセッションごとにまとめて作る
- コンバージョンは
MAX(IF(event_name = 'コンバージョンのイベント', 1, 0))で、セッション単位で判定する - CASE式でチャネルにまとめると、自社に合った分類で比較できる
- GA4の画面とは、直接の訪問の扱いなどの違いで数字がずれる
流入元ごとのCVRがわかれば、「どの集客施策が成果につながっているか」を、セッション数だけでなく成果の面から評価できるようになります。
NEWSLETTER
Data Schoolの新着記事をメールで受け取る
新しい記事が公開されたら、メールでお知らせします。配信はいつでも停止できます。
