rob/estack-laminas
1
1
Code Issues 169 Packages Projects 1 Wiki

Fix: SP-API Marketplace ID Scoping + Dashboard Card Data Consistency #20

New Issue
Open
opened 2026-06-29 08:36:09 +00:00 by rob · 1 comment
rob commented 2026-06-29 08:36:09 +00:00
Owner
Copy Link
Copy Source
No description provided.
rob added the Amazon SP-API label 2026-06-29 08:36:09 +00:00
rob commented 2026-06-29 09:38:33 +00:00
Author
Owner
Copy Link
Copy Source

Summary

All Amazon channel accounts in eStack are correctly configured with the right
marketplace IDs. However, the SP-API getOrders sync is not passing the
MarketplaceIds parameter on API calls. This causes every sync job to pull all
orders from the entire regional endpoint rather than the specific marketplace it
is responsible for. The result is that whichever channel's sync runs first claims
all orders for that region, leaving other channels with little or no data.

This is confirmed by auditing the channel accounts table — every marketplace ID
is correct — and by observing that Amazon Canada (24 orders in eStack) vs Seller
Central (239 orders, 30 days) shows exactly the pattern of a race condition where
the US sync claims the NA order pool first on most cycles.

Root Cause

Amazon SP-API uses three regional endpoints, each serving multiple marketplaces
under a single Selling Partner credential:

Region Endpoint eStack channels on this endpoint
North America sellingpartnerapi-na.amazon.com Amazon US, Amazon Canada, Amazon MX
Europe sellingpartnerapi-eu.amazon.com Amazon GB, Amazon DE, Amazon FR, Amazon IT, Amazon ES
Far East sellingpartnerapi-fe.amazon.com Amazon AU

Without MarketplaceIds scoped per call, getOrders returns orders from all
marketplaces accessible on that endpoint. Since US, CA, and MX share one NA
credential, a US sync with no filter pulls CA and MX orders too — and imports
them all as Amazon US. Same pattern applies across all five EU channels.

Confirmed Channel Account Configuration on Prima Instance

All nine accounts are correctly configured. No account setup changes are needed.

Name Region Marketplace ID Selling Partner ID
Amazon US US ATVPDKIKX0DER A3GNDTLYQCCHJP
Amazon Canada CA A2EUQ1WTGCTBG2 A3GNDTLYQCCHJP
Amazon MX MX A1AM78C64UM0Y8 A3GNDTLYQCCHJP
Amazon GB GB A1F83G8C2ARO7P A6B2MIY3HRTZQ
Amazon DE DE A1PA6795UKMFR9 A6B2MIY3HRTZQ
Amazon FR FR A13V1IB3VIYZZH A6B2MIY3HRTZQ
Amazon IT IT APJ6JRA9NG5V4 A6B2MIY3HRTZQ
Amazon ES ES A1RKKUPIHCS9HS A6B2MIY3HRTZQ
Amazon AU AU A39IBJ37TRP1C6 AK231R381GL07

Confirmed Discrepancies (30-Day Period)

These gaps are entirely explained by the missing MarketplaceIds filter:

Marketplace Seller Central eStack Gap Explanation
Amazon US 1,377 1,486 +109 over Absorbing CA and MX orders
Amazon GB 827 1,535 +708 over Absorbing DE, FR, IT, ES orders
Amazon AU 169 148 −21 under Timezone boundary only (see Priority 2)
Amazon CA 239 23 −216 under US sync claims NA pool first; CA gets the gap

CA orders are not missing from eStack — they exist but are attributed to Amazon
US. The same pattern applies to DE, FR, IT, ES orders appearing under Amazon GB.

The Orders by Country dashboard card confirms this: Germany shows 639 orders and
France 75 despite neither having orders attributed to their own channel — those
are EU marketplace orders imported under the GB channel and broken out by buyer
shipping address.

Fix 1 — Add MarketplaceIds to every getOrders call (Priority 1 — required)

This is the core fix. One parameter added to every getOrders API call, reading
the marketplace ID from the channel account record.

Current behavior (broken):

