Weld logo
amazon-mws logo
Amazon Selling Partner API

Monthly Order Update Tracking Report

Extracts a monthly order update tracking report for Amazon integration. It retrieves data from the "orders_by_last_updated_date_report" table and calculates various metrics such as total orders updated, total updates, average price updated, status breakdown, and channel breakdown. The results are sorted by month, status count, and channel count in descending order.
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;
Example of output from model:
+---------------------+-------------------+--------------+-------------------+--------------+-------------+----------------+--------------+
|        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.

Ready to start modeling your own amazon-mws data?

Get started building your data warehouse with amazon-mws and connect all your apps and databases.

amazon-mws logo
Weld logo
Sign up for free
© 2024 Weld. All rights reserved.