Weld logo
amazon-mws logo
Amazon Selling Partner API

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.
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;
Example of output from model:
+---------------------+--------------+---------------------+------------------+---------------------+---------------+-----------------+----------------------+-------------+
|        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.

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.