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.
- Grain
- month x order status x order channel, by last-updated date
- Built from
- 1Raw tables
- 1Core
- 1Analytics
- Reads from Amazon MWS
- orders_by_last_updated_date_report
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.amazon_mws.monthly_order_update_tracking_report
2-- Monthly Order Update Tracking Report. Extracts a monthly order update tracking
3-- report for Amazon integration. It retrieves data from the
4-- "orders_by_last_updated_date_report" table and counts the orders updated in
5-- each month, broken down by order status and sales channel.
6--
7-- Grain: month x order status x order channel, by last-updated date. Each row
8-- counts the distinct orders updated in that month with that status and
9-- channel, the report rows behind them, and their average item price. An order
10-- that changed status within a month appears under each status it had, so
11-- add up orders_updated across statuses with care.
12--
13-- From the weld.app template library, corrected. Not reconciled against Amazon
14-- Seller Central; check the output against the source before relying on it.
15
16select
17 date_trunc(cast(last_updated_date as date), month) as month
18 , order_status
19 , order_channel
20 , count(distinct amazon_order_id) as orders_updated
21 , count(*) as updates
22 , avg(item_price) as average_item_price
23from
24 {{raw.amazon_selling_api_oauth.orders_by_last_updated_date_report}}
25group by
26 month
27 , order_status
28 , order_channel
29order by
30 month desc
31 , orders_updated descThis 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 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.
Raw tables it reads
- raw.amazon_selling_api_oauth.orders_by_last_updated_date_report
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.