EC事業の粗利率をBigQueryで商品×チャネル別に自動計算する

はじめに：売上は見ても粗利は見ていない

ECを運営していると、売上の数字は毎日のように目に入ります。管理画面を開けば今日いくら売れたかがすぐ分かりますし、月末には前月比でどう伸びたかも自然と気になります。売上は分かりやすく、手応えを感じやすい指標です。

一方で、粗利を同じ解像度で見ている店舗は、思いのほか少ないのではないでしょうか。「売上は伸びているのに、なぜか手元のお金が増えない」という感覚を持ったことがあるなら、それは粗利を見ていないサインかもしれません。

売上が伸びても、その裏で原価の高い商品ばかりが売れていたり、手数料の重いチャネルに偏っていたり、送料を負担しすぎていたりすると、利益はちっとも増えません。むしろ、売上を伸ばすほど赤字が膨らむケースすらあります。

問題は、粗利の計算が売上の確認よりずっと面倒なことです。原価はモール側の管理画面には載っていませんし、手数料や送料はチャネルごとにバラバラで、これらを商品別・チャネル別に手作業で突き合わせるのは現実的ではありません。だから多くの店舗が、粗利を「ざっくり全体で月1回」しか見られていないのです。

この記事では、受注データを集約したBigQueryを使い、粗利率を商品×チャネル別に自動計算する仕組みを作ります。一度組んでしまえば、あとはスケジュールクエリが毎日更新してくれるので、「どの商品が、どのチャネルで、本当に儲かっているのか」を売上と同じ解像度で見られるようになります。

なお、本記事は受注データやGA4データがすでにBigQueryに集約されていることを前提にしています。データの集約や売上の突合がまだの場合は、以下の記事も合わせて参考にしてください。

ECのセール施策効果をGA4×BigQueryでbefore/after比較する分析テンプレート

粗利の考え方：売上−原価−手数料−送料

仕組みを作る前に、ここで扱う粗利の定義を揃えておきます。会計上の「売上総利益」とは厳密には少しずれますが、ECの日々の運営で「この商品はこのチャネルで儲かっているか」を判断するための実務的な粗利として、次のように定義します。

粗利 = 売上 − 原価 − 販売手数料 − 送料負担
粗利率 = 粗利 ÷ 売上

それぞれの要素を整理します。

売上：商品が売れた金額です。値引きやクーポンを適用した場合は、実際に受け取った金額（値引き後）で計算します。定価ベースで集計すると、粗利が実態より大きく見えてしまうため注意が必要です。
原価：その商品を仕入れる、または製造するのにかかった金額です。外部のデータには載っていないので、自社で原価マスタとして用意する必要があります。後述するとおり、ここが粗利計算でいちばんつまずきやすいポイントです。
販売手数料：モールやカート、決済代行に支払う手数料です。チャネルごとに料率が異なるのが厄介で、自社ECなら決済手数料の数%、モールなら出店料率を含めて10%以上ということも珍しくありません。チャネル別に粗利を見たい最大の理由が、この手数料の差にあります。
送料負担：店舗が負担している送料です。送料無料にしている商品では、この負担がそのまま粗利を削ります。送料込みで価格設定しているつもりでも、地域別の実送料が想定を超えていると、知らないうちに利益が薄くなっていることがあります。

この4要素を商品×チャネル別に並べて初めて、「売上は大きいが手数料負けしているチャネル」や「原価率が高くて薄利な商品」が見えてきます。

商品×チャネル別の粗利計算SQL

ここから実際の計算に入ります。前提として、次のようなテーブルが揃っているとします。

orders：受注明細。1行が「ある注文に含まれる、ある商品の1明細」を表す。order_id / channel / product_id / quantity / sales_amount（値引き後売上）/ fee_amount（販売手数料）/ shipping_cost（送料負担）/ order_date を持つ
cost_master：原価マスタ。product_id / unit_cost（1個あたり原価）を持つ

まず、原価マスタを受注明細に結合します。ここで重要なのは、内部結合（INNER JOIN）ではなく左結合（LEFT JOIN）を使うことです。原価マスタに登録されていない商品があっても、その明細を売上から落とさないためです。

SELECT
  o.order_date,
  o.channel,
  o.product_id,
  o.quantity,
  o.sales_amount,
  o.fee_amount,
  o.shipping_cost,
  c.unit_cost
FROM `your_project.ec.orders` AS o
LEFT JOIN `your_project.ec.cost_master` AS c
  ON o.product_id = c.product_id

