BigQuery

【BigQuery活用】AI経由のCV貢献を「後日CV」まで追跡する設計とハマりポイント

2026年5月15日

前々編で記事マスタをBigQueryに連携する仕組みを作り、前編で「AIに引用されている記事の特定とLLMO最適化」における1つの案を出した。

この記事はその第3弾になる。

GA4標準のCVR計測では、AI経由ユーザーの本当の貢献度が見えない。理由はAI経由で初めてサイトに接触し、その日は買わずに帰った人が、後日指名検索で戻ってきてCVした場合、その「後日CV」はAI経由として記録されない。

そこで今回はBigQueryで user_pseudo_id ベースの横断追跡を組んで、★様のサイトで実データを取ってみた。結果として「AI経由のCV貢献はゼロ」という、残念な結果となったが、設計から検証までの一連の流れを共有する。

この記事で分かること

なぜGA4標準のCVR計測ではAI経由の貢献を過小評価してしまうのか
ユーザー単位でAI接触からCVまでを追跡するSQL設計
後日CV時のソースまで分類する集計ロジック
実行結果が「異常値」っぽいときの検証フロー
実データで見えた現実と、その解釈

なぜGA4のCVR計測では足りないのか

GA4の標準レポートで「セッションソース別CVR」を見ると、AI経由のCVRが極端に低く出ることがある。

これはAIの責任ではなく、計測仕様の制約だ。GA4は基本的にセッション単位でCVを紐づける。あるユーザーがChatGPT経由で初訪問してフォームを覗き見だけして離脱し、3週間後に「ブランド名申込」と指名検索で戻ってきてCVした場合、そのCVは「指名検索のCV」としてカウントされ、ChatGPTには1ポイントも入らない。

しかし実態として、そのユーザーをCVに導いたのはChatGPTでの最初の接触だったかもしれない。

この「上流効果」を可視化するためには、ユーザー単位での時系列追跡が必要になる。GA4ではできないが、BigQuery × user_pseudo_id ならできる。

設計のポイント

ユーザーごとに以下の情報を持つテーブルを作る。

そのユーザーが初めてAI経由で来た時のセッション情報（AIソース、ランディングページ、訪問日時）
そのユーザーが最終的にCVしたかどうか
CVした場合、それは「同一セッション」「同日別セッション」「後日」のどれか
後日CVの場合、CVが発生したセッションの参照ソースは何だったか

この4点が揃えば、「ChatGPT経由ユーザーの後日CVは、直接アクセスが70%、オーガニック検索が20%、再度AI経由が10%」のような分析が走るようになる。

抽出する項目の全体構造

4ブロックで整理した。

A. ユーザー識別

ai_source（chatgpt.com / perplexity.ai / gemini.google.com / copilot.com）
landing_url（初回AI訪問のランディングページ）

B. CVのタイミング分類

total_cv：AI経由ユーザーの総CV数
cv_same_session：同一セッション内CV
cv_same_day：同日別セッションCV
cv_later_visit：後日CV
user_cvr_pct：ユーザーベースCVR
avg_days_to_cv：CVまでの平均日数

C. 後日CV時のソース内訳

later_cv_direct：直接アクセス
later_cv_organic：オーガニック検索
later_cv_ai_again：再度AI経由
later_cv_paid：広告
later_cv_social：SNS
later_cv_referral：他サイト
later_cv_email：メール
later_cv_other：その他

D. 流入規模

ai_users：AI経由で獲得したユニークユーザー数

SQL実装

WITH

