Amazon Reconciliation Dashboard

Build a complete, single-file interactive HTML dashboard that reconciles 6 months of Amazon Order Line Items against Settlements & P&L Components, using the DataDoe MCP toolchain. The dashboard must be functional after a single execution — the user opens one HTML file in a browser and gets KPIs, charts, a daily table, and an Order Explorer with a month selector wired across all sections.

API docs: https://api.datadoe.com/api/v1/docs Data scheme: https://api.datadoe.com/api/v1/spec/data-scheme

Agent Type Detection

Determine your agent type before starting:

• MCP-enabled assistant (Claude Desktop, ChatGPT with MCP, Cursor with MCP, etc.): follow this entire skill — all data is pulled live through DataDoe MCP tools. This is the primary supported path.
• Low-level coding agent without MCP (Cursor, Claude Code, Codex, Aider in environments without DataDoe MCP): substitute the equivalent DataDoe REST endpoints described in the REST Fallback section. The dashboard output is identical.
• High-level vibe coding assistant (Lovable, Base44, Bolt, v0, etc.): ignore the tech stack section entirely; use whatever the platform provides. Follow only the Dashboard Features, DataDoe Data Integration, and Implementation Rules sections.

Dashboard Features

1. Month Selector (Sticky Header)

• Always visible at the top of the dashboard while scrolling.
• Renders a prominent pill-group or styled <select> showing all 6 months in the window (e.g., Sep 2025 | Oct 2025 | Nov 2025 | Dec 2025 | Jan 2026 | Feb 2026).
• Includes an "All 6 Months" option for the aggregate view.
• The most recent month is selected by default.
• Changing the selection instantly updates every section below: KPI cards, trend chart, daily overlay, donut, waterfall, daily table, and Order Explorer.
• Visually prominent: large styled buttons or a pill-group — not a tiny native dropdown.

2. Summary KPI Cards

A grid of cards that recompute on every month change:

• Shipped Orders | Settled Orders | Order Gap
• Order Revenue | Settled Revenue | Revenue Gap (all in the account currency). Settled Revenue is the gross settled value (item_price + item_tax on ORDER rows) so it is like for like with the VAT-inclusive item_price_value; Revenue Gap = Order Revenue - Settled Revenue on that basis.
• Total Refunds (count + amount) | Cancelled Orders | Reconciliation Rate (%)
• When "All 6 Months" is selected, show aggregate totals across the full window.

3. Monthly Trend Mini-Chart

• Visible only when a single month is selected.
• Small sparkline or bar chart showing the selected KPI (default: shipped orders) across all 6 months, with the currently selected month highlighted.
• Lets the user see the trend at a glance without switching months.

4. Daily Overlay Chart

• Grouped bar chart with three data layers per day:
  • Amber/yellow bars: daily shipped order count.
  • Red/coral bars: daily ORDER-type settlement count.
  • Blue line with markers: daily refund count (secondary Y axis).
• Toggle buttons switch between "Order Counts" and "Revenue" views.
• Legend explains each colour.
• When "All 6 Months" is selected, show monthly aggregates instead of daily.

5. Reconciliation Status Donut

• Green: Settled (orders with a matching ORDER settlement entry).
• Amber: Pending (shipped, no settlement yet).
• Red: Cancelled (no settlement expected).
• Blue: Refunded.

6. Revenue Waterfall

• Bars: Gross Revenue → + Tax (item_tax) → - Tax withheld by Amazon (marketplace_facilitator_vat, stored negative) → - Referral Fees → - FBA Fees → - Refunds → - Other → = Net Payout.
• On UK/EU accounts marketplace_facilitator_vat offsets + Tax almost exactly, so show it as its own bar next to Tax (or net the two into one "Tax net of withholding" bar). Never leave it inside "Other" — there it silently cancels the tax the waterfall just added and dominates the "Other" bar.
• Green bars for positive contributions, red for deductions, blue for the net total.

7. Daily Summary Table

• One row per day. Sortable.
• Columns: Date | Orders | Settled | Gap | Order Rev | Settled Rev | Rev Gap | Refunds | Refund Amt | Net Total | Status.
• Settled Rev is gross (item_price + item_tax on ORDER rows) so Rev Gap compares like for like with the VAT-inclusive item_price_value.
• Status dot: green (gap ≤5), amber (gap ≤20), red (gap >20).
• Scrollable with sticky header.

