Retrieve "Shopify Payments" fees by Order # for reconciliation with Accounting + Bank Deposits?

Question

Reconciling deposits, within an accounting system, coming into a Shopify User's bank account from a Shopify store running Shopify Payments (or otherwise) appears to be one of the things people frequently stumble on — based on the number of requests about the topic on Shopify's Support Community Forums, for example: https://community.shopify.com/c/Shopify-APIs-SDKs/Fees-charged-by-Shopify-via-API/m-p/265261/

The issue (for accounting) is that Shopify Payments removes the Fee Amount (for a full list of fees see: https://www.shopify.com/pricing) prior to depositing the balance of the transaction into a Store Owner's bank account.

Shopify Fees generally look something like: 2.6% of transaction value + $0.30

Calculation of the fees that should be taken out based on the above is pretty simple (hard code the fee schedule), until those fees change at some point in the future.

How does this impact Integrations?

For many of the available simple integrations this causes a discrepancy between the invoice amount of money hitting the bank account and the amount of money we 'Expect' to hit the bank account based on the Shopify Invoice Amount that is readily retrieved via Shopify's API by many such solutions.

That discrepancy means that it is necessary for an Accounting based user to manually deduce which Shopify Invoice is associated with which incomming bank deposit — and then manually add a negative line item to account for the difference / balance the books.

This is the suggested methodology coming from several different Accounting System / Software vendors (specifically Xero and Quickbooks) when asked how their customers solved the above issue.

I know that there are solutions out there that attempt to solve all of the above problems (with varying degrees of success), but due to the amount of Kitting that my current situation involves none of them adequately solve the other required functionality for our business.

We need to be able to find the fees (or alternately calculate them ourselves) and add those fees to the invoice so our accounting system is expecting the correct amount of funds to hit our bank account — hence the question: How can we retrieve the current accounts "Shopify Payments" fees / rates.

Ideally I want to avoid hard coding this information since Shopify likely does change the fees (potentially with only minor notification) meaning that at that point our integration would break. It would be much better to be able to retrieve the fee schedule for a given account via API request thereby avoiding issue if the fees change.

I have multiple clients who are also facing the same issue — so I figured it was worth checking to see if anyone has faced / solved the problem, and discuss my current solution

What does Shopify Say?

Generally their comment is that there is no road map to including the fees deducted from an invoice when responding to an API Request as taken from: https://community.shopify.com/c/Shopify-APIs-SDKs/Fees-charged-by-Shopify-via-API/m-p/265261/ (which seems strange) — meaning that 'wont do' is the best classification for the feature request that would provide this information with simple / readily available calls to the currently available API Endpoints.

Without access to Fee amounts on a per order basis I am not sure how Shopify Stores are currently interfacing with their account systems.

I would love to hear solutions from any Shopify user who may have encountered this issue previously! I opened a ticket request with support so I will see what I get back over there as well and post back.

Answer 1

OK so this isn't a total solution but I am updating as I go and will continue to edit this answer until I find a full solution (or until someone who has one adds an answer).

Whitelist / API Endpoint Approval Required

I found a Shopify API endpoint that should give the information I am looking for over here: https://help.shopify.com/en/api/reference/shopify_payments/transaction

According to the Shopify API docs the response should contain the Fee Amount and the API Endpoint would be: /admin/shopify_payments/balance/transactions.json

That would get me exactly what I need, except that sending a properly formatted request to the avoid Endpoint gives the following response:

{
    "errors": "[API] This action requires merchant approval for read_shopify_payments_payouts scope."
}

Basically it seems like it needs to be approved per merchant making it a less than ideal solution for you guys as an App. I requested the functionality and will add to this if it makes something easier (when / if it's granted to our account).

Public Access to Similar Fee Data

While playing around with some of the other endpoints I ran into this one (which IS public and accessible for Apps — I am a partner / develop Shopify Apps myself) which seems to provide similar functionality:

https://help.shopify.com/en/api/reference/orders/transaction

When making a call (and receiving the "kind": "sale" result for a given Order Id) I get the following pieces of JSON that are interesting:

        "gateway": "shopify_payments",
        "status": "success",

and more importantly:

"balance_transaction": {
                    "id": "XXXXXXXXXX",
                    "object": "balance_transaction",
                    "amount": 12881,
                    "available_on": 1553040000,
                    "created": 1552943224,
                    "currency": "usd",
                    "description": "[email protected]",
                    "exchange_rate": null,
                    "fee": 339,
                    "fee_details": [
                        {
                            "amount": 339,
                            "application": "ca_1vQrdCwnvOuC2Ypn5R9whwXkGxb4XJjx",
                            "currency": "usd",
                            "description": "Shopify Payments application fee",
                            "type": "application_fee"
                        }
                    ],

Where the JSON 'amount; property corresponds to the invoice sales amount (including shipping) as a decimal free value.

The "fee": 339 appears to indicate the fee amount deducted by Shopify Payments (based on the JSON idicator attached: description": "Shopify Payments application fee").

Yep. That's the Fee.

After checking the Shopify Account tier (in this case Advanced) the fees deducted should be: 2.4% + 30¢ meaning that the total does match — 2.4% * $128.81 = $3.09144 + 30 cents (per transaction) = $3.39.

So basically everything exists (with no whitelisting required) to get the fee amounts on a per order basis for Shopify Payments users. This probably includes the Shopify Fees charged to external gateways as well although I don't use one so I can't say for sure.

That endpoint functionality means Shopify Fees likely COULD be added to any Shopify App that integrates with an accounting system — but also means it is relatively easy to pull in after the fact if the integration you are working with deposits the Invoiced amount into accounting but DOES NOT add a negative line item associated with the Shopify Fees — like mine does.

Still leaves me with some questions but at least the initial 'How do I find the Shopify Fees?' question is taken care of.