スプレッドシートのデータを、毎日自動で BigQuery に取り込む〜外部テーブル＋差分更新クエリの王道パターン〜

こんにちは、広告データインフラ「Roboma（ロボマ）」のスタッフです。

GA4 の流入数、アドエビスのコンバージョン、HubSpot のリード。気づくと、案件ごとに別々のスプレッドシートに手作業で集めている方へ。

そのシートを BigQuery から直接読めたら、AI に「先月の流入経路別に CV まで一気通貫で見せて」と聞くだけで分析できるようになります。

これまで、GA4 のローデータを BigQuery にエクスポートする方法と、Search Console のデータを BigQuery に出力する方法を記事にしてきました。今回はその続きとして、自動連携の口がないデータ、たとえば手元のスプレッドシートにしか無いデータを BigQuery に毎日自動で取り込む方法をご紹介します。

GA4・アドエビス・HubSpot のように、API でまとめて出すのが難しいデータも、いったんスプレッドシートに集めてしまえば BigQuery に蓄積できます。しかも一度設定してしまえば、あとは毎朝、直近分だけを差分更新し続けられます。

なぜ「スプレッドシート → BigQuery」を作るのか

広告データそのものは Roboma 経由で BigQuery に出力できます。でもマーケティング分析の現場で本当に見たいのは、広告の手前と奥にあるデータです。

GA4 のセッション、アドエビスのアトリビューション、HubSpot のリードや商談。この3つが広告データと同じ BigQuery の中に並んで初めて、「どの広告を経由して、サイトでどう動いて、最終的に商談になったか」をひとつのクエリで追えます。

ところが GA4 はともかく、アドエビスや HubSpot のレポートは、案件によって出し方がバラバラです。担当者がスプレッドシートに貼り付けて管理している、というケースは多いと思います。

だったらそのスプレッドシートを、BigQuery のデータソースとしてそのまま使ってしまえば良いと思ったのが今回の発想の起点です。スプレッドシートに新しい行が増えれば、直後には BigQuery のテーブルにも反映されている。そんな状態を目指します。

全体像は4ステップ

やることは大きく4つです。

1. 出力先の BigQuery にデータセットを作る

2. スプレッドシートを「外部テーブル」として定義する

3. 外部テーブルをクレンジングして、日付でパーティションを切った「ネイティブテーブル」を初回構築（1回だけ）

4. 直近分だけを洗い替える差分更新クエリを、毎日のスケジュールに登録する

ここで一番大事なのが、2 の「外部テーブル」と 3 の「ネイティブテーブル」の役割の違いです。先に図で全体像を掴んでおきます。

「外部テーブル」は、データのコピーを持ちません。クエリが走るたびにスプレッドシートを直接読みに行く、ビューのような存在です。だから「スプレッドシート → 外部テーブル」という取り込み処理は存在しません。スプレッドシートを更新すれば、外部テーブルは常に最新を反映します。

一方の「ネイティブテーブル」は、型を整えた実体のあるテーブルです。分析や JOIN はこちらに対して行います。毎日の更新は、このネイティブテーブルの「直近分だけ」を作り直します。全部を毎回作り直さないのがコスト削減のポイントです。

この違いが分かると、後半の差分更新がすっきり理解できます。順番に作っていきましょう。

ステップ1：出力先のデータセットを作る

最初に、テーブルを置く受け皿を用意します。BigQuery コンソールで「データセットを作成」を開いて、次のように設定します。

• データセット ID：import_spreadsheet（分かりやすい名前で可）

• ロケーションは：asia-northeast1（東京）

• テーブルの有効期限：なし

設定で一番気をつけたいのがロケーションです。BigQuery はリージョンが違うデータセット同士を1つのクエリで JOIN できません。

GA4 や Search Console、Roboma の広告データをすでに東京リージョン（asia-northeast1）に蓄積されているなら、このデータセットも必ず同じ asia-northeast1 にそろえてください。ここが違うと、横断分析のたびにデータをコピーすることになります。

リージョンは後から変えられません。最初の一手として、既存データのリージョンを確認してから作成するのが安全です。

ステップ2：スプレッドシートを「外部テーブル」にする

データセットができたら、シートを外部テーブルとして定義します。GA4・アドエビス・HubSpot の3シート分、同じ要領で作ります。以下はGA4（ext_ga4）の例です。

CREATE OR REPLACE EXTERNAL TABLE `PROJECT_ID.import_spreadsheet.ext_ga4`
OPTIONS (
  format = 'GOOGLE_SHEETS',
  uris = ['https://docs.google.com/spreadsheets/d/SHEET_ID'],
  sheet_range = 'ga4!A:K',
  skip_leading_rows = 0
);

