Finance Total Sales Report export - field Sale ID is a mystery

[macgyver22]

Hello Team,

We access the Admin API with an integration platform to feed our cloud data warehouse with all Shopify order and transaction data.  Our Finance team relies upon reports from the portal to post financials, but we are trying to automate this and bridge the gap between portal reports and our detailed integrations. 

The issue we are running into with this reconciliation is that our data from the Admin API contains Order ID and Line Item ID, and then for payment info Transaction ID -- whereas their Finance Total Sales report contains Order ID and Sale ID.  The "Sale ID" is at the line item level, but it does not match the Admin API Line Item ID -OR- Transaction ID.  

So at the line item level, I don't have any transaction ID match outside of also using product/variant ID.  This does not always work in cases where we show the same variant more than once on an order (e.g. buy 1, get 1 50% off).  Either way, "Sale ID" is a mystery and it seems as if there is misalignment between the Admin API and the GraphQL API when it comes to transaction/record ID's.

Any insights for me?  Thanks in advance!

[AdilRaza]

This is very important question that needs answer from Shopify. I've a full export of the Shopify data into Big Query using a third party tool. It has dozens of tables related to order and orderliness, however, there is no sign of Sale ID anywhere in the structure.

Secondly, Shopify compute Gross Sale and Net Sale on the basis of Sale ID that happen on a given date, and attribute revenue to the date of the Sale ID. Since there is no Sale ID in the exported data via API, it's nearly impossible to match figures with Shopify dashboard.

It is very important that this question is answered!

Many Thanks