Monthly Shipping and Delivery Reports
1with
2 MonthlyShippingPerformance as (
3 select
4 date_trunc(cast(PurchaseDate as date), month) month
5 , count(distinct AmazonOrderId) as NumberOfOrders
6 , sum(NumberOfItemsShipped) as TotalItemsShipped
7 , sum(NumberOfItemsUnshipped) as TotalItemsUnshipped
8 , avg(
9 timestamp_diff(LatestShipDate, EarliestShipDate, day)
10 ) as AvgDaysToShip
11 , sum(
12 case
13 when IsPremiumOrder then 1
14 else 0
15 end
16 ) as PremiumOrders
17 , sum(
18 case
19 when not IsPremiumOrder then 1
20 else 0
21 end
22 ) as RegularOrders
23 from
24 {{raw.amazon_selling_api_oauth.orders}}
25 group by
26 month
27 )
28 , ShippingServiceLevels as (
29 select
30 date_trunc(cast(PurchaseDate as date), month) month
31 , ShipServiceLevel
32 , count(distinct AmazonOrderId) as ServiceLevelOrders
33 from
34 {{raw.amazon_selling_api_oauth.orders}}
35 group by
36 month
37 , ShipServiceLevel
38 )
39select
40 m.Month
41 , m.NumberOfOrders
42 , m.TotalItemsShipped
43 , m.TotalItemsUnshipped
44 , m.AvgDaysToShip
45 , m.PremiumOrders
46 , m.RegularOrders
47 , s.ShipServiceLevel
48 , s.ServiceLevelOrders
49from
50 MonthlyShippingPerformance m
51 left join ShippingServiceLevels s on m.Month = s.Month
52order by
53 m.Month desc
54 , s.ShipServiceLevel;+---------------------+----------------+-------------------+---------------------+--------------+---------------+----------------+-------------------+-------------------+
| Month | NumberOfOrders | TotalItemsShipped | TotalItemsUnshipped | AvgDaysToShip | PremiumOrders | RegularOrders | ShipServiceLevel | ServiceLevelOrders|
+---------------------+----------------+-------------------+---------------------+--------------+---------------+----------------+-------------------+-------------------+
| 2022-09-01 00:00:00 | 150 | 500 | 100 | 2.5 | 100 | 50 | Standard | 100 |
| 2022-09-01 00:00:00 | 150 | 500 | 100 | 2.5 | 100 | 50 | Expedited | 75 |
| 2022-08-01 00:00:00 | 200 | 600 | 200 | 3.2 | 150 | 50 | Standard | 120 |
| 2022-08-01 00:00:00 | 200 | 600 | 200 | 3.2 | 150 | 50 | Expedited | 80 |
+---------------------+----------------+-------------------+---------------------+--------------+---------------+----------------+-------------------+-------------------+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.
