Weld logo
amazon-mws logo
Amazon Selling Partner API

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.
1with
2    MonthlyInventory as (
3        select
4            date_trunc(cast(last_updated_time as timestamp), month) as month
5          , sum(total_quantity) as TotalItemsListed
6          , sum(fulfillable_quantity) as TotalFulfillableQuantity
7          , sum(
8                carrier_damaged_quantity + customer_damaged_quantity + defective_quantity + distributor_damaged_quantity + expired_quantity + warehouse_damaged_quantity
9            ) as TotalDamaged
10          , sum(expired_quantity) as ExpiredItems
11          , sum(total_unfulfillable_quantity) as TotalUnfulfillableQuantity
12          , sum(inbound_shipped_quantity) as ItemsInTransit
13          , sum(inbound_receiving_quantity) as ItemsReceiving
14        from
15            {{raw.amazon_selling_api_oauth.fba_inventory_summary}}
16        group by
17            month
18    )
19select
20    month
21  , TotalItemsListed
22  , TotalFulfillableQuantity
23  , TotalDamaged
24  , ExpiredItems
25  , TotalUnfulfillableQuantity
26  , ItemsInTransit
27  , ItemsReceiving
28from
29    MonthlyInventory
30order by
31    month desc
Example of output from model:
+---------------------+------------------+------------------------+-------------+--------------+------------------------+---------------+---------------+
|        month        | TotalItemsListed | TotalFulfillableQuantity | TotalDamaged | ExpiredItems | TotalUnfulfillableQuantity | ItemsInTransit | ItemsReceiving |
+---------------------+------------------+------------------------+-------------+--------------+------------------------+---------------+---------------+
| 2022-03-01 00:00:00 |       1500       |           1200           |      100     |      50      |           1000           |      200      |      300      |
| 2022-02-01 00:00:00 |       1800       |           1500           |      80      |      70      |           1200           |      250      |      400      |
| 2022-01-01 00:00:00 |       2000       |           1800           |      120     |      90      |           1500           |      300      |      350      |
+---------------------+------------------+------------------------+-------------+--------------+------------------------+---------------+---------------+

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.

Ready to start modeling your own amazon-mws data?

Get started building your data warehouse with amazon-mws and 100+ more apps and databases available.

amazon-mws logo
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync and analyze your data with AI in minutes. Connect to 150+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.