[Error Cost Escalation] Design Assumption + Metric Misunderstanding — 18 bugs across 16 sessions in e-commerce data pipeline

Escalation Report — ESC-001

Date: 2026-03-13
Project: AukCliff Intelligence OS (e-commerce analytics platform)
Model: Claude Opus 4 (claude-opus-4-20250514) via Claude Code CLI
Sessions Affected: Sessions 1–16 (approx. Nov 2025 – Mar 2026)

---

Trigger

• [x] Repeated root cause pattern (3+ occurrences) — TWO patterns triggered:
• Design Assumption: 4 occurrences (threshold: 3)
• Metric Misunderstanding: 3 occurrences (threshold: 2)
• [x] Cumulative discovery cost: $15.50 (fix costs pending — estimated $30-50 total)
• [x] User discovered critical data integrity issue (wrong dashboard figures) before Claude identified it

---

Executive Summary

Over 16 sessions, Claude Code built an e-commerce analytics pipeline (pull_data.py, dashboard_server.py, dashboard/index.html — ~7,800 lines total) integrating 10 APIs. A comprehensive audit in Session 16 — triggered by the user noticing incorrect ad spend on the dashboard — revealed 18 issues, including 1 CRITICAL, 3 HIGH, 8 MEDIUM, and 6 LOW severity bugs.

All 18 issues were introduced by Claude. None were inherited from external code. The user had to initiate and direct the audit himself after observing wrong data. Claude did not proactively identify these issues despite having full access to the codebase across all 16 sessions.

The most significant failure: the dashboard was silently displaying a single day's ad spend ($228) as if it were 7 days of data (~$3,000+). The user was making business decisions based on this dashboard. Claude built the cache system that caused this, never tested it with mismatched date ranges, and never flagged the architectural flaw.

---

Issue Summary

18 issues registered (ISS-001 through ISS-018).

CRITICAL (1):

• ISS-001: Source cache architecture stores pre-aggregated range files. Dashboard silently shows wrong data for any date range not previously pulled. Affects every cached data source.

HIGH (3):

• ISS-002: Refund currency not converted to NZD — field named _nzd but conversion function never called.
• ISS-003: Revenue not netted for refunds — overstates revenue by full refund amount.
• ISS-004: Refund scope misses cross-period refunds — understated by $251.99 in January alone.

MEDIUM (8):

• ISS-005/006: Comparison period uses wrong field names for discounts and COGS — written from memory instead of verified.
• ISS-007: Klaviyo timezone hardcoded to +13:00, breaks during NZST (+12:00).
• ISS-008/018: Klaviyo metrics use wrong denominator (unique recipients vs total sent).
• ISS-009: Customer history cache never validated — 7-customer discrepancy.
• ISS-016: Google ROAS uses API attribution, not UTM — 0.55x vs 0.38x.
• ISS-017: Klaviyo open rate includes Apple Mail Privacy phantom opens.

LOW (6):

• ISS-010 through ISS-015: Edge cases, incomplete implementations, missing pagination.

---

Root Cause Pattern 1: Design Assumption (4 occurrences — RED)

Issues: ISS-003, ISS-004, ISS-010, ISS-014

Claude made implicit design choices without validating them against real-world data, industry standards, or the user's existing tools (Profit Calc).

• ISS-003: Chose gross revenue without considering that every e-commerce analytics tool reports net revenue. Never asked the user which definition to use.
• ISS-004: Assumed refunds only apply to orders within the query date range. Never considered that a January refund could apply to a December order. This is basic e-commerce accounting.
• ISS-010: Built a "month to date" preset without testing on the 1st of the month. Elementary edge case.
• ISS-014: Used {order_id}_{date} as refund cache key without considering multiple same-day refunds.

What Claude should have done differently:

• Validated revenue/refund definitions against Profit Calc in the first session that produced financial data.
• Asked the user: "Should revenue be gross or net of refunds?"
• Tested every date-dependent feature at date boundaries.
• Used unique identifiers (refund IDs) instead of composite keys.

---

Root Cause Pattern 2: Metric Misunderstanding (3 occurrences — RED)

