Weld
Start free
Amazon MWS model

Monthly Sales Performance Reports

Extracts monthly sales performance reports for Amazon integration. It calculates the total sales amount, number of orders, and total items sold for each month. Additionally, it calculates the average order value by dividing the total sales amount by the number of orders. The results are ordered in descending order by month.

See the SQLAll Amazon MWS models
Grain
See the notes
Built from
  1. 1
    Raw tables
  2. 1
    Core
  3. 1
    Analytics
Reads from Amazon MWS
orders
Ships as
Weld and dbt SQL, open source on GitHub
core.amazon_mws.monthly_sales_performance_report
1-- core.amazon_mws.monthly_sales_performance_report
2-- Monthly Sales Performance Reports. Extracts monthly sales performance reports for
3-- Amazon integration. It calculates the total sales amount, number of orders, and
4-- total items sold for each month. Additionally, it calculates the average order
5-- value by dividing the total sales amount by the number of orders. The results are
6-- ordered in descending order by month.
7--
8-- From the weld.app template library. Not reconciled against Amazon Seller Central;
9-- check the output against the source before relying on it.
10
11with
12    MonthlySales as (
13        select
14            date_trunc(cast(PurchaseDate as date), month) month
15          , sum(OrderTotal_Amount) as TotalSalesAmount
16          , count(distinct AmazonOrderId) as NumberOfOrders
17          , sum(NumberOfItemsShipped + NumberOfItemsUnshipped) as TotalItemsSold
18        from
19            {{raw.amazon_selling_api_oauth.orders}}
20        group by
21         
22           month
23    )
24select
25    month
26  , TotalSalesAmount
27  , NumberOfOrders
28  , TotalItemsSold
29  , TotalSalesAmount / NumberOfOrders as AverageOrderValue
30from
31    MonthlySales
32order by
33    month desc

This 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().

Notes

How it counts. And where it stops.

The Monthly Sales Performance Reports SQL template is designed to analyze sales data from Amazon. It calculates various metrics such as total sales amount, number of orders, total items sold, and average order value on a monthly basis. The template utilizes the Amazon Selling API to retrieve order data and aggregates it by month. By running this SQL code, you can gain insights into the performance of your monthly sales on Amazon. The TotalSalesAmount metric provides an overview of the revenue generated each month, while the NumberOfOrders metric indicates the number of orders placed. The TotalItemsSold metric represents the total quantity of items sold during the specified period. Additionally, the AverageOrderValue metric is calculated by dividing the total sales amount by the number of orders, giving you an understanding of the average value of each order. The SQL template is particularly useful for generating monthly sales performance reports, allowing you to track sales trends, identify peak months, and compare performance across different time periods. This information can help you make data-driven decisions, optimize inventory management, and evaluate the effectiveness of marketing campaigns on Amazon.

Raw tables it reads

  • raw.amazon_selling_api_oauth.orders

More Amazon MWS models

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.

Start for freeTalk to us