Sales over time
Traffic by date" page, and puts all three revenue definitions on one row.
- Grain
- day x seller x marketplace
- Built from
- 1Raw tables
- 1Staging
- 1Core
- 1Analytics
- Reads from Amazon Selling Partner API
- sales_and_traffic_report_by_date
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- amazon_seller_sales_over_time - recreates Amazon's Business Reports "Sales and
2-- Traffic by date" page, and puts all three revenue definitions on one row.
3--
4-- Grain: day x seller x marketplace. Currency: the marketplace's own.
5-- Depends on: staging.amazon_seller.sales_and_traffic_by_date
6--
7-- WHY START FROM AMAZON'S OWN REPORT RATHER THAN THE ORDER DATA. Because this is
8-- the number your team already quotes. Seller Central's dashboard shows
9-- `ordered_product_sales`, so that is the figure in the weekly update and in the
10-- board deck, and a model that computes its own from order lines will be close but
11-- never equal - Amazon applies cancellation and pending-order logic it does not
12-- document. Take the reported number as given here, and use
13-- core.amazon_seller.product_sales_over_time when you need it split by ASIN.
14--
15-- THE THREE REVENUE NUMBERS, and why they never match:
16--
17-- ordered_product_sales what customers ordered, on the ORDER date.
18-- Amazon's headline. Includes orders not yet shipped,
19-- excludes tax, net of cancellations.
20-- shipped_product_sales what actually shipped, on the SHIP date. Lags ordered
21-- by fulfilment time, so a fast-growing month always
22-- looks smaller here.
23-- deposits what Amazon paid you, on the SETTLEMENT date. Net of
24-- every fee. Lives in core.amazon_seller.settlement_ledger
25-- because it comes from a different report entirely.
26--
27-- All three are correct. They are different questions - demand, fulfilment, and
28-- cash - and the argument about which is "the real revenue number" only ends when
29-- all three are on the same row, which is what this model is for.
30
31SELECT
32 d.date,
33 d.amazon_seller,
34 d.marketplace,
35 d.currency,
36
37 -- Demand: the Seller Central headline.
38 d.ordered_product_sales,
39 d.units_ordered,
40 d.total_order_items,This model and the 2 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().
How it counts. And where it stops.
WHY START FROM AMAZON'S OWN REPORT RATHER THAN THE ORDER DATA. Because this is the number your team already quotes. Seller Central's dashboard shows `ordered_product_sales`, so that is the figure in the weekly update and in the board deck, and a model that computes its own from order lines will be close but never equal - Amazon applies cancellation and pending-order logic it does not document. Take the reported number as given here, and use core.amazon_seller.product_sales_over_time when you need it split by ASIN. THE THREE REVENUE NUMBERS, and why they never match: ordered_product_sales what customers ordered, on the ORDER date. Amazon's headline. Includes orders not yet shipped, excludes tax, net of cancellations. shipped_product_sales what actually shipped, on the SHIP date. Lags ordered by fulfilment time, so a fast-growing month always looks smaller here. deposits what Amazon paid you, on the SETTLEMENT date. Net of every fee. Lives in core.amazon_seller.settlement_ledger because it comes from a different report entirely. All three are correct. They are different questions - demand, fulfilment, and cash - and the argument about which is "the real revenue number" only ends when all three are on the same row, which is what this model is for.
Raw tables it reads
- raw.amazon_seller_central.sales_and_traffic_report_by_date
More Amazon Selling Partner API models
Put Amazon Selling Partner API in front
of your agents.
Connect Amazon Selling Partner API, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.