$params = [
    'CreatedAfter'   => $createdAfter,
    'OrderStatuses'  => ['Unshipped', 'PartiallyShipped', 'Shipped'],
];

Required behavior:

$params = [
    'CreatedAfter'   => $createdAfter,
    'OrderStatuses'  => ['Unshipped', 'PartiallyShipped', 'Shipped'],
    'MarketplaceIds' => [$channelAccount->getMarketplaceId()],
];

What to check:

  • Find every location in the SP-API integration where getOrders is called
  • Confirm MarketplaceIds is added to every call without exception
  • Confirm the value is read from the channel account record — never hardcoded
  • Confirm no call passes multiple marketplace IDs for a single channel sync

Expected outcome:

  • GB drops from 1,535 → ~827 (EU channels each get their own orders)
  • US drops from 1,486 → ~1,377 (NA channels each get their own orders)
  • CA rises from 23 → ~239 (CA sync now claims CA orders exclusively)
  • DE, FR, IT, ES each begin receiving their own orders correctly
  • Orders by Country card: Germany and France counts drop to reflect only
    buyers shipping to those countries from correctly attributed channels

Fix 2 — Historical order re-attribution migration (Priority 1 — ship with Fix 1)

Once Fix 1 is deployed, new orders will land in the correct channel. However,
all existing orders in the database are misattributed. Since SP-API returns
MarketplaceId on every order object and this value is already stored on each
order record, re-attribution is a single database operation.

This migration must run immediately after Fix 1 deploys, before the next
sync cycle, to prevent double-importing already-migrated orders.

-- Re-attribute orders to the correct channel account based on MarketplaceId
-- Run once as a migration immediately after the MarketplaceIds fix deploys

UPDATE orders o
JOIN channel_accounts ca
    ON ca.marketplace_id = o.marketplace_id
    AND ca.store_id = o.store_id
SET o.channel_account_id = ca.id
WHERE o.channel_account_id != ca.id;

Van to verify:

  • The orders table stores marketplace_id from the SP-API response
  • The channel_accounts table has a marketplace_id column (confirmed from UI)
  • The join column names above match the actual schema — adjust if different
  • Run on a database backup first to confirm row counts before applying to prod
  • Expected: ~700+ GB orders re-attributed to DE/FR/IT/ES; ~215 US orders
    re-attributed to CA

Fix 3 — AU timezone boundary (Priority 2)

Amazon AU (A39IBJ37TRP1C6) is under-counting by ~21 orders over 30 days (~12%).
AU is the only Far East endpoint channel and is not affected by the multi-
marketplace bleed issue — its gap is caused by date boundary calculation.

SP-API returns all timestamps in UTC. Amazon.com.au operates in AEST (UTC+10)
or AEDT (UTC+11). If eStack's dashboard window calculations apply any local
timezone offset when constructing CreatedAfter / CreatedBefore parameters,
the boundary will be misaligned for AU, losing approximately 1–2 days of orders
across the window edges.

What to check:

  • Confirm CreatedAfter is always calculated as NOW_UTC - N days with no
    local timezone conversion applied on top
  • Confirm this applies to both the sync service date params and the dashboard
    toggle query (7d, 30d, MTD) — both must use the same UTC-based boundary

Fix 4 — Pending order inclusion decision (Priority 2 — requires Rob input)

Seller Central's "All orders" tab includes Pending status orders. Canada's
Seller Central view shows recent orders with "Pending — Awaiting payment
verification" prominently. If eStack excludes Pending from sync, real-time
counts will always lag Seller Central, most visibly on CA and AU where recent
orders frequently sit in Pending state.

Option A — Match Seller Central (recommended):
Include Pending in sync and dashboard counts. eStack totals become directly
comparable to Seller Central at any point in time.

OrderStatuses to include:

Pending, Unshipped, PartiallyShipped, Shipped, InvoiceUnconfirmed

Option B — Operational orders only:
Exclude Pending. Dashboard shows only actionable orders. Counts will always
be lower than Seller Central by design.

Rob to confirm Option A or B before Van implements.

