小売ECのBigQuery分析SQLをClaude Codeで作る: 売れ筋、返品、広告費を見る

小売ECで売上CSV、返品CSV、広告費CSVが別々に置かれていると、週次会議の前にExcelで貼り合わせる作業が残ります。売れ筋だけを見ると好調に見える商品でも、返品率と広告費を入れると赤字に近いことがあります。担当者は「在庫を増やす商品」と「商品ページを直す商品」を分けられないまま、次の広告費を決めてしまいます。

この記事では、小売ECの担当者がBigQueryで売れ筋、返品、広告費、粗利を同じ表に戻すためのSQL設計を扱います。Claude CodeにはSQLの下書き、列名の確認、レビュー表の作成を任せます。価格、原価、広告停止、個人情報の扱いは人が見ます。

この記事の要点

• 小売ECの分析では、売上だけでなく返品率、広告費、粗利、在庫を同じ行に置きます。
• Claude Codeには、BigQueryのテーブル名、JOINキー、NULLの扱い、確認SQL、レビュー表を作らせます。
• 人が見る範囲は、原価、返品理由、広告キャンペーンの紐づけ、個人情報、広告停止判断です。
• 3つのUse caseは、売れ筋分析、返品理由からの商品ページ修正、広告費込みの粗利確認です。
• ROIは、広告費、返品率、商品ページCVR、在庫切れ、分析にかかる作業時間で見ます。

現場で起きる失敗シーン

小売ECの週次レポートでよくある失敗は、注文CSVだけで商品を評価する形です。売上順に並べると上位に見える商品でも、サイズ違いの返品が多い、広告費が高い、発送後キャンセルが多い、在庫切れで機会損失が出ている、といった情報が別ファイルに残ります。

もうひとつの失敗は、顧客情報を分析用の下書きに入れてしまうことです。BigQueryで見るべきなのは、商品ID、日付、カテゴリ、注文数、返品数、広告費、粗利です。顧客名、住所、電話番号、メールアドレスは、この記事の分析には入れません。

参考にする公式情報は、BigQueryのCloud StorageからCSVを読み込む説明、集計関数、関数一覧、IAMでリソースアクセスを管理する説明です。公式情報は正しいですが、自社の商品名、返品理由、広告費の持ち方までは決めてくれません。そこをClaude Codeで作業表にします。

業務フロー: BigQueryへ入れる前に表を分ける

小売ECのBigQuery分析は、いきなりSQLを書く前に、売上CSV、返品CSV、広告費CSV、商品マスタ、原価表、在庫表を並べるところから始めます。読者が今見ているのは、管理画面から落としたCSV、広告管理画面、返品理由のメモ、在庫管理表です。

入力する表	最低限見る列	入れないもの
注文CSV	date, order_id, product_id, quantity, revenue	顧客名、住所、電話番号
返品CSV	date, order_id, product_id, return_reason, refund_amount	個別のお客さんの事情
広告費CSV	date, campaign_id, product_id, ad_cost, clicks	広告アカウントの秘密情報
商品マスタ	product_id, category, cost, price	仕入先の契約メモ
在庫表	product_id, stock, reorder_point	倉庫担当者の個人連絡先

Claude Codeには、この表を渡して「JOINキーが足りるか」「日付粒度がずれていないか」「返品が注文に紐づかない時にどう扱うか」を出させます。BigQueryのSQLでは、0除算を避けるためにSAFE_DIVIDEのような関数を使う場面もあります。関数名や集計方法は公式ドキュメントで確認し、本文では自社の列名へ落とします。

Claude Codeに任せる範囲と人が見る範囲

小売ECの担当者は、Claude Codeに現在のCSV列名、目標の指標、確認したい商品カテゴリ、BigQueryのテーブル候補を読ませます。任せるのは、テーブル設計メモ、SQLの下書き、レビュー観点、抜けている列の洗い出し、公開前チェックです。

人が見る範囲は、原価、広告費の正確さ、返品理由の分類、個人情報、広告停止や在庫発注の判断です。Claude Codeが「この広告を止める」と書いても、そのまま採用しません。人が見るのは、粗利、在庫、季節性、キャンペーンの目的、返品コメントです。

