Apaleo Pay Reconciliation with make.com
We implemented a MAKE.com scenario that reads the settlement reports from the Apaleo Payment API and converts them to a human-readable and accounting-friendly format.
->If you are not familiar with MAKE, check out our section and articles in the community: https://community.apaleo.com/c/no-code-automations/
-> To connect this template to your Apaleo Account, go here
What does the template do?
In the first run, the scenario will load all settlements of the last 14 days. You can define a property filter to only process settlement files for certain properties. You can schedule the template to run daily or weekly or also monthly and it will then always process all settlements since the last run.
The output currently has two delivery methods implemented:
- Upload the result to a Google Sheet and then mail you a link to the newly created file
- Send an email via Microsoft 365 mails and attach the result as a CV file
The user can choose which of the delivery mechanism they would like to use and deactivate the one they don’t need. Also, they can add other delivery flows and for example convert the resulting data into a CSV file that can be imported into DATEV or other finance systems directly.
What results does the template produce?
The template converts the settlement files into an accounting-ready format and allows to combine settlements of multiple properties and days into one file to make it easier to process them.
Only merge settlement reports into one file for different properties if the legal entity operating those properties is the same. Ideally, the payouts from the settlements you merge should also go to the same payout account, but this depends on how your finance system allows you to reconcile your bank statements.
| Timestamp | The original time stamp of the respective payment transaction. If it is a settlement or refund this time stamp tells you when exactly the sales or refund action took place. For chargebacks and second chargebacks this will reflect the date and time of the original sales transaction and can be months back. |
| BookingDate | The booking date reflects the date the end of day closing was done and all the respective transactions and fees were finally booked to your merchant account. This depends also on your payout schedule, which is by default set to Sales Day + 2. That means transactions for the August 18th are booked to your merchant account end of day August 19th and paid out on August 20th. For weekends Friday, Saturday and Sunday is closed Monday end of day and paid out on Tuesday. |
| Debited Account | In the debited accounts you see all the different payment accounts for which the original payment was booked in the Apaleo Finance & Accounting module. The account names are aligned with the account names from Apaleo Accounting, so by clearing out one by one using the Psp Reference you should be able to reconcile the payment accounts. It is important that no manual payment postings happen on the same payment accounts you use for payments processed by Apaleo Pay as those payments never show up in the settlements and thus will stay open. Additionally for deducted fees the amount will be booked to the Apaleo Pay Creditor account as a prepayment to the fees which are settled in the invoice at the end of the month. For an adjustment of the deposit to secure your merchant risk and adjustments to the refund reserve the debited account will be Apaleo Pay Deposit and Apaleo Pay Refund Reserve respectively. The payout will be booked from your merchant account to Bank Account. |
| Credited Account | The credited account is always your Apaleo Pay Merchant Account. On this account the money of all the different payment schemes are collected, fees and additional costs are deducted and adjustments to the deposit or the refund reserve are counter-booked with this account. The remaining amounts are paid out to your registered payout account depending on the payout schedule you agreed with your CS agent (default is Sales Day + 2) |
| Currency | The currency the transaction was processed in |
| Amount | The amount. If the amount is negative it is taken from the credit account and booked on the debited account. |
| Command | The Commands allow you to filter for certain actions in order to reconcile your monthly invoices. More to that later. |
| Psp Reference | Each payment transaction has its unique id called the Psp Reference. Any modification to this transaction like multiple separate captures, refunds or chargebacks will have the same reference. This supports you in reconciling the payment transactions as the Apaleo Pay transactions in the Apaleo Finance & Accounting module also carry the same reference. |
| Merchant Reference | This is the reference set by the Apaleo Finance & Accounting module into the transaction and will be the folio id the payment was originally posted to. For transactions initialized by any app from the Apaleo ecosystem the references will be different. Some booking engines set the session id of the booker into the transaction and allow you to trace back the payment to the booking request logs. |
| Merchant | This is the name of the merchant account. The merchant account structure differs based on your legal entity structure and your own requirements on how granular you want to be able to direct payouts to different payout accounts. At least eCommerce and POS merchants are always separated as they do carry different risk and deposit rules. |
| Batch | For each and every payout day a batch is generated and the batch numbers by merchant account increase continuously by one. Please note that not every batch process necessarily results in a payout. If the payout amount is very small or all the amounts are consumed by fee deduction and any adjustments to the deposit or refund reserve a batch can also result in no payout, but still has to be processed in order to properly reconcile. |
| BatchDate | This is the date the final settlement batch was created and the payout has been advised. Depending on your bank the actual payout might only show up on your bank statement with some business days delay. |
How to reconcile my monthly invoice?
The following is an example of an invoice for a German customer for August 2022. For German customer also VAT has to be applied and this makes the invoice reconciliation the most compliacated for now.
As you can see all the fees are shown as already paid, as Apaleo automatically deducts the fees from your settlements. If you filter in the resulting file for the booking date August and for all the prepayments to the Apaleo Pay Creditor account you will get to the EUR 2,773.33 in the example above.
The difference of EUR 29.79 will then be taken from one of your next settlements via a line called Invoice Deduction referencing the invoice number.
Right now we can not yet deduct the VAT as well from the next settlement, so the remaining amount due of EUR 532.59 has to be taken separately from the credit card or bank account you provided to Apaleo when activating Apaleo Pay.
What does the invoice deduction contain?
There are transactions that do not lead to a settlement amount. For example when a payment account is tokenized for storing it on file or when a transaction got refused. Also for those transactions fees apply. Additionally for some eCommerce transactions the revenue protect risk rules are applied and finally the Mastercard QMR fees are also not related to a specific transaction. Those additional fees only show up at the end of month invoice and will then be taken from one of your next settlements via the Command InvoiceDeduction.