# CSV Upload Schema

When the owner doesn't have QuickBooks or PayPal connected, they can upload exported CSV files. This document specifies what columns to expect and how to handle variations.

## Revenue CSV (transactions export)

Expected columns (order doesn't matter; headers are case-insensitive):

| Column | Required | Description |
|---|---|---|
| `date` | Yes | Transaction date (any standard format: YYYY-MM-DD, MM/DD/YYYY, etc.) |
| `item` or `product` or `service` or `description` | Yes | What was sold |
| `amount` or `revenue` or `total` | Yes | Transaction amount (USD) |
| `quantity` or `qty` | No | Units sold — if missing, assume 1 per transaction |

**Exports that typically match this format:**
- PayPal: Activity → Download (CSV) — use "Transaction ID, Date, Name, Type, Status, Currency, Amount"
- Square: Reports → Transactions → Export
- Shopify: Orders → Export

If the export has more columns, ignore the extras. If a required column is missing, ask the owner which column maps to it.

## Cost CSV (expense or COGS export)

Expected columns:

| Column | Required | Description |
|---|---|---|
| `date` | No | Expense date (useful for trend analysis) |
| `item` or `product` or `service` or `category` | Yes | What the cost relates to |
| `amount` or `cost` or `expense` | Yes | Cost amount (USD) |
| `type` | No | COGS vs. operating expense — if absent, ask the owner |

**Exports that typically match this format:**
- QuickBooks: Reports → Profit & Loss Detail → Export
- Brex: Transactions → Export → Filter by expense category

## Handling messy CSVs

Real-world exports are messy. Common issues:

- **Extra header rows:** Skip rows until you find one that looks like column names
- **Currency symbols:** Strip `$`, `,` from numeric fields before parsing
- **Negative refunds:** Include them — they reduce net revenue
- **Mixed currencies:** Flag it and ask which currency to use; default to USD if unclear
- **"Gross" vs "Net" amounts:** Prefer net (after fees) for revenue; ask if unclear

## After loading

Confirm the data shape with the owner before proceeding:
- "I loaded X transactions from [date] to [date] across Y products. Does that look right?"
- If the date range or product count looks off, ask them to double-check the export filters.

# Example: Retail Boutique — Apparel

**Business:** Small women's clothing boutique, brick-and-mortar + Shopify
**Connectors:** QuickBooks (costs), PayPal (online sales)
**Data:** 14 months of transactions

---

## What the owner asked

"My wholesale costs have gone up a lot this year. I feel like I need to raise prices but I don't want to lose customers. Can you look at my margins?"

---

## Step 1: Pre-flight

`company-info` returns Industry: "Retail — Apparel". Pre-flight passes.

---

## Step 2: Scope clarification

Owner wants to analyze her top 5 SKU categories:
- Dresses
- Tops
- Denim
- Accessories
- Knitwear

Metric priority: gross margin % (she wants to understand profitability, not just revenue).

---

## Step 3–4: Data pulled

- **QuickBooks:** 14 months of COGS by product category
- **PayPal:** 14 months of transaction history (online sales only; in-store via Square separately)

---

## Step 5: Unit economics output

| Category | Revenue | COGS | Gross Profit | Margin % |
|---|---|---|---|---|
| Dresses | $42,800 | $18,200 | $24,600 | 57.5% |
| Tops | $31,500 | $15,100 | $16,400 | 52.1% |
| Denim | $18,900 | $11,300 | $7,600 | 40.2% |
| Accessories | $9,400 | $2,800 | $6,600 | 70.2% |
| Knitwear | $14,200 | $9,600 | $4,600 | 32.4% ⚠️ |

⚠️ Knitwear is flagged: 32.4% is below the 40–55% typical range for apparel retail.

---

## Step 6: Benchmarking

"For apparel retail, typical gross margins run 45–65%. Your portfolio average is 51%, which is within range. However, Knitwear at 32.4% is below the floor — this could indicate that wholesale costs for knitwear rose faster than your prices over the past year. Your overall cost base rose approximately 12% year over year, while revenue grew 7% — that 5-point gap is where the margin pressure is coming from."

---

## Step 7: Pricing scenarios — Knitwear (low-margin item)

Current average Knitwear price: $89. Current cost: ~$61.

| Scenario | New Price | Est. Revenue* | Gross Margin % |
|---|---|---|---|
| +5% ($93) | $93 | ~$14,560 (−3% vol) | 34.4% |
| +10% ($98) | $98 | ~$14,130 (−6% vol) | 37.8% |
| +15% ($102) | $102 | ~$13,700 (−8% vol) | 40.2% |

*Volume estimates based on 14-month elasticity: historical data shows ~0.6 volume drop per 1% price increase for this category.

Note: "These projections use your 14-month history as a guide. Actual response depends on your customers and what competitors are charging for similar items."

---

## Step 8: What was presented

The owner received the full unit economics table, the benchmarking paragraph, and the knitwear scenario table. She noted that Accessories was the standout performer (70% margin). The analysis did not recommend a course of action — it surfaced that knitwear was the category most worth evaluating for a price adjustment.

---

## Notes for skill author

- This example shows graceful handling of mixed data sources (PayPal online + Square in-store noted as limitation)
- Elasticity was computable because the owner had raised Knitwear prices once 8 months ago (from $79 → $89) and volume dipped 5% the following month — that's the one-data-point limitation referenced in gotchas.md

# Example: Professional Services — Marketing Consultant

**Business:** Solo marketing consultant, project-based engagements
**Connectors:** QuickBooks (invoices + expenses), PayPal (payment collection)
**Data:** 9 months of transactions

---

## What the owner asked

"I feel like I'm working more but not making more money. Can you look at whether I'm charging enough for each of my services?"

---

## Step 2: Scope clarification

Owner offers three service types:
- Brand strategy (fixed-fee, $2,500/project)
- Monthly retainer (ongoing, $1,200/month)
- Ad campaign management (% of spend, variable)

Metric priority: revenue per hour (she wants to know where her time is best spent).

Note: Revenue per hour requires the owner to provide estimated hours. Ask: "For each service type, roughly how many hours does a typical engagement take?"

Owner estimates:
- Brand strategy: ~20 hours
- Monthly retainer: ~8 hours/month
- Ad management: ~6 hours/month

---

## Step 5: Unit economics output

| Service | Revenue (9 mo) | COGS* | Gross Profit | Margin % | Rev/Hour |
|---|---|---|---|---|---|
| Brand strategy | $17,500 (7 projects) | $1,400 (tools, contractors) | $16,100 | 92% | $125/hr |
| Retainer | $21,600 (20 months) | $900 (tools) | $20,700 | 96% | $150/hr |
| Ad management | $14,200 | $600 | $13,600 | 96% | ~$118/hr |

*COGS here is direct expenses (software tools, occasional contractor help) — owner's own labor is excluded from COGS per accounting convention but captured in the Rev/Hour column.

---

## Step 6: Benchmarking

"For professional services, gross margins of 60–80% are typical. Your margins are 92–96%, which reflects a low direct cost structure. The more meaningful question is revenue per hour: at $118–$150/hr, you're in a solid range for independent marketing consultants. However, if your effective hourly rate (after all business expenses, not just COGS) is lower, the picture changes. To get that number, divide your net income by total hours worked."

---

## Step 7: Pricing scenarios — Brand Strategy (lowest revenue/hour)

Current rate: $2,500/project. Owner's estimate: 20 hours average.

| Scenario | New Rate | Est. Projects* | Revenue | Rev/Hour |
|---|---|---|---|---|
| +10% ($2,750) | $2,750 | ~6.5 (−7% vol) | $17,875 | $138/hr |
| +20% ($3,000) | $3,000 | ~6 (−14% vol) | $18,000 | $150/hr |
| +30% ($3,250) | $3,250 | ~5.5 (−21% vol) | $17,875 | $159/hr |

*Volume estimates are rough — only 7 brand strategy projects in 9 months is a small sample. These numbers should be treated as directional, not precise.

"If the 9-month pattern holds, a 20–30% rate increase on brand strategy projects might generate similar or slightly higher revenue with fewer projects — freeing up hours you could use for retainers (your highest-margin service)."

Note: This surfaces data, not a recommendation. The owner decides.

---

## Notes for skill author

- Service businesses often have near-100% gross margins on paper because they have minimal direct costs — the meaningful metric is revenue per hour or revenue per engagement
- Always ask for estimated hours before computing Rev/Hour; don't assume
- With only 7 brand strategy projects in 9 months, the sample is small — call that out explicitly

# Gotchas

## Gotcha: Treating revenue as profit

An owner sees $50K in PayPal transactions and thinks that's what they made. It isn't — it's what they collected.

**Why it matters:** If you surface revenue figures without immediately pairing them with costs, the owner may misread the analysis and feel reassured when they shouldn't be.

### ✗ Bad
"Your PayPal revenue last quarter was $50,000."

### ✓ Good
"Your PayPal revenue last quarter was $50,000. After direct costs of $31,000, your gross profit was $19,000 — a 38% gross margin."

---

## Gotcha: Using list price instead of effective price

Owners often have discounts, refunds, and promotions that reduce what they actually receive per transaction. PayPal transaction data reflects actual collected amounts, but QuickBooks invoices may show list price.

**Why it matters:** If you compute margins using list price but PayPal shows actual collections, your cost-per-unit math will look better than reality.

### ✗ Bad
Use invoice amounts from QB for revenue and PayPal costs for COGS → margin appears inflated.

### ✓ Good
Use PayPal/Square transaction amounts as the revenue source (actual collected), and QB for costs. Note any discrepancy between QB invoice totals and payment totals — it's worth surfacing.

---

## Gotcha: Elasticity from a single price change

A business raised prices once, volume dipped for a month, then rebounded. Declaring that as "a 5% increase causes 3% volume loss" is overfit to one event.

**Why it matters:** One data point isn't a pattern. Seasonal dips, external events, or marketing changes may have caused the volume move, not the price change.

### ✗ Bad
"In March 2024, you raised prices 8% and volume fell 4%. Elasticity = −0.5."

### ✓ Good
"In March 2024, you raised prices 8% and the following month showed a 4% volume drop. Note: this is a single observation — the actual relationship between price and demand likely varies. I'll use this as a rough guide and show a range of scenarios."

---

## Gotcha: Ignoring service delivery costs for service businesses

For product businesses, COGS is usually clear (materials, manufacturing). For service businesses, owners often undercount their real cost — especially their own labor.

**Why it matters:** A service business can show 80% gross margin on paper while the owner is effectively paying themselves nothing after accounting for time.

### ✗ Bad
A freelance designer reports $0 COGS because they have no physical materials. Gross margin shows 100%.

### ✓ Good
Ask service businesses: "For this service, what does it cost you in time and any direct expenses to deliver it? Including your own labor at a rough hourly rate?" Use that as COGS for the analysis.

---

## Gotcha: QuickBooks COGS not broken down by product/service

Many small businesses use QuickBooks but record COGS as a single line item, not broken out by product. `profit-loss-quickbooks-account` may not return item-level cost data.

**Why it matters:** You can't compute per-product margins if COGS is lumped together.

### ✗ Bad
Call `profit-loss-quickbooks-account` → Get total COGS $22,000 → Try to divide across 12 products → Numbers are meaningless.

### ✓ Good
If QB doesn't have item-level COGS, ask the owner: "QuickBooks has your total costs but not a breakdown by product. Do you have a rough sense of what each item costs you to make or deliver? Even ballpark figures work." Proceed with owner-provided figures and flag the limitation in the output.

---

## Gotcha: PayPal rate-limiting on repeated calls

Rapid repeated calls to `list_transactions` (e.g., iterating through multiple date ranges) can trigger PayPal's rate limiter.

**Why it matters:** Without a retry strategy, the skill fails mid-analysis.

### ✗ Bad
Call list_transactions in a loop → 429 error → skill crashes with no data.

### ✓ Good
Call `list_transactions` → if 429, pause 30 seconds → retry once → if the retry succeeds, continue normally, but treat any *second* 429 in the same session as a signal to stop retrying. After a second rate-limit event (even if separated by a successful call), immediately surface the fallback: "PayPal is rate-limiting repeated calls in this session. I can switch to Square for the revenue data, or you can upload a PayPal CSV export — either works. What would you prefer?" Do not attempt a third retry.

---

## Gotcha: Presenting scenarios as forecasts

The pricing scenario tables are math, not predictions. Volume elasticity is estimated from limited data, and real-world responses depend on competition, customer sensitivity, and timing.

**Why it matters:** An owner might act on the table as if it's a forecast, then feel misled when actual results differ.

### ✗ Bad
"If you raise prices 10%, you'll make $55,000 next quarter."

### ✓ Good
"If you raise prices 10% and volume drops ~5% (based on available history), revenue would be approximately $53,000. This is a rough projection — actual results will depend on your specific customers and competitive environment."

# Industry Gross Margin Benchmarks

Use these as context when explaining whether an SMB owner's margins are typical, above average, or concerning. These are rough ranges based on industry averages — not targets the owner must hit.

Always frame benchmarks as context, not judgment: "For your industry, gross margins typically run 40–55%. You're at 38%, which is slightly below average — here's what that means for your pricing scenarios."

## Benchmarks by category

| Category | Typical Gross Margin | Notes |
|---|---|---|
| **Retail — General** | 40–55% | Varies widely by product category |
| **Retail — Apparel / Fashion** | 45–65% | Higher for brands, lower for resellers |
| **Retail — Food & Grocery** | 20–35% | Thin margins; volume-dependent |
| **E-commerce** | 30–50% | Depends on fulfillment costs |
| **Restaurant / Food Service** | 60–70% | Gross margin on food; net is much lower after labor |
| **Professional Services** | 60–80% | Consulting, legal, accounting, design |
| **Skilled Trades** | 35–55% | Plumbing, electrical, HVAC; materials-heavy |
| **Cleaning / Maintenance Services** | 40–60% | Labor-intensive |
| **Health & Wellness** | 50–70% | Fitness studios, massage, coaching |
| **SaaS / Software** | 70–85% | Software delivery cost is low; high gross margins are expected |
| **Wholesale / Distribution** | 20–35% | Low margins; depends on volume and terms |
| **Manufacturing** | 25–45% | Varies by product complexity |
| **Construction** | 20–35% | Project-based; materials-heavy |

## How to use these

1. **Match the owner's industry** using the `company-info` profile from QuickBooks, or ask directly if it's unclear.
2. **State the range, not a single number.** Margins vary within categories.
3. **Flag when margins are well below the floor.** Under 20% gross margin in most service categories usually means the owner is underpricing or missing cost items.
4. **Don't cite a specific source.** These are general industry knowledge, not live data feeds.

## When benchmarks don't fit

Some SMBs run hybrid models (e.g., a yoga studio that also sells retail apparel). In those cases, segment the analysis if possible, or note: "Your business spans multiple categories — I'll use the benchmarks for each revenue stream separately."