8. Order Explorer (Critical Section)

The most prominent and functional section. Every order across all 6 months in a single client-side table.

Columns:

Required interactive features:

• Synced with month selector: when a month is selected in the header, the Order Explorer auto-filters to that month's orders. "All 6 Months" shows everything.
• Global text search: live filter as the user types — searches Order ID and all text fields. Debounce ~200ms.
• Column filters (dropdowns above the table):
  • Status: All / Shipped / Canceled / Pending. Spell Canceled (not Cancelled). Some marketplaces return Shipped / Unshipped with a marketplace-specific prefix (Shipped* / Unshipped*), so bucket those two with a beginsWith/contains match (client-side startsWith, export filter operator beginsWith) rather than exact equality.
  • Channel: All / Amazon / Merchant.
  • Recon Status: All / Settled / Refunded / Pending / Cancelled.
  • B2B: All / Yes / No.
  • Cross-Month: All / Same Month / Cross-Month Only.
• Date range filter: From/To date pickers.
• Sortable columns: click any header to sort asc/desc with a sort-indicator arrow.
• Pagination: 50 rows per page with Prev / 1 2 3 ... / Next.
• Row count display: "Showing X of Y orders (Z filtered)".
• Row colour coding (subtle background tint by recon status):
  • Settled: faint green | Refunded: faint blue | Pending: faint amber | Cancelled: faint red.
• Click-to-copy Order ID with a brief toast notification.
• Export filtered results: a button that downloads the currently visible rows as CSV.

Implementation notes:

• Embed the full 6-month merged order data as a JavaScript array inline in the HTML.
• All filtering, sorting, and pagination happen client-side.
• Use efficient DOM updates — rebuild only the visible page, not the entire table.
• Default sort: Order Date descending (newest first).
• Default filter: selected month's orders, all statuses.

9. Global Interactive Behaviour

• The month selector controls every other section simultaneously.
• Clicking an overlay-chart bar highlights the corresponding row in the Daily Summary Table.
• Hover tooltips show the full breakdown.
• Toggle buttons work for the counts vs revenue view.
• Smooth scroll between sections via in-page navigation.

10. Loading & Error States

• Show a progress message during MCP discovery, export polling, and download.
• Show user-friendly error messages on MCP tool failures, export ERROR status, or empty datasets.
• On poll timeout (10 attempts × 5s), surface a clear error and abort rather than retrying indefinitely.

DataDoe Data Integration

MCP-First Workflow

The dashboard is generated by chaining DataDoe MCP tool calls. The same data shape and rules apply if you fall back to REST — the only thing that changes is the transport.

Phase 1: Discovery

1. Review the DataDoe usage instructions.

• The DataDoe MCP server sends its usage instructions with the connection, so you already have them when the session starts. They describe seller/vendor scoping, rate limits, and required fields — do not skip them.
• If you need more detail on a specific feature, call datadoe_user_docs_table_of_contents_get to list the documentation pages, then datadoe_user_docs_page_get to read one.

2. List sellers/vendors.

• Call sellers_and_vendors_list.
• If only one seller exists, use it automatically.
• If multiple sellers exist, ask the user which one to use (show names).
• Save the sellerOrVendorId (UUID).

3. Determine the time period.

• The dashboard always covers 6 full calendar months.
• If the user says "February" or "last month", calculate the 6-month window ending with that month. E.g., "February 2026" → September 2025 through February 2026.
• If the user just says "generate my dashboard" with no month, use the most recent 6 full calendar months from today.
• Calculate the overall from (1st of the earliest month, 00:00:00.000Z) and to (last day of the latest month, 23:59:59.999Z).
• Also calculate the explicit list of months in the window (e.g., ["2025-09", "2025-10", "2025-11", "2025-12", "2026-01", "2026-02"]) — required for the month selector.

Phase 2: Data Export

1. Get available export sources.

• Call exports_sources_get with the sellerOrVendorId.
• Find and save the source IDs for:
  • "Order Line Items" (table: amazon_order_items_with_cogs) - line-item grain: one row per order item, NOT pre-aggregated per order.
  • "Settlements & P&L Components" (table: amazon_settlements_with_cogs).