INNER JOINにしてしまうと、原価マスタに載っていない商品の明細が結果から消えます。すると売上の合計がほかの集計と合わなくなり、「なぜか売上が少ない」という分かりにくいバグになります。LEFT JOINにしておけば売上は必ず残り、原価が未登録なら unit_cost が NULL になるだけなので、欠損を後から検知できます。

原価NULLの検知

LEFT JOINにした分、原価が NULL の明細が混ざる可能性が出てきます。これを放置して粗利を計算すると、NULL を含む引き算の結果が NULL になり、その明細の粗利がまるごと消えてしまいます。粗利の合計が実態より小さく出る原因になります。

そこで、原価が登録されているかどうかをフラグとして持ち、登録漏れの規模を可視化します。

WITH joined AS (
  SELECT
    o.order_date,
    o.channel,
    o.product_id,
    o.quantity,
    o.sales_amount,
    o.fee_amount,
    o.shipping_cost,
    c.unit_cost,
    c.unit_cost IS NULL AS is_cost_missing
  FROM `your_project.ec.orders` AS o
  LEFT JOIN `your_project.ec.cost_master` AS c
    ON o.product_id = c.product_id
)
SELECT
  channel,
  COUNT(*) AS line_count,
  COUNTIF(is_cost_missing) AS missing_cost_lines,
  SUM(IF(is_cost_missing, sales_amount, 0)) AS missing_cost_sales
FROM joined
GROUP BY channel
ORDER BY missing_cost_sales DESC

このクエリで、原価未登録の明細が何件あり、それが売上のうちいくら分を占めるかが分かります。missing_cost_sales が無視できない金額なら、原価マスタを整備してから粗利を読むべきだと判断できます。逆に、ごく少額なら近似値として割り切る判断もできます。どちらにせよ、欠損の規模を知らないまま粗利を信じるのは危険です。

粗利率の集計

欠損の状況を確認したら、商品×チャネル別に粗利を集計します。原価が NULL の場合に粗利全体が NULL にならないよう、COALESCE で原価を 0 として扱い、別途「原価込みで計算できた売上」も持っておきます。

WITH joined AS (
  SELECT
    o.channel,
    o.product_id,
    o.quantity,
    o.sales_amount,
    o.fee_amount,
    o.shipping_cost,
    c.unit_cost,
    c.unit_cost IS NULL AS is_cost_missing
  FROM `your_project.ec.orders` AS o
  LEFT JOIN `your_project.ec.cost_master` AS c
    ON o.product_id = c.product_id
)
SELECT
  channel,
  product_id,
  SUM(sales_amount) AS sales,
  SUM(COALESCE(unit_cost, 0) * quantity) AS cost,
  SUM(fee_amount) AS fee,
  SUM(shipping_cost) AS shipping,
  SUM(sales_amount)
    - SUM(COALESCE(unit_cost, 0) * quantity)
    - SUM(fee_amount)
    - SUM(shipping_cost) AS gross_profit,
  SAFE_DIVIDE(
    SUM(sales_amount)
      - SUM(COALESCE(unit_cost, 0) * quantity)
      - SUM(fee_amount)
      - SUM(shipping_cost),
    SUM(sales_amount)
  ) AS gross_margin_rate,
  SUM(IF(is_cost_missing, sales_amount, 0)) AS sales_without_cost
FROM joined
GROUP BY channel, product_id
ORDER BY gross_profit DESC

ポイントを補足します。

原価は1個あたりの unit_cost に quantity を掛けて明細の原価にしています。COALESCE(unit_cost, 0) で、未登録の場合は原価ゼロとみなしています。原価ゼロは粗利を過大評価する向きに効くので、sales_without_cost で「どれだけの売上が原価ゼロ扱いになったか」を必ず横に置いておきます。
粗利率は SAFE_DIVIDE で計算しています。売上がゼロの組み合わせがあってもゼロ除算エラーにならず、NULL が返るためです。
gross_profit DESC で並べると、儲かっている商品×チャネルの組み合わせが上に来ます。逆に末尾を見れば、粗利がマイナスになっている「売るほど損する」組み合わせが浮かび上がります。

この結果を見ると、たとえば「自社ECでは粗利率35%なのに、同じ商品がモールでは手数料負けして20%まで下がっている」といった、チャネル間の差がはっきりします。商品単位の平均だけでは見えない、チャネルごとの収益構造が分かるわけです。

スケジュールクエリで自動更新する

