A merchant processing company had retained us to build and maintain their residual (commission) reporting system since early 2008.As time went on, the volume of data they were ingesting and managing was outgrowing the calculator they were using to pay out commissions.
As the system aged and the size of data grew, the data management process 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 lead to messy data, meaning 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.
Their residuals team was constantly stretched to complete their monthly reporting cycle due to the amount of manual intervention needed. Reducing the manual data handling was a priority, and would require a new data management 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.
Our clients were able to plan and eventually get to a place where they had the resources to handle overhauling their residuals system. Once the system overhaul was underway, we had to ensure that the data and data pipelines were properly managed so that:
- No one had to edit the Excel reports after they were generated.
- The final residuals calculations would stored in the database for easy reporting.
- Data processing and reporting performance speed would increase significantly.
- Calculations would be more accurate and errors would be prevented or detected automatically.
Our solution was broken up into three main components and stages so we could use an iterative development process:
- The residuals data model
- Report generation
- Everything else
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.
Once the data processing workflow as stable we were able to serve our client with software customization to aide in their management and interaction with the data:
- 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.