A top B2B retailer in the US needed to automate their complex commission process for their affiliates and sales partners worldwide. Previously, they manually calculated commissions, but as their customer base grew and the number of orders increased, this method became prone to errors, time-consuming, and costly. As a result, there was a pressing need for a solution to streamline the commission process.
The commission calculation was complex, and the technical difficulties made it one of the toughest projects I’ve tackled. Due to the complexity, it took a while to develop the solution. Let’s delve into the journey, the challenges, and how we overcame them.
An overview of how the commission is processed:
1.) Some of the customers who buy from the website have pre-assigned sales representatives. The role of the sales representative is to provide their customers with assistance while placing orders and they are commissioned when these orders are placed.
2.) The sales representatives receive a commission percentage calculated on a pre-defined minimum spend value in a given timeframe which renews daily, monthly and weekly. For example, a sales representative is getting a commission based on the weekly cycle of a minimum spend of $500. It means that the customer should spend more than $500 weekly for the respective sales representative to avail the commission.
3.) Each customer can have multiple sales representatives and vice versa. Also, the commission value and minimum spend value for different representatives will differ.
A quick overview of the solution
To manage the complex workflow involved in the commission process, we have developed a separate sales commission calculation system for the back office team. The system allows the team to add details required for commission calculation, such as commission rate, minimum spend, cycle type (daily, weekly,monthly), type of commission, etc, for various sales representatives.
When a customer’s order is processed and shipped, the commission is calculated for all related sales reps based on pre-set rules. If the customer receives a discount or returns a product, the commission will be adjusted automatically. The overall commission is then updated for each sales rep and can be viewed by the back office. To see an individual’s commission, they can simply select them from a drop-down menu and get the details instantly.
This approach worked well when there were just a few hundred orders. But as the number of orders increased to thousands daily, the commission calculation became too demanding for the system to handle.
The major technical challenge that we faced was:
Unwanted load on the server
We have implemented MySQL and PHP for the commission calculation. But it involved several database calls that increased the load on the servers, affecting the efficiency of the system. To address the issue we implemented Firebase.
Using Firebase for handling the calculation
Firebase is a kind of real-time database that can handle a large number of data at a time. So, instead of doing calculations using PHP and MySQL, we started processing them using Firebase.
When the order is shipped, we send all the data to Firebase, and the whole commission calculation is done there. And then, we fetch the details from Firebase and display them in our sales representative commission panel. This reduces the total number of database calls and the load on our server.
But when we thought that we had created a robust system that would not fail, surprisingly, implementing Firebase also didn’t work out. Here is why –
Limitations of Firebase
The solution we implemented involved using Firebase’s scheduler function, functions.pubsub.schedule(‘every 5 minutes’).onRun(async (context). We used to run the process of commission calculation every five minutes. During this time, whatever orders came into the system, the calculation would be performed for all those orders. So, we were processing commissions for multiple orders at a time.
Firebase has some limitations, such as limited querying capabilities, and, since our processes were data-intensive mathematical calculations, they intercepted those limitations. In Firebase, the total number of writes (updates) that can be passed to a commit operation is 500. Secondly, the time limit for a single transaction is 270 seconds with 60 second idle expiration time. In our process, this time limit was exceeded and also the total writes were more than 500 since we were performing several functions at once for several orders that are:
1.) Getting data for commission calculation
2.) Performing the mathematical calculation with the data
3.) Updating the commission for each sales representative
The final solution
To solve the Firebase issue, we came up with the solution of real-time database triggers of Firebase. Instead of sending multiple data entries to Firebase at a single time and using the scheduler function to process multiple orders, we used database triggers of Firebase. Using it, we send data for every order shipped, and calculation is done for one order at a time. It leverages the cloud functions that enable the system to handle events without updating the client code. The benefit of cloud functions is that it lets you run realtime database functions while giving you administrative privileges.
This feature has solved many financial problems that our client was previously facing. In the past, commissions were processed and given, only for them to discover later that a product was returned or there was an issue during shipping. Now, everything is calculated and updated automatically, so if a customer returns a product, the commission will be adjusted when the return is complete. This automation has allowed our client to save 40-50% of their costs while making the entire process smoother.
