Monthly Inventory Reports
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+---------------------+------------------+------------------------+-------------+--------------+------------------------+---------------+---------------+
| 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.
