Amazon Strategy Price Comparison

Side-by-side of every strategy's calculated Min/Max price band against the current live Amazon price for each SKU, with a variance % per band and current stock — so you can spot where a strategy's price has drifted from the market.

Screenshot

Purpose

Sales / Pricing managers use it to audit whether each strategy's computed band still tracks the live Amazon selling price.
Highlights bands that deviate more than 5% from the current Amazon price (rendered red).
Shows AFN (Amazon-fulfilled) vs. other-warehouse stock alongside prices, to read price and availability together.
Each rebuild re-fetches the live Amazon selling price first, so the comparison is always against fresh prices.

How to Access

Desk > Reports > Amazon Strategy Price Comparison, or /app/query-report/Amazon Strategy Price Comparison. Report type: Script Report. Reference DocType: Amazon Product Pricing. Roles: Sales User, Sales Manager, System Manager.

Filters

Filter	Type	Required	Description
Proxy SKU	Link (Proxy SKU)	No	Restrict the comparison to a single SKU.

Columns

Column	Type	Description
Proxy SKU	Link (Proxy SKU)	The SKU.
Is FBA	Check	`Proxy SKU.is_fba`.
FBA Min	Currency	`calculated_min_price` of the linked FBA strategy.
FBA Max	Currency	`calculated_max_price` of the FBA strategy.
SFP Min	Currency	`calculated_min_price` of the SFP strategy.
SFP Max	Currency	`calculated_max_price` of the SFP strategy.
Backorder Min	Currency	`calculated_min_price` of the Backorder strategy.
Backorder Max	Currency	`calculated_max_price` of the Backorder strategy.
Non-Prime Min	Currency	`calculated_min_price` of the Default strategy (labelled "Non-Prime").
Non-Prime Max	Currency	`calculated_max_price` of the Default strategy.
Current Amazon Price	Currency	`Proxy SKU.current_selling_price` — the live price fetched at rebuild.
FBA Min Var % … Non-Prime Max Var %	Percent	One variance column per band: `(price − amazon_price) / amazon_price * 100`, rounded to 2 dp. Blank when either side is missing/zero.
AFN Stock	Float	Total `Bin.actual_qty` in the AFN warehouse (`Amazon SP API Settings.afn_warehouse`).
Other Stock	Float	Total `Bin.actual_qty` across all non-rejected warehouses excluding the AFN warehouse.

How It Works

The data query joins Amazon Product Pricing to its four strategy slots — fba_strategy, sfp_strategy, backorder_strategy, default_strategy (aliased non_prime) — pulling each one's calculated_min_price / calculated_max_price, plus the Proxy SKU's current_selling_price. For each row and each of the 8 bands, a variance % is computed against the current Amazon price; variance is left blank if either the band price or the Amazon price is zero/missing (a 0 price is deliberately not treated as −100%). Stock is summed from Bin: AFN stock from the configured AFN warehouse, other stock from every non-rejected warehouse except AFN.

flowchart LR
  REFRESH[refresh_amazon_listing_prices<br/>fetch live selling price<br/>for all active Proxy SKUs] --> PSKU
  APP[Amazon Product Pricing] --> JOIN[join 4 strategy slots<br/>fba / sfp / backorder / default]
  STRAT[Amazon Product Strategy<br/>calculated_min/max_price] --> JOIN
  PSKU[Proxy SKU<br/>current_selling_price] --> JOIN
  JOIN --> VAR[per-band variance %<br/>vs current Amazon price]
  BIN[Bin + Warehouse<br/>actual_qty] --> STOCK[AFN vs Other stock]
  VAR --> OUT[price bands + Var % + stock<br/>red when |var| > 5%]
  STOCK --> OUT

Conditional formatting: the JS pairs each price cell with its variance cell (PRICE_TO_VARIANCE map). When |variance| > 5 (VARIANCE_THRESHOLD), both the price cell and its variance cell render red and bold.

Strategy meaning (priority order used elsewhere to pick the active one): FBA (FBA stock > 0) → SFP (SFP available, no pending restock) → Backorder (pending restock date) → Default/Non-Prime (fallback). This report shows all four bands at once regardless of which is active, so you can see what each would price at.

Notes

This report runs a live Amazon price fetch on every rebuild (process_bulk_selling_price_fetch over all active Proxy SKUs) and commits — it is heavier than a plain read report.
A strategy slot left unlinked on the pricing record yields blank Min/Max and blank variance for that band.
The "Default" strategy is surfaced under the Non-Prime label in the columns.
Related: Amazon Pricing Feed Preview for what the active strategy's band would actually push.

Navgold