Refund Amount by Refund Date Report

[olimills]

Hi all,

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?

Thanks

Oli

[AnkitVyas]

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.

[olimills]

Glad to hear it's not just me struggling here!

[AnkitVyas]

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.

[olimills]

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.

[rob_cgc]

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

[olimills]

Hi Rob, thank you for the advice! 

I have been spending some more time looking into this. Our Shopify schema has the following tables:

abandoned_checkouts
collects
custom_collections
customers
metafields
order_refunds
orders
products
transactions

The one I need here is surely order_refunds. That table has the following columns:

admin_graphql_api_id
created_at
Primary Key id
note
order_adjustments
order_id
processed_at
refund_line_items
restock
user_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:

subtotal
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.

Oli