権限も分けます。分析担当がBigQueryで商品別の集計表を見るだけなら、顧客情報の列まで読める必要はありません。BigQueryのIAMや列単位の扱いは、社内の権限設計と合わせて確認します。Claude Codeには「誰が、どの表を、何のために読むか」を表にさせます。

3つのUse case

Use case 1: 売れ筋商品を粗利つきで見る

• 入力: 注文CSV、商品マスタ、原価表、在庫表
• 出力: 商品別の売上、注文数、粗利、粗利率、在庫数、在庫切れ候補
• 人の確認: 原価、在庫数、セール対象、季節商品、欠品時の代替商品

売上順だけでは、広告を増やす商品を選べません。Claude Codeには、売上、粗利、在庫を同じ表で見るSQLを作らせます。人は、原価が古くないか、セット販売やクーポンが粗利を歪めていないかを確認します。

Use case 2: 返品理由から商品ページを直す

• 入力: 返品CSV、商品ページURL、サイズ表、レビュー、問い合わせメモ
• 出力: 商品別の返品率、主な返品理由、商品ページで直す説明、FAQ候補
• 人の確認: 返品理由の分類、サイズ表の正確さ、写真表現、保証や返金条件

返品が多い商品は、悪い商品とは限りません。サイズ表が見づらい、色味写真が足りない、配送時の注意が下にある、といったページ側の問題もあります。Claude Codeには、返品理由を「サイズ」「色」「期待違い」「配送」「説明不足」に分けさせ、商品ページのどこを直すかを表にします。

Use case 3: 広告費を引いた後の利益を見る

• 入力: 広告費CSV、注文CSV、商品マスタ、キャンペーンID、日付範囲
• 出力: 商品別またはキャンペーン別の広告費、売上、粗利、広告費差引後利益
• 人の確認: キャンペーンと商品の紐づけ、ブランド認知目的の広告、季節性、停止判断

ROASだけを見ると、返品後の利益が見えません。Claude Codeには、広告費を商品またはキャンペーンへ紐づけるSQL案を作らせます。人は、広告の目的が新規獲得なのか、在庫処分なのか、ブランド認知なのかを確認します。

コピペで使えるプロンプト

小売ECのBigQuery分析レビュー担当として作業してください。

目的:
売上、返品、広告費、粗利、在庫を同じ表で見られるSQLとレビュー表を作る。

入力:
- 注文テーブル名:
- 返品テーブル名:
- 広告費テーブル名:
- 商品マスタ名:
- 原価列:
- JOINキー候補:
- 日付範囲:
- 個人情報を含む列:
- 人が確認する担当:

出力:
1. BigQueryに入れる表と列の一覧
2. 入れてはいけない個人情報の一覧
3. 商品別の売上、返品率、広告費、粗利を見るSQL
4. JOINキーが足りない場合の確認質問
5. 人が見る判断項目
6. 公開前チェックリスト

制約:
- 顧客名、住所、電話番号、メールアドレスは分析SQLに入れない
- 広告停止や在庫発注は断定しない
- 公式ドキュメントへのリンクを残す
- 初心者が次に確認する1手を最後に書く

まずは検証用の小さなCSVで試します。全商品、全広告、全期間を一気に入れるより、カテゴリを1つ、期間を7日、商品を20件に絞る方がミスを見つけやすくなります。

動く確認コード

SELECT
  s.product_id,
  SUM(s.revenue) AS revenue,
  SUM(s.gross_profit) AS gross_profit,
  SUM(a.ad_cost) AS ad_cost,
  SAFE_DIVIDE(SUM(r.return_count), SUM(s.order_count)) AS return_rate,
  SUM(s.gross_profit) - SUM(a.ad_cost) AS profit_after_ad
FROM `project.dataset.sales_daily` AS s
LEFT JOIN `project.dataset.returns_daily` AS r
  ON s.product_id = r.product_id AND s.date = r.date
LEFT JOIN `project.dataset.ad_cost_daily` AS a
  ON s.product_id = a.product_id AND s.date = a.date
GROUP BY s.product_id
ORDER BY profit_after_ad DESC
LIMIT 50;

