Compute "Returns" data using raw orders data exported using API

Retired Boards Appdev Tools
1

I have (all fields of) all orders (including order refunds) data exported as a json to my local computer using Admin REST API.

My goal is to find correct numbers to aggregate (the correct way) and match the result with “Returns” column of “Sales over time” report (daily amount of returns) on a particular date. So far I’ve been trying to sum up the subtotal field in line_items under all refunds objects inside an individual order where refunds.[item].created_at = my_date . However the number I get in the result is sometimes off by up to 50% from the “Sales over time” report.

Much appreciated

2

HI Aproshyan,

The discrepancy may be due to several factors. Here are some possibilities:

  1. Partial Refunds: Partial refunds can cause discrepancies. The refund might be for a part of the order and not the whole order itself.

  2. Multiple Refunds on Same Order: If an order has been refunded multiple times, ensure that all the refunds are being accounted for.

  3. Refund Method: The method of refunding can also affect the result. For instance, if products were not selected while refunding and the refund was changed directly, the subtotal field in line_items might not accurately represent the refunded amount.

  4. Restocking fees and Shipping fees: If there were restocking fees or shipping fees involved, these might not be included in the subtotal field in line_items under refunds.

  5. Taxes and Discounts: Taxes and discounts can also cause discrepancies. For instance, in some countries when a customer returns an item, the full value of the sales tax is considered to have been refunded. Also, discounts are divided proportionally amongst the line items, so the subtotal field might not include these.

  6. Currency Conversion: If your store deals in multiple currencies, make sure to consider currency conversion rates as they can cause discrepancies.

To resolve the issue, you might want to look into the following fields:

  • refunds.[item].refund_line_items.[item].subtotal - This is the subtotal of the line item being refunded.
  • refunds.[item].refund_line_items.[item].total_tax - This is the total tax of the line item being refunded.
  • refunds.[item].refund_line_items.[item].quantity - This is the quantity of the line item being refunded.
  • refunds.[item].shipping.amount - This is the shipping amount being refunded.
  • refunds.[item].transactions.[item].amount - This is the actual amount refunded to the customer.

By accurately aggregating these fields, you should be able to match the “Returns” column in the “Sales over time” report.

Hope this helps!