【GA4×BigQuery】GA4×GSCで「着地ページ×クエリ」の貢献度を可視化するSQL

導入（このSQLで解決できる課題）

SEOや広告運用の現場では、「どの着地ページが、どの流入（SEO/広告）で、どれだけCVに繋がっているか」を一枚で把握したい場面が頻出します。

ところが、GA4は行動・CVに強いが検索クエリは見えにくい、一方でSearch Consoleはクエリ・掲載順位は見えるがCVが見えないという縦割りがボトルネックになり、担当者は毎回それぞれのUIやレポートを往復して解釈せざるを得ません。

分析のたびにスクリーンショットやCSVを寄せ集めるのは、属人化とスピード低下の原因になります。

本SQLは、GA4のセッション粒度で着地（ランディング）を正確に再現しつつ、検索クエリ×URLの露出（Imp）・クリック（Click）・平均掲載順位（Avg Position）をSearch ConsoleからJOINします。

さらに、CV（任意イベント名）を着地ページ単位で集計し、クエリ別クリックのページ内シェアを用いた“CV按分（cv_share_adjusted）”まで自動計算。

これにより、例えば

「/lp/lease/ はモバイル検索でどのクエリが効いて、CVはどの程度を担っているか」

「広告（cpc）とSEO（organic）で着地の役割がどう違うか」

を、単一テーブルで意思決定に使える解像度へ落とし込めます。媒体横断の予算配分・LP改修の優先度付け・タイトル/ディスクリプションの改善ターゲット選定まで一気通貫で議論できるのが、このSQLの最大の価値です。

実行結果イメージ（抜粋）

• click_share：同じページに対するそのクエリのクリック比率（%）

• cv_share_adjusted：click_share × cv_all で、ページ内CVをクエリ比率で按分した推計値（例：どの検索クエリがCVに寄与したかの目安）

背景・利用シーン

• • SEOの勝ち筋特定：着地×クエリでImp/Click/順位とCV（全体/SEO/CPC）を同時に見て、「順位は良いのにCTRが低い＝メタ改善」「CTRは良いがCV弱い＝LP/導線見直し」を即判定。

  • LP改善の優先順位付け：visit_all と cv_all を基準に、流入規模×成果の掛け算で投資インパクトが大きいLPを先に改修。

  • 広告とSEOの役割分担：visit_cpc と visit_seo のバランスで、検索意図に応じた集客チャネルの再配分を議論。

  • 編集会議の材料：avg_position×click_share×cv_share_adjusted で、「狙うべき見出し語」「補強すべき内部リンク」を具体化。

抽出結果からできる議論

• 編集長「“cheap car lease”は掲載順位4.9でCTR高め、なのにCV按分が次点だね。」
  SEO「LPのファーストビューで“安さ訴求”をもう一段強めればCVも伸びそうです。」

• 広告「このLP、CPC流入は多いけどCV率はSEOに劣る。」
  アナリスト「広告キーワードがLPの訴求とズレてる可能性。クエリ別にLP差し替えたい。」

• SEO「“(anonimized)”のクリックシェアが2割超。具体語が見えない。」
  編集「カテゴリ横断の包括的ハブ記事とFAQ拡充で、隠れ需要を拾いましょう。」

• PdM「順位は良いのにCTRが伸びないクエリが複数ある。」
  UX「モバイルSERPでの視認性（タイトル前方の価値訴求・全角/半角記号）をABで最適化。」

• 経営「予算配分の判断材料は？」
  マーケ「cv_share_adjusted 上位クエリに合わせ、SEOは記事強化、広告は同義語キーワードへ拡張します。」

SQLコード

-- 期間・CVイベント名をパラメータ化
WITH
params AS (
  SELECT
    '2024-07-01' AS startDate,
    '2024-08-20' AS endDate,
    'purchase'   AS eventNameComplete      -- ★CVイベント（例：purchase / generate_lead など）
),