uris にスプレッドシートの URL、sheet_range に読みたいシート名と列範囲を指定します。これだけで、シートが BigQuery のテーブルのように見えるようになります。

ここでひとつコツがあります。skip_leading_rows = 0 にして、ヘッダー行ごと全部読む書き方にしています。

理由は、シートのヘッダーに記号や日本語が混ざっていると、列名として扱うときにエラーになりやすいからです。skip_leading_rows = 0 で読むと、列名は自動的に string_field_0、string_field_1 という連番になります。記号ヘッダー問題を避けられて、後の整形も書きやすくなります。ヘッダー行そのものは、次のステップで除外します。

もう一点。この段階では、型を一切指定しません。全部 STRING として素直に読みます。

「日付列は DATE 型で読めばいいのでは」と思うところですが、外部テーブルは読み取りのたびにシートをパースします。20260101 のような形式は BigQuery の DATE として読めず、28,000 のようなカンマ入りの数字を INT64 にしようとすると、その1行でクエリ全体がエラーになってしまいます。マスキングされた「********」や空セルも、型不一致を起こす原因です。

汚れたシートを壊れずに受け止めるには、外部テーブルは STRING で素直に読んで、整形は次のステップに回す。この二段構えにすることでかなり安定するはずです。

ステップ3：クレンジングしてネイティブテーブルを構築する

ここが実務の山場です。外部テーブルの STRING を、分析で使える型とカラム名に整えて、ネイティブテーブルとして焼き付けます。この構築クエリは初回だけ実行します。

なぜクレンジングが要るのか。生のシートの値は、そのままでは集計に使えないからです。具体的には、こうした汚れを綺麗にしていきます。

実際のクエリは以下のようになります。アドエビスのシートを例にしています。注目していただきたいのが、「PARTITION BY」です。日付の列でパーティション（テーブルの内部的な区分け）を切っておきます。これが後の差分更新を軽くする布石になります。

CREATE OR REPLACE TABLE `PROJECT_ID.import_spreadsheet.adebis`
PARTITION BY DATE(cv_time) AS
SELECT
  string_field_0                                           AS cv_name,
  SAFE.PARSE_DATETIME('%Y-%m-%d %H:%M:%S', string_field_1) AS cv_time,
  NULLIF(string_field_3, '')                               AS user_name,
  SAFE_CAST(REPLACE(string_field_4, ',', '') AS INT64)     AS revenue,
  NULLIF(string_field_15, '********')                      AS direct_media_type,
  NULLIF(string_field_19, '********')                      AS direct_ad_name
FROM `PROJECT_ID.import_spreadsheet.ext_adebis`
WHERE string_field_0 != 'CV名';   -- ヘッダー行を除外

クレンジングのテクニック4つを押さえてください。

一つ目、日付と時刻。SAFE.PARSE_DATETIME で文字列を DATETIME に変換します。SAFE. を頭に付けると、変換できない値が来ても NULL を返してエラーで止まりません。HubSpot のようにミリ秒のタイムスタンプで来る列は、TIMESTAMP_MILLIS(SAFE_CAST(string_field_1 AS INT64)) で変換します。

二つ目、単位付き・カンマ付きの金額。28,000 のような値は、まず REPLACE でカンマを消してから SAFE_CAST で INT64 にします。「¥」や「円」が付く場合も、同じ要領で REPLACE で剥がします。

三つ目、マスキングと空セル。******** でマスクされた値は NULLIF(列, '********') で NULL に倒します。空文字は NULLIF(列, '') で NULL にします。これをやらないと、空のセルが空文字として集計に混ざってしまいます。

四つ目、ヘッダー行の除外。ステップ2で skip_leading_rows = 0 にした分、ここで WHERE string_field_0 != 'CV名' のようにヘッダー文字列を弾きます。

このクエリを GA4・アドエビス・HubSpot それぞれに用意して1回流せば、全期間分の型付きネイティブテーブルが3つできあがります。パーティションのキーは、GA4 なら日付列、アドエビスなら DATE(cv_time)、HubSpot なら DATE(registered_at) のように、それぞれの日付列に合わせます。

ネイティブテーブルまで作成すると以下のようなテーブル構成になるかと思います。「ext_」と付いたものが外部テーブルで、「adebis」「ga4」「hubspot」などがネイティブテーブルとなります。

ステップ4：差分更新を毎日スケジュールする

最後に、毎日の自動更新クエリを設定します。毎回全データを作り直さないのがポイントです。

