はじめに

Google広告を回していると、「クリック直後の購入」は計測できているのに、「あとから確定した売上」が広告に戻せていない、という悩みが出てきます。

たとえば、こんなケースです。

  • 問い合わせフォームから来た見込み客が、数日後に電話やメールで受注になった
  • ECでカート投入後に離脱した人が、後日あらためて注文した
  • 申し込みは入ったが、その後にキャンセルや返品があり、本当の売上はもっと少なかった

Google広告の自動入札は「コンバージョンの価値」をもとに配信を最適化します。そのため、広告に渡している値が「フォーム送信1件」のままだと、本当は売上ゼロの問い合わせも、100万円の受注も、同じ重みで扱われてしまいます。これでは、入札が「実際に儲かる客」に寄っていきません。

この記事では、後から確定した実売上・受注データを、gclidを起点にBigQueryで突合し、Google広告へオフラインコンバージョン（オフラインCV）として戻す流れを解説します。手作業のCSVアップロードを、できるだけ自動化することがゴールです。

なお、Google広告側のレポートをBigQueryへ取り込む準備がまだの場合は、先に Google広告データをBigQuery Data Transfer Serviceで自動連携する を済ませておくと、この記事の話がつながりやすくなります。

オフラインコンバージョンインポートとは

オフラインコンバージョンインポート（Offline Conversion Import、以下オフラインCV）は、ウェブサイト上では完結しなかったコンバージョンを、あとからGoogle広告に取り込むしくみです。

仕組みの中心になるのが gclid（Google Click Identifier） です。ユーザーがGoogle広告をクリックすると、遷移先URLに gclid というパラメータが付きます。この値が「どのクリック由来の訪問か」を示す目印になります。

おおまかな流れは、次のとおりです。

  1. 広告クリック時に付いた gclid を、サイト側で取得して保存する
  2. その人が問い合わせ・購入をしたら、gclid と一緒に受注情報を記録する
  3. 後日、受注が確定（または金額が確定）したら、gclid と確定金額をGoogle広告に送る
  4. Google広告が gclid を手がかりに「どのクリックの成果か」を紐づける

ポイントは、広告クリックとコンバージョンを結ぶキーが gclid だということです。つまり、gclid を取りこぼすと、その成果は広告に戻せません。ここが運用の生命線になります。

gclid のほかにも、メールアドレスや電話番号をハッシュ化して送る「拡張コンバージョン（リード用）」のような方式もあります。どの方式が使えるか、必須項目は何かは変わりやすいため、実装前に最新の公式ドキュメントで確認してください。

gclid × 受注をBigQueryで突合する

ここからは、データがBigQueryに揃っている前提で、突合の考え方を整理します。

用意するテーブルは、ざっくり2つです。

  • クリック／問い合わせログ: gclid と、フォーム送信や会員登録などのイベントを記録したテーブル
  • 受注テーブル: 受注ID、確定金額、確定日時、ステータスなどを持つ業務データ

この2つを、問い合わせ時に発行した自社のID（リードIDや顧客ID）でつなぎ、最終的に「gclid と確定金額の組み合わせ」を作るのが目標です。

まずは、gclid を持つリードと受注を結合してみます。

-- gclidを持つリードと、確定した受注を突合する
SELECT
  l.gclid,
  o.order_id,
  o.confirmed_amount AS conversion_value,
  o.confirmed_at     AS conversion_time
FROM `myproject.crm.leads` AS l
JOIN `myproject.crm.orders` AS o
  ON l.lead_id = o.lead_id
WHERE l.gclid IS NOT NULL
  AND o.status = 'confirmed'
  AND o.confirmed_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)

実務では、1つのリードに複数の受注が紐づいたり、同じ受注が重複して入っていたりします。そのまま送ると二重計上になるため、受注IDで重複を除き、必要なら金額を集計しておきます。

-- 受注IDで重複を除き、アップロード用の最小カラムに整える
WITH dedup AS (
  SELECT
    l.gclid,
    o.order_id,
    o.confirmed_amount,
    o.confirmed_at,
    ROW_NUMBER() OVER (
      PARTITION BY o.order_id
      ORDER BY o.updated_at DESC
    ) AS rn
  FROM `myproject.crm.leads` AS l
  JOIN `myproject.crm.orders` AS o
    ON l.lead_id = o.lead_id
  WHERE l.gclid IS NOT NULL
    AND o.status = 'confirmed'
)
SELECT
  gclid,
  'purchase_offline'                                       AS conversion_name,
  FORMAT_TIMESTAMP('%Y-%m-%d %H:%M:%S%Ez', confirmed_at)  AS conversion_time,
  ROUND(confirmed_amount)                                  AS conversion_value,
  'JPY'                                                    AS currency
