Level 12

Merchant Residuals System Overhaul

The Challenge A merchant processing company had retained us to build and maintain their residual (commission) reporting system since early 2008. As time went on, the system was regularly expanded to handle additional calculations and logic related to the payment residuals. Unfortunately, the way the system was originally built was not ideal for the volume and complexity of calculations that were eventually asked of it. As the system aged and the size of data grew, the calculations began to take longer and longer and the server resources used grew larger and larger. In addition, the needs of the customer were changing. Their monthly residuals process involved manually editing the Excel reports after they were generated to handle edge cases that the residuals calculator didn’t know about or didn’t have the data to handle. This meant that the actual residuals amounts paid to the customer would vary between the file and that data in the database, leading to difficulties when trying to report on residuals. As time progressed, our customer desired to extend their residual reporting with more complex pricing options than the original system could support. In addition to this, their residuals team was constantly stretched to complete their monthly reporting cycle due to the amount of manual intervention needed. Reducing this workload was a priority, and would require a new reporting process. The need to be able to ensure that the calculated residuals numbers were correct, as well as the reports themselves, also rose in importance. In 2014, after years of recommending an overhaul of this system, our customer was able to allocate the funds necessary to begin working on the project. The top priorities were: All residuals related calculations had to happen in the system. No more edits to the Excel reports after they were generated. The final residuals calculations would need to be stored in the database for easy reporting. Performance would need to be increased significantly. Feature enhancements that would make the calculation process more accurate and prevent or detect errors. Add non-bankcard payment products to the residuals reports. The Solution Our solution was broken up into three main components and stages so we could use an iterative development process: The residuals calculator Report generation Everything else We began working with the customer to fully spec the residuals calculation process, in order to determine what calculations and types of records would need to be supported. We then began coding with a full rewrite of the data model and calculator for residual records. Tests were written at the same time for automated confirmation of the calculator’s results, to prevent changes from having unintended results or bug regressions. When the calculator was completed and “covered” with testing, our focus shifted to the reports. Again, tests were created to ensure that each cell on each worksheet was checked for accuracy. The reports generated from the new system were compared to the reports generated by the old system, plus any manual adjustments, to verify accuracy. Any discrepancies were debugged and fixed. This proved to be an incredibly valuable process as we sometimes found that the new system was correct and the older system and/or the manual changes being applied were creating errors. At the point that the calculator and reporting process were determined to be significantly stable, we went to work on all the additional functionality. This included: Modifications to make it easier to enter and report on one-off monetary adjustments Pricing profile UI and database cleanup Emailing of residuals reports Improved monthly finalization logic Exception reporting Audit trail for pricing: if any rate, fee, adjustment, etc. is changed by a user, the customer has a record of that change so that calculations could always be reconstructed if necessary. The inclusion of residuals calculations for a number of different payment products beyond credit and debit cards.