Monthly Sales Performance Reports
1with
2 MonthlySales as (
3 select
4 date_trunc(cast(PurchaseDate as date), month) month
5 , sum(OrderTotal_Amount) as TotalSalesAmount
6 , count(distinct AmazonOrderId) as NumberOfOrders
7 , sum(NumberOfItemsShipped + NumberOfItemsUnshipped) as TotalItemsSold
8 from
9 {{raw.amazon_selling_api_oauth.orders}}
10 group by
11 month
12 )
13select
14 month
15 , TotalSalesAmount
16 , NumberOfOrders
17 , TotalItemsSold
18 , TotalSalesAmount / NumberOfOrders as AverageOrderValue
19from
20 MonthlySales
21order by
22 month desc+---------------------+-----------------+----------------+-----------------+-------------------+
| month | TotalSalesAmount | NumberOfOrders | TotalItemsSold | AverageOrderValue |
+---------------------+-----------------+----------------+-----------------+-------------------+
| 2022-09-01 | 5000.00 | 10 | 50 | 500.00 |
| 2022-08-01 | 7000.00 | 15 | 75 | 466.67 |
| 2022-07-01 | 6000.00 | 12 | 60 | 500.00 |
| 2022-06-01 | 4000.00 | 8 | 40 | 500.00 |
| 2022-05-01 | 5500.00 | 11 | 55 | 500.00 |
+---------------------+-----------------+----------------+-----------------+-------------------+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.