FROM dedup
WHERE rn = 1

ここで作っている conversion_name は、Google広告側であらかじめ作成しておくコンバージョンアクションの名前と合わせます。conversion_time の書式や currency の指定方法は仕様変更があり得るので、列名・フォーマットは最新の公式仕様で必ず確認してください。

この「gclid・コンバージョン名・日時・金額」が揃ったテーブルが、アップロードの材料になります。

なお、ここで作った確定売上は、チャネル別の売上帰属の分析にもそのまま使えます。広告に戻すだけでなく分析にも回したい場合は、BigQueryでEC受注データ×GA4データを結合して正確な売上帰属分析をする の考え方も合わせて読むと、データの持ち方を整理しやすくなります。

整形してアップロードを自動化する流れ

突合結果ができたら、それをGoogle広告に送ります。送り方は大きく2系統あります。

方式1: スプレッドシート経由

BigQueryのクエリ結果をGoogleスプレッドシートへ書き出し、Google広告のスケジュール設定でそのシートを定期的に読み込ませる方式です。

  • メリット: コードが少なく、中身を目で確認しやすい
  • デメリット: シートの列の並びや書式に依存しやすく、ズレるとアップロードが失敗する

BigQueryからスプレッドシートへ自動でデータを出す部分は、BigQueryの結果をGoogleスプレッドシートへ自動エクスポートする のような連携と相性が良い部分です。まずはこの方式で「正しく戻せるか」を確かめるのがおすすめです。

方式2: API経由

Google Ads APIを使い、突合結果をプログラムから直接アップロードする方式です。スプレッドシートを介さないぶん、件数が増えても安定しやすく、エラーも拾いやすくなります。

全体の自動化は、たとえば次のような並びになります。

  1. BigQueryのスケジュールドクエリで、毎日「確定したぶんだけ」突合テーブルを更新する
  2. その結果を、API（またはスプレッドシート）でGoogle広告にアップロードする
  3. アップロード結果（成功・失敗・件数）をログとして残す

毎日の更新部分は、BigQueryのスケジュールドクエリでデータマートを毎日更新する で扱った定期実行の考え方がそのまま使えます。

どちらの方式でも、いきなり全件を本番アカウントへ送るのは避けてください。最初は数件から試し、Google広告の管理画面で「取り込めた件数」「エラーの内容」を必ず確認します。テストで挙動を確かめてから、件数を増やしていくのが安全です。

API側の認証方法・必須フィールド・1回あたりの上限件数などは変更されることがあります。実装に入る前に、最新の公式ドキュメントで仕様を確認してください。

注意点

オフラインCVは便利な反面、押さえておくべき注意点があります。

gclid を確実に保持する

くり返しになりますが、gclid が無いと成果は戻せません。フォームのhidden項目に gclid を入れる、購入時にデータベースへ保存する、といった「取りこぼさない設計」が前提になります。CookieやURLパラメータの扱いが変わると取得方法も変わるため、ここは定期的に動作確認をしておくと安心です。

アップロードの期限に注意する

gclid を使ったオフラインCVには、「クリックからアップロードまでに許される期間」の上限があります。確定が遅い商材だと、送ろうとしたときには期限切れ、ということが起こり得ます。この期限は変更される可能性があるため、最新の公式ドキュメントで確認し、確定が遅いぶんはこまめに送る運用にしておきましょう。

同意（コンセント）の扱い

ユーザーデータを広告に送る以上、取得時の同意やプライバシーポリシーの整合が必要です。とくにメールアドレスや電話番号を使う方式では、同意の状態に応じて送るかどうかを切り替える設計が求められます。同意管理の要件は地域や時期で変わるため、こちらも最新の公式情報と自社のポリシーを確認してください。

まとめ

オフラインCVは、「クリック後に確定した本当の成果」を広告に戻すための仕組みです。

この記事で整理した流れは、次のとおりです。

  • gclid を起点に、クリック・問い合わせと受注をひもづける
  • BigQueryで gclid × 確定金額を突合し、重複を除いてアップロード用テーブルを作る
  • スプレッドシートまたはAPIで、定期的にGoogle広告へ送る
  • gclid の保持・アップロード期限・同意の3点に気をつける

ここまで整えると、自動入札が「フォーム1件」ではなく「実際の売上」を見て配信を調整できるようになります。まずは少件数のテスト送信から始めて、確定データが正しく広告へ戻ることを確認するところから進めてみてください。