2. Create Daily Summary exports (these cover the full 6-month range — small enough to fit in one export each):

• Daily Summary — Orders: ~180 days × ~4 status combos = ~720 rows.
• Daily Summary — Settlements: ~180 days × 3 types = ~540 rows.

3. Create Order-Level Detail exports (for the Order Explorer):

• Provide individual order granularity across all 6 months.
• Every export is capped (REST and MCP alike): 1,000 rows as JSON, 5,000 rows as CSV. A monthly batch never fits — a mid-size seller produces ~1,500 order lines a day (~45k a month, plus a similar number of settlement rows).
• Size the batches from the Daily Summary counts you already have (lines per day), then batch by day (by week only for small sellers whose weekly volume stays under the cap) and page each batch with limit / skip (skip = 0, limit, 2 × limit, …) until a page comes back with fewer rows than limit. A page with exactly limit rows means more rows remain — treat an unpaged export that hits the cap as truncated, never as complete.
• Apply the same day/week batching to the order-matched settlement rows (filter settlement_type in ORDER, REFUND), keyed by posted date.

4. Poll and download ALL exports.

• For each export, check status via exports_get.
• If not COMPLETED, wait 5 seconds and poll again (up to 10 attempts, ~50s total per export).
• When COMPLETED, call exports_raw_download to get the data.
• Parse the JSON rawContent from each export.
• Combine all batched order-level results into one unified orders array.
• Combine all batched settlement-level results into one unified settlements array.
• Order Line Items are line-item grain (one row per item): group by amazon_order_id and sum quantity / item_price_value / item_tax_value to get order-level values. Also deduplicate by amazon_order_id across overlapping batches.

Phase 3: Analysis

All data must be organised per calendar month so the dashboard can switch between months instantly.

A) Daily Summary processing (for charts and daily table):

For each of the 6 months, filter the daily summary data to that month's dates and aggregate.

Orders — group by date:

• Sum shipped orders, cancelled orders, pending orders.
• Calculate daily shipped revenue (sum of item_price_value) and tax (sum of item_tax_value).
• Track Amazon vs Merchant fulfillment_channel breakdown.

Settlements — group by date, separated by settlement_type:

• ORDER entries: count, item_price, item_tax (gross settled revenue = item_price + item_tax), marketplace_facilitator_vat (VAT withheld by Amazon on UK/EU, negative), referral_fee, FBA fees (sum every fba_* fee column), total. (Fees are negative.)
• REFUND entries: count, refunded amount, total.
• OTHER entries: total (fees, reimbursements, etc.).

B) Order-Level reconciliation (for the Order Explorer):

For each unique amazon_order_id across all 6 months:

1. Record which month the order belongs to (by orders date).
2. Look up whether it has a matching settlement entry (by amazon_order_id in settlement exports).
3. Note that the settlement date (posted date) may be in a different month than the order date — this is normal and critical for cross-month reconciliation. Settlements also carry order_date for the original purchase date.
4. Assign a reconciliation status:
   • Settled → order has a matching ORDER settlement entry.
   • Refunded → order has a matching REFUND settlement entry.
   • Settled + Refunded → order has both (partial refund).
   • Pending → shipped order with no settlement match (expected for recent month-end orders).
   • Cancelled → amazon_order_status is Canceled (no settlement expected).
5. Calculate per-order:
   • Order side (aggregate line items per amazon_order_id): revenue (sum item_price_value), tax (sum item_tax_value), quantity (sum quantity).
   • Settlement side: settled revenue = sum of item_price + item_tax on ORDER rows (gross), fees (referral + FBA), refund amount = sum of refunded_amount + refund_tax on REFUND rows (gross), net total.
   • Delta: order revenue - settled revenue, like for like: item_price_value is VAT-inclusive on UK/EU marketplaces while settlement item_price is the ex-VAT principal with the VAT in item_tax, so comparing item_price_value with item_price alone reports a permanent ~17% "gap" (£3,716 instead of £0.01 on a two-day sample where 2,992 of 3,007 matched lines satisfy item_price_value == item_price + item_tax). Equivalent ex-VAT form: compare item_price_value - item_tax_value with item_price.
