Weld logo
amazon-mws logo
Amazon Selling Partner API

Monthly Financial Analysis

Generates a monthly financial analysis for Amazon integration. It calculates the gross revenue, refunds, net revenue, average item price, average promotion discount, average shipping price, and average shipping tax. The analysis is based on data from the settlement report and orders by last updated date report. The results are sorted in descending order by month.
1with
2    MonthlyRevenue as (
3        select
4            date_trunc(
5                cast(
6                    format_date('%Y-%m-%d', parse_date('%d.%m.%Y', posted_date)) as date
7                )
8              , month
9            ) month
10          , sum(
11                case
12                    when amount_type = 'ItemPrice' then amount
13                    else 0
14                end
15            ) as GrossRevenue
16          , sum(
17                case
18                    when amount_type = 'Refund' then amount
19                    else 0
20                end
21            ) as Refunds
22          , avg(amount) as AverageItemPrice
23        from
24            {{raw.amazon_selling_api_oauth.settlement_report}}
25        group by
26            month
27    )
28  , OrderDiscounts as (
29        select
30            date_trunc(cast(purchase_date as date), month) month
31          , avg(item_promotion_discount) as AveragePromotionDiscount
32          , avg(shipping_price) as AverageShippingPrice
33          , avg(shipping_tax) as AverageShippingTax
34        from
35            {{staging.amazon_selling.orders_by_last_updated_date_report}}
36        group by
37            month
38    )
39select
40    r.Month
41  , r.GrossRevenue
42  , r.Refunds
43  , r.GrossRevenue - r.Refunds as NetRevenue
44  , r.AverageItemPrice
45  , d.AveragePromotionDiscount
46  , d.AverageShippingPrice
47  , d.AverageShippingTax
48from
49    MonthlyRevenue r
50    join OrderDiscounts d on r.Month = d.Month
51order by
52    r.Month desc;
Example of output from model:
+------------+--------------+---------+------------+-------------------+-------------------------+---------------------+-------------------+
|   Month    | GrossRevenue | Refunds | NetRevenue | AverageItemPrice  | AveragePromotionDiscount | AverageShippingPrice | AverageShippingTax |
+------------+--------------+---------+------------+-------------------+-------------------------+---------------------+-------------------+
| 2022-03-01 |   2500.00    |  100.00 |  2400.00   |      25.00        |          5.00            |        10.00        |        1.50       |
| 2022-02-01 |   3000.00    |  200.00 |  2800.00   |      30.00        |          6.00            |        12.00        |        1.80       |
| 2022-01-01 |   3500.00    |  150.00 |  3350.00   |      35.00        |          7.00            |        14.00        |        2.10       |
+------------+--------------+---------+------------+-------------------+-------------------------+---------------------+-------------------+

The Monthly Financial Analysis SQL template is designed to provide insights into the financial performance of an Amazon integration. This SQL code calculates various metrics related to revenue, refunds, item prices, promotion discounts, shipping prices, and shipping taxes on a monthly basis. The SQL code starts by creating two subqueries: MonthlyRevenue and OrderDiscounts. The MonthlyRevenue subquery calculates the gross revenue, refunds, and average item price for each month based on the data from the Amazon settlement report. The OrderDiscounts subquery calculates the average promotion discount, average shipping price, and average shipping tax for each month based on the data from the Amazon orders report. The main query then joins the results from the MonthlyRevenue and OrderDiscounts subqueries on the month column and selects various metrics such as gross revenue, refunds, net revenue (gross revenue minus refunds), average item price, average promotion discount, average shipping price, and average shipping tax. The results are ordered by month in descending order. This SQL template can be useful for conducting monthly financial analysis on Amazon integration data. It provides insights into revenue trends, refund patterns, average item prices, promotion discounts, and shipping costs. By analyzing these metrics over time, businesses can gain valuable insights into their financial performance and make informed decisions to optimize their Amazon operations.

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.