「この広告グループから何件成約したか」を正確に把握するのは意外と難しい。Google Adsのコンバージョンタグだけでは「フォーム送信」までしか追えず、その先のカウンセリング→成約は別システム（CRM）に記録されるため、データが分断される。

特に、問い合わせチャネルがフォームと電話の両方ある業態では、データの統合がさらに困難になる。

この記事では、GTM・n8n・BigQuery・コールトラッキングを組み合わせて、広告クリックから成約までを一気通貫で追跡できる計測基盤を構築した方法を紹介する。

全体アーキテクチャ

広告クリック（gclid/UTM付き）
    ↓
[GTM] パラメータ取得 → Cookie/localStorage保存
    ↓
[Jotform] Hidden Fieldに自動注入 → BQ（フォーム経由）
[コールトラッキング] URLパラメータ記録 → BQ（電話経由）
    ↓
[BigQuery] 問い合わせテーブル（フォーム + 電話をUNION）
    ↓ 電話番号でJOIN
[CRM] カウンセリング・成約データ
    ↓
[BQビュー] ファネル集計（キャンペーン×広告グループ別）

ポイントはフォームと電話の両チャネルを統一フォーマットでBQに集約し、CRMデータと電話番号で突合するところ。

Step 1: GTMでパラメータを取得・保存する

GTMに3つのカスタムJavaScriptタグを追加した。

タグ1: UUID生成
各訪問者にユニークIDを付与し、Cookie（2年）とlocalStorageに保存する。これにより、フォーム送信時に「どの訪問者の問い合わせか」を一意に特定できる。

タグ2: Click ID & UTMパラメータ取得
URLから gclid、fbclid、yclid、utm_source、utm_medium、utm_campaign 等を抽出し、Cookie（90日）に保存。Google Adsの自動タグ（gclid）だけでなく、MetaやYahooのクリックIDにも対応した。

タグ3: Jotform Hidden Field自動注入
ページ内のJotform iframeを検出し、保存済みのパラメータをiframeのURLにクエリパラメータとして注入する。MutationObserverで動的に追加されるiframeにも対応。

3タグすべてを「DOM Ready - All Pages」トリガーで発火させる。

Step 2: フォームにHidden Fieldを追加

Jotform側では、以下のHidden Fieldを追加しておく:

• lead_uuid
• gclid
• utm_source / utm_medium / utm_campaign / utm_content / utm_term
• ca（キャンペーンID）/ gr（広告グループID）/ cr（クリエイティブID）/ kw（キーワード）

GTMタグ3がこれらのフィールドにパラメータを自動で埋めるため、ユーザーの入力は不要。フォーム送信時に広告の帰属情報がそのまま送信される。

Step 3: コールトラッキングの広告帰属

電話経由の問い合わせには、コールトラッキングサービスを利用する。訪問者がサイトに来た際のURLがそのまま記録されるため、そこからUTMパラメータを抽出できる。

n8nで日次バッチを組み、コールトラッキングAPIからデータを取得→BigQueryに書き込む。

ハマりポイント: n8nのJavaScriptサンドボックス

n8nのCode nodeではJavaScriptを実行できるが、サンドボックス環境では URL.searchParams.get() が動作しないという罠がある。new URL() コンストラクタは通るし、origin や pathname も取得できるのに、searchParams だけがnullを返す。

回避策として、手動でクエリパラメータを解析する関数を書いた:

function getParam(urlStr, paramName) {
  if (!urlStr) return null
  const qIdx = urlStr.indexOf('?')
  if (qIdx === -1) return null
  const query = urlStr.substring(qIdx + 1)
  const pairs = query.split('&')
  for (const pair of pairs) {
    const eqIdx = pair.indexOf('=')
    if (eqIdx === -1) continue
    const key = pair.substring(0, eqIdx)
    if (key === paramName) {
      return decodeURIComponent(pair.substring(eqIdx + 1))
    }
  }
  return null
}

これはn8nの既知の挙動のようだが、公式ドキュメントには明記されていない。URLパラメータの解析が必要な場合は最初からこの方式で書くことを推奨する。

Step 4: BigQueryでファネルビューを構築

フォームと電話のデータを UNION ALL で統合し、CRMデータと LEFT JOIN する。

CREATE OR REPLACE VIEW funnel_attribution AS

