Internal vs PG Reconciliation for eCommerce Orders: A Complete Guide for Finance Teams

Internal vs PG reconciliation is the process of comparing internal order records with payment gateway, wallet, gift card, COD, BNPL, settlement, and refund reports.

For eCommerce and D2C brands, customer collections do not come from one source. Orders may be paid through payment gateways, wallets, gift cards, COD logistics partners, BNPL providers, checkout platforms, or refunds. The internal order system may show the expected order value, while external reports show the actual payment, settlement, remittance, or refund value.

This reconciliation helps finance teams answer:

• Are all internal orders paid, settled, or remitted correctly?
• Are all payment gateway transactions linked to valid orders?
• Are COD remittances matched with AWB or order references?
• Are wallet and gift card payments reflected correctly?
• Are refunds and exchanges adjusted properly?
• Which orders are missing, mismatched, duplicated, or delayed?

For high-volume eCommerce finance teams, internal vs PG reconciliation is essential for revenue accuracy, settlement tracking, refund control, COD monitoring, and audit readiness.

What Is Internal vs PG Reconciliation?

Internal vs PG reconciliation compares the company’s internal order report with multiple external payment and settlement reports.

Side A: Internal order report
This represents the company’s own order data, usually from an order management system such as Unicommerce. It may contain order amount, display order code, exchange order reference, tracking number, payment reference, payment ID, order notes, and order date.

Side B: External payment and settlement reports
This includes partner-side reports such as payment gateway files, settlement reports, wallet reports, gift card reports, COD remittance reports, BNPL reports, shipping partner remittance reports, and refund reports.

The goal is to confirm whether every internal order amount is correctly supported by external payment, settlement, COD, wallet, gift card, or refund data.

Reports Involved

Side A: Unicommerce Order Report

The internal order report represents the business’s expected transaction view.

Important fields include:

• Final calculated order amount
• Display order code
• Exchange original order ID
• Tracking number
• Payment reference
• Payment ID
• Payment notes or attributes
• Order date

These fields are useful because different external partners may not use the same identifier. Some transactions may match by order ID, some by payment reference, some by AWB, and some by refund or exchange reference.

Side B: Payment, COD, Wallet, Gift Card, BNPL, and Refund Reports

The external side includes several payment and settlement sources, such as:

• Payment gateway transaction reports
• Razorpay settlement reports
• Cashfree settlement reports
• Wallet reports
• Gift card reports
• COD remittance reports from logistics partners
• Shipping or aggregator reports
• PayU reports
• BNPL or affordability partner reports
• Refund reports

These reports may contain fields such as transaction amount, final amount, net payments, remitted amount, COD amount, signed amount, signed order value, order value, refund amount, merchant transaction ID, platform order number, AWB, order number, Shopify order number, payment ID, settlement date, remittance date, shipped date, and refund created date.

Because every report uses different columns, internal vs PG reconciliation needs flexible reference and amount matching.

How Matching Typically Works

Internal vs PG reconciliation usually depends on three key elements:

1. Reference
2. Amount
3. Date

A typical matching process looks like this:

1. The Unicommerce order report is uploaded.
2. Payment gateway, wallet, gift card, COD, BNPL, shipping, and refund reports are uploaded.
3. Internal references such as display order code, payment reference, tracking number, exchange order ID, and payment ID are compared with partner-side references.
4. Internal final amount is compared with transaction amount, final amount, net payment, remitted amount, COD amount, order value, or refund amount.
5. Order date is compared with transaction date, settlement date, remittance date, shipped date, or refund created date.
6. Records are categorized as matched, partially matched, unmatched, or skipped.

For example:

• Internal order report shows an order value of ₹2,000 with a payment reference.
• A payment gateway report shows a transaction of ₹2,000 with the same merchant transaction ID.
• The transaction is treated as matched.

If the reference matches but the amount differs, it becomes an amount mismatch.

If the order exists internally but not in any external report, it becomes an internal-only exception.

If an external report contains a transaction but no matching internal order is found, it becomes an external-only exception.

Why This Reconciliation Is Complex

This reconciliation is more complex than a simple sales vs payment gateway match because it covers many collection and adjustment channels.

Common complexities include:

• Multiple payment gateways
• COD remittance from different logistics partners
• Wallet and gift card reports
• BNPL settlement reports
• Refund reports
• Exchange order references
• AWB-based matching
• Payment IDs stored differently across systems
• Settlement dates differing from order dates
• Refund dates differing from sale dates
• Net payment values differing from gross order values

One order may be paid through a gateway, another through COD, another through wallet or gift card, and another may be refunded or exchanged. A good reconciliation process must identify the correct source and compare the right fields.

Common Exceptions in Internal vs PG Reconciliation

1. Internal Order Missing in External Reports

This happens when an order exists in the internal report but no matching payment, COD, wallet, gift card, BNPL, or refund record is found.

Possible reasons include:

• Payment failed after order creation
• Wrong partner report was uploaded
• Settlement is delayed
• COD remittance has not been received
• Payment reference is missing
• Order was cancelled, returned, or exchanged
• Transaction belongs to another period

This exception should be reviewed because the business may be expecting money that is not confirmed by any partner report.

2. External Transaction Missing in Internal Orders

This happens when a partner report shows a payment, settlement, remittance, or refund, but no matching internal order is found.

Possible reasons include:

• Internal order report is incomplete
• Payment callback failed
• Order was stored under another reference
• Transaction belongs to another period
• Duplicate external record exists
• Manual payment or adjustment was processed separately

