Note: Businesses that have recently configured Amazon Payment Services (APS), may see a bunch of 1 SAR/Dirham transactions while performing a reconciliation. These are test transactions performed while onboarding the business to APS. Such transactions are refunded with void invoices and do not appear on the Digital Payments report.
Open an Excel spreadsheet.
Copy and paste the data from the Digital Payments report (Zenoti) in sheet 1.
Name this sheet Zenoti.
Add another sheet and paste the data from the Amazon Settlement report.
Name this sheet as Amazon.
In sheet Zenoti, navigate to the last column of the report.
Perform a VLOOKUP with the following parameters:
Variable 1 - Zenoti's Transaction Number that is on column E.
Variable 2 - Date range in the Amazon sheet from columns D to M (Merchant Reference to RRN).
Variable 3 - Return the value as RRN (This is the bank reference number that indicates settlement and is used for verification in the bank statement).
Variable 4 - False (indicated by '0')
Note: Your VLOOKUP formula might look like this: =VLOOKUP(E:E,Data!D:M,13,0). This formula is just for reference.
In the Zenoti sheet, the Transaction Number column is now mapped with the corresponding bank RRN numbers. If transactions do not have a corresponding RRN number, they are not settled. If the RRN number is NA, it means that the transactions are refunded. (Refund transactions will have the word "refund" at the end of their Zenoti's transaction number).
You can also perform a vice-versa reconciliation where you are looking up Zenoti's data in the Amazon sheet. This second step reconciliation is performed just for data validation and is optional. This is done when guests have queries regarding an additional deduction from their card due to a network outage or processing delay.