Orders7 min read
Shopify Month-End Close: Tie Orders to Payouts and Transactions
Why Shopify order and payout exports never tie out on the first pass, and a month-close routine that bridges orders, refunds, fees and bank deposits.
In this guide
- Why don’t my Shopify orders and payouts match?
- What do I need before I start?
- How do I close the month step by step?
- How do I get sales by SKU after refunds and discounts?
- Shopify’s exports or an export app: which should I use?
- Things to check before you send the files
- FAQ
- Try Columna: Order Export & Sheets
Your Shopify orders export and your Shopify Payments payouts export never tie out on the first pass because they do not share a grain: orders are one row per order dated when the order was placed, payouts are one row per bank deposit dated when Shopify paid it out. The bridge between them is the payout transactions export, which has one row per charge, refund and adjustment with the order number on it. Close the month in three files, in that order, and list the timing differences instead of hunting for “missing” money.
This walkthrough is for small shops that close the month for an accountant and keep copying orders into a spreadsheet to make the numbers meet. It uses Shopify Payments; if you take payments through another provider, the same logic applies with that provider’s statements.
Why don’t my Shopify orders and payouts match?
Each export answers a different question about the same money:
| Export | One row per | Dated by | Includes fees? |
|---|---|---|---|
| Orders | Order (or line item) | When the order was placed | No |
| Payout transactions | Charge, refund or adjustment | The payout it was paid in | Yes, per transaction |
| Payouts | Payout, which is one bank deposit or withdrawal | When the payout was issued | Yes, added up |
So the same month, exported three ways, contains different money:
- Orders placed on the last days of the month are often paid out in the next month. They are in this month’s orders file but next month’s payout files.
- Refunds of older orders sit on the original order in the orders export, whatever the month. In the payout files they appear in the payout they were taken from.
- Fees never appear on an order. The bank receives the amount after fees.
- Orders paid another way, such as PayPal, a manual payment or a gift card, are in the orders file but not in Shopify Payments payouts.
- Reserves, adjustments and disputes change a payout without changing any order total.
None of these is an error. They are reconciling items, and month-close is mostly writing them down.
What do I need before I start?
- The month’s dates, in your store’s time zone (Shopify Settings > General). Use the same dates for every file.
- Your bank statement for the month.
- A way to export payouts and payout transactions. Shopify lets you export payout transactions from Finance > Payouts, as described in its payout details guide; the CSV is emailed to you. In Columna: Order Export & Sheets, both reports are presets on the Accounting tab of Templates.
- An orders export with one row per order and the money columns: subtotal, discounts, shipping, taxes, total, refunded amount and payment gateways.
How do I close the month step by step?
Step 1: Tick off the bank with the payouts file
Export Shopify Payments payouts for the month. The date range selects payouts by the date they were issued, and each row is one payout with its gross charges, refunds, adjustments, reserves, fees and Net.
- Match each Paid payout’s Net to a bank credit. Use the Bank reference when it is filled in.
- Look for a Withdrawal as a debit on the bank statement, not a credit.
- Note payouts that are Scheduled or In transit at month end as timing differences: they reach the bank next month.
Our guide to matching Shopify Payments payouts to bank deposits covers every column of this file.
Step 2: Open each payout with the payout transactions file
Export Payout transactions with the same date range. The range selects transactions by the date of the payout they were paid in, so this file describes the same money as the payouts file. Transactions not paid out yet are left out.
- Build a pivot table on Payout ID with the sums of Amount, Fee and Net.
- Check that each payout’s summed Net equals its Net in the payouts file.
- Book the summed Fee as payment processing costs for the month.
Step 3: Match payout transactions to orders
Export your orders for the month, one row per order. Then, in the payout transactions file, look up each Charge row’s Order in the orders file, for example with XLOOKUP.
- Charges whose order is not in this month’s orders file belong to orders placed last month and paid out this month.
- Orders with no charge were paid another way, are unpaid, or are paid out next month. The payment gateways column tells you which.
- Refund rows point to the refunded order. If that order is from an earlier month, it is a refund of an older sale, not a change to this month’s orders.
Step 4: Write the reconciling items
Put one short table at the top of the folder you send your accountant:
| Item | Amount | Why |
|---|---|---|
| Orders placed this month, paid out next month | Timing | |
| Orders placed last month, paid out this month | Timing | |
| Refunds of earlier orders | Refund date differs from order date | |
| Shopify Payments fees | Taken before the deposit | |
| Orders paid through other gateways | Not in Shopify Payments payouts | |
| Reserves, adjustments, disputes | Change the payout, not the order |
When orders, minus those items, equal the bank deposits, the month ties.
How do I get sales by SKU after refunds and discounts?
Rebuilding sales by SKU from the orders CSV by hand each month is where most of the copying happens. In Columna a template can write a summary instead of one row per order: choose One row per SKU in Group rows and each row has the number of orders, quantity, Refunded quantity, Gross sales, Discounts, Net sales and Tax.
Two things to know before you hand it on:
- Net sales is gross sales minus discounts, including order discounts and discount codes allocated to the line items. Refunds are not subtracted; use Refunded quantity, or a line-item export with the Item refunded amount column, to adjust for them.
- Shipping and order-level fees are not in the SKU summary, because they do not belong to a product. They stay in the one-row-per-order file.
Shopify’s exports or an export app: which should I use?
| Shopify’s own exports | Columna: Order Export & Sheets | |
|---|---|---|
| Payout transactions | Exported from Finance > Payouts, emailed as CSV | Payout transactions preset, downloaded as CSV or Excel |
| One row per payout | View payouts in Finance > Payouts | Shopify Payments payouts preset |
| Orders, one row per order | Orders CSV puts each line item on its own row | Saved template with one row per order |
| Sales by SKU | Shopify’s sales reports under Analytics > Reports | Template summary with one row per SKU |
| Repeating next month | Repeat each export | Same templates, new date range |
Both routes give you the same Shopify data. Shopify’s finance reports remain the source for period totals; use the exports to explain them.
Things to check before you send the files
- Use the same date range and time zone for every file.
- The Order column in payout transactions is the Shopify order name, such as #1042, not an invoice number from an invoicing app.
- Shopify Payments does not include VAT per payout, so payouts cannot tell you how much VAT you collected.
- These files are worksheets, not bookkeeping entries or tax returns. Compare the totals with Shopify’s finance reports and agree the treatment with your accountant.
For which order columns accountants usually ask for, see our Shopify order export for accounting guide.
FAQ
Why is my Shopify payout less than my sales for the same days?
A payout is a net amount. Shopify subtracts refunds, fees, adjustments and reserves from the payments customers made before it sends the rest to your bank.
Which date should I use for month-end: order date or payout date?
Use both, in different files. Orders are dated when they were placed; payout files are dated by payout. The difference between the two is the timing item in your reconciliation, so agree with your accountant which one drives the books.
Why are some orders missing from my payout transactions?
They were paid through another gateway, are not paid out yet, or are not paid at all. Payout transactions only list Shopify Payments money that has been paid out.
Does the payouts export include VAT?
No. Shopify Payments does not include VAT per payout. Take VAT from your tax reports or a sales journal.
Does Columna work if I don’t use Shopify Payments?
The order exports and summaries work with every payment provider. The two payout reports need Shopify Payments and are greyed out on stores that do not use it.
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 for the month, and a saved template gives you the same orders file every time. Try Columna: Order Export & Sheets on the Shopify App Store.