Build Retail EC BigQuery Analysis SQL with Claude Code

Retail EC teams often keep sales CSV, returns CSV, ad cost CSV, product cost, and stock in separate screens. The weekly meeting then starts with copy-paste work instead of decisions. A product can look strong by revenue and still lose margin after returns and ads.

This article turns BigQuery into a small, reviewable retail EC workflow. Claude Code drafts SQL, checks missing join keys, and writes the review memo. Humans confirm cost, return reasons, ad mapping, private data, and budget decisions.

What This Article Covers

• 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.

Failure Scene

Use the official references for CSV loading, aggregate functions, GoogleSQL functions, and IAM resource access. 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 Before 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 and Human Review

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.

Three Use Cases

Use case 1: Product ranking with gross margin

• 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: Return reasons mapped to product page fixes

• 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: Profit after ad cost

• 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.

Copy-Paste 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.

The first practical action is to write down column names only: product_id, date, order_id, revenue, return_reason, ad_cost, and cost. Do not paste customer names, addresses, phone numbers, emails, or order notes into the drafting prompt. That single boundary keeps the analysis useful while reducing risk.

Working Check Code

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: Looking Only at Sales

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.

Consultation Path

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.

What I Verified

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.