Weld logo
amazon-mws
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 100+ more apps and databases available.

amazon-mws
Weld logo
Sign up for free
Weld logo

Tired of scattered data? Sync data to your data warehouse with Weld in minutes with our powerful ETL, SQL Transformations, Reverse ETL and AI Assistant, Ed - connected to 100+ apps, files and databases.

Backed by leading investors
Frontline logoCherry logoInnnovation Fund logo
Twitter LogoLinkedIn Logo
GDPR logoSOC2
© 2024 Weld. All rights reserved.