I'm trying to replicate the Shopify 'Sales over time' report in our Snowflake instance. One component of that report calculates the total amount refunded to customers on that date, i.e. the refund date (not the order date).
I have been struggling to find in the schema the refund amount, and am finding some confusing behaviour like structural differences between a full and partial refund.
Please can someone point me in the right direction here?
I have the same issue as well. Not able to find exact solution to match with shopify sales timeline report.
I have consider order created at, cancelled at , and refund dates as well, but still not perfect match for all dates.
Mostly issue with the orders which have Items removed from order line items.
We have consider Cancelled_at, created_at and refund_at dates for calculation from order api.
But still there are many amount that is not match with shopify reports.
Yeah, I went through two different tables and tried various different date and aggregate fields, but none of them seemed to work. Shopify were not able to help, or provide details of the schema. I had to go through Singer.io who built the Stitch Shopify integration, and there's no support to help me there.
I'm running into similar issues, and haven't quite got it figured out, but depending on your schema setup, you should have two tables: shopify_refunds and shopify_refund_line_items. I believe the timestamp in those two tables corresponds to the actual refund time, not the initial order date. Hope this helps
Hi Rob, thank you for the advice!
I have been spending some more time looking into this. Our Shopify schema has the following tables:
The one I need here is surely order_refunds. That table has the following columns:
Primary Key id
Order_adjustments looks like it has the information I need for some orders but some rows don't have any data in here.
Refund_line_items I can't seem to find refund amount, though there are the following which might be right:
presentment money amount
shop money amount
However, none of them seem right and they are all nested deep within the JSON.
Can confirm the created_at date property on order_refunds does relate to the date of refund, so that's good.