Créer du SQL d'analyse BigQuery pour retail EC avec Claude Code

Une équipe retail EC garde souvent les ventes, retours, coûts publicitaires, coûts produit et stocks dans des écrans séparés. La réunion hebdomadaire commence alors par du collage de CSV, pas par une décision.

Cet article transforme BigQuery en workflow réduit et vérifiable. Claude Code rédige le SQL, repère les clés JOIN manquantes et prépare la table de revue. Les humains vérifient coûts, motifs de retour, campagnes, données privées et budget.

Points Clés

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

Scène d’Échec

Les références officielles servent pour le chargement CSV, les fonctions d’agrégation, les fonctions GoogleSQL et l’accès IAM. 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 Avant le 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.

Périmètre de Claude Code et Revue Humaine

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.

Trois Use Cases

Use case 1: Classement produit avec marge brute

• 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: Motifs de retour reliés aux corrections de page produit

• 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 après coût publicitaire

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

Prompt à Copier

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.

La première action consiste à noter seulement les colonnes: product_id, date, order_id, revenue, return_reason, ad_cost et cost. Ne collez pas noms, adresses, téléphones, emails ou notes de commande. Cette limite garde l’analyse utile sans exposer les clients.

Code de Vérification

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: Ne Voir Que les Ventes

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.

Questions Fréquentes

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.

Chemin de Consultation

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.

Résultat Vérifié

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.