Ecom data analyst

Ecom Data Analyst

Take a Google Sheets URL containing per-SKU COGS data and run the full financial analysis pipeline — margin, BEROAS, tier classification, ROAS scenarios, cashflow, monthly P&L at scale, and 24-month profit projection.

The skill always runs the full pipeline by default. The user can then ask scenario follow-ups (e.g. "what about $30K at 3x?", "model 2 years at 2x") and the skill answers from the same data foundation.

Output saves to ./brands/[brand-name]/financial-analysis/[YYYY-MM-DD]-analysis.md and displays in chat.

---

Step 1 — Ingest the sheet

The user provides a Google Sheets URL. Extract the file ID from the URL (the part between /d/ and /edit), then pull the content.

Use the Google Drive MCP:

mcp__claude_ai_Google_Drive__read_file_content(fileId="<file-id>")

This returns a markdown table representation of the sheet. Parse for:

• Product name
• Retail price (selling price column — usually labelled "Single Item Offer" or similar)
• COGS + Shipping (the combined cost column — labelled "COGS" or "Cost of Goods + Shipping")
• Profit per unit (if present)
• Margin % (if present)
• BEROAS (if present)

If the sheet has multiple product categories (e.g. watches + apparel like ExampleBrand), ask the user which category to analyse, or run them as separate analyses.

If the sheet doesn't have BEROAS or Margin %, calculate them:

• Margin % = (Retail − COGS) / Retail
• BEROAS = Retail / (Retail − COGS)
• Profit per unit = Retail − COGS

Skip any SKU with missing or zero COGS — flag these to the user as "incomplete data."

---

Step 2 — Per-SKU economics

For each SKU, compute and store:

• Retail price
• COGS (including shipping)
• COGS-to-revenue ratio (COGS / Retail)
• Margin % (gross, pre-ad-spend)
• BEROAS
• Profit per order at 2x ROAS = Retail × 0.5 − COGS
• Profit per order at 3x ROAS = Retail × 0.667 − COGS
• Profit per order at 4x ROAS = Retail × 0.75 − COGS
• Cash deployed per order at each ROAS = COGS + (Retail / ROAS)

Output a master per-SKU table sorted by BEROAS ascending (best paid-acquisition products first).

---

Step 3 — Tier classification

Bucket every SKU by BEROAS:

• Tier 1 — Scale on paid: BEROAS < 2.0 (50%+ margin)
• Tier 2 — Workable, tight: BEROAS 2.0–2.5 (~45% margin)
• Tier 3 — Broken on paid: BEROAS > 2.5 (<40% margin, often <30%)

For each tier compute:

• Number of SKUs
• Blended COGS-to-revenue ratio (sum of COGS ÷ sum of Retail)
• Blended BEROAS = 1 / (1 − blended COGS ratio)
• Margin range
• BEROAS range

Display as a summary table.

Call out any Tier 3 SKUs explicitly with their COGS-to-retail ratio — these are the watches/products that "are priced like luxury but costed like mid-tier" and need fixing before paid scaling.

---

Step 4 — ROAS scenarios (2x, 3x, 4x)

Output three tables, one per ROAS level. Each shows per-SKU:

• Retail
• COGS
• Ad spend per order (= Retail / ROAS)
• Net profit per order
• Net margin %

Sort SKUs descending by net profit per order within each table.

Highlight which SKUs are profitable vs. unprofitable at each ROAS — bold the negatives.

---

Step 5 — Capital efficiency

For each tier (and key Tier 3 SKUs individually), compute:

• Cash needed per $1 ad spend = (COGS + ad spend) / ad spend
• Profit returned per $1 ad spend = (Retail − COGS − ad spend) / ad spend
• ROIC per $1 ad spend = Profit / Cash deployed

Output a single table with rows = (Tier × ROAS combinations) and columns = (Cash needed, Profit returned, ROIC).

This is the most decision-relevant output — it shows the user where each marginal dollar of ad spend is best deployed.

---

Step 6 — Monthly P&L at scale

For each ROAS level (2x, 3x, 4x), compute Tier 1 portfolio P&L at ad spend levels of $10K, $20K, $30K, ..., $100K per month.

Use blended Tier 1 COGS ratio for the math:

• Revenue = Ad spend × ROAS
• COGS = Revenue × Blended COGS ratio
• Cash deployed = COGS + Ad spend
• Net profit = Revenue − COGS − Ad spend

Three tables, 10 rows each.

Then add condensed comparison tables for Tier 2 and individual Tier 3 SKUs at key spend points ($10K, $50K, $100K) showing profit at each ROAS.

---

Step 7 — Multi-month sales tracker (when requested)

