Claude Code로 소매 EC BigQuery 분석 SQL 만들기

소매 EC 팀은 매출 CSV, 반품 CSV, 광고비 CSV, 상품 원가, 재고를 서로 다른 화면에 두는 경우가 많습니다. 주간 회의는 판단보다 복사와 붙여넣기로 시작됩니다.

이 글은 BigQuery 분석을 작고 검토 가능한 흐름으로 나눕니다. Claude Code는 SQL 초안, JOIN 키 확인, 리뷰 표를 만듭니다. 사람은 원가, 반품 사유, 광고 연결, 개인정보, 예산 결정을 봅니다.

이 글의 요점

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

실패 장면

공식 문서는 CSV 로딩, 집계 함수, GoogleSQL 함수, 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.

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 범위와 사람 검토

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.

세 가지 Use case

Use case 1: 매출과 매출총이익을 함께 보는 상품 순위

• 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: 반품 사유를 상품 페이지 수정으로 연결

• 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: 광고비를 뺀 뒤의 이익

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

복사해서 쓰는 프롬프트

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.

첫 작업은 product_id, date, order_id, revenue, return_reason, ad_cost, cost 같은 컬럼명만 적는 것입니다. 고객 이름, 주소, 전화번호, 이메일, 주문 메모는 프롬프트에 넣지 않습니다. 이 경계가 분석을 안전하게 만듭니다.

실행 가능한 확인 코드

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: 매출만 보는 문제

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.

자주 묻는 질문

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.

상담 경로

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.

실제로 확인한 결과

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.