Marketplace Reconciliation Checklist

Reconciliation between marketplace sales and external settlement reports is a recurring operational process that protects revenue and prevents surprises. A clear checklist reduces manual effort, shortens review cycles, and surfaces root causes of differences quickly.

This checklist focuses on practical steps finance teams, controllers, and operations managers can follow to reconcile marketplace data against settlement, PSP, and bank reports. It combines data preparation, deterministic matching, and AI-assisted review where needed.

Use this guide to create a repeatable, auditable workflow that handles common problems such as timing differences, fees, refunds, and grouped settlements.

Why this topic matters

Marketplaces, payment service providers, and banks publish different reports that often use differing formats and levels of detail. Without a robust reconciliation process, teams face revenue leakage, delayed variance detection, and time-consuming investigations.

A reliable reconciliation process preserves working capital accuracy, speeds month-end close, and produces evidence for auditors and stakeholders. It also reduces the risk of duplicate refunds, missed settlements, and untracked fees.

Core components

Successful reconciliations rest on a reproducible pipeline. Break the process into data ingestion, normalization, matching, exception handling, and reporting.

Data sources

• Primary internal file (Side A): orders, invoices, or ledger exports representing the business's expected records.
• External files (Side B): marketplace settlement reports, PSP payout files, payment gateway reports, and bank statements.
• Supporting files: fee rate tables, returns/chargebacks, product master, mapping files, and merchant configuration data.

Collect these files in CSV, XLS, or XLSX formats and keep a versioned archive for audit purposes.

Identifiers and mapping

• Identify strong keys: order ID, settlement ID, transaction reference, or UTR where available.
• Map columns consistently: Date column, Amount column, and Identifier column(s) must be explicitly selected for each upload.
• Create mapping files for partner-specific IDs or transformed references to ensure consistent comparison across sources.

Matching logic: rule-based then AI

• Rule-based matching: start with deterministic matches using exact identifier equality and exact amounts.
• Flexible rules: allow one-to-many, many-to-one, net-to-net, and contra matching where settlements are grouped or fees are netted.
• Secondary rules: fall back to date+amount, period-level totals, or similarity-based identifier matching when exact identifiers are missing.
• AI-assisted matching: use AI to propose matches for inconsistent references, partial identifiers, or complex grouping scenarios. The AI should prioritize identifier matches, require reasonable amount balancing, and avoid forced low-confidence matches.

Supporting data and derived columns

• Supporting data enriches records without being part of the core reconciliation. Use it to add missing identifiers, fee calculations, or order metadata.
• Derived columns: create calculated fields (for example, net payout = gross - fees - refunds) using Excel-style formulas or generated expressions to harmonize inputs.
• Reuse derived columns as matching fields or outputs to simplify manual review.

Outputs: matched, partial, unmatched, skipped

• Fully matched: records that reconcile cleanly by configured rules.
• Partially matched: identifiers match but amounts differ — flag for investigation.
• Unmatched: present on one side only and requires discovery of missing reports, delays, or data errors.
• Skipped: incomplete or invalid rows excluded from matching but retained for review (e.g., missing amounts, invalid dates).

Design reports that clearly separate these categories and include contextual columns for reviewer action.

Practical implementation steps

1. Inventory required files and stakeholders.

• List internal reports, marketplace settlement exports, PSP files, and bank statements.
• Identify owners and file delivery schedules (e.g., daily, weekly, monthly).

2. Standardize file formats and an onboarding checklist.

• Define required columns, header row location, and acceptable formats (CSV/XLS/XLSX).
• Create a simple onboarding template for new marketplaces or PSPs so files match expected layouts.

3. Configure mapping and derived columns.

• Select the date, amount, and identifier columns for each file.
• Create derived columns for net payout, converted currency, or adjusted amounts to account for fees and refunds.

4. Run deterministic rule-based matching.

• Execute exact identifier and exact amount matches first to capture high-confidence entries.
• Use grouping rules for settlements that aggregate many orders into one payout line.

5. Apply relaxed rules and AI review.

• Use date+amount and similarity matching for remaining items.
• Let AI propose matches for hard-to-resolve items; review proposals and accept or reject with notes.

6. Investigate partially matched and unmatched items.

• For partial matches, compare fee calculations, currency conversions, or timing differences.
• For unmatched items, verify missing reports, incorrect imports, or duplicated entries.

7. Record manual adjustments and rationale.

• When users perform manual matches or adjustments, capture the reason and make these changes auditable.

8. Produce an audit-ready reconciliation report.

• Export the final status with matched groups, supporting evidence, and a list of skipped records.
• Save configuration and outputs so the reconciliation can be rerun for the next period.

9. Automate and iterate.

• Where possible, automate file ingestion via SFTP, email, or API and schedule reconciliation runs.
• Periodically review matching rules and supporting data quality to reduce exceptions.

Common mistakes to avoid

• Relying solely on amount matching: amounts can align by coincidence; prefer identifiers when available.
• Ignoring supporting data: fee files, returns, and mapping tables often explain most differences.
• Over-accepting low-confidence AI matches: require human review for ambiguous cases.
• Not versioning files and reports: lose audit trails and make investigations harder.
• Failing to track manual interventions: unrecorded manual matches break reproducibility.

Key Takeaways

• Standardize inputs and choose strong identifiers before attempting matching.
• Start with deterministic rules, then escalate to relaxed rules and AI for complex cases.
• Use supporting data and derived columns to explain fees, refunds, and grouped settlements.
• Keep skipped and manual actions visible and auditable to preserve traceability.
• Automate file ingestion and reconciliation runs where stable inputs exist to reduce manual effort.

Conclusion

A disciplined marketplace reconciliation process reduces revenue risk and keeps finance operations efficient. Implementing this checklist helps teams reconcile faster, escalate exceptions sooner, and produce auditable results using marketplace reconciliation best practices.

Start your 14-day free trial with Cointab. No credit card required. 14-day free trial.