Weld
Start free
Amazon MWS model

Monthly Inventory Reports

Extracts monthly inventory reports for Amazon integration. It retrieves data from the Amazon Selling API to calculate various metrics such as total items listed, total fulfillable quantity, total damaged items, expired items, total unfulfillable quantity, items in transit, and items receiving. The results are ordered by month in descending order.

See the SQLAll Amazon MWS models
Grain
See the notes
Built from
  1. 1
    Raw tables
  2. 1
    Core
  3. 1
    Analytics
Reads from Amazon MWS
fba_inventory_summary
Ships as
Weld and dbt SQL, open source on GitHub
core.amazon_mws.monthly_inventory_report
1-- core.amazon_mws.monthly_inventory_report
2-- Monthly Inventory Reports. Extracts monthly inventory reports for Amazon
3-- integration. It retrieves data from the Amazon Selling API to calculate various
4-- metrics such as total items listed, total fulfillable quantity, total damaged
5-- items, expired items, total unfulfillable quantity, items in transit, and items
6-- receiving. The results are ordered by month in descending order.
7--
8-- From the weld.app template library. Not reconciled against Amazon Seller Central;
9-- check the output against the source before relying on it.
10
11with
12    MonthlyInventory as (
13        select
14            date_trunc(cast(last_updated_time as timestamp), month) as month
15          , sum(total_quantity) as TotalItemsListed
16          , sum(fulfillable_quantity) as TotalFulfillableQuantity
17          , sum(
18                carrier_damaged_quantity + customer_damaged_quantity + defective_quantity + distributor_damaged_quantity + expired_quantity + warehouse_damaged_quantity
19            ) as TotalDamaged
20          , sum(expired_quantity) as ExpiredItems
21          , sum(total_unfulfillable_quantity) as TotalUnfulfillableQuantity
22          , sum(inbound_shipped_quantity) as ItemsInTransit
23          , sum(inbound_receiving_quantity) as ItemsReceiving
24        from
25            {{raw.amazon_selling_api_oauth.fba_inventory_summary}}
26        group by
27            month
28    )
29select
30    month
31  , TotalItemsListed
32  , TotalFulfillableQuantity
33  , TotalDamaged
34  , ExpiredItems
35  , TotalUnfulfillableQuantity
36  , ItemsInTransit
37  , ItemsReceiving
38from
39    MonthlyInventory
40order by

This model and the 1 it depends on. Deploy them together; refs like {{staging.…}} resolve to the matching folders in Weld, and the dbt view uses source() and ref().

Notes

How it counts. And where it stops.

The Monthly Inventory Reports SQL template is designed to generate monthly inventory reports for Amazon integration. This SQL model retrieves data from the Amazon Selling API using OAuth authentication. The SQL code calculates various inventory metrics for each month, including the total quantity of items listed, total fulfillable quantity, total damaged items, expired items, total unfulfillable quantity, items in transit, and items receiving. By executing this SQL template, you can gain valuable insights into your monthly inventory performance on Amazon. The generated report provides a comprehensive overview of key inventory metrics, allowing you to track inventory trends, identify potential issues such as damaged or expired items, and monitor the flow of items in transit and receiving. The report is sorted in descending order by month, enabling you to easily analyze the most recent inventory data first. This SQL template is particularly useful for inventory management, supply chain optimization, and identifying areas for improvement in your Amazon selling operations.

Raw tables it reads

  • raw.amazon_selling_api_oauth.fba_inventory_summary

More Amazon MWS models

Put Amazon MWS in front
of your agents.

Connect Amazon MWS, deploy the models, and ask questions in Claude, Cursor or ChatGPT. Free for 14 days, no credit card.

Start for freeTalk to us