Web Analytics

GA4×BigQueryで流入元(参照元・メディア)別のコンバージョンを集計する方法

酒井 裕麻株式会社Bistro 代表取締役社長

「どの流入元から来た人が、お問い合わせにつながっているか」は、広告や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 からセッションの流入元を作る方法を紹介します。

セッションの流入元を作って、コンバージョンを数える

考え方は次のとおりです。

  1. イベントから、user_pseudo_id・ga_session_id・source・medium を取り出す
  2. セッションごとにまとめて、流入元(source・medium)と、コンバージョンがあったかどうかを1行にする
  3. 流入元ごとに、セッション数とコンバージョン数を集計する
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
google organic 217 5 2.3
(direct) (none) 128 5 3.91
google cpc 75 3 4
instagram social 64 1 1.56
yahoo organic 49 0 0
newsletter email 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
Email 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
google organic 156
(direct) (none) 86
google cpc 50
instagram social 45
yahoo organic 34
newsletter email 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がわかれば、「どの集客施策が成果につながっているか」を、セッション数だけでなく成果の面から評価できるようになります。

CONTACT

データ活用のお悩み、
直接相談しませんか。

「何から始めればいいかわからない」段階でも大丈夫です。課題の整理からお手伝いします。