WITH inquiries AS (
  -- フォーム経由
  SELECT
    'form' AS channel,
    submission_id AS inquiry_id,
    created_at AS inquiry_at,
    phone,
    COALESCE(ca, utm_campaign) AS campaign_id,
    COALESCE(gr, utm_content) AS ad_group_id,
    kw AS keyword
  FROM jotform_submissions

  UNION ALL

  -- 電話経由
  SELECT
    'call' AS channel,
    call_id AS inquiry_id,
    called_at AS inquiry_at,
    caller_phone AS phone,
    campaign_id AS campaign_id,
    ad_group_id AS ad_group_id,
    keyword
  FROM call_logs
  WHERE call_duration >= 30
),

crm_latest AS (
  SELECT phone, counseling_date, contract_status,
    ROW_NUMBER() OVER (
      PARTITION BY phone ORDER BY created_at DESC
    ) AS rn
  FROM crm_records
  WHERE phone IS NOT NULL
)

SELECT
  i.*,
  c.counseling_date,
  c.contract_status,
  CASE
    WHEN c.contract_status = '成約' THEN 'contracted'
    WHEN c.counseling_date IS NOT NULL THEN 'counseled'
    WHEN c.phone IS NOT NULL THEN 'matched'
    ELSE 'inquiry_only'
  END AS funnel_stage
FROM inquiries i
LEFT JOIN crm_latest c
  ON i.phone = c.phone AND c.rn = 1

ポイント:

• COALESCE(ca, utm_campaign): Google Adsのカスタムパラメータ（ca）があればそちらを優先し、なければUTMにフォールバック
• call_duration >= 30: 30秒未満の通話（間違い電話等）を除外
• ROW_NUMBER()でCRMの最新レコードのみ取得: 同一人物の複数回問い合わせに対応

Step 5: 週次レポート用サマリービュー

代理店との週次ミーティング用に、キャンペーン×広告グループ別の集計ビューも作成:

SELECT
  campaign_id,
  ad_group_id,
  COUNT(*) AS inquiries,
  COUNTIF(funnel_stage IN ('counseled','contracted')) AS counseled,
  COUNTIF(funnel_stage = 'contracted') AS contracted,
  SAFE_DIVIDE(
    COUNTIF(funnel_stage IN ('counseled','contracted')),
    COUNT(*)
  ) AS counseling_rate
FROM funnel_attribution
WHERE campaign_id IS NOT NULL
GROUP BY campaign_id, ad_group_id

これで「どの広告グループが成約に最も貢献しているか」が一目で分かる。CPAだけでなくCPA×成約率で評価できるようになるのが最大のメリットだ。

構築時にハマったこと

1. BigQueryのストリーミングバッファ

insertAll APIで書き込んだ直後のデータは「ストリーミングバッファ」に入り、UPDATEもDELETEもできない。通常90分程度で解消されるが、データ修復時にこの制約を忘れると意味不明なエラーに悩まされる。対策: 修復が必要な場合はバッファ解消後に実行するか、CREATE OR REPLACE TABLE ... AS SELECT で丸ごと作り直す。

2. n8nのURL.searchParams問題（前述）

これに気づくまでに数日分のデータがNULLで蓄積された。n8nの実行ログでは「成功」と表示されるため、BQ側のデータを直接確認しないと発見できない。パイプライン構築後は必ず末端のデータを目視確認すること。

3. 電話番号の形式統一

今回はフォーム・コールトラッキング・CRMすべてで 09012345678 形式だったため、そのままJOINできた。しかし、ハイフン付き（090-1234-5678）やCRMが国際形式（+819012345678）のケースもあり得る。統一関数を挟んでおくのが安全:

REGEXP_REPLACE(phone, r'[^0-9]', '')

まとめ

この基盤があれば、代理店との週次ミーティングで「このキャンペーンはCPAは低いが成約率も低い」「このキーワードは問い合わせは少ないが成約率が高いので予算を増やすべき」といったデータドリブンな議論ができるようになる。

構築コストはn8nとBQで月額ほぼゼロ（BQの無料枠内）。GTMとJotformも無料。ノーコード×BQの組み合わせは、フォーム+電話の2チャネルで問い合わせを受けている業態にとって、最もコスパの良い計測基盤だと感じた。