BigQuery で表をつなぐ進め方｜1行が何かを書き出し、日にまとめて日付の表を軸につなぐ

広告のキャンペーンを1本足した月に、月次の報告で広告費あたりの売上（売上÷広告費）が前の月より上がっていたら、翌月も広告費を増やしたくなります。その前に、同じ月の売上を、注文を受けているシステムの画面と並べてみてください。報告の表の売上のほうが多ければ、上がった分は、BigQuery で売上と広告の表をつないだときの数え方から来ているかもしれません。

当社は、データ基盤の構築と、広告や売上のデータをつなぐ仕組みづくりを支援している会社です。説明のために、当社の検証用の BigQuery に架空の通販の注文と広告のデータを作り、同じデータをつなぎ方だけ変えて集計しました。日別の売上に広告の表を日付でそのままつなぐと、広告費あたりの売上は7月が 13.07、8月が 14.69 と、上がって見えました。そのままつないだ値が上がったのは、8月からキャンペーンが1本増え、広告のある日は、1日の売上を足す回数が2回から3回になったからです。この表を正しく数えると、8月は7月より上がっていませんでした（6.54 と 5.72）。

数字を合わせる鍵は、つなぐ前にあります。表ごとに「1行は何か」とつなぐ列を書き出し、1行の単位が違う表は先に同じ単位（ここでは日）にまとめて、日付を1日1行で並べた表を軸につなぐ。そうして作った表は、3か月とも元の注文の表と広告の表と同じ数字になりました。つないだ後に元の表と照らすときにも、落とし穴が1つあります。注文の表に明細の表をつないだ集計では、7月の売上が元の 15,601,390円の約2倍の 30,960,290円になったのに、注文の数を「同じ注文は1回と数える」数え方で出すと 3,351件で、元の表とぴったり同じでした。

同じデータを4つのつなぎ方で集計した、月ごとの売上が次の図です。

点線が、元の注文の表の売上です。ここから、つなぐ前の準備、数字が変わった3つのつなぎ方、一致したつなぎ方の順に見ていきます。

決めたいのは、どの表を、何の単位にそろえてつなぐか

作りたいのは、月ごとに売上・注文の数・広告費・広告費あたりの売上が1行に並ぶ表です。売上と注文の数は注文の表に、広告費は広告の表にあり、カテゴリ別に見たいなら明細の表も要ります。別々の表にある数字を1つの表に並べるには、どこかで表をつなぐことになります。

決めるのは2つです。1つは、何の単位にそろえてからつなぐか。月の数字は日の数字を足して作れるので、ここでは「日」にそろえます。もう1つは、どの表を軸にするか。片方の表にしか無い日があるとき、その日を残すかどうかは、軸の選び方で決まります。

答え合わせの相手も、つなぐ前に出しておきます。元の注文の表で月ごとに数えると、注文の数は7月 3,351件・8月 3,674件・9月 3,440件、売上（税抜）は 15,601,390円・17,226,090円・15,987,830円でした。広告の表の広告費は 2,386,474円・3,009,946円・3,515,346円です。

表ごとに「1行は何か」と、つなぐ列を書き出す

次に、使う表を並べて、それぞれの1行が何を表しているかと、つなぐときに照らし合わせる列（キー）を書き出します。あわせて、表の行の数と、つなぐ列に入っている値の種類の数を数えます。つなぐ列に空の値が無ければ、2つが同じとき、その列の値1つにつき1行です。違えば、同じ値の行が何行かあります。

注文の表は、注文番号ごとに1行でした。明細の表は 20,932行に注文番号が 10,465種類で、1つの注文に平均 2.0行あります。広告の日別の表は 224行に日付が 85種類で、1日に2〜3行。7月1日から9月30日までの92日のうち、7日は広告の行がありません。

印を付けた2つの表は、注文番号や日付でつなぐと、相手の1行に自分の何行かが付きます。そのとき相手の表の金額は、付いた行の数だけ繰り返されます。この一覧を作っておけば、どの組み合わせで数字が膨らむかは、つなぐ前に分かります。

注文の表に明細をつなぐと、売上が明細の行の数だけ繰り返して足される

商品のカテゴリ別にも売上を見たくて、注文の表に明細の表を注文番号でつなぎ、注文の金額を月ごとに足したのが、最初の図の2行目です。売上は7月が 30,960,290円（元の 1.98倍）、8月が 34,665,350円（2.01倍）、9月が 32,037,490円（2.00倍）でした。

つないだ後の表では、1つの注文に、その注文の明細の数だけ行が付きます。7月は 3,351件の注文が 6,668行になり、注文の金額はそれぞれ明細の行の数だけ足されました。今回の表は1つの注文に平均 2.0行なので約2倍でしたが、膨らみ方は注文ごとの明細の行の数で決まり、金額の大きい注文に明細が多いほど大きくなります。