This can create accounting, customer support, and reporting issues.

3. Amount Mismatch

Amount mismatches occur when the reference matches but the amount differs.

Possible causes include:

• Partial payment
• Gateway fee or adjustment
• Wallet or gift card split
• COD short remittance
• Refund or reversal impact
• BNPL settlement difference
• Rounding issue
• Incorrect internal final amount

Amount mismatches directly affect collections, receivables, and revenue reporting.

4. COD Remittance Mismatch

COD reconciliation depends heavily on AWB, order number, shipped date, remittance date, and COD amount.

Common COD exceptions include:

• Delivered order not remitted
• Short remittance
• AWB not found in internal data
• Remittance received against wrong order
• Logistics partner report missing
• Duplicate AWB entry
• COD amount mismatch

These issues are important for D2C brands with high COD order volumes.

5. Refund Mismatch

Refunds may appear in a separate refund report and may use refund IDs, platform order numbers, or original transaction references.

Common refund issues include:

• Refund processed but not recorded internally
• Refund recorded internally but missing externally
• Refund amount mismatch
• Refund linked to the wrong order
• Refund appears in a later period
• Duplicate refund entry

Refund mismatches affect customer liability and revenue reversal accuracy.

6. Wallet or Gift Card Mismatch

Wallet and gift card transactions may not follow the same format as payment gateway transactions.

Possible issues include:

• Gift card redemption not linked to order
• Wallet payment missing internally
• Net payment amount mismatch
• Partial wallet usage not captured
• Duplicate gift card transaction
• Order split across wallet and another payment method

These transactions should be reviewed separately because they may not behave like normal gateway payments.

7. Date Difference

Internal reports may use order date, while external reports may use transaction date, settlement date, remittance date, shipped date, or refund created date.

Date differences can happen due to:

• Settlement cycles
• COD delivery timelines
• Refund processing delays
• Gateway reporting cutoffs
• Month-end timing differences
• Shipping and delivery delays

Date differences are common, but they should be visible in the reconciliation output.

Why Manual Excel Reconciliation Is Difficult

Many finance teams try to reconcile internal orders with multiple external reports manually in Excel.

The usual process includes:

1. Exporting the internal order report.
2. Downloading payment, wallet, gift card, COD, BNPL, shipping, and refund reports.
3. Cleaning order IDs, AWB numbers, payment IDs, and references.
4. Matching using lookup formulas.
5. Comparing amounts across different report formats.
6. Separating prepaid, COD, wallet, gift card, refund, and BNPL transactions.
7. Preparing an exception report.

This becomes difficult because there are too many files, formats, and references.

Common Excel challenges include:

• Wrong reference column selected
• Broken formulas
• Missed COD or refund files
• Duplicate records not detected
• Amount signs handled incorrectly
• Manual copy-paste errors
• No clear audit trail
• Repeated effort every period

What a Good Internal vs PG Reconciliation Process Should Include

A reliable process should include:

• Correct internal order report selection
• Mapping for all payment, wallet, gift card, COD, BNPL, and refund reports
• Multiple reference matching
• Amount comparison by transaction type
• AWB-based COD matching
• Refund and exchange handling
• Duplicate detection
• Date difference visibility
• Matched, partially matched, unmatched, and skipped categories
• Audit-ready output

The output should clearly show which orders are fully matched and which need follow-up.

How Cointab Helps

Cointab can help finance teams automate internal vs PG reconciliation by comparing Unicommerce order data with multiple external payment, COD, wallet, gift card, BNPL, shipping, and refund reports.

Finance teams can map the required fields once and reuse the same workflow for future periods.

Cointab helps teams:

• Upload internal and partner reports
• Match using order ID, payment ID, AWB, tracking number, platform order number, and refund ID
• Compare final order amounts with transaction, settlement, remittance, wallet, gift card, and refund amounts
• Identify fully matched records
• Highlight amount mismatches
• Show internal-only and external-only exceptions
• Review skipped or invalid records
• Download audit-ready Excel reports

This reduces manual Excel work and gives finance teams a clearer view of real exceptions.

Business Value

Internal vs PG reconciliation helps eCommerce finance teams:

• Validate order-level collections
• Track payment gateway settlements
• Reconcile COD remittances
• Control wallet and gift card usage
• Review refunds and exchanges
• Detect missing or delayed partner payments
• Improve month-end close
• Strengthen audit documentation
• Reduce manual reconciliation effort

It also helps finance, operations, and customer support teams resolve order-level payment issues faster.

Best Practices

Finance teams should follow these best practices:

• Reconcile internal orders and partner reports regularly
• Use multiple references, not only order ID
• Separate prepaid, COD, wallet, gift card, BNPL, and refund logic
• Match COD using AWB and order references
• Review amount mismatches by partner
• Track delayed remittances and refunds
• Check duplicate payment and AWB references
• Maintain period-wise reconciliation history
• Document manual corrections clearly

Conclusion

Internal vs PG reconciliation helps eCommerce finance teams confirm whether internal order records match external payment, settlement, COD, wallet, gift card, BNPL, shipping, and refund reports.

Because this workflow includes multiple payment partners, COD partners, wallet reports, gift card reports, settlement reports, refund reports, and shipping references, manual reconciliation can become slow and error-prone.

With Cointab, finance teams can automate internal vs PG reconciliation, reduce Excel effort, identify exceptions faster, and generate audit-ready reports for review.

Start your 14-day free trial with Cointab and automate internal vs PG reconciliation without relying on manual Excel work. No credit card required.

Visit: https://www.cointab.net/