6. Flag cross-month orders: if the settlement date month differs from the order date month, tag the row as "cross-month settlement".

C) Reconciliation logic — business rules:

• Settlement entries use "posted-date" (when Amazon processed them); orders use "purchase-date" (when the customer ordered).
• Orders placed at month-end frequently settle in the next month — the 6-month window makes this visible instead of hiding it.
• Cancelled orders should NOT be expected in settlements.
• The order count gap between Orders and Settlements is normal due to date-shifting and MCF orders.

D) Summary KPIs (per month AND overall 6-month totals):

• Total shipped orders vs total settled orders (and gap).
• Total order revenue vs total settled revenue (and gap) — settled revenue is gross (item_price + item_tax), matching the VAT-inclusive item_price_value.
• Total refund count and amount.
• Total cancelled orders.
• Net payout (sum of all settlement totals).
• Total fees breakdown: referral (referral_fee), FBA (sum the fba_* columns), tax withheld by Amazon (marketplace_facilitator_vat), other (total_non_principal_amount minus referral, FBA and marketplace_facilitator_vat; do not use the deprecated total_fees, removed 2026-10-31). Fees are negative — normalize signs.
• Reconciliation rate: % of shipped orders with a settlement match.
• Cross-month settlements count: orders placed in month X that settled in month Y.

Phase 4: Dashboard Generation

Generate a single, self-contained HTML file with the interactive dashboard:

• Chart.js 4.x loaded from CDN: https://cdn.jsdelivr.net/npm/chart.js@4.4.4/dist/chart.umd.min.js.
• All CSS inline.
• All 6 months of data embedded as inline JavaScript objects keyed by month (e.g., dashboardData["2026-02"]).
• Save the HTML file and present it to the user. If shell access is available, open it in the browser. Otherwise provide it as a downloadable file.

Data Shape — Required Fields

Order Line Items (table amazon_order_items_with_cogs; resolve the source id via /exports/sources) — LINE-ITEM grain; aggregate client-side per amazon_order_id:

Settlements & P&L Components (table amazon_settlements_with_cogs; resolve the source id via /exports/sources) — expenses are NEGATIVE values:

Sign convention: expenses (fees, refunds) are stored negative. Standardize signs before comparing with the orders table or building the Referral / FBA / Other waterfall (take absolute values for fee magnitudes; let total keep its sign for net payout).

For the full column reference, see: https://api.datadoe.com/api/v1/spec/data-scheme

CRITICAL — Export Request Rules (Discovered Through Testing)

• columns: [] (empty array) does NOT return all columns. It returns only seller-context metadata columns (seller id / name, marketplace, connection ids) and none of the table fields. Always specify the required columns explicitly. DataDoe prepends those utility columns before your requested fields, so access every field by name, never by position.
• Date range uses top-level from and to fields (ISO 8601 datetime strings like 2025-09-01T00:00:00.000Z) — not inside filters.
• filters.combinator uses lowercase values: "and" or "or" (not "AND" / "OR").
• Each filter rule must include the not field (boolean): { "field": "...", "operator": "...", "value": "...", "not": false }.
• If no filter rules are needed, either omit filters entirely or pass { "combinator": "and", "rules": [] }.
• sendToAllOrganizationMembers is required — set it to false for dashboard generation.
• Use outputType: "JSON" for every export.

Anti-Rate-Limit Guardrails (Mandatory)

DataDoe limits to 2 req/s per organisation. The reconciliation workflow creates multiple exports back-to-back, so pacing matters:

1. Global pacing: cap all DataDoe calls to max 1 request every 600ms (~1.67 req/s), leaving headroom under 2 req/s.
2. Serialize export creation: create exports one at a time, not in parallel.
3. Single in-flight poll per export: do not stack poll loops.
4. Respect Retry-After exactly on 429, then add incremental delay for subsequent retries.
5. Bounded poll loop: 10 attempts × 5s per export, then abort with a clear error.

REST Fallback (No-MCP Agents Only)

If the agent does not have DataDoe MCP access, substitute these REST endpoints — the data shape and rules are identical:

Auth: send datadoe-api-key: <key> (mutually exclusive with datadoe-organization-id). Always attach Content-Type: application/json and Accept: application/json. The same anti-rate-limit guardrails apply.

