Automated Commission Engine for a 25-Person Sales Team

The problem

With 25 sales reps across three tiers, multiple product lines, split deal rules, and quarterly accelerators, commission calculation had become a quarterly nightmare. The operations manager spent two full days each quarter running Excel macros, and reps routinely challenged the numbers.

Context

Sales commission processes tend to grow organically. A startup starts with a simple flat rate. Then it adds a second product line, so there is a rate for product A and a rate for product B. Then it introduces tiers to reward high performers. Then it adds quarterly accelerators to drive end-of-quarter behavior. Then it closes a large enterprise deal with a complex split between an inside rep and a field rep. Each of these changes is reasonable at the time. Together they produce an Excel model that only one person fully understands - and that person is now a single point of failure for a process that directly affects every salesperson's take-home pay.

In this case, the ops manager had built the Excel model over three years. It ran to hundreds of rows and used nested IF statements, VLOOKUP chains, and manually maintained lookup tables. It worked, mostly, but it had to be updated every time a rep changed tiers or a deal structure changed, and it required running a full recalculation manually at the end of each quarter. The two-day quarterly run was partly the calculation itself and partly the verification - checking outputs against deal records to catch any cases where data had not been entered correctly into HubSpot. Three disputes in one quarter was the breaking point. Leadership recognized that the problem was structural, not a one-time error.

What we built

Technical approach

The calculation engine is written in Python and reads directly from the HubSpot API. When a deal is closed or modified, a webhook fires and the engine recalculates affected commissions immediately. The rules engine handles all the logic that previously lived in Excel: base rates by product line, tier thresholds, split attribution between reps, quarterly accelerator calculations, and clawback rules for deals that cancel within a defined period.

The rep-facing dashboard is built in React and pulls from the calculation engine's data store in real time. Each rep sees their own view: deals closed this quarter, commission earned to date, projected end-of-quarter payout including accelerator scenarios, and a deal-by-deal breakdown showing exactly how each number was calculated. The transparency was a deliberate design choice - if a rep can see the logic, they can verify it themselves rather than raising a dispute. The payroll export runs automatically at quarter close and is formatted for direct import into their payroll provider, removing the final manual step from the ops manager's process. All rules are stored in a configuration file rather than in code, so the ops team can update tiers, rates, and accelerator thresholds without requiring a developer.

The automation timeline

Challenges and how we solved them

The Excel model had accumulated edge cases over three years, including a special commission structure for a legacy client that predated the current tier system. This client had been signed under an early pricing arrangement where the selling rep received a higher rate in exchange for a reduced retainer fee. When the tier system was introduced, this client was grandfathered in rather than renegotiated. The Excel model handled it with a hardcoded override. We preserved it in the new system as a named rule in the configuration file - "legacy enterprise override" - with a comment noting its origin, so that future operators would understand why it exists rather than treating it as a bug.

The second challenge was data quality in HubSpot. About 12% of deals had incomplete or inconsistent field data - missing close dates, ambiguous deal owner attributions for split deals, or product line fields that had been entered in non-standard formats. Rather than silently calculating on bad data, the engine flags these deals and surfaces them in a data quality queue for the ops manager to resolve before the quarter-end export runs. This was more useful than trying to infer correct values automatically, because commission disputes caused by bad source data are harder to resolve after the fact than data quality issues caught before calculation.

Results

Commission disputes dropped to zero. The ops manager recovered two full days per quarter. Sales reps report higher trust in the numbers.

The behavioral change in the sales team was more significant than expected. Reps who could see their live commission position mid-quarter started making different decisions about deal timing and deal size. Two reps who were close to accelerator thresholds in the final month of the quarter pushed deals to close rather than slipping them to the next period - something they would not have been able to do previously because they had no visibility into where they stood. The ops manager, freed from the quarterly calculation run, has since taken on the role of building out the team's sales forecasting model, work that was previously blocked by the time cost of the commission process.

Key learning

Commission disputes are almost never about the money. They are about trust and transparency. The dashboard that shows reps their live commission calculation is worth more than the time saved on quarterly processing. If you are running a manual commission process and experiencing disputes or rep dissatisfaction, the solution is usually not better math - it is better visibility. Reps who can see how their number is calculated rarely challenge it. The automation is valuable, but the transparency it enables is what actually changes the culture of the sales team.