Shopify Payout Reconciliation

Problem

Shopify does not deposit what you sold. It deposits what is left after processing fees, refunds, chargebacks, marketplace tax adjustments and Shop Cash, netted together and wired days later on a schedule that has nothing to do with your fulfillment calendar. One deposit can cover hundreds of orders captured across several days.

All of it has to be reconciled against Business Central before it can be posted. Accounting was doing that by hand, one payout at a time, and it was taking 25 to 30 hours a week.

The hours were the obvious cost. They were not the real one. It was not that the work was being done badly. Three quarters of a person was going into it every week and the answer at the end was still approximately, because there was a clearing account balance nobody could fully explain and no way to explain it except matching hundreds of order lines against a moving target.

That is the part that breaks. Not the accounting, the volume.

There was also a whole class of problem a manual check could not see at all. Reconciling a payout against invoice totals proves the right amount was invoiced. It does not prove the money reached the clearing account, or which account it reached.

Approach

A Python job builds the accounting team’s own reconciliation workbook for each payout, filling its tabs from the Shopify and Business Central APIs and leaving every formula in place. The workbook stays the control record, so the checks are the ones accounting already trusts rather than a second opinion written in code.

Tie against real ledger entries, not document totals. The job pulls the actual general ledger lines for each invoice and credit memo, so the check proves where the money landed and which account it landed in.

Match every charge by invoice number. What Shopify collected against what Business Central posted, per order. An order edited after import gets named and priced instead of surfacing as an unexplained difference at month end.

Match every refund by order number. The mirror check on the credit memo side, which catches a refund that never made it into the ledger.

Use the total tie as a backstop. Order charges, refund credit memos and the journal have to net to zero for the payout. If the per-order checks pass and the total does not, the difference is an entry sitting in the clearing account with no order behind it, and it equals the miss exactly. Nothing else in the process could do that check.

Sweep both directions once a week. Every other check starts from a Shopify transaction and confirms the ledger agrees, which makes all of them blind to ledger documents with nothing behind them. A manually keyed invoice, a duplicate, a document on the wrong customer. This one runs the other way so those surface too.

Watch for drift rather than a number. Clearing balance, plus open unposted sales orders, less open unposted credit memos, should roughly equal Shopify’s pending deposits. It never ties exactly, because the two systems post on slightly different clocks. The signal is not the size of the gap, it is whether the gap is stable or growing and which of the four figures moved.

Separate Shop Cash from every other non-card tender. Only Shop Cash is money Shopify owes back. Gift cards and manual payments are not, and putting them all in one account would turn it into a dumping ground. The job reads the payment gateway on each order so only genuine Shop Cash reaches the receivable.

It runs on the schedule accounting already used, three to five days after a payout settles so Business Central has finished invoicing, and emails the finished workbooks each morning with the exceptions summarised in the body.

What it found

The controls started naming things in the first week.

Shop Cash was being counted twice. When a customer pays part of an order with Shop Cash, the ERP invoices the whole order but the payout only carries the card portion. The difference was being booked as extra revenue, which recorded the same sale twice and left the Shop Cash amount stranded in the clearing account permanently. Small per order, structural and silent across a year of them.

It now flows through its own account, tracked per order and aged until Shopify remits it. No amount of reviewing the balance would have found that. It took matching at the order level.

A clearing account had been switched mid-day and nobody had dated it. Because the account assignment follows order capture time while the ERP invoices at ship, orders kept posting to the old account for days afterwards. Reconciling a single payout pinned the switch to a twenty eight minute window, which turned an unexplainable balance into a list of specific documents.

Some refunds had been netted into the original invoice rather than raised as a credit memo, so the refund existed in Shopify and effectively nowhere in the ledger. Harmless one at a time. Not harmless as a pattern.

Result

25 to 30 hours a week back, around 1,300 hours a year, or roughly two thirds of a full-time role.

Every payout now arrives reconciled, with the journal entry ready to post and real exceptions flagged with a plain-English reason. Reviewers read a short list instead of checking every order. It runs unattended on a schedule, with no Excel and no desktop involved.

The comparison that matters is not the hours though. The old process consumed three quarters of a person and still produced a balance nobody could fully defend. The new one runs before anyone gets in and names the specific order when something is wrong. It does not say the account looks off.

Where this applies

If you sell on Shopify and your books live in an ERP rather than a simple bookkeeping tool, you have this problem whether or not it has surfaced yet. It usually shows up as a clearing account nobody can fully explain, a reconciliation that quietly slipped from weekly to monthly, or a month end that takes three days longer than it should.

The pattern is not Shopify specific either. Any high volume settlement feed has the same shape, whether that is marketplace payouts, payment processors or third party logistics billing. Many small transactions, netted into a few large ones, on a timetable that does not match yours.