I have a system the takes sales data from people like Amazon and eBay, and analyses individual transactions to calculate international VAT liability.
I’m try to import Shopify data into this system, and we have a Shopify app in testing now that does a few things, one of which is to set up two webhooks for Sales and Refunds.
The JSON block this sends goes to my web server, where I try to process it into the database format we use.
I’ve figured out that for a ‘normal’ sale, I can use the current_subtotal_price_set as the gross price of all the items, total_shipping_price_set as the gross price for the shipping cost, and current_total_price_set seems to be the total gross of the items and the shipping combined.
If I get a refund though, the item gross of (for example) £10 and shipping gross of £3.99 don’t add up to the total gross of £12. Investigating the refunds block in the JSON shows a transactions block of amount £10.99, which I also can’t explain. Additionally, in the refunds/order_adjustments block, there are two entries: one with an amount £10.99, and a ‘kind’ of “refund_discrepancy”, and another entry of -£1.99 with a ‘kind’ of “shipping_refund”. I just can’t make any of these numbers add up.
I have a third JSON block which lists a refund and an additional item sale in the one place: I assume this is some kind of exchange operation.
The figures here seem to be even worse than the refund, which I take as another sign that I don’t understand the refund figures properly. The refund part of this one has no transactions and no order_adjustments, just one ‘refund_line_items’ entry with a ‘price_set’ of £10. The sales part of this one has a new ‘line_items’ entry with a ‘price_set’ of £22, which matches the outer JSON values of current_subtotal_price_set=£22, but then I can’t figure out how the total_shipping_price_set=£3.99 could add up to the current_total_price_set=£24, let alone where that refund of £10 went.
It’s clear that I don’t understand the JSON blocks well enough to work out what I’m getting wrong.
Can anyone shed any light on these, and what the correct interpretations ought to be?