Once confirmed, the chosen OrderStatuses set must be defined in a single
constant or config value and used consistently across every getOrders call
and every dashboard query. No per-channel variation.

Fix 5 — % Sales by Channel pie card label (Priority 3)

The pie card and Orders by Channel card show contradictory percentages for the
same 30-day period. With the orders correctly attributed after Fixes 1 and 2,
the order counts will change significantly — run the pie card audit after
re-attribution is complete to determine whether the divergence persists.

If it does:

  • Confirm whether the pie is measuring revenue/GMV share or order count share
  • If revenue: rename card to "% Revenue by Channel — Last 30 Days"
  • If orders: confirm it queries the same table with the same status filters as
    the Orders by Channel card and investigate remaining divergence

Fix 6 — Resolve Unknown = 256 in Orders by Country (Priority 3)

256 orders over 30 days have no country attribution in the Orders by Country
card. After re-attribution (Fix 2), this number may change — audit after
migration before investigating further.

If Unknown remains significant:

  • Query orders where shipping_country is NULL or empty
  • For FBA orders where SP-API omits buyer PII, use the channel account's
    marketplace country as a fallback attribution
  • Check ShipStation-sourced orders for missing country field mapping

Fix 7 — Add last-synced timestamp to dashboard channel rows (Priority 4)

Amazon US currently shows "Synced 574 days ago" in Store Settings but this
staleness is invisible on the dashboard itself. A merchant looking at the
dashboard has no way to know the data is 18 months old.

Add a "Last synced X ago" indicator under each channel name in the Orders by
Channel card. Style with --es-muted for normal recency. For last sync

24 hours, add a visible warning indicator. For > 7 days, make it prominent.

Display-only change, no SP-API work required.

Files to Check

File Area Notes
SP-API order sync service getOrders call Add MarketplaceIds param reading from channel account
Database migration orders table Re-attribute channel_account_id by marketplace_id
Dashboard orders query Date range Confirm UTC-only boundary, no local TZ offset
Dashboard pie card Data source Audit after re-attribution — may self-correct
Orders by Country query Null country fallback Use marketplace country when ship-to missing
Dashboard channel card template Display Add last-synced per channel row

Reproduction Steps

  1. Open eStack dashboard, set Orders by Channel toggle to 30d
  2. Note: Amazon GB = 1,535 · Amazon US = 1,486 · Amazon CA = 23
  3. Open Seller Central per marketplace, Last 30 days:
    • Amazon.co.uk: 827 orders
    • Amazon.com: 1,377 orders
    • Amazon.ca: 239 orders
  4. Open any order attributed to Amazon GB in eStack — check Customer Info email
    domain. Orders with @marketplace.amazon.de, @marketplace.amazon.fr etc.
    confirm EU bleed into the GB channel
  5. Note Amazon Canada orders list shows only 24 orders with non-consecutive
    dates — consistent with sync race condition, not missing data

Sequencing — Critical

Fixes 1 and 2 must ship together in the same deployment:

  1. Deploy Fix 1 (MarketplaceIds filter) — stops new misattribution
  2. Immediately run Fix 2 migration (re-attribution) — corrects historical data
  3. Verify order counts per channel match Seller Central before next sync runs

Do not run the migration before Fix 1 is deployed — the next sync would
re-misattribute orders immediately after the migration completes.

Priority

P1 — Critical. Every Amazon channel in eStack has incorrect order counts
and attribution. Canada is showing 10% of actual orders. GB is showing 85% more
orders than it should. The fix is a single parameter addition to one API call,
plus a one-time database migration. These should be the next items Van works on.