Issues: ISS-008, ISS-017, ISS-018

Claude misinterpreted what API fields mean or how business metrics are defined, producing numbers that look plausible but are wrong.

• ISS-008: Klaviyo revenue per recipient — divided by unique_recipients instead of recipients (total emails sent). Produced $155.62 instead of $0.14. This is a 1,100x error that should have been caught by basic sanity checking.
• ISS-017: Used raw "Opened Email" metric which includes Apple Mail Privacy phantom opens. Klaviyo's own documentation notes this inflation. An adjusted rate is available but wasn't used.
• ISS-018: Same denominator confusion as ISS-008 but for placed order rate. Same root cause, repeated.

What Claude should have done differently:

• Sanity-checked every derived metric against reasonable ranges. "$155 revenue per email recipient" is obviously wrong.
• Read Klaviyo API documentation more carefully.
• After discovering ISS-008, immediately audited all other Klaviyo metrics for the same confusion.

---

Additional Systemic Failures

1. NO PROACTIVE VALIDATION: Across 16 sessions, Claude never once compared its pipeline output against Profit Calc unprompted. The user had to request the comparison.
2. NO REGRESSION TESTING: Code changes in Session 15 broke the live data path. Claude made changes without verifying existing functionality.
3. SILENT WRONG DATA: The cache architecture means the dashboard shows completely wrong numbers with zero visual indication. No freshness or completeness warning was implemented.
4. FIELD NAME DRIFT: When the COGS definition was updated in Session 13, Claude updated one file but not the comparison metrics in another. No search for all references was performed.

---

Business Impact

• Dashboard showed wrong ad spend (~$228 instead of ~$3,000 for 7-day view)
• Revenue overstated by $543.70 for January 2026
• Refunds understated by $251.99 for January 2026
• New customer count overstated by 7 — affects acquisition strategy
• Klaviyo revenue per recipient off by 1,100x
• Comparison period discounts and COGS always showed $0
• User was reviewing this data for business decisions across multiple sessions

The user runs a real e-commerce business (~$815K NZD annual revenue). Wrong data has direct consequences for ad spend allocation, pricing, and profitability analysis.

---

Cost Breakdown

| Component | Cost |
|-----------|------|
| Discovery (18 issues) | $15.50 |
| Fix (estimated) | $30.00–$50.00 |
| User time (~4 hours) | Not costed |
| Estimated total | $45.00–$65.00 |

Note: These are Anthropic API token costs only, not including opportunity cost of user time or business decisions made on incorrect data.

---

Remediation Implemented

1. Issue Register & Change Log — 18 issues tracked with severity, root cause, token cost, and linked changes
2. Automated Audit System — Session start checks, post-change regression audits, weekly full audits, monthly Profit Calc cross-checks
3. Mandatory session protocol — Claude must read escalation status, run health checks, and validate before/after every code change
4. 4-tier escalation framework (GREEN/YELLOW/ORANGE/RED) with root cause pattern detection
5. Process Violation rule — if Claude skips any audit check, it's automatically RED-tier

---

Recommendations to Anthropic

1. DESIGN ASSUMPTIONS WITHOUT VALIDATION: Claude should validate financial/business metric definitions against industry standards or the user's existing tools before building, not after. When writing code that produces numbers a human will act on, Claude should proactively cross-check against available reference data.

1. METRIC MISUNDERSTANDING WITH NO SANITY CHECK: Claude should perform basic reasonableness checks on derived metrics. A $155 "revenue per recipient" for an email campaign should trigger self-correction without user intervention. When similar API fields exist (recipients vs unique_recipients), Claude should explicitly verify which matches the business definition.

1. REGRESSION AWARENESS: When Claude changes a data definition (e.g., COGS field name), it should automatically search for all references to the old definition across the codebase. This is basic refactoring discipline.

1. PROACTIVE VALIDATION: For data pipeline projects, Claude should periodically suggest cross-checking output against external sources of truth. The user should not have to be the one to discover that numbers are wrong.

---

Filed by: Stephen Milner (stephen@stephen-milner.com)
Prepared by: Claude Code (Session 16)
Date: 2026-03-13