A sales commission plan runs cleanly in CaptivateIQ when three things are true before you touch the tool: the plan is written down as rules a machine can follow, every deal arrives from one system of record with one ID, and the hierarchy of who rolls up to whom has effective dates. I have built commission structures at three companies, first in spreadsheets and then in CaptivateIQ, and every dispute I have ever handled traced back to one of those three things, never to the software. This guide walks through the setup in the order that avoids the rework: plan design, data, hierarchies and roll-ups, tiers and accelerators, clawbacks, testing, and the monthly run.
Why do commission runs go wrong before the tool is even involved?
Most companies start paying commissions from a spreadsheet, and the spreadsheet works while there are four reps and one plan. It stops working when a second plan appears, a manager is paid on team attainment, a deal is split, or a customer churns and someone asks whether the commission comes back. At that point the spreadsheet has become a set of undocumented exceptions held in the controller's head, the monthly calculation takes days, and reps stop trusting the number because they cannot reproduce it.
Moving to CaptivateIQ does not fix any of that by itself. What it does is force you to write the rules down, because the tool can only calculate what has been defined. That discipline is the actual value. The setup work is mostly plan design and data hygiene; the configuration itself is the shorter part.
What has to be decided on paper first?
Write the plan as a specification, one page per plan, before you open the tool. Each plan needs the following, in this order.
| Component | Decision to write down | Typical trap |
|---|---|---|
| Quota | Per rep, per period; annual with monthly or quarterly split; in bookings, ARR or invoiced revenue | Quota defined in one currency, deals booked in another |
| Crediting event | What counts: closed-won, signed contract, first invoice, cash collected | CRM close date and ERP invoice date fall in different months |
| Rate tiers | Attainment bands and the rate in each; marginal or retroactive | Nobody wrote down whether tiers are marginal, so reps assume retroactive |
| Accelerators | Rate above 100% of quota; any cap | Uncapped accelerator on a mis-set quota |
| Clawbacks | Trigger (churn inside 90 days, non-payment at 120 days), amount, timing | Clawback policy exists in the contract but not in the calculation |
| Splits | How shared deals are credited: percentage per rep, or full credit to each with a different rate | Splits agreed verbally after the deal closes |
| Hierarchy | Who rolls up to whom, from which date | Manager changes mid-quarter with no effective date |
| Payout timing | Month after crediting, on invoice, or on cash | Paying on bookings for customers who never pay |
The document does two jobs. It is the source for configuring CaptivateIQ, and it is the thing you show a rep who disputes a statement. If a rule is not on that page, you cannot pay on it.
Where should the data come from?
CaptivateIQ is a calculation layer. It is only as good as the tables you feed it, so the data design is the setup. In the implementations I have run, the sources were HubSpot or Salesforce for deals, NetSuite for invoicing and cash, and Google Sheets for the things that do not live in any system: quota assignments, plan membership, approved exceptions and manual adjustments.
The rules that kept those feeds clean:
- One deal ID, end to end. The CRM opportunity ID is the primary key. Invoices in NetSuite carry it in a custom field, so a booked deal, its invoice and its cash receipt join without fuzzy matching on customer name.
- One crediting date per plan. If the plan credits on closed-won, use the CRM close date and never adjust it in the tool. If it credits on invoicing, use the NetSuite invoice date. Mixing the two is the most common source of "my deal is missing this month".
- Owner and amount frozen at crediting. Reps change, deals get re-assigned, amounts get corrected. Snapshot the deal at the crediting date into CaptivateIQ and treat later CRM edits as adjustments with an approval, not as silent recalculations.
- Currency converted once, at a documented rate. Deals in USD, EUR and GBP against a quota in one currency need one rate table per month. Store the rate with the deal record so the statement can show it.
- Quotas and plan membership in a controlled sheet. A Google Sheet with edit rights limited to finance and sales operations, one row per rep per period, versioned. It feeds CaptivateIQ as a table. Nobody edits quotas inside the tool.
Set up each source as a scheduled sync and put a reconciliation step before the calculation: total credited bookings in CaptivateIQ must equal closed-won bookings in the CRM for the period, and total invoiced amounts must equal the NetSuite sales journal. If the totals do not match, the run does not start.
How do hierarchies and roll-up targets work?
Managers and directors are usually paid on the attainment of their teams, so the hierarchy is part of the plan. In CaptivateIQ this is a hierarchy table: rep, manager, effective from, effective to. Every roll-up target is then derived from it, not typed in.
Three practices that prevented most disputes:
- Effective-dated hierarchy rows. When a rep moves teams on the 15th, both managers can see which deals rolled up to them and from which date. Without effective dates you end up paying two managers on the same deal or neither.
- Roll-up quota equals the sum of team quotas, adjusted once. If a manager's target is set independently of the team's targets, attainment percentages diverge and the manager argues about the base every month. Derive it, and document the exceptions (ramping hires excluded for their first two months, for example).
- Team attainment on the same crediting event as the reps. A manager paid on invoiced revenue while reps are paid on bookings produces a statement nobody can reconcile.
How should tiers and accelerators be modelled?
The decision that matters is marginal versus retroactive tiers. With marginal tiers, each band of attainment is paid at its own rate, like income tax. With retroactive tiers, crossing a threshold re-rates everything from zero. Retroactive plans create cliffs, and cliffs create end-of-quarter behaviour you will not like. The plans I have implemented used marginal tiers with an accelerator above quota.
An illustrative structure, not a recommendation for any specific company:
| Attainment band | Rate on bookings in the band |
|---|---|
| 0% to 60% of quota | 6% |
| 60% to 100% of quota | 8% |
| Above 100% of quota | 12% (accelerator), capped at 250% attainment |
A rep at 120% attainment on a 500,000 quota is paid 6% on the first 300,000, 8% on the next 200,000 and 12% on the 100,000 above quota. In CaptivateIQ this is a tiered rate table referenced by the plan, calculated per period on cumulative attainment. Write the worked example into the plan document; it is the single most effective way to end arguments about how tiers work.
Two configuration details: decide whether attainment is measured per month, per quarter or cumulatively year to date, and state how a quota change mid-period is prorated. Both are one line in the specification and a week of dispute handling if left out.
When and how should clawbacks apply?
Clawbacks recover commission already paid when the underlying deal does not hold. The two common triggers are a customer churning or downgrading within a defined window, and an invoice unpaid after a defined number of days. The model that worked:
- The trigger is detected from data, not from a manager's email: churn from the CRM or subscription system, non-payment from NetSuite AR ageing.
- The clawback appears as a negative adjustment line on the next statement, referencing the original deal and the original payout period.
- The amount is the commission actually paid on that deal, at the rate it was paid, not a recalculation of the whole period.
- There is a floor: a statement cannot go below zero in a month; any remainder carries forward.
Clawbacks are also where the plan document earns its keep. Reps accept a clawback they were told about at plan signature and can trace on the statement. They do not accept one that arrives as a surprise deduction.
How do you test the setup before the first live payout?
Run in parallel with the existing spreadsheet for two full cycles. The test is not "do the totals match", because the spreadsheet is probably wrong in places you have not found. The test is: for every difference between the two, can you name which one is right and why? Each difference becomes either a fix in CaptivateIQ, a fix in the source data, or a documented change in how the plan is interpreted, communicated to the affected reps before go-live.
A checklist that caught most issues:
- Every rep on a plan has a quota row for every period.
- Every credited deal has an owner who is on a plan.
- Split deals sum to 100% of the deal or to the documented split rule.
- Hierarchy has no gaps and no overlaps for any rep on any date.
- Currency-converted totals reconcile to the CRM in the reporting currency.
- Statements render for every rep, including those with zero attainment.
What does the monthly run look like once it works?
Once the data and rules are stable, commission day becomes a short checklist rather than a project:
- Sources sync on the first working day; the reconciliation step confirms bookings and invoicing totals against the CRM and NetSuite.
- The calculation runs; finance reviews exceptions, which are the deals flagged by rules (splits, adjustments, clawbacks), not the whole population.
- Statements go to reps with a fixed dispute window, usually five working days. Disputes are raised in the tool against a specific line, not by email.
- Approval by sales leadership and finance, then the payroll file and the accrual journal to NetSuite. The commission accrual is booked in the month the deal was credited, whether or not cash has been paid out yet.
- The total, by team and by plan, goes into the monthly board pack as a line item with the attainment distribution behind it. At one client the monthly exposure was between USD 300,000 and 700,000, which is a number a board wants to see with its drivers, not as a single line in opex.
In the setups I have run, the calculation went from days to hours, and the number of disputes fell to a handful per cycle, almost all of them about data (a deal booked in the wrong month) rather than about the rule. That is the goal: arguments about facts, which can be checked, instead of arguments about interpretation, which cannot.
What does this mean for the forecast and the hiring plan?
Commission is variable cost, and a plan modelled properly gives FP&A two things it did not have before. First, an accurate accrual by month, so the budget-versus-actuals review no longer shows a commission catch-up every quarter end. Second, a clean view of cost of sales per rep, which is the input a hiring plan tied to runway needs to decide whether the next sales hire pays back inside the planning horizon. If the commission model lives in a spreadsheet nobody trusts, neither of those numbers is available.
FAQ
How long does a CaptivateIQ implementation take?
For a company with two to four plans, one CRM and one ERP, plan on six to ten weeks: two to three for the plan specification and data design, two for configuration, and two full parallel cycles before go-live. The specification and the data take longer than the configuration.
Should commissions be paid on bookings or on cash?
Paying on bookings motivates closing but exposes the company to deals that never pay; paying on cash protects the company but delays the rep's reward by 30 to 90 days. A common middle ground is paying on invoicing with a clawback for non-payment after a defined period, which is straightforward to model when the ERP invoice and the CRM deal share an ID.
Do we need a tool at fewer than ten reps?
Usually not, if there is one plan and no roll-ups. The moment you have managers paid on team attainment, split deals or clawbacks, the spreadsheet starts costing more in dispute handling than a tool costs in licence fees.
Who should own the commission process?
Finance owns the calculation and the payout; sales operations owns the CRM data quality and the hierarchy; sales leadership owns plan design and approvals. When one function owns all three, either the data or the controls suffer.
Where to start
If commission day still takes the better part of a week, the fix starts with the plan document and the deal ID, not with the software. The free finance diagnostic on this site includes a short section on variable compensation and reporting cadence; it takes ten minutes and tells you where the gaps are. Or book a call and bring your current commission spreadsheet. I have seen most versions of it. If you would rather hand the whole thing over, the sales compensation service covers plan design, the CaptivateIQ build and the monthly run.