Before making the calculation, the operator first needs to distinguish between the return rate and the refund rate. The return rate is the ratio of the number of return orders to the total number of orders. If the return rate of FBA products exceeds 10%, the link will be warned and banned by Amazon. In the backend data report > return report, you can download the product return form. By analyzing the reasons for buyers’ returns, you can improve the product in time or develop new products.
The refund rate refers to the ratio of the amount of return loss to the total sales. The calculation formula is: FBA refund rate = order refund loss amount/sales × 100%
Single order refund loss amount = FBA delivery fee + FBA customer return fee + refund order commission
When calculating the refund rate, you need to select the standard order in the data report > payment > all settlements, and download the template file V2 for each period. Since the return window of the order can reach 30 days, all V2 files are unified into one table for calculation.
The specific calculation steps of the refund rate are as follows.
1 According to the formula, first filter out the sales volume of the product. Click the PivotTable, add the filter transaction type transaction-type as sales order Order, amount type amount-type as ItemPrice order price, amount description amount-description as the original order Principal, and the data before 30 days in the latest V2 file cycle in posted-date. Add the order number order-id to the row data, add the sum of the amount amount to the value data, and copy the filtered data to the new table.
2 Secondly, filter out the FBA delivery fee, FBA customer return fee and refund order commission. In the “PivotTable field”, filter the transaction type transaction-type as FBA delivery fee FBAPerUnitFulfillmentFee, FBA customer return fee FBACustomerReturnPerUnitFee, and refund order commission RefundCommission, and copy the filtered data to the new table respectively.
3 In the data obtained in step 1, use the VLOOKUP function to match the product SKU, retain the parent SKU, and match the FBA delivery fee, FBA customer return fee, and refund order commission obtained in step 2.
4 Save the matched data in numeric format, and use Find and Replace to replace the invalid value #N/A with 0. At this time, there is still unrefundable order data in the FBA delivery fee. Filter all orders with a refund order commission of 0, and delete the corresponding FBA delivery fee data.
5 Use the Calculated Field function in the PivotTable tool to calculate the final product refund rate according to the formula mentioned above.
At this point, the sales and refund rate of all products in the store within the set period are obtained.
It is not difficult to see that in order to calculate accurate refund rate data, there will be at least a 30-day delay, and situations such as multiple products in one order will also cause the refund rate to fluctuate. For products with short operating cycles and strong seasonality, operators can directly use the refund amount generated in the current period divided by the current sales amount to estimate the product refund rate. The refund rate of products can be analyzed to provide more timely information for operations.
In addition, return analysis is an optimization of the product system. During the operation process, products with high sales volume do not necessarily have high profits. Some products with high refund rates appear to have brought in revenue on the surface, but in fact, after investing a lot of effort, they will also bring a large number of negative reviews in the later stage, which will lead to a decline in link sales and eventually redundant inventory. Therefore, during the growth period of the product, it is necessary to analyze the reasons for returns and return rates in a timely manner so as to gradually adjust the products and ensure that the link enters the mature stage stably.
For products in the growth stage, as sales volume increases, product costs may gradually decrease. As competition intensifies, new product features begin to appear and product markets begin to segment. In order to maintain the continued growth of the link, operators can maintain or slightly increase promotional expenses, and appropriately increase prices at nodes such as FBA warehousing to ensure that the product profit margin is positive. At the same time, operators can also improve product quality, such as adding new functions, increasing product styles, and improving product competitiveness.