Design Requirements

• Dark professional theme: background #0f1729, cards #1e293b, text #e2e8f0.
• Rounded corners (12px), subtle shadows.
• System font stack: Inter, -apple-system, BlinkMacSystemFont, "Segoe UI", Roboto, sans-serif.
• All monetary values formatted in the currency of the data (map the ISO code to its symbol: GBP → £, EUR → €, USD → $, and so on) with thousand separators. Never hardcode one symbol - the account may not be a UK seller.
• Responsive grid layout.
• The month selector must be visually prominent (large styled buttons or a pill-group — not a tiny native dropdown).
• Order Explorer table uses alternating row shading for readability.
• Filter/search bar visually prominent with a search icon.
• Active filters render as coloured pills/badges that can be clicked to clear.
• Cross-month settlement badges are visually distinct (e.g., orange outline pill).
• Footer: "Data powered by DataDoe".

Important Business Context

Settlement reports and order reports will NEVER match exactly. This is expected. The dashboard exists to make the gaps visible and explainable, not to alarm the user about normal discrepancies.

Common reasons for gaps:

• Date shifting: orders placed on Jan 31 may appear in the Feb settlement; Feb 28 orders may settle in March.
• MCF / Multi-Channel Fulfillment orders: identify them by sales_channel (a non-Amazon value instead of Amazon.<tld>), not by price alone. They appear in settlements with fulfilment fees and may carry a zero or null item_price_value in the orders report.
• B2B deferred orders: business orders may be deferred for 30 days before settlement.
• Canceled orders: appear in orders (amazon_order_status = Canceled) but not in settlements.
• Refunds: separate REFUND settlement entries posted on their own date (typically 1–5 weeks after the order), but they carry amazon_order_id and order_date, so they can be attributed to the original order and order month.

The dashboard should help the user build confidence that the data is correct.

Implementation Rules

1. Single run: the dashboard must work after one execution. Do not leave TODOs or placeholders.
2. Read the DataDoe instructions resource first before any other DataDoe MCP call.
3. Always specify columns explicitly in every export — never pass columns: [].
4. 6 full calendar months is the fixed window — never less, never partial months.
5. Date defaults: from = first day of earliest month at 00:00:00.000Z; to = last day of latest month at 23:59:59.999Z.
6. Use outputType: "JSON" for every export.
7. Poll interval: 5 seconds per status check; abort after 10 attempts with a clear error.
8. Retry on 429 using Retry-After, with incrementally increasing delays.
9. Group orders by amazon_order_id before assigning reconciliation status — deduplicate across batches.
10. Cross-month settlements are normal — flag them visually, do not treat them as errors.
11. Embed all 6 months of data inline in the HTML — the dashboard must not require any runtime fetch.
12. All filtering, sorting, and pagination happen client-side in the Order Explorer.
13. Render with Chart.js 4.x via CDN (https://cdn.jsdelivr.net/npm/chart.js@4.4.4/dist/chart.umd.min.js) — no other charting library.
14. Self-contained output: one .html file. No external CSS, no separate JS, no build step.
15. Report the output file path explicitly to the user; open it in the browser if shell access is available.

Example User Interactions

Verification Checklist

After implementation, verify:

• The DataDoe instructions resource was fetched before any other MCP call.
• A sellerOrVendorId was resolved (auto if single seller, asked if multiple).
• Both source IDs were found: "Order Line Items" and "Settlements & P&L Components".
• All exports reached COMPLETED status and were downloaded.
• Order data was deduplicated by amazon_order_id.
• Each unique order received a reconciliation status (Settled / Refunded / Settled+Refunded / Pending / Cancelled).
• Cross-month settlements are flagged.
• The HTML file opens standalone and renders without console errors.
• The month selector switches every section (KPIs, charts, daily table, Order Explorer) on change.
• "All 6 Months" aggregates correctly.
• Order Explorer search, column filters, date range, sort, pagination, click-to-copy, and CSV export all work.
• Row colour coding by recon status is visible.
• Cross-month badges are visually distinct.
• All monetary values display in the data's currency (symbol derived from the ISO code, never hardcoded) with thousand separators.
• The output file path is reported to the user.