カテゴリ別の売上が欲しいなら、足すのは注文の金額ではなく明細の金額です。そうすると、7月のカテゴリ別の売上を足した合計は 15,601,390円で、元の表と一致しました。ただし、カテゴリごとの注文の数（そのカテゴリの商品を含む注文の数）を足すと 5,334件で、注文の数 3,351件より多くなります。1つの注文に、いくつものカテゴリの商品が入っているからです。カテゴリ別の注文の数は、足しても全体の注文の数にならない数字として扱います。

日別の売上に広告の表をそのままつなぐと、売上がその日のキャンペーンの数だけ足される

次は、注文を日ごとにまとめた売上に、広告の日別の表を日付でそのままつないだ場合です（最初の図の3行目）。広告の表は1行が日×キャンペーンなので、1日に2〜3行あります。つなぐと、その日の売上が広告の行の数だけ繰り返されます。

この広告の表は、7月はキャンペーン2本、8月からは3本で、売上は7月が元の 2.00倍、9月が 3.00倍になりました。8月は、広告が3本ある日と、広告の行が無い7日が混ざって 2.57倍です。売上の側の日を残すつなぎ方なので、広告の無い日の売上は1回だけ残りました。注文の数も同じように足され、7月は 6,702件と、元の2倍になっています。

一方で、広告の行は1行ずつしか足されないので、広告費は3か月とも広告の表と同じでした。売上だけが膨らみ、それがそのまま広告費あたりの売上に乗ります。

日にまとめてからつないだ値は 6.54・5.72・4.55、そのままつないだ値は 13.07・14.69・13.64でした。膨らみ方は1日の広告の行の数で決まるので、広告の表の1日の行の数が変わった月（キャンペーンを足した月や、止めて行が無くなった日がある月）は、前の月との比べまでずれます。冒頭の上がり方は、このずれでした。この数字を見て翌月の広告費を増やすかどうかを決めると、数え方のずれで決めることになります。

広告を日にまとめても、両方にある日だけを残すと、広告の無い日の売上が消える

広告の表を先に日ごとにまとめれば、1日に1行になり、売上が繰り返されることはなくなります。ただ、つなぐときに「両方の表にある日だけを残す」形を選ぶと、広告の行が無い日が、その日の売上ごと落ちます（最初の図の4行目）。

8月は、日数が31日から24日になり、売上は 13,499,790円と、元より 3,726,300円（21.6%）少なくなりました。広告の行が無かった8月10日〜16日の7日分です。7月と9月は広告が毎日あったので、元の表と一致しています。

3か月のうち2か月が合っていると、つなぎ方は正しいと思いやすくなります。しかも広告費は全部残るので、8月の広告費あたりの売上は、今度は実際より低く出ます。ずれる向きが、そのままつないだ場合と逆です。

先に両方を日にまとめ、日付の表を軸につなぐ

3か月とも元の表と一致したのは、次の順に作った場合です（最初の図のいちばん下の行）。

1. 7月1日から9月30日まで、日付を1日1行で並べた表（日付の表）を作る。
2. 注文を日ごとにまとめ、日に1行の売上と注文の数にする。
3. 広告も日ごとにまとめ、日に1行の広告費にする。
4. 日付の表に、2と3を日付でつなぐ。軸は日付の表にして、売上や広告の無い日も行を残す。
5. 最後に、月ごとに足す。

日付の表を軸にするのは、売上の無い日も広告の無い日も、行として残すためです。どちらかの表を軸にすると、軸にしなかった表にだけある日が落ちます。つなぐ3つの表はどれも日付ごとに1行なので、何かが繰り返して足されることもありません。

結果は、日数が31日・31日・30日、注文の数と売上は3か月とも元の注文の表と同じで、広告費も広告の表と同じでした。広告費あたりの売上は 6.54・5.72・4.55 です。カテゴリ別の売上は、この表には入れず、明細をつないだ節に書いたとおり、明細の金額を足す別の表にしました。

つなぐ前に重なりを数え、つないだ後は日数や注文の数まで元の表と並べる

4つのつなぎ方を、8月の数字で1枚に並べたのが次の図です。

表をつなぐ集計で新しく加わるのは①です。相手の表のつなぐ列が1行ずつでなければ、つないだ後に何かが繰り返されます。明細をつないだ集計と、広告の表をそのままつないだ集計は、つなぐ前のここで分かります。

②と③は、つないだ後の件数と合計を元の表と並べる確かめです。kintone のデータを外で集計する前の記事で書いた照らし方と同じで、ここでは月ごとに並べます。8月は、間違った3つのつなぎ方のどれも③で元の表と違いましたが、両方にある日だけを残した集計は7月と9月は一致しました。月をまとめて照らすと、このずれは見えにくくなります。

つないだ表で気をつけたいのは、②の件数の数え方です。図の点線で囲んだのは、明細をつないだ集計で、注文の数を「同じ注文は1回と数える」数え方で出した 3,674件で、元の表と同じでした。件数をこの数え方だけで照らすと、売上が膨らんだ表が合格に見えます。つないだ表では、行の数も、元の表の数（日でつないだなら日数、注文でつないだなら注文の数）と並べます。

作る人とは、表の一覧を1枚そろえて話す

