How to Reconcile Marketplace Orders, Payouts, Refunds & Fees
A practical reconciliation workflow for matching marketplace orders with settlement lines, refunds, adjustments, and bank payouts in Google Sheets.
Sales and bank deposits rarely match one-to-one. A marketplace may settle several orders together, deduct commissions and programme fees, hold funds, reverse a transaction later, or apply an adjustment that is not present in the order export.
Reconciliation connects the commercial event, the marketplace settlement, and the cash received. The goal is not to force every number into one row; it is to explain each difference with a traceable status and supporting source record.
Gather evidence
Collect orders, finance lines, refunds, and bank transactions
- ✓Order or order-item data with identifiers, status, timestamps, item values, discounts, and buyer-paid totals.
- ✓Finance or settlement data with settlement references, fee types, adjustments, released amounts, and payout dates.
- ✓Refund, cancellation, return, and dispute records when they are separate from the main finance report.
- ✓Bank transactions with value date, amount, currency, payer description, and bank reference.
- ✓A fee mapping that groups marketplace labels into commission, transaction fee, fulfilment, advertising, tax, refund, and other categories.
Avoid false matches
Identify the grain and key of every file
An order file may contain one row per order, order item, package, or status event. A settlement file may contain several financial lines per order. A bank payout may aggregate hundreds of settlement lines. Write the grain at the top of each raw tab before creating lookups.
Preserve marketplace identifiers as text and keep the original values. Build matching keys from stable source fields, not row numbers or display labels. When no single key spans all sources, use a bridge table that connects order IDs, settlement references, and payout references.
A reviewable model
Build six tabs with clear responsibilities
- 1
Raw_Orders
Marketplace order or item records, unchanged from the source.
- 2
Raw_Finance
Settlement, fee, refund, and adjustment lines, unchanged from the source.
- 3
Raw_Bank
Bank deposits or payment-provider transactions.
- 4
Fee_Map
Source fee labels mapped to consistent reporting categories and sign rules.
- 5
Reconciliation
Aggregated order, settlement, expected payout, actual payout, variance, and status fields.
- 6
Exceptions
Only records that require investigation, with owner, reason, evidence, and next action.
Run in layers
Reconcile orders to settlements before settlements to cash
- 1
Normalize signs and currency
Make charges, refunds, and reversals follow one sign convention while retaining original source amounts.
- 2
Aggregate at the matching grain
Summarize order items to order level or finance lines to settlement level before comparing values.
- 3
Match orders to finance
Join using stable marketplace references and classify unmatched records rather than deleting them.
- 4
Calculate expected settlement
Start from the relevant buyer or merchandise amount, then apply mapped discounts, refunds, fees, taxes, and adjustments.
- 5
Match settlements to payouts
Group released settlements by payout reference, date, currency, or documented batch rule and compare them with bank deposits.
- 6
Assign a reconciliation status
Use statuses such as matched, timing difference, partial settlement, refund pending, fee review, missing order, or missing payout.
Investigate intelligently
Classify common differences before treating them as errors
- ✓Timing: the order, completion, settlement, and bank dates fall in different periods.
- ✓Grain: an order has multiple items, packages, fee lines, or refund events.
- ✓Status: cancelled, returned, disputed, or partially refunded orders changed after the first export.
- ✓Definition: the dashboard uses GMV while finance uses net settled amount.
- ✓Currency: local amounts, payout currency, conversion rates, or rounding differ.
- ✓Scope: advertising charges, programme fees, taxes, reserves, or adjustments appear in another report.
Close the period
Use an exception-driven weekly or monthly close
Refresh the source tabs, run data-quality checks, rebuild the reconciliation, and freeze a dated close snapshot. Assign every material exception to an owner and preserve the supporting marketplace or bank reference.
Spyliner can reduce repeated collection for supported orders, finance, and other available datasets. It does not decide your accounting treatment, fee categories, cash timing rules, or materiality threshold; those controls remain with your business and accountant.
Quick answers
Questions people usually ask
Why does marketplace sales value not equal the bank payout?+
Payouts can combine multiple orders and periods, then deduct fees, refunds, taxes, reserves, advertising charges, or adjustments. Timing and currency conversion can also differ.
Should reconciliation be done by order or payout?+
Use both layers: reconcile orders to settlement lines first, then reconcile released settlement batches to bank payouts.
What should happen to unmatched records?+
Keep them in an exception queue with a reason, owner, age, evidence, and next action. Do not delete or manually alter raw source records to make totals agree.
