Monthly Returns Analysis
1with
2 MonthlyReturns as (
3 select
4 date_trunc(cast(return_request_date as date), month) as month
5 , count(distinct order_id) as TotalReturns
6 , sum(refunded_amount) as TotalRefundedAmount
7 , countif(in_policy) as InPolicyReturns
8 , countif(not in_policy) as OutOfPolicyReturns
9 , sum(
10 case
11 when is_prime then 1
12 else 0
13 end
14 ) as PrimeReturns
15 , sum(
16 case
17 when not is_prime then 1
18 else 0
19 end
20 ) as NonPrimeReturns
21 from
22 {{returns_by_return_date_report}}
23 group by
24 month
25 )
26 , ReturnReasons as (
27 select
28 date_trunc(cast(return_request_date as date), month) as month
29 , return_reason
30 , count(distinct order_id) as ReasonCount
31 from
32 {{raw.amazon_selling_api_oauth.returns_by_return_date_report}}
33 group by
34 month
35 , return_reason
36 )
37select
38 m.Month
39 , m.TotalReturns
40 , m.TotalRefundedAmount
41 , m.InPolicyReturns
42 , m.OutOfPolicyReturns
43 , m.PrimeReturns
44 , m.NonPrimeReturns
45 , r.return_reason
46 , r.ReasonCount
47from
48 MonthlyReturns m
49 left join ReturnReasons r on m.Month = r.Month
50order by
51 m.Month desc
52 , r.ReasonCount desc;+---------------------+--------------+---------------------+------------------+---------------------+---------------+-----------------+----------------------+-------------+
| Month | TotalReturns | TotalRefundedAmount | InPolicyReturns | OutOfPolicyReturns | PrimeReturns | NonPrimeReturns | return_reason | ReasonCount |
+---------------------+--------------+---------------------+------------------+---------------------+---------------+-----------------+----------------------+-------------+
| 2022-01-01 00:00:00 | 150 | $5000 | 100 | 50 | 75 | 25 | Damaged Item | 50 |
| 2021-12-01 00:00:00 | 200 | $8000 | 150 | 50 | 100 | 100 | Wrong Item | 75 |
| 2021-11-01 00:00:00 | 100 | $4000 | 75 | 25 | 50 | 50 | Late Delivery | 25 |
+---------------------+--------------+---------------------+------------------+---------------------+---------------+-----------------+----------------------+-------------+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.
