Amazon Top Selling SKU

Ranks the best-selling Item+UOM combinations over a date window and attaches their mapped Amazon SKUs/ASINs.

Purpose

Sales/merchandising see which products move the most by revenue or quantity in a period.
Each top row carries its Amazon SKU(s) and ASIN(s) so a buyer can jump straight to the listing.
Narrow the ranking to a single Item Group and cap it at the top N.

How to Access

Desk > Reports > Amazon Top Selling SKU, or /app/query-report/Amazon%20Top%20Selling%20SKU. Report type: Script Report. Reference DocType: Sales Order.

Filters

Filter	Type	Required	Description
From Date	Date	Yes	Window start. Defaults to 30 days ago (`add_to_date(getdate(), days=-30)`).
To Date	Date	Yes	Window end. Defaults to today (`getdate()`).
Rank By	Select (Revenue / Qty)	No	Sort key. Revenue sorts by `Sum(amount)`, Qty by `Sum(qty)`. Defaults to Revenue.
Show Top N	Int	No	Row cap. Defaults to 50 (`cint(top_n) or 50`).
Item Group	Link (Item Group)	No	When set, restricts to items whose `item_group` matches (adds an inner join to Item).

Columns

Column	Type	Description
Item	Link (Item)	`item_code` of the sold line.
UOM	Data	`uom` the quantity was sold in.
Amazon SKU(s)	Data	`sku_display` — comma-joined mapped Proxy SKU names, or `(unmapped)` if none.
ASIN(s)	Data	`asin_display` — comma-joined ASINs from the mapped active Proxy SKUs.
Qty Sold	Float	`total_qty` — `Sum(qty)` across submitted Sales Order Items.
Revenue	Currency	`total_amount` — `Sum(amount)` across submitted Sales Order Items.
Orders	Int	`order_count` — `Count(DISTINCT Sales Order.name)`.

How It Works

The aggregate runs on Sales Order Item inner-joined to Sales Order (item.parent == order.name), filtered to docstatus == 1 and transaction_date within [from_date, to_date], grouped by item_code, uom.

Sales aren't linked to a specific Proxy SKU, and one Item+UOM can map to several active Proxy SKUs, so the query aggregates at the real sales grain (item + uom) to avoid the fan-out double-counting a join to Proxy SKU would cause. SKUs/ASINs are attached afterwards.

flowchart LR
  SOI[Sales Order Item] -->|parent == name| SO[Sales Order]
  SO -->|docstatus=1, transaction_date in window| AGG[group by item_code, uom: Sum qty, Sum amount, Count distinct orders]
  IG[Item Group filter] -.inner join Item.-> AGG
  AGG -->|order by Revenue or Qty desc, limit top_n| ROWS[ranked rows]
  PSKU[Proxy SKU is_active=1] --> ATTACH[attach_proxy_skus by item+uom]
  ROWS --> ATTACH --> OUT[rows + sku_display / asin_display]

Sort. Sum(qty) when Rank By = Qty, otherwise Sum(amount), always Order.desc, then .limit(top_n).
SKU/ASIN attach. attach_proxy_skus collects the distinct item_codes, fetches Proxy SKU rows where is_active = 1 and item in (...), and maps them by (item, uom). Each row gets skus (Proxy SKU names) and asins (only entries with an ASIN). Unmatched rows render (unmapped) for SKU and an empty ASIN cell.

Notes

Rows are at item+uom grain, not per Proxy SKU — a single row may list several SKUs/ASINs.
Only submitted Sales Orders (docstatus == 1) count; drafts and cancelled orders are excluded.
The Item Group filter only changes which items qualify, not the aggregation grain.
Related: Amazon Daily Order Stats, Amazon Order Sync Health.