How to calculate refund as shown in SALES OVER TIME

[Bill6]

I would like to know how to aggregate order refunds to arrive at the same value calculated in the "SALES OVER TIME" report.  Its not simply add up the transaction amounts for kind="refund" as many have suggested.  

There are transactions but also "order_adjustments" with "shipping_refunds" and "refund_discrepancy" records and its not clear exactly how to add them all up to arrive at the same refund amount that is derived in the "sales over time".  I have a back-end program that extracts ALL order refund transactions and order adjustments.  I am now trying to aggregate them by date and arrive at the same refund value for a given day in the sales over time report.  I cannot find an algorithm that works.  Some days match.  But when there are several refund_discrepancy records it gets whacked. 

How are we supposed to combine the "refund", "refund_discrepancy", and "shipping_refund" records to arrive at the same value reported by Shopify for a given day?  How about getting a Shopify Developer to spill the beans!?! Does anyone at Shopify even read these posts?

[Bill6]

After doing some more auditing I can attribute at least some of the deviations to how taxes seem to handled on Gift Certificates issued for refunds (but only in some cases!). 

For example (see attached image), I found an order where the "paid by customer" amount included a tax amount (33.66) that was not listed under the "Tax" in the "Paid" breakdown.  The customer paid the tax but it was not listed and the breakdown is completely wrong. Why?  

The order was then returned on 1/9/2021 and a Gift Certificate was issued for the refund to the amount that included the 33.66 tax.  Then...   In SALES OVER TIME report on 1/9/2021 we see the 33.66 showing up as a refund!  There are no Transactions for this 33.66 amount to account for this.  How was it refunded to the customer?  More importantly how do we account for this in the same way the "SALES OVER TIME" report does?  I do not see any way to detect and handle this kind if refund scenario.  What gives?

Our business must be able to audit the metrics reported in the SALES OVER TIME and demonstrate to our partners that the numbers are correct.  In this case it appears there was a refund with no auditable Transaction. 

[kingsire23]

Hi @Bill6 

I'm noticing similar issues here on my end. I just recently opened a case with our data integration partner Fivetran about this. My goal is to be able to get refunds for each product rolling up to the order level which each order ties to a date so sales over time would tie from there. When we checked the API we noticed that some refund ids did not have refund line item detail. This is puzzling because refund is part of order response itself and refund_line_item is part of refund i.e. order→refunds→refund_line_items so the issue is with how Shopify makes this data available and not on the data integration partner. Hope this helps. 

@Shopify please get this issue resolved! This is a significant discrepancy that severely impacts businesses with their internal reporting and analysis.