Monthly Order Update Tracking Report
1with
2 MonthlyOrderUpdates as (
3 select
4 date_trunc(cast(last_updated_date as date), month) as month
5 , count(distinct amazon_order_id) as TotalOrdersUpdated
6 , count(amazon_order_id) as TotalUpdates
7 , avg(item_price) as AveragePriceUpdated
8 from
9 {{staging.amazon_selling.orders_by_last_updated_date_report}}
10 group by
11 month
12 )
13 , StatusBreakdown as (
14 select
15 date_trunc(cast(last_updated_date as date), month) as month
16 , order_status
17 , count(distinct amazon_order_id) as StatusCount
18 from
19 {{staging.amazon_selling.orders_by_last_updated_date_report}}
20 group by
21 month
22 , order_status
23 )
24 , ChannelBreakdown as (
25 select
26 date_trunc(cast(last_updated_date as date), month) as month
27 , order_channel
28 , count(distinct amazon_order_id) as ChannelCount
29 from
30 {{staging.amazon_selling.orders_by_last_updated_date_report}}
31 group by
32 month
33 , order_channel
34 )
35select
36 m.Month
37 , m.TotalOrdersUpdated
38 , m.TotalUpdates
39 , m.AveragePriceUpdated
40 , s.order_status
41 , s.StatusCount
42 , c.order_channel
43 , c.ChannelCount
44from
45 MonthlyOrderUpdates m
46 left join StatusBreakdown s on m.Month = s.Month
47 left join ChannelBreakdown c on m.Month = c.Month
48order by
49 m.Month desc
50 , s.StatusCount desc
51 , c.ChannelCount desc;+---------------------+-------------------+--------------+-------------------+--------------+-------------+----------------+--------------+
| Month | TotalOrdersUpdated | TotalUpdates | AveragePriceUpdated | order_status | StatusCount | order_channel | ChannelCount |
+---------------------+-------------------+--------------+-------------------+--------------+-------------+----------------+--------------+
| 2022-07-01 00:00:00 | 150 | 300 | 25.99 | Delivered | 100 | Amazon | 200 |
| 2022-07-01 00:00:00 | 150 | 300 | 25.99 | Shipped | 80 | eBay | 150 |
| 2022-07-01 00:00:00 | 150 | 300 | 25.99 | Pending | 70 | Walmart | 100 |
| 2022-06-01 00:00:00 | 200 | 400 | 30.50 | Delivered | 120 | Amazon | 180 |
| 2022-06-01 00:00:00 | 200 | 400 | 30.50 | Shipped | 90 | eBay | 160 |
| 2022-06-01 00:00:00 | 200 | 400 | 30.50 | Pending | 80 | Walmart | 120 |
+---------------------+-------------------+--------------+-------------------+--------------+-------------+----------------+--------------+.The Monthly Order Update Tracking Report SQL template is designed to provide insights into the monthly order updates for Amazon integration. It retrieves data from the "orders_by_last_updated_date_report" table in the staging.amazon_selling schema. The SQL code utilizes common table expressions (CTEs) to organize the data into three main sections: MonthlyOrderUpdates, StatusBreakdown, and ChannelBreakdown. The MonthlyOrderUpdates CTE calculates the total number of orders updated, total updates, and average price updated for each month. The StatusBreakdown CTE breaks down the order status count for each month. The ChannelBreakdown CTE breaks down the order channel count for each month. The final SELECT statement combines the data from the CTEs and joins them based on the month. It retrieves the month, total orders updated, total updates, average price updated, order status, status count, order channel, and channel count. The results are ordered by month in descending order, followed by status count and channel count in descending order. This SQL template can be useful for tracking and analyzing monthly order updates in the Amazon integration. It provides insights into the total number of orders updated, the number of updates, average price updated, order status breakdown, and order channel breakdown. These insights can help identify trends, monitor performance, and make data-driven decisions related to order updates in the Amazon integration.
