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.
- Grain
- See the notes
- Built from
- 2Raw tables
- 1Core
- 1Analytics
- Reads from Amazon MWS
- orders_by_last_updated_date_reportsettlement_report
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.amazon_mws.monthly_financial_analysis
2-- Monthly Financial Analysis. Generates a monthly financial analysis for Amazon
3-- integration. It calculates the gross revenue, refunds, net revenue, average item
4-- price, average promotion discount, average shipping price, and average shipping
5-- tax. The analysis is based on data from the settlement report and orders by last
6-- updated date report. The results are sorted 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 MonthlyRevenue as (
13 select
14 date_trunc(
15 cast(
16 format_date('%Y-%m-%d', parse_date('%d.%m.%Y', posted_date)) as date
17 )
18 , month
19 ) month
20 , sum(
21 case
22 when amount_type = 'ItemPrice' then amount
23 else 0
24 end
25 ) as GrossRevenue
26 , sum(
27 case
28 when amount_type = 'Refund' then amount
29 else 0
30 end
31 ) as Refunds
32 , avg(amount) as AverageItemPrice
33 from
34 {{raw.amazon_selling_api_oauth.settlement_report}}
35 group by
36 month
37 )
38 , OrderDiscounts as (
39 select
40 date_trunc(cast(purchase_date as date), month) monthThis 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 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.
Raw tables it reads
- raw.amazon_selling_api_oauth.orders_by_last_updated_date_report
- raw.amazon_selling_api_oauth.settlement_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.