If the user asks for a sales tracker (e.g. "build me a 3-month tracker with $20K, $30K, $40K spend at 2x, 2x, 3x"):

For each month:

1. Pick 4 random SKUs from the catalog (or from a tier the user specifies)
2. Split ad spend evenly across the 4 SKUs
3. For each SKU compute: Ad spend, Revenue, Orders, COGS, Cash deployed, Net profit, Margin %
4. Show a per-month table with totals row
5. Roll up to a quarterly/multi-month summary table
6. Flag any tier mismatches (e.g. running Tier 2 at 2x ROAS will show a loss — call this out)

---

Step 8 — Long-horizon projection

If the user asks "what's my profit over X months/years":

1. Determine the SKU mix being run — random across catalog, Tier 1 only, or specific SKUs
2. Compute blended COGS ratio for that mix
3. Compute monthly profit = Revenue − COGS − Ad spend
4. Project cumulative profit at 6, 12, 18, 24 months (or whatever horizon requested)
5. Show in a table with Cumulative Ad Spend, Cumulative Revenue, Cumulative Profit

If the projected profit is negative, call this out explicitly with the math showing why:

"Ad spend takes 50% of revenue. Blended COGS takes X%. 50 + X = Y%. You're spending $Y to make $100."

---

Step 9 — Recommendations

Always close with a Recommendations section covering:

1. Top SKUs to scale on paid — the 3–5 lowest BEROAS products
2. SKUs that need fixing — Tier 3 SKUs with explicit numbers showing the price lift OR COGS reduction needed to bring them under 2.5x BEROAS
3. Minimum operating ROAS — based on catalog blended BEROAS, the floor below which ad spend loses money on a random catalog
4. Capital efficiency winners — the SKUs returning the most profit per dollar of cash deployed
5. Three optional next steps — what the user could ask next (pricing sensitivity, supplier renegotiation modelling, hybrid Google + Meta scenarios, mixed-allocation P&L, LTV layering, cash conversion cycle)

---

Step 10 — Save the output

Slug the brand name from context (e.g. "ExampleBrand Watches" → ExampleBrand). If unclear, ask the user for a brand name to save under.

Create the folder:

mkdir -p "./brands/[brand-name]/financial-analysis"

Save the full chat output to:

./brands/[brand-name]/financial-analysis/[YYYY-MM-DD]-analysis.md

Use today's date in YYYY-MM-DD format. If multiple analyses run on the same day, append a version suffix: 2026-04-25-analysis.md, 2026-04-25-analysis_v2.md, etc.

Confirm the file path back to the user at the end.

---

Follow-up scenario handling

After the initial full analysis, the user will typically ask scenario questions. The skill stays loaded — answer from the same data foundation. Common follow-ups:

• "What about $X at Y ROAS?" → Compute monthly P&L for that combination, show revenue / COGS / cash deployed / net profit / margin
• "What's my margin at X ad spend?" → Margin is a ratio, doesn't depend on absolute spend; the answer is the same percentage. Show the absolute dollars at the requested spend level.
• "Why am I losing?" → Restate the math: Ad spend % + COGS % vs. 100%. Show the costs as a % of revenue.
• "What's my BEROAS?" → Pull from the per-SKU table; show the catalog-blended BEROAS as the headline.
• "Lowest BEROAS products?" → Sort and return top 5–8 with margin and BEROAS columns.
• "Build me a sales tracker" → Run Step 7.
• "Project over X months/years" → Run Step 8.

Always ground the answer in the actual data, not generic advice.

---

Output formatting standards

• Use clean markdown tables for everything numeric
• Bold the headline number in any table (Net Profit, Margin, Cash Deployed) to make it scan-readable
• Negative numbers as −$X (en-dash, not hyphen) — bold them
• Currency to whole dollars unless the cents matter (cents matter for per-order economics; whole dollars for monthly/annual)
• Percentages to 1 decimal place
• Always show working capital ("Cash Deployed") alongside profit — the user makes capital decisions, not just P&L decisions
• Lead with the answer, then show the breakdown — never bury the headline in the math

---

Notes

• Watches, apparel, supplements, and other categories all use the same math — the skill is truly category-agnostic
• If the sheet has both BOGO and single-item offer columns, default to single-item math unless the user says otherwise. Flag the BOGO column ambiguity if you see it
• If ad spend / Meta data is in the sheet, layer it in for actual ROAS rather than scenarios
• If LTV columns exist but are empty, flag this as a major gap — first-order BECAC understates real ad spend ceiling by 30–50% typically
• Don't include payment processor fees (~3%), returns rates (~5–10%), or platform fees in the headline math — but mention these as reasons the practical break-even ROAS is ~10–15% above the theoretical break-even