Monthly Shipping and Delivery Reports
Generates a monthly shipping and delivery report for Amazon integration. It calculates various metrics such as the number of orders, total items shipped and unshipped, average days to ship, and the count of premium and regular orders. It also includes information on different shipping service levels and the number of orders for each level. The final result is sorted by month and shipping service level.
- Grain
- See the notes
- Built from
- 1Raw tables
- 1Core
- 1Analytics
- Reads from Amazon MWS
- orders
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.amazon_mws.monthly_shipping_and_delivery_report
2-- Monthly Shipping and Delivery Reports. Generates a monthly shipping and delivery
3-- report for Amazon integration. It calculates various metrics such as the number
4-- of orders, total items shipped and unshipped, average days to ship, and the count
5-- of premium and regular orders. It also includes information on different shipping
6-- service levels and the number of orders for each level. The final result is
7-- sorted by month and shipping service level.
8--
9-- From the weld.app template library. Not reconciled against Amazon Seller Central;
10-- check the output against the source before relying on it.
11
12with
13 MonthlyShippingPerformance as (
14 select
15 date_trunc(cast(PurchaseDate as date), month) month
16 , count(distinct AmazonOrderId) as NumberOfOrders
17 , sum(NumberOfItemsShipped) as TotalItemsShipped
18 , sum(NumberOfItemsUnshipped) as TotalItemsUnshipped
19 , avg(
20 timestamp_diff(LatestShipDate, EarliestShipDate, day)
21 ) as AvgDaysToShip
22 , sum(
23 case
24 when IsPremiumOrder then 1
25 else 0
26 end
27 ) as PremiumOrders
28 , sum(
29 case
30 when not IsPremiumOrder then 1
31 else 0
32 end
33 ) as RegularOrders
34 from
35 {{raw.amazon_selling_api_oauth.orders}}
36 group by
37 month
38 )
39 , ShippingServiceLevels as (
40 selectThis 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().
How it counts. And where it stops.
The Monthly Shipping and Delivery Reports SQL template is designed to provide insights into the shipping performance and service levels of Amazon orders on a monthly basis. By integrating with the Amazon Selling API, this SQL code retrieves data such as the number of orders, total items shipped and unshipped, average days to ship, premium and regular orders, and the service level of each order. The SQL code consists of two common table expressions (CTEs): MonthlyShippingPerformance and ShippingServiceLevels. The MonthlyShippingPerformance CTE calculates various metrics related to shipping performance, such as the number of orders, total items shipped and unshipped, average days to ship, and the breakdown of premium and regular orders. The ShippingServiceLevels CTE provides information on the service level of each order. The final SELECT statement combines the data from both CTEs and retrieves the desired fields, including the month, number of orders, total items shipped and unshipped, average days to ship, premium and regular orders, service level, and the number of orders for each service level. The result is ordered by month in descending order and then by the service level. This SQL template can be useful for analyzing and monitoring the shipping performance of Amazon orders over time. It allows you to identify trends, track the efficiency of order fulfillment, compare service levels, and make data-driven decisions to optimize shipping processes. The generated output from this SQL provides a comprehensive overview of the monthly shipping and delivery performance, enabling you to gain valuable insights into your Amazon business operations.
Raw tables it reads
- raw.amazon_selling_api_oauth.orders
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.