BigQuery-Analyse-SQL für Retail-EC mit Claude Code erstellen

Retail-EC-Teams halten Umsatz, Retouren, Anzeigenkosten, Produktkosten und Lager oft in getrennten Oberflächen. Das Wochenmeeting beginnt dann mit CSV-Arbeit statt mit klaren Entscheidungen.

Dieser Artikel macht BigQuery zu einem kleinen, prüfbaren Workflow. Claude Code entwirft SQL, findet fehlende JOIN-Schlüssel und schreibt eine Review-Tabelle. Menschen prüfen Kosten, Retourengründe, Anzeigenzuordnung, Datenschutz und Budgetentscheidungen.

Kernaussagen

• The workflow starts from retail EC screens, not from a generic SQL tutorial.
• Sales, returns, ad cost, margin, and stock need one review table.
• Claude Code drafts SQL and review notes; humans confirm cost, privacy, campaigns, and budget decisions.
• The main CTA is training because BigQuery, permissions, and data boundaries are company-specific.
• Measure ROI with ad cost, return rate, product page CVR, stockouts, and weekly analysis time.

Fehlerszene

Offizielle Quellen helfen bei CSV-Loading, Aggregatfunktionen, GoogleSQL-Funktionen und IAM-Ressourcenzugriff. Use BigQuery CSV loading, BigQuery aggregate functions, BigQuery functions, BigQuery IAM resource access as anchors. The documentation explains the platform, but the store must decide product IDs, return reasons, cost columns, and who can read each table.

The common failure is ranking products by revenue alone. A high-revenue SKU can have many size-related returns, high ad cost, low margin, or stockout risk. The decision needs a table that shows the trade-off.

Workflow Vor SQL

Start with the screen or CSV the team already has: orders, returns, ad cost, product master, cost, and stock. Write the column names before writing SQL. Remove customer names, addresses, phone numbers, emails, and free-text order notes.

Create a small BigQuery review memo. It should contain the dataset name, table names, join keys, date range, metrics, privacy rule, reviewer, and CTA. That memo is easier to review than a long chat transcript.

Claude-Code-Scope und menschliche Prüfung

Claude Code can draft JOIN logic, aggregate SQL, missing-field questions, and a review checklist. It can also explain why return_rate or profit_after_ad should be visible before changing advertising spend.

Humans review product cost, return reason taxonomy, ad campaign mapping, private data, and final budget decisions. If a table includes customer-level data, stop and create a safer aggregate table first.

Drei Use Cases

Use case 1: Produktranking mit Bruttomarge

• Input: orders table, product master, cost table, stock table.
• Output: revenue, order count, gross profit, margin rate, stock warning.
• Human review: product cost, seasonal campaign, stock count, bundle pricing.

Use case 2: Retourengründe mit Produktseiten-Fixes verbinden

• Input: returns table, product page URL, size guide, reviews, support notes.
• Output: return rate, reason category, page fix list, FAQ draft.
• Human review: reason classification, refund policy, warranty wording, page promise.

Use case 3: Gewinn nach Anzeigenkosten

• Input: ad cost table, orders table, product master, campaign ID, date range.
• Output: ad cost, revenue, gross profit, profit after ad, budget review note.
• Human review: campaign purpose, attribution, seasonality, stop-or-continue decision.

Kopierbarer Prompt

Act as a BigQuery reviewer for a retail EC store.
Goal: create one analysis memo for sales, returns, ad cost, margin, and stock.
Inputs: table names, column names, join keys, date range, privacy rule, reviewer.
Outputs: table list, SQL draft, missing questions, human review checklist, next action.
Rules: do not include customer names, addresses, phone numbers, emails, or order notes.

Die erste Aktion ist nur eine Spaltenliste: product_id, date, order_id, revenue, return_reason, ad_cost und cost. Kundennamen, Adressen, Telefonnummern, E-Mails und Bestellnotizen gehören nicht in den Prompt. Diese Grenze hält die Analyse nützlich und sicherer.

Prüfcode

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.");

The code checks whether the analysis memo has the minimum fields. In a real repository, the same check can read JSON, Markdown, SQL files, or MDX frontmatter before publishing an article or running a query.

Pitfall: Nur Umsatz betrachten

Cause: the team cleans only the orders table. Fix: add returns and ad cost at the same product and date grain. Cause: return reasons stay as free text. Fix: create a small reason taxonomy and have a person review it.

Cause: private data enters the analysis prompt. Fix: use product-level, date-level, and campaign-level data first. If customer-level rows are required, create a separate approved process.

FAQ

Q. Can this start in a spreadsheet?

A. Yes. Start by aligning product_id and date across sales, returns, and ad cost. Move to BigQuery when the same work repeats every week.

Q. Should Claude Code decide which ads to stop?

A. No. It can show profit_after_ad and missing fields. The EC owner decides after checking stock, campaign purpose, and seasonality.

Q. Where should the conversion path go?

A. For cloud data workflows, point readers to training. The reader often needs help with data boundaries and review flow, not only a downloadable template.

Beratungsweg

The business path is training or consultation. A store with revenue, returns, ads, and stock in separate tools needs a safe workflow, not only a SQL snippet. Use the article to show the first step, then invite readers to review their own tables.

Geprüftes Ergebnis

I verified the slug, frontmatter, internal CTA, external BigQuery references, executable JavaScript, code fence, FAQ, pitfall section, and human review path. The next action is to list safe column names and check the JOIN keys before writing production SQL.