毎朝シート全体を読み直してテーブルをゼロから作ると、スプレッドシートのデータが増えるほど読み取り量が増え、BigQuery のスキャン料金もかさみます。そこで、書き換えるのは直近の数日分だけにします。前日に増えた行と、直近で後から修正が入った行を拾えれば、実務上は困らないかと思います。

やり方はシンプルで、直近3日分を一度消して、同じ3日分をシートから読み直して入れ直すだけです。

-- 毎日スケジュール実行する差分更新（直近3日分だけ洗い替え）
DECLARE win_start DATE DEFAULT DATE_SUB(CURRENT_DATE('Asia/Tokyo'), INTERVAL 3 DAY);

-- ① 直近3日分を一度削除する
DELETE FROM `PROJECT_ID.import_spreadsheet.adebis`
WHERE DATE(cv_time) >= win_start;

-- ② 同じ3日分をシートから読み直して入れ直す（整形はステップ3と同じ）
INSERT INTO `PROJECT_ID.import_spreadsheet.adebis`
SELECT
  string_field_0                                           AS cv_name,
  SAFE.PARSE_DATETIME('%Y-%m-%d %H:%M:%S', string_field_1) AS cv_time,
  NULLIF(string_field_3, '')                               AS user_name,
  SAFE_CAST(REPLACE(string_field_4, ',', '') AS INT64)     AS revenue,
  NULLIF(string_field_15, '********')                      AS direct_media_type,
  NULLIF(string_field_19, '********')                      AS direct_ad_name
FROM `PROJECT_ID.import_spreadsheet.ext_adebis`
WHERE string_field_0 != 'CV名'
  AND SAFE.PARSE_DATE('%Y-%m-%d', SUBSTR(string_field_1, 1, 10)) >= win_start;

このやり方には、良い点が3つあります。

書き換わるのは直近のパーティションだけなので、読み取り量が小さくコストを抑えられます。同じ日に2回流れても、一旦消してから入れ直すので重複しません。前日分の追記だけでなく、直近3日のうちに後から修正された行もちゃんと拾えます。「直近 N 日」のウィンドウは、データの修正がどれくらい後追いで入るかに合わせて、3日でも7日でも調整可能です。

スケジュールに登録するのはこの差分更新クエリの方です。BigQuery コンソールのクエリエディタにこのクエリを貼り付けて「スケジュール」を押し、繰り返しを「毎日」に設定します。

ここで1つだけ大事なタイミングの話があります。この差分更新は、スプレッドシートが最新化された後に走らせるようにしてください。

多くの場合、GAS や RPA などで毎朝スプレッドシートに前日分を書き足すと思います。その追記が終わる前に BigQuery 側の差分更新が走ると、古いシートを読んでしまいます。例えばスプレッドシートの更新が朝 7 時台なら、BigQuery の差分更新は 8 時台に、というように時間をずらして設定するのが安全です。

これで「スプレッドシートが最新化される → BigQuery が直近分だけ取り込む」という流れができます。Roboma の広告データが BigQuery に出力されるタイミングと時間帯を揃えておくと、その日のうちに広告データと突き合わせた分析に入れます。

まとめ

スプレッドシートを毎日 BigQuery に取り込む流れを、改めて整理します。

• ステップ1：他のデータと同じリージョンでデータセットを作る。

• ステップ2：シートを STRING のまま外部テーブルとして定義する。

• ステップ3：日付・金額・マスキング・空セルをクレンジングして、日付でパーティションを切ったネイティブテーブルを1回だけ構築する。

• ステップ4：直近分だけを洗い替える差分更新クエリを、シート更新の後の時刻で毎日スケジュールする。

ポイント3つです。

• 外部テーブルは STRING で素直に読んで整形は SELECT 側に寄せること。

• ネイティブテーブルは日付でパーティションを切って、毎日は直近分だけ差分更新すること。

• 最後に、差分更新はシートが最新化された後に走らせること。

この3つを押さえれば、どんなスプレッドシートでも効率的に、壊れずに更新し続けられるかと思います。

GA4・アドエビス・HubSpot などのデータが同じ BigQuery の中で型付きでそろうと、AI に投げられる仮説の幅が一気に広がります。流入元から商談まで横断でAI分析できる感動をぜひ味わってみてください。

弊社では、広告データインフラ「Roboma」を提供しています。60以上の広告媒体のデータを一元管理し、BigQuery に自動出力して、AI 分析しやすいように加工・集計しています。

スプレッドシートにしか無いデータの取り込みや、BigQuery を起点にした分析環境構築のご相談も承っています。興味のある方はこちらからお問い合わせください。

関連記事