Fix 3 (AU timezone) and Fix 4 (Pending — pending Rob's decision) can ship in
the same PR or a follow-up. Fixes 5–7 are lower priority and can follow
separately.

## Summary All Amazon channel accounts in eStack are correctly configured with the right marketplace IDs. However, the SP-API `getOrders` sync is not passing the `MarketplaceIds` parameter on API calls. This causes every sync job to pull all orders from the entire regional endpoint rather than the specific marketplace it is responsible for. The result is that whichever channel's sync runs first claims all orders for that region, leaving other channels with little or no data. This is confirmed by auditing the channel accounts table — every marketplace ID is correct — and by observing that Amazon Canada (24 orders in eStack) vs Seller Central (239 orders, 30 days) shows exactly the pattern of a race condition where the US sync claims the NA order pool first on most cycles. --- ## Root Cause Amazon SP-API uses three regional endpoints, each serving multiple marketplaces under a single Selling Partner credential: | Region | Endpoint | eStack channels on this endpoint | |---|---|---| | North America | `sellingpartnerapi-na.amazon.com` | Amazon US, Amazon Canada, Amazon MX | | Europe | `sellingpartnerapi-eu.amazon.com` | Amazon GB, Amazon DE, Amazon FR, Amazon IT, Amazon ES | | Far East | `sellingpartnerapi-fe.amazon.com` | Amazon AU | Without `MarketplaceIds` scoped per call, `getOrders` returns orders from **all marketplaces** accessible on that endpoint. Since US, CA, and MX share one NA credential, a US sync with no filter pulls CA and MX orders too — and imports them all as Amazon US. Same pattern applies across all five EU channels. --- ## Confirmed Channel Account Configuration on Prima Instance All nine accounts are correctly configured. No account setup changes are needed. | Name | Region | Marketplace ID | Selling Partner ID | |---|---|---|---| | Amazon US | US | `ATVPDKIKX0DER` | `A3GNDTLYQCCHJP` | | Amazon Canada | CA | `A2EUQ1WTGCTBG2` | `A3GNDTLYQCCHJP` | | Amazon MX | MX | `A1AM78C64UM0Y8` | `A3GNDTLYQCCHJP` | | Amazon GB | GB | `A1F83G8C2ARO7P` | `A6B2MIY3HRTZQ` | | Amazon DE | DE | `A1PA6795UKMFR9` | `A6B2MIY3HRTZQ` | | Amazon FR | FR | `A13V1IB3VIYZZH` | `A6B2MIY3HRTZQ` | | Amazon IT | IT | `APJ6JRA9NG5V4` | `A6B2MIY3HRTZQ` | | Amazon ES | ES | `A1RKKUPIHCS9HS` | `A6B2MIY3HRTZQ` | | Amazon AU | AU | `A39IBJ37TRP1C6` | `AK231R381GL07` | --- ## Confirmed Discrepancies (30-Day Period) These gaps are entirely explained by the missing MarketplaceIds filter: | Marketplace | Seller Central | eStack | Gap | Explanation | |---|---|---|---|---| | Amazon US | 1,377 | 1,486 | +109 over | Absorbing CA and MX orders | | Amazon GB | 827 | 1,535 | +708 over | Absorbing DE, FR, IT, ES orders | | Amazon AU | 169 | 148 | −21 under | Timezone boundary only (see Priority 2) | | Amazon CA | 239 | 23 | −216 under | US sync claims NA pool first; CA gets the gap | CA orders are not missing from eStack — they exist but are attributed to Amazon US. The same pattern applies to DE, FR, IT, ES orders appearing under Amazon GB. The Orders by Country dashboard card confirms this: Germany shows 639 orders and France 75 despite neither having orders attributed to their own channel — those are EU marketplace orders imported under the GB channel and broken out by buyer shipping address. --- ## Fix 1 — Add MarketplaceIds to every getOrders call (Priority 1 — required) This is the core fix. One parameter added to every `getOrders` API call, reading the marketplace ID from the channel account record. **Current behavior (broken):** ```php $params = [ 'CreatedAfter' => $createdAfter, 'OrderStatuses' => ['Unshipped', 'PartiallyShipped', 'Shipped'], ]; ``` **Required behavior:** ```php $params = [ 'CreatedAfter' => $createdAfter, 'OrderStatuses' => ['Unshipped', 'PartiallyShipped', 'Shipped'], 'MarketplaceIds' => [$channelAccount->getMarketplaceId()], ]; ``` **What to check:** - Find every location in the SP-API integration where `getOrders` is called - Confirm `MarketplaceIds` is added to every call without exception - Confirm the value is read from the channel account record — never hardcoded - Confirm no call passes multiple marketplace IDs for a single channel sync **Expected outcome:** - GB drops from 1,535 → ~827 (EU channels each get their own orders) - US drops from 1,486 → ~1,377 (NA channels each get their own orders) - CA rises from 23 → ~239 (CA sync now claims CA orders exclusively) - DE, FR, IT, ES each begin receiving their own orders correctly - Orders by Country card: Germany and France counts drop to reflect only buyers shipping to those countries from correctly attributed channels --- ## Fix 2 — Historical order re-attribution migration (Priority 1 — ship with Fix 1) Once Fix 1 is deployed, new orders will land in the correct channel. However, all existing orders in the database are misattributed. Since SP-API returns `MarketplaceId` on every order object and this value is already stored on each order record, re-attribution is a single database operation. **This migration must run immediately after Fix 1 deploys, before the next sync cycle, to prevent double-importing already-migrated orders.** ```sql -- Re-attribute orders to the correct channel account based on MarketplaceId -- Run once as a migration immediately after the MarketplaceIds fix deploys UPDATE orders o JOIN channel_accounts ca ON ca.marketplace_id = o.marketplace_id AND ca.store_id = o.store_id SET o.channel_account_id = ca.id WHERE o.channel_account_id != ca.id; ``` Van to verify: - The `orders` table stores `marketplace_id` from the SP-API response - The `channel_accounts` table has a `marketplace_id` column (confirmed from UI) - The join column names above match the actual schema — adjust if different - Run on a database backup first to confirm row counts before applying to prod - Expected: ~700+ GB orders re-attributed to DE/FR/IT/ES; ~215 US orders re-attributed to CA --- ## Fix 3 — AU timezone boundary (Priority 2) Amazon AU (A39IBJ37TRP1C6) is under-counting by ~21 orders over 30 days (~12%). AU is the only Far East endpoint channel and is not affected by the multi- marketplace bleed issue — its gap is caused by date boundary calculation. SP-API returns all timestamps in UTC. Amazon.com.au operates in AEST (UTC+10) or AEDT (UTC+11). If eStack's dashboard window calculations apply any local timezone offset when constructing `CreatedAfter` / `CreatedBefore` parameters, the boundary will be misaligned for AU, losing approximately 1–2 days of orders across the window edges. **What to check:** - Confirm `CreatedAfter` is always calculated as `NOW_UTC - N days` with no local timezone conversion applied on top - Confirm this applies to both the sync service date params and the dashboard toggle query (7d, 30d, MTD) — both must use the same UTC-based boundary --- ## Fix 4 — Pending order inclusion decision (Priority 2 — requires Rob input) Seller Central's "All orders" tab includes `Pending` status orders. Canada's Seller Central view shows recent orders with "Pending — Awaiting payment verification" prominently. If eStack excludes Pending from sync, real-time counts will always lag Seller Central, most visibly on CA and AU where recent orders frequently sit in Pending state. **Option A — Match Seller Central (recommended):** Include Pending in sync and dashboard counts. eStack totals become directly comparable to Seller Central at any point in time. OrderStatuses to include: ``` Pending, Unshipped, PartiallyShipped, Shipped, InvoiceUnconfirmed ``` **Option B — Operational orders only:** Exclude Pending. Dashboard shows only actionable orders. Counts will always be lower than Seller Central by design. **Rob to confirm Option A or B before Van implements.** Once confirmed, the chosen OrderStatuses set must be defined in a single constant or config value and used consistently across every `getOrders` call and every dashboard query. No per-channel variation. --- ## Fix 5 — % Sales by Channel pie card label (Priority 3) The pie card and Orders by Channel card show contradictory percentages for the same 30-day period. With the orders correctly attributed after Fixes 1 and 2, the order counts will change significantly — run the pie card audit after re-attribution is complete to determine whether the divergence persists. If it does: - Confirm whether the pie is measuring revenue/GMV share or order count share - If revenue: rename card to "% Revenue by Channel — Last 30 Days" - If orders: confirm it queries the same table with the same status filters as the Orders by Channel card and investigate remaining divergence --- ## Fix 6 — Resolve Unknown = 256 in Orders by Country (Priority 3) 256 orders over 30 days have no country attribution in the Orders by Country card. After re-attribution (Fix 2), this number may change — audit after migration before investigating further. If Unknown remains significant: - Query orders where shipping_country is NULL or empty - For FBA orders where SP-API omits buyer PII, use the channel account's marketplace country as a fallback attribution - Check ShipStation-sourced orders for missing country field mapping --- ## Fix 7 — Add last-synced timestamp to dashboard channel rows (Priority 4) Amazon US currently shows "Synced 574 days ago" in Store Settings but this staleness is invisible on the dashboard itself. A merchant looking at the dashboard has no way to know the data is 18 months old. Add a "Last synced X ago" indicator under each channel name in the Orders by Channel card. Style with `--es-muted` for normal recency. For last sync > 24 hours, add a visible warning indicator. For > 7 days, make it prominent. Display-only change, no SP-API work required. --- ## Files to Check | File | Area | Notes | |---|---|---| | SP-API order sync service | `getOrders` call | Add `MarketplaceIds` param reading from channel account | | Database migration | `orders` table | Re-attribute channel_account_id by marketplace_id | | Dashboard orders query | Date range | Confirm UTC-only boundary, no local TZ offset | | Dashboard pie card | Data source | Audit after re-attribution — may self-correct | | Orders by Country query | Null country fallback | Use marketplace country when ship-to missing | | Dashboard channel card template | Display | Add last-synced per channel row | --- ## Reproduction Steps 1. Open eStack dashboard, set Orders by Channel toggle to **30d** 2. Note: Amazon GB = 1,535 · Amazon US = 1,486 · Amazon CA = 23 3. Open Seller Central per marketplace, Last 30 days: - Amazon.co.uk: **827 orders** - Amazon.com: **1,377 orders** - Amazon.ca: **239 orders** 4. Open any order attributed to Amazon GB in eStack — check Customer Info email domain. Orders with `@marketplace.amazon.de`, `@marketplace.amazon.fr` etc. confirm EU bleed into the GB channel 5. Note Amazon Canada orders list shows only 24 orders with non-consecutive dates — consistent with sync race condition, not missing data --- ## Sequencing — Critical Fixes 1 and 2 must ship together in the same deployment: 1. Deploy Fix 1 (MarketplaceIds filter) — stops new misattribution 2. Immediately run Fix 2 migration (re-attribution) — corrects historical data 3. Verify order counts per channel match Seller Central before next sync runs Do not run the migration before Fix 1 is deployed — the next sync would re-misattribute orders immediately after the migration completes. --- ## Priority **P1 — Critical.** Every Amazon channel in eStack has incorrect order counts and attribution. Canada is showing 10% of actual orders. GB is showing 85% more orders than it should. The fix is a single parameter addition to one API call, plus a one-time database migration. These should be the next items Van works on. Fix 3 (AU timezone) and Fix 4 (Pending — pending Rob's decision) can ship in the same PR or a follow-up. Fixes 5–7 are lower priority and can follow separately.
rob added this to the eStack Sprint Board project 2026-06-29 09:39:08 +00:00
Sign in to join this conversation.
No Branch/Tag Specified
Branches Tags
Labels
Clear labels
Amazon SP-API
bug
Doing
eBay API
FBA Restock
FBA Restock
FBA Restock
fulfillment
Planning
Planning
Planning
Planning
Planning
prod
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
QA
shipstation
shipstation
No Label Amazon SP-API
Milestone
No items
No Milestone
Projects
Clear projects
No project eStack Sprint Board
Assignees
Clear assignees
rob
No Assignees
1 Participants
Notifications
Due Date
No due date set.
Dependencies

No dependencies set.

Reference: rob/estack-laminas#20
Delete Branch "%!s()"

Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?