Orders6 min read
Match Shopify Payouts to Orders in One Spreadsheet
Use the order number on each Shopify Payments payout transaction as the key between payments and orders, and build one workbook that ties payouts, fees and orders.
In this guide
- Which number links a Shopify payout to an order?
- What goes into the spreadsheet?
- How do I build the workbook step by step?
- How do I match each payment to its order?
- How do I check each payout against the bank?
- What about Klarna, PayPal and other gateways?
- Things to check before you send the workbook
- FAQ
- Try Columna: Order Export & Sheets
To match Shopify payouts to orders, use the payout transactions export as the key: it has one row per charge, refund and adjustment, with the order number, Shopify’s fee and the net amount on each row. Put it in one spreadsheet next to an orders export and a payouts export, look up each transaction’s order number in the orders sheet, and sum the transactions per payout to check them against the bank. You do not need a payment reference or confirmation number from the orders file for money that went through Shopify Payments.
This guide is for merchants and accountants who check payments against orders, often in Germany and the rest of the EU, and who keep asking for “the number that links a payment to an order”. It covers Shopify Payments payouts. Money that another provider pays out to you directly is matched with that provider’s own statement.
Which number links a Shopify payout to an order?
The order number, such as #1042, on the payout transaction. Each payout is made of transactions, and each transaction that belongs to an order carries that order’s number. So the chain is:
- Bank deposit to payout: the payout’s Net and, when Shopify has it, the Bank reference.
- Payout to transactions: the Payout ID on every transaction.
- Transaction to order: the Order column on every charge and refund.
The orders export does not need to carry a payment key, because the payout transactions export carries the order key.
What goes into the spreadsheet?
Three exports for the same month, each on its own sheet of one workbook:
| Sheet | One row per | Key columns | Used for |
|---|---|---|---|
| Payouts | Payout (one bank deposit or withdrawal) | Payout ID, Bank reference, Net | Ticking off the bank statement |
| Payout transactions | Charge, refund or adjustment | Order, Payout ID, Amount, Fee, Net | Linking money to orders |
| Orders | Order | Order, Total, Refunded, Payment gateways | Checking what each order should have brought in |
In Columna: Order Export & Sheets, the first two are presets on the Accounting tab of Templates: Shopify Payments payouts and Payout transactions. The third is any template with one row per order.
The Payout transactions sheet
| Column | What it holds |
|---|---|
| Transaction date | When the charge, refund or adjustment happened |
| Type | Charge, Refund, Adjustment, Dispute and so on |
| Order | The order number, when the transaction belongs to an order |
| Payout date | The issue date of its payout; blank when that payout was issued outside the date range |
| Payout ID / Payout status | The payout the transaction was paid in |
| Amount / Fee / Net | The transaction amount, Shopify’s fee, and what is left |
| Currency | The transaction currency |
The date range selects transactions by the date of the payout they were paid in. Transactions not paid out yet, transfers and test transactions are left out.
The Payouts sheet
One row per payout, selected by the date it was issued, with Payout date, Payout ID, Status, Type (Deposit or Withdrawal), Bank reference, Gross charges, Refunds, Adjustments, Reserves and other, Fees, Net and Currency.
How do I build the workbook step by step?
- In Columna, open Templates, then the Accounting tab. The first time, click Allow payout access on a payout report; Shopify asks you to allow read-only access to your Shopify Payments account and payouts.
- Click Export report on Shopify Payments payouts. The Orders page opens with the report selected. Set the month, click Export, choose CSV or Excel, and click Export again.
- Do the same for Payout transactions, with the same dates.
- Export the orders for the same month with a one-row-per-order template that has Order, Total, Refunded, Financial status and Payment gateways. Add Transaction gateways and Transaction amounts from the Accounting group if you want each order’s own payment lines too.
- Open the three files in one workbook, one sheet each: Payouts, Transactions, Orders.
How do I match each payment to its order?
On the Transactions sheet, add a lookup column next to Order:
=XLOOKUP([@Order], Orders[Order], Orders[Total], "not in this month")
Then read the results by type:
- Charge rows with an order total are payments for orders placed in the month.
- Charge rows marked “not in this month” are payments for orders placed earlier and paid out now. Export the earlier month’s orders, or note them as timing items.
- Refund rows point to the refunded order. If that order is from an earlier month, it is a refund of an older sale.
- Rows with no order, such as adjustments, reserves and disputes, change the payout without belonging to one sale. List them separately.
On the Orders sheet, do the reverse lookup to find orders with no Shopify Payments charge. The Payment gateways column usually tells you why: the order was paid through another provider, by manual payment or gift card, is unpaid, or will be paid out next month.
How do I check each payout against the bank?
- Build a pivot table on the Transactions sheet: rows by Payout ID, values as the sums of Amount, Fee and Net.
- Look up each Payout ID on the Payouts sheet and compare the summed Net with the payout’s Net.
- Match each Paid payout’s Net to a bank credit, using the Bank reference when it is filled in. A Withdrawal is a debit on the bank statement.
- Book the summed Fee as payment processing costs.
When every payout’s transactions add up to its Net, and every Net has a bank line, each deposit is explained down to the order. Our guide to matching Shopify Payments payouts to bank deposits explains every payout column, and the month-end close walkthrough shows how to write up the timing differences.
What about Klarna, PayPal and other gateways?
The payout reports read Shopify Payments only. A payment method that is processed by Shopify Payments shows up in payout transactions like any other charge. A provider that pays you out on its own, with its own settlements, does not: match those orders with the provider’s own statement, and use the Payment gateways column on the Orders sheet to split them off. If your store does not use Shopify Payments at all, the payout reports are greyed out with the reason, and the order exports still work.
Things to check before you send the workbook
- Use the same dates, in your store’s time zone, for all three exports.
- The Order column is the Shopify order name, such as #1042, not an invoice number from an invoicing app. Map the two yourself if your invoices are numbered differently.
- Shopify Payments does not include VAT per payout, so payouts cannot tell you how much VAT you collected.
- The files are worksheets, not bookkeeping entries. Compare the totals with Shopify’s own finance reports and agree the treatment with your accountant.
FAQ
Does the payout transactions export include the order number?
Yes. Each transaction that belongs to an order has the order number in the Order column. Adjustments and other transactions that do not belong to an order leave it blank.
Why doesn’t a payout equal the orders paid out in it?
A payout is a net amount. Fees, refunds, adjustments and reserves are subtracted from the charges before Shopify sends the rest to your bank.
Why is an order missing from my payout transactions?
It was paid through another gateway, is not paid out yet, or is not paid. Payout transactions only list Shopify Payments money that has been paid out.
Can I match payouts if my store doesn’t use Shopify Payments?
Not with the payout reports, which need Shopify Payments. Use your payment provider’s statements and an orders export with the Payment gateways column.
Try Columna: Order Export & Sheets
Columna: Order Export & Sheets exports Shopify orders to CSV or XLSX with line-item properties such as engraving text, gift messages and upload links, processed in your browser, for shops that hand orders to production, fulfilment or accounting. Its Accounting templates give you the payouts and payout transactions files with the order number on every charge, so the three sheets of your workbook come from one place. Try Columna: Order Export & Sheets on the Shopify App Store.