Monthly Returns Analysis
Extracts a monthly returns analysis for Amazon integration. It calculates various metrics such as total returns, total refunded amount, in-policy returns, out-of-policy returns, prime returns, and non-prime returns. Additionally, it provides information on return reasons and their respective counts. The results are sorted by month and reason count in descending order.
- Grain
- See the notes
- Built from
- 1Raw tables
- 1Core
- 1Analytics
- Reads from Amazon MWS
- returns_by_return_date_report
- Ships as
- Weld and dbt SQL, open source on GitHub
1-- core.amazon_mws.monthly_returns_analysis
2-- Monthly Returns Analysis. Extracts a monthly returns analysis for Amazon
3-- integration. It calculates various metrics such as total returns, total refunded
4-- amount, in-policy returns, out-of-policy returns, prime returns, and non-prime
5-- returns. Additionally, it provides information on return reasons and their
6-- respective counts. The results are sorted by month and reason count in descending
7-- order.
8--
9-- From the weld.app template library. Not reconciled against Amazon Seller Central;
10-- check the output against the source before relying on it.
11
12with
13 MonthlyReturns as (
14 select
15 date_trunc(cast(return_request_date as date), month) as month
16 , count(distinct order_id) as TotalReturns
17 , sum(refunded_amount) as TotalRefundedAmount
18 , countif(in_policy) as InPolicyReturns
19 , countif(not in_policy) as OutOfPolicyReturns
20 , sum(
21 case
22 when is_prime then 1
23 else 0
24 end
25 ) as PrimeReturns
26 , sum(
27 case
28 when not is_prime then 1
29 else 0
30 end
31 ) as NonPrimeReturns
32 from
33 {{raw.amazon_selling_api_oauth.returns_by_return_date_report}}
34 group by
35 month
36 )
37 , ReturnReasons as (
38 select
39 date_trunc(cast(return_request_date as date), month) as month
40 , return_reasonThis 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 Returns Analysis SQL template is designed to analyze monthly returns data from the Amazon integration. It calculates various metrics such as total returns, total refunded amount, in-policy returns, out-of-policy returns, prime returns, and non-prime returns. The SQL code utilizes two common table expressions (CTEs) named MonthlyReturns and ReturnReasons. The MonthlyReturns CTE aggregates the returns data by month and calculates the desired metrics. The ReturnReasons CTE further breaks down the returns by month and return reason, providing insights into the reasons behind the returns. By executing this SQL template, you can obtain a comprehensive analysis of monthly returns, including the total number of returns, the total refunded amount, and the breakdown of returns based on policy, prime/non-prime status, and return reasons. This analysis can help identify trends, patterns, and potential areas of improvement in the returns process for Amazon integration.
Raw tables it reads
- raw.amazon_selling_api_oauth.returns_by_return_date_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.