-- ======================
-- GA4：着地ページ×流入×CV を集計
-- ======================
ga4 AS (
  SELECT
    -- 着地URLは「クエリパラメータ除去＋小文字化」で正規化
    REGEXP_EXTRACT(LOWER(landing_page_location), r'^([^\?]+)') AS landing_page_location,

    COUNT(DISTINCT ssid) AS visit_all,
    COUNT(DISTINCT CASE WHEN landing_medium = 'organic' THEN ssid END) AS visit_seo,
    COUNT(DISTINCT CASE WHEN landing_medium = 'cpc'     THEN ssid END) AS visit_cpc,

    COUNT(DISTINCT CASE WHEN event_name = (SELECT eventNameComplete FROM params) THEN ssid END) AS cv_all,
    COUNT(DISTINCT CASE WHEN event_name = (SELECT eventNameComplete FROM params) AND landing_medium = 'organic' THEN ssid END) AS cv_seo,
    COUNT(DISTINCT CASE WHEN event_name = (SELECT eventNameComplete FROM params) AND landing_medium = 'cpc'     THEN ssid END) AS cv_cpc
  FROM (
    -- セッション内の「最初のpage_location/流入」を着地として採用
    SELECT
      user_pseudo_id,
      LOWER(FIRST_VALUE(page_location IGNORE NULLS)
        OVER(PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp)) AS landing_page_location,
      FIRST_VALUE(source IGNORE NULLS)
        OVER(PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp) AS landing_source,
      FIRST_VALUE(medium IGNORE NULLS)
        OVER(PARTITION BY user_pseudo_id, ga_session_id ORDER BY event_timestamp) AS landing_medium,
      event_name,
      event_timestamp,
      CONCAT(user_pseudo_id, '-', ga_session_id) AS ssid   -- セッションID（ユーザー×セッションの擬似キー）
    FROM (
      -- GA4生データ（必要列のみ抽出）
      SELECT
        event_date,
        user_pseudo_id,
        REGEXP_EXTRACT(LOWER( (SELECT value.string_value FROM UNNEST(event_params) WHERE key = 'page_location') ), r'^([^\?]+)') AS page_location,
        (SELECT value.int_value FROM UNNEST(event_params) WHERE key = 'ga_session_id') AS ga_session_id,
        -- ★収集時の手動参照元：運用に合わせて traffic_source.* を使うのも可
        collected_traffic_source.manual_source AS source,
        collected_traffic_source.manual_medium AS medium,
        event_name,
        event_timestamp
      FROM `***************.analytics_366268950.events_*`
      WHERE _table_suffix BETWEEN (SELECT REPLACE(startDate, '-', '') FROM params)
                               AND (SELECT REPLACE(endDate,   '-', '') FROM params)
    )
  )
  GROUP BY landing_page_location
),

-- ======================
-- GSC：URL×クエリの露出・クリック・順位
-- ======================
gsc AS (
  SELECT
    REGEXP_EXTRACT(LOWER(url), r'^([^\?]+)') AS landing_page_location,
    IFNULL(query, '(anonimized)') AS query,
    SUM(impressions) AS imp,
    SUM(clicks)      AS click,
    ((SUM(sum_position) / SUM(impressions)) + 1.0) AS avg_position  -- Search Consoleの平均掲載順位
  FROM `**************.searchconsole.searchdata_url_impression`
  WHERE data_date BETWEEN CAST((SELECT startDate FROM params) AS DATE)
                      AND   CAST((SELECT endDate   FROM params) AS DATE)
    AND search_type = 'WEB'
  GROUP BY landing_page_location, query
)

-- ======================
-- 最終結合：着地ページ単位でGSCをJOIN
-- ======================
SELECT
  ga4.landing_page_location,
  gsc.query,
  gsc.imp,
  gsc.click,
  SUM(gsc.click) OVER (PARTITION BY gsc.landing_page_location) AS click_by_page,
  gsc.avg_position,
  ga4.visit_all,
  ga4.visit_seo,
  ga4.visit_cpc,
  ga4.cv_all,
  ga4.cv_seo,
  ga4.cv_cpc,

  -- クエリ別クリックのページ内シェア（%）
  ROUND(gsc.click / NULLIF(SUM(gsc.click) OVER (PARTITION BY gsc.landing_page_location), 0) * 100, 2) AS click_share,

  -- クエリ別CV按分（ページのCVをクリック比で配分した推計）
  ROUND((gsc.click / NULLIF(SUM(gsc.click) OVER (PARTITION BY gsc.landing_page_location), 0)) * ga4.cv_all, 2) AS cv_share_adjusted

FROM ga4
LEFT JOIN gsc USING (landing_page_location)
ORDER BY landing_page_location, click DESC;

確認すべきポイント

• JOINキーの正規化一致

  • GA4 / GSCともに「クエリ除去＋小文字化」で統一。www. の有無やトレーリングスラッシュ差は追加正規化（REGEXP_REPLACE）で吸収推奨。

• チャネル判定のソース

  • 本SQLは collected_traffic_source.manual_medium を採用。UTM運用が未整備なら traffic_source.source/medium へ切替を検討。

• CVイベント名の妥当性

  • params.eventNameComplete に設定したイベントが セッション内で重複カウントされないか（複数回発火）を確認。必要ならセッション単位の最初のCVだけに絞るロジックに変更。

• CV按分（cv_share_adjusted）の性質

  • クリックシェア比例配分＝ヒューリスティック です。厳密なアトリビューション（Shapley値・Markov）は別設計。まずは探索的な優先度付けに使う、という位置づけで。

• 検索タイプ/デバイスのフィルタ

  • 例では search_type='WEB' 固定。モバイル重視なら device='MOBILE' を加え、PC/TABLET比較も検証。

• 期間整合性

  • GA4とGSCの計測タイムゾーンや遅延の差に留意（GSCは1～2日の遅延あり）。期間は十分な幅で比較。

• 重複・クロスドメイン

  • マルチドメイン/サブドメインを運用する場合、ドメイン除去の正規化を追加。

• サンプルサイズ

  • ImpやClickが極端に少ないクエリは、CTRやcv按分が不安定。**閾値（例：Imp≥50, Click≥10）**でフィルタして運用レポートに載せると実務的。

まとめ

• 着地ページ×クエリで、露出（Imp）→クリック（CTR/順位）→セッション（SEO/CPC）→CVを一気通貫で可視化。

• click_share と cv_share_adjusted により、CV寄与が高い検索クエリをスピーディに特定。

• 投資判断（SEO/広告の配分、LP改善の優先度、メタ改善の対象選定）を、属人作業なしで反復できる。