const analysisPlan = {
  dataset: "ec_analytics",
  salesTable: "orders_daily",
  returnTable: "returns_daily",
  adTable: "ad_cost_daily",
  joinKeys: ["product_id", "date"],
  outputMetrics: ["revenue", "gross_profit", "return_rate", "ad_cost", "profit_after_ad"],
  privacyRule: "exclude customer name, address, phone, and email",
  reviewer: "EC owner checks cost, return reason, and campaign mapping",
  cta: "/training/"
};

const required = [
  "dataset",
  "salesTable",
  "returnTable",
  "adTable",
  "joinKeys",
  "outputMetrics",
  "privacyRule",
  "reviewer",
  "cta"
];

const missing = required.filter((key) => !analysisPlan[key]);

if (missing.length > 0) {
  throw new Error("Missing BigQuery analysis fields: " + missing.join(", "));
}

if (!analysisPlan.outputMetrics.includes("profit_after_ad")) {
  throw new Error("Profit after ad cost must be visible before budget decisions.");
}

if (!analysisPlan.cta.startsWith("/")) {
  throw new Error("CTA must be an internal path.");
}

console.log("Retail EC BigQuery analysis plan is ready for human review.");

上のJavaScriptは、BigQuery分析メモに必要な項目が欠けていないかを見る小さな確認コードです。実運用では、JSONやMarkdownで持っている分析設計メモを読み込み、個人情報の除外方針、レビュー担当、CTAが空なら公開前に止めます。

Pitfall: BigQuery分析で売上だけ見てしまう落とし穴

原因の1つ目は、注文CSVをきれいにした時点で満足してしまう点です。直し方は、返品CSVと広告費CSVを必ず同じ粒度で並べる形です。商品IDと日付がそろわないなら、SQLを書く前に確認質問を出します。

原因の2つ目は、返品理由を自由記述のまま集計してしまう点です。直し方は、サイズ、色、配送、説明不足、破損、その他のように分類を作り、人が確認した上で商品ページ修正へつなげます。

原因の3つ目は、分析用テーブルに個人情報を残すことです。直し方は、注文ID、商品ID、日付、数量、金額だけで足りるかを先に見ます。顧客名、住所、電話番号、メールアドレスが必要ないなら、分析用テーブルから外します。

よくある質問

Q. BigQueryを使わず、Excelだけでも始められますか。

A. 始められます。まずはExcelで売上、返品、広告費を同じ商品IDで並べます。毎週同じ集計を繰り返す段階になったら、BigQueryに移す候補です。

Q. Claude Codeに本番CSVをそのまま貼ってよいですか。

A. 貼りません。顧客名、住所、電話番号、メールアドレス、注文メモは伏せます。列名とサンプル値だけでSQLの下書きは作れます。

Q. 相談につなげるなら、記事のどこをCTAにしますか。

A. クラウド連携と権限設計が絡むため、主CTAは研修・相談です。教材リンクを並べるより、自社データでどこまで読ませるかを相談できる方が近い読者です。

相談へ進むなら

小売ECのBigQuery分析は、SQLだけの話ではありません。商品マスタ、返品理由、広告費、原価、権限、レビュー担当をそろえて、毎週同じ判断ができる形へ戻す仕事です。ここまで整えると、広告収益よりも問い合わせや研修につながる記事になります。

ClaudeCodeLabの研修・相談では、Claude Codeに読ませる資料、BigQueryへ入れる表、伏せる個人情報、レビュー担当、公開前チェックまで一緒に作る流れにできます。今日やるなら、まず直近7日分の売上CSV、返品CSV、広告費CSVの列名だけを書き出してください。

実際に試した結果

この記事では、slug、frontmatter、内部リンク、外部リンク、コードブロック、CTA、BigQuery公式URL、商品別の売上・返品・広告費を見る構成を確認しました。JavaScriptの確認コードでは、dataset、salesTable、returnTable、adTable、joinKeys、outputMetrics、privacyRule、reviewer、ctaが空でないことを確認しています。まず今日やる1手は、顧客情報を外した列名メモを作り、売上CSV、返品CSV、広告費CSVのJOINキーをチェックする作業です。