ai_users AS (

  SELECT

    user_pseudo_id,

    CASE

      WHEN LOWER(traffic_source.source) LIKE '%perplexity%' THEN 'perplexity.ai'

      WHEN LOWER(traffic_source.source) LIKE '%chatgpt%'    THEN 'chatgpt.com'

      WHEN LOWER(traffic_source.source) LIKE '%gemini%'     THEN 'gemini.google.com'

      WHEN LOWER(traffic_source.source) LIKE '%copilot%'    THEN 'copilot.com'

    END AS ai_source,

    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS first_session_id,

    REGEXP_EXTRACT(

      (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location'),

      r'(https?://[^?#]+)'

    ) AS first_landing_url,

    TIMESTAMP_MICROS(event_timestamp) AS first_visit_time

  FROM `sample-project.analytics_sample.events_*`

  WHERE event_name = 'session_start'

    AND _TABLE_SUFFIX BETWEEN '20250101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())

    AND (

      LOWER(traffic_source.source) LIKE '%perplexity%'

      OR LOWER(traffic_source.source) LIKE '%chatgpt%'

      OR LOWER(traffic_source.source) LIKE '%gemini%'

      OR LOWER(traffic_source.source) LIKE '%copilot%'

    )

  QUALIFY ROW_NUMBER() OVER (PARTITION BY user_pseudo_id ORDER BY event_timestamp ASC) = 1

),

all_conversions AS (

  SELECT

    user_pseudo_id,

    (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS cv_session_id,

    TIMESTAMP_MICROS(event_timestamp) AS conversion_time,

    LOWER(COALESCE(collected_traffic_source.manual_source, '(direct)')) AS cv_session_source,

    LOWER(COALESCE(collected_traffic_source.manual_medium, '(none)')) AS cv_session_medium

  FROM `sample-project.analytics_sample.events_*`

  WHERE event_name = 'sample_cv_event'

    AND _TABLE_SUFFIX BETWEEN '20250101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())

),

ai_user_cv AS (

  SELECT

    u.user_pseudo_id,

    u.ai_source,

    u.first_landing_url,

    u.first_session_id,

    u.first_visit_time,

    c.cv_session_id,

    c.conversion_time,

    -- CV未発生ならNULLを返す（ここのガードが超重要）

    CASE

      WHEN c.conversion_time IS NULL THEN NULL

      WHEN c.cv_session_id = u.first_session_id THEN 'same_session'

      WHEN DATE(c.conversion_time) = DATE(u.first_visit_time) THEN 'same_day'

      ELSE 'later_visit'

    END AS cv_timing,

    CASE

      WHEN c.cv_session_source IS NULL THEN NULL

      WHEN c.cv_session_source LIKE '%perplexity%'

        OR c.cv_session_source LIKE '%chatgpt%'

        OR c.cv_session_source LIKE '%gemini%'

        OR c.cv_session_source LIKE '%copilot%' THEN 'ai_again'

      WHEN c.cv_session_source IN ('(direct)', 'direct') THEN 'direct'

      WHEN c.cv_session_medium = 'organic' THEN 'organic'

      WHEN c.cv_session_medium IN ('cpc', 'ppc', 'paid') THEN 'paid'

      WHEN c.cv_session_medium IN ('social', 'sns') THEN 'social'

      WHEN c.cv_session_medium = 'referral' THEN 'referral'

      WHEN c.cv_session_medium = 'email' THEN 'email'

      ELSE 'other'

    END AS cv_source_category,

    TIMESTAMP_DIFF(c.conversion_time, u.first_visit_time, DAY) AS days_to_cv

  FROM ai_users u

  LEFT JOIN all_conversions c

    ON u.user_pseudo_id = c.user_pseudo_id

    AND c.conversion_time >= u.first_visit_time

  QUALIFY ROW_NUMBER() OVER (PARTITION BY u.user_pseudo_id ORDER BY c.conversion_time ASC NULLS LAST) = 1

)

SELECT

  ai_source,

  first_landing_url AS landing_url,

  COUNT(DISTINCT user_pseudo_id) AS ai_users,

  SUM(IF(cv_timing IS NOT NULL, 1, 0)) AS total_cv,

  ROUND(SUM(IF(cv_timing IS NOT NULL, 1, 0)) / COUNT(DISTINCT user_pseudo_id) * 100, 2) AS user_cvr_pct,

  SUM(IF(cv_timing = 'same_session', 1, 0)) AS cv_same_session,

  SUM(IF(cv_timing = 'same_day', 1, 0)) AS cv_same_day,

  SUM(IF(cv_timing = 'later_visit', 1, 0)) AS cv_later_visit,

  SUM(IF(cv_timing = 'later_visit' AND cv_source_category = 'direct', 1, 0)) AS later_cv_direct,

  SUM(IF(cv_timing = 'later_visit' AND cv_source_category = 'organic', 1, 0)) AS later_cv_organic,

  SUM(IF(cv_timing = 'later_visit' AND cv_source_category = 'ai_again', 1, 0)) AS later_cv_ai_again,

  SUM(IF(cv_timing = 'later_visit' AND cv_source_category = 'paid', 1, 0)) AS later_cv_paid,

  SUM(IF(cv_timing = 'later_visit' AND cv_source_category = 'social', 1, 0)) AS later_cv_social,

  SUM(IF(cv_timing = 'later_visit' AND cv_source_category = 'referral', 1, 0)) AS later_cv_referral,

  SUM(IF(cv_timing = 'later_visit' AND cv_source_category = 'email', 1, 0)) AS later_cv_email,

  SUM(IF(cv_timing = 'later_visit' AND cv_source_category = 'other', 1, 0)) AS later_cv_other,

  ROUND(AVG(IF(cv_timing IS NOT NULL, days_to_cv, NULL)), 1) AS avg_days_to_cv,

  MAX(IF(cv_timing = 'later_visit', days_to_cv, NULL)) AS max_days_to_cv

FROM ai_user_cv

GROUP BY 1, 2

ORDER BY ai_users DESC;

実行してハマったポイント：NULLハンドリングのバグ

最初に書いたSQLでは、CV分類のCASE文にNULLハンドリングがなかった。これが原因で「全AI経由ユーザーがCVしている、全部later_visitのotherソース」というあり得ない結果が出てしまった。

LEFT JOINで「CVしていないユーザー」も結果に含まれるが、そのとき c.conversion_time はNULLになる。ところがNULLハンドリングを書いていないと、CASEはどの条件にもマッチせずELSE節に落ちる。結果として、CVしていないユーザーが全員「later_visit」「other」に分類されてしまった。

修正は単純で、CASE文の先頭に「conversion_time IS NULL なら NULL」というガード句を入れるだけ。

CASE

  WHEN c.conversion_time IS NULL THEN NULL  -- これがないと全部おかしくなる

  WHEN c.cv_session_id = u.first_session_id THEN 'same_session'

  ...

LEFT JOINの後のCASE文を書くときは、必ずNULLガードを入れる。これを忘れると「100%のCVRが出る」みたいな明らかな異常値で気づけるが、もう少し微妙な集計だと見落とすリスクがある。

結果の正当性を確認する流れ

SQLの修正後、今度は逆に「全AI経由ユーザーがCV 0」という結果になった。これも本当か疑う必要がある。「全部CVあり」と「全部CV無し」、どちらも極端な値だ。

確認のために2段階のクエリを走らせた。

Step 1：CVイベントが期間内に発火しているか

SELECT

  COUNT(*) AS total_cv_events,

  COUNT(DISTINCT user_pseudo_id) AS unique_cv_users

FROM `sample-project.analytics_sample.events_*`

WHERE event_name = 'sample_cv_event'

  AND _TABLE_SUFFIX BETWEEN '20250101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE());

★様の場合、結果は「総CVイベント約500件、ユニークCVユーザー300人超」だった。つまりCVイベント自体は正常に発火している。

Step 2：AI経由ユーザーが「いつかは」CVしているか

タイミング条件を完全に外して、AI経由ユーザーとCVユーザーの重複を直接見るクエリを走らせる。

WITH ai_users AS (

  SELECT DISTINCT user_pseudo_id

  FROM `sample-project.analytics_sample.events_*`

  WHERE event_name = 'session_start'

    AND _TABLE_SUFFIX BETWEEN '20250101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())

    AND (

      LOWER(traffic_source.source) LIKE '%perplexity%'

      OR LOWER(traffic_source.source) LIKE '%chatgpt%'

      OR LOWER(traffic_source.source) LIKE '%gemini%'

      OR LOWER(traffic_source.source) LIKE '%copilot%'

    )

),

cv_users AS (

  SELECT DISTINCT user_pseudo_id

  FROM `sample-project.analytics_sample.events_*`

  WHERE event_name = 'sample_cv_event'

    AND _TABLE_SUFFIX BETWEEN '20250101' AND FORMAT_DATE('%Y%m%d', CURRENT_DATE())

)

SELECT

  (SELECT COUNT(*) FROM ai_users)  AS ai_users_total,

  (SELECT COUNT(*) FROM cv_users)  AS cv_users_total,

  COUNT(DISTINCT a.user_pseudo_id) AS ai_users_who_converted

FROM ai_users a

INNER JOIN cv_users c USING (user_pseudo_id);

このクエリの結果が3パターンに分かれる。

パターン	ai_users_who_converted	意味
A	0	AI経由ユーザーは本当に1人もCVしていない
B	数件	元SQLのタイミング判定で漏れている。AI訪問前のCVを拾う必要
C	一定数	元SQLの結合ロジックに問題があるので再調査

★様のケースでは結果はパターンA、つまり「ai_users_who_converted = 0」だった。

実データで見えた現実

整理すると、データから以下が判明した。

AI経由で初回流入したユニークユーザーは約60人
期間内のCVユーザーは300人超
この2つのユーザー集合の重複はゼロ

要するに「cookieベースで追跡できる範囲では、AI経由はCVに1件も繋がっていない」ということ。

これはBigQueryの集計バグでも、SQLの不具合でもない。GA4の標準レポートでも同じ結果が出るはずだ。違いは、BigQueryで確認したことで「これは集計の問題ではなく、真にゼロだ」と確証が持てたことにある。

この結果をどう解釈すべきか

「AI経由は無意味だ」と結論づけるのは早い。理由は3つある。

1. AI流入の絶対量がまだ小さい

60人という数は、2ヶ月強の累計でこの規模だ。月次に均すと月30人前後。CVRを語る前に、流入量自体を増やす必要がある。

2. cookie追跡では本質的にAIの貢献が捕捉できない

AIで初めて自社を知ったユーザーが、

スマホでChatGPT → PCで指名検索してCV
AIで読んだ後、cookieを消してから来訪してCV
AIで知って、1ヶ月後に紙のチラシを見てから申込

このパターンは全てcookie追跡では切れる。「AI経由が直接CVに繋がらない」のは想定範囲内とも言える。

3. AIは「上流効果」を持つ可能性が残る

直接CVは生まなくても、

ブランド認知を作る
検討材料として記事を読ませる
指名検索を増やす

という間接効果はあり得る。これを測るには別の指標が必要になる。

次の打ち手の候補と仮説を立てる

A. AI経由ユーザーのファネル進行状況を見る

CVには至らなくても、フォーム到達（form_start）はしているのかを確認する。検討フェーズの深度が分かれば、「AI経由は無意味」と「AI経由は検討中」の判別ができる。

B. CVユーザーの流入元分布を分析する

300人超のCVユーザーがどこから来たのかを集計し、指名検索が増えているか観察する。AI経由流入の月次増加と指名検索CVの月次推移に相関があれば、間接効果になる？

ただし、AI検索経由の流入数はすべて計測できるわけではなく、 (direct) / (none) になることが多いため正確な計測は現状不可能である。AI検索経由の流入数推移と (direct) / (none) の流入数推移の比較・検証については、継続的な検証が必要になる。

C. cookie追跡以外の測定手段を追加する

申込フォームに「どこで知りましたか？」というアンケート項目を追加し、AI経由認知を直接ヒアリングする。または、AI経由ユーザー専用URLパラメータでブランド検索LPに誘導するなど、cookie切れに強い計測設計を組む。

D. AI流入量自体を増やすフェーズに切り替える

「測定できないほど少ない」のなら、CV議論より先に流入量を増やすべき、という判断。LLMO最適化に投資を振り切る方針も合理的。

まとめ

今回の検証で見えたのは、AI経由流入の「直接CV貢献」はcookieベース測定ではゼロだったということ。これは想像していたよりシビアな結果だが、同時に「BigQueryで踏み込んで確認したからこそ確信を持って言える」結果でもある。

GA4標準レポートだけ見ていたら「サンプル数が少ないから出ない」「集計バグかも」と結論を保留しがちな部分を、BigQueryで根拠を持って「真にゼロだ」と確認できた。これがBigQuery活用の意義だ。

そしてこの結果は、「AIに投資するな」ではなく「直接CV以外の方法でAI効果を測れ」というメッセージとして読むのが正しい。多くの企業がAI流入の効果測定で同じ問題に直面しているはずだ。

補足：実行時の注意点

collected_traffic_source カラムがGA4エクスポートに含まれているか事前に確認すること。古いGA4設定だと存在しない場合がある。

キーイベント名（サンプルSQL中の sample_cv_event）は実環境のイベント名に置換する。日本語イベント名でもそのまま指定できる。

期間設定は、後日CV分析の性質上、長めの期間を取ったほうが結果が見えやすい。1ヶ月だけだとサンプル数が少なすぎて傾向が出ない。

QUALIFY 句が使えないBigQuery環境では、ROW_NUMBER をサブクエリでフィルタする形に書き換える必要がある。