ここまでのクエリを手で実行してもよいのですが、それでは結局「月1回しか見ない」状態に逆戻りしてしまいます。粗利を売上と同じ感覚で毎日チェックできるよう、BigQueryのスケジュールクエリで自動更新します。

考え方はシンプルで、集計結果を保存するデータマート用のテーブルを1つ用意し、そこへ毎日上書きするだけです。たとえば ec.daily_gross_margin というテーブルを作り、商品×チャネル×日付の粒度で粗利を書き込みます。

CREATE TABLE IF NOT EXISTS `your_project.ec.daily_gross_margin` (
  order_date DATE,
  channel STRING,
  product_id STRING,
  sales NUMERIC,
  cost NUMERIC,
  fee NUMERIC,
  shipping NUMERIC,
  gross_profit NUMERIC,
  gross_margin_rate FLOAT64,
  sales_without_cost NUMERIC
)
PARTITION BY order_date;

order_date でパーティション分割しておくと、日付で絞った読み出しが軽くなり、コストも抑えられます。

スケジュールクエリ側では、前日分だけを計算して該当パーティションを入れ替える形にします。全期間を毎日作り直すと、データが増えるほど無駄なスキャン費用がかさむためです。次のように、対象日を絞ってから書き込みます。

DELETE FROM `your_project.ec.daily_gross_margin`
WHERE order_date = DATE_SUB(@run_date, INTERVAL 1 DAY);

INSERT INTO `your_project.ec.daily_gross_margin`
SELECT
  o.order_date,
  o.channel,
  o.product_id,
  SUM(o.sales_amount) AS sales,
  SUM(COALESCE(c.unit_cost, 0) * o.quantity) AS cost,
  SUM(o.fee_amount) AS fee,
  SUM(o.shipping_cost) AS shipping,
  SUM(o.sales_amount)
    - SUM(COALESCE(c.unit_cost, 0) * o.quantity)
    - SUM(o.fee_amount)
    - SUM(o.shipping_cost) AS gross_profit,
  SAFE_DIVIDE(
    SUM(o.sales_amount)
      - SUM(COALESCE(c.unit_cost, 0) * o.quantity)
      - SUM(o.fee_amount)
      - SUM(o.shipping_cost),
    SUM(o.sales_amount)
  ) AS gross_margin_rate,
  SUM(IF(c.unit_cost IS NULL, o.sales_amount, 0)) AS sales_without_cost
FROM `your_project.ec.orders` AS o
LEFT JOIN `your_project.ec.cost_master` AS c
  ON o.product_id = c.product_id
WHERE o.order_date = DATE_SUB(@run_date, INTERVAL 1 DAY)
GROUP BY o.order_date, o.channel, o.product_id;

@run_date はスケジュールクエリが実行日を渡してくれるパラメータです。前日分を一度削除してから入れ直すことで、後から受注データが補正されても二重計上を防げます。スケジュールは、受注データの取り込みが終わったあとの時間帯、たとえば早朝に設定しておくと安全です。

このデータマートをLooker Studioにつなげば、商品別・チャネル別の粗利率を毎日自動で見られるダッシュボードになります。スケジュールクエリでデータマートを日次更新する手順の詳細は、以下の記事で解説しています。

BigQueryのスケジュールクエリでデータマートを毎日自動更新する

なお、原価マスタの更新を忘れると、新商品がずっと原価ゼロのまま集計され続けます。先ほどの sales_without_cost をダッシュボードの片隅に出しておき、この数字が増えてきたら原価マスタを見直す、という運用にしておくと安心です。

まとめ

売上は分かりやすいからこそ毎日見てしまいますが、本当に手元に残るお金を決めているのは粗利です。そして粗利は、商品×チャネル別に分解して初めて、「どこで儲け、どこで損しているのか」が見えてきます。

この記事で紹介した流れは次のとおりです。粗利を「売上 − 原価 − 販売手数料 − 送料負担」として実務的に定義し、原価マスタはLEFT JOINで結合して未登録商品の明細を売上から落とさず、原価が NULL の明細を検知して欠損の規模を金額で把握してから粗利を読み、スケジュールクエリでデータマートを日次更新して売上と同じ解像度で粗利を見られるようにする、というものです。

最初に原価マスタを整える手間はかかりますが、一度仕組みを組んでしまえば、あとは毎日勝手に更新されます。売上だけを追って一喜一憂する状態から、粗利という本当の指標で意思決定できる状態へ。その第一歩として、まずは自社の受注データに原価をひも付けるところから始めてみてください。