集計を業者や社内のデータ担当に作ってもらっているなら、「1行は何か」を書き出した節の表の一覧を、作る人と同じ形で1枚そろえておくと話が早くなります。表ごとに埋めてもらうのは、次の4つです。

1. 1行は何か（注文1件、注文の中の商品1つ、日×キャンペーン、など）。
2. つなぐ列と、どの表を軸にしてつないでいるか。
3. 表の行の数と、つなぐ列の値の種類の数。
4. つないだ後の表の、月ごとの行の数（日でつないだなら日数、注文でつないだなら注文の数）と、売上と広告費の合計。

1から3が埋まれば、どの組み合わせで売上が繰り返されるか、どこで日が落ちるかを、集計の式を読まずに確かめられます。4を元の表の日数や注文の数、売上と広告費の合計と並べて同じなら、その売上と広告費の合計は、つなぎ方で膨らんだり欠けたりはしていない、と言えます。

ここでの広告費あたりの売上は、月の売上の総額を月の広告費の総額で割った値で、広告から来た売上でも、キャンペーン別の値でもありません。この照らし方で確かめられるのは、その割り算の元になる2つの数字が、つなぎ方でずれていないことまでです。

この照らし方が受け持たないこと

照らして分かるのは、つないだ後の表が、つなぐ前の元の表と同じ件数と合計になっているかまでです。元の表そのものが正しいかは、この外です。広告のレポートを取り込むときに合計の行が混じったり、同じ日を二度入れたりして、元の表の段階でずれていることもあります（取り込みの段階の確かめ方はYahoo!広告のデータを BigQuery に入れる記事に書きました）。注文を受けているシステムの画面と元の注文の表が、日付の区切り方（時差）、返品の扱い、税込か税抜かで合わないことも、ここでは試していません。

件数と合計が両方合っても、見えない違いは残ります。たとえば明細のカテゴリの割り振りを間違えていても、全体の件数と合計は変わりません。使う切り口の数字は、その切り口で照らします。また、ユーザーの数のように、日別の数を足しても月の数にならない指標には、日にまとめてから月へ足すこの作り方は、そのままでは使えません（GA4 の日別の数字を足すと期間と合わない記事）。

今回の倍率の大きさ（約2倍、2.00〜3.00倍、21.6%）は、この表の明細の行の数、キャンペーンの数、広告の無い日の数で決まった値で、ほかの表で同じ大きさになるわけではありません。言えるのは、1行の単位が違う表をつなぐと、つなぐ列の重なりの数だけ足し込まれる、という数え方の性質です。

この図と画面の作り方

当社の検証用の BigQuery（東京リージョン、オンデマンド）で、説明用の通販の注文の表（7月1日〜9月30日の10,465件）から、1つの注文を1〜3行に分けた明細の表と、日×キャンペーンの広告の表（8月10日〜16日は行なし）を作り、2026年10月に、つなぎ方ごとに1回ずつ集計しました。金額は税抜です。元の表と一致した集計は次のとおりです。先に日にまとめた集計の写真は、ここから日数と注文の数の列を省いて流したもので、そのままつないだ集計の写真は、広告の表を日にまとめずに日付でつないだ別の集計です。

WITH cal AS (SELECT d FROM UNNEST(GENERATE_DATE_ARRAY('2026-07-01', '2026-09-30')) AS d),
s AS (SELECT order_date AS d, COUNT(*) AS orders, SUM(amount_ex_tax) AS sales FROM blog_demo.ec_orders
      WHERE order_date BETWEEN '2026-07-01' AND '2026-09-30' GROUP BY d),
a AS (SELECT ad_date AS d, SUM(spend) AS ad_spend FROM blog_demo.ad_daily GROUP BY d)
SELECT FORMAT_DATE('%Y-%m', cal.d) AS month, COUNT(*) AS days,
  SUM(s.orders) AS orders, SUM(s.sales) AS sales, SUM(a.ad_spend) AS ad_spend
FROM cal LEFT JOIN s USING (d) LEFT JOIN a USING (d)
GROUP BY month ORDER BY month

作る人とそろえる一覧の3つ目は、つなぐ列で COUNT(*) と COUNT(DISTINCT 列) を並べることに当たります。両方にある日だけを残すのが INNER JOIN、軸の表の行をすべて残すのが LEFT JOIN で、BigQuery のクエリの構文の説明にも、INNER JOIN は条件に合わない組み合わせを捨て、LEFT JOIN は左の表の行をすべて残す、とあります。同じページの例では、表 B に同じ値の行が2つあり、表 A のその値の行が、それぞれ2行ずつになって結果に出てきます。

まずは、いまの報告の元になっている表を一覧に書き出す

いまの月次の報告が、どの表をどうつないで作られているかを、上の表の一覧の形で書き出してみてください。行の数とつなぐ列の値の種類の数が違う表が、日にまとめないまま、あるいは日付の表を軸にせずにつながっていれば、そこが見直す場所です。

一覧づくりから、日付の表を軸にした集計への組み直しまでを進めるなら、当社のBigQuery の導入支援でお手伝いします。