Here at Sales Cookie, one of our potential customers asked us to model a sales commission plan with a special characteristic – pay one third of the commission every month over three months. Here is an example:
- A deal worth $1000 was closed January 10
- The sales commission rate is 6% so $60
- Pay one third (2%) of the commission ($20) in January
- Pay one third (2%) of the commission ($20) in February
- Pay one third (2%) of the commission ($20) in March
Suppose that you run a sales commission calculation for the month of March. The process must be able to look-back in time to also find transactions in January and March (and assign one third of the commission). Here is a diagram explaining the challenge:
Is Sales Cookie flexible enough to handle this type of look-back in time? The answer is yes. After each transaction has been processed (and credited to users / teams), Sales Cookie lets you run a SQL-like query to calculate rewards. Each reward query is executed against a virtual set of transactions credited to each user. This is of course an advanced feature.
For example, suppose that you have a bunch of transactions, some of which will be credited to John, and some of which will be credited to Sarah by our crediting engine. Sales Cookie automatically creates a virtual set of transactions for John, and another virtual set of transactions for Sarah. Next, you can define a SQL-like query, which will be evaluated against each individual set to compute John’s or Sarah’s actual rewards.
To handle the 3 month look-back in time, we can create a reward query which does the following: “if a calculation’s start date falls anywhere between a/ a transaction’s start date, and b/ a transaction’s start date plus 3 months, then pay 2% of revenue“. Below is what the advanced query looks like. Note that our team usually creates those as they can be complex – this service is provided free of charge.
SELECT SUM(
CASE WHEN
[Calculation].[start_date] >= [Transaction].[Transaction Date (System)] AND
[Calculation].[start_date] < date([Transaction].[Transaction Date (System)], ‘+3 months’) THEN [Transaction].[calculated_revenue] * 0.02 END)
FROM Transactions
Here is a screenshot of the application showing the query-based reward in action:
Now let’s say we start a calculation for the month of March. By default, we will only process transactions from March 1st to March 31st. So even though our reward query can determine whether to pay a commission or not with look-back in time, we’re stuck because only March transactions will be processed. We are missing one piece of the puzzle!
The solution is to set advanced options on our incentive plan to widen the time window used to process transactions. What this means in practice is… thanks to our override, calculations will scan ALL transactions – not just those defined by the calculation period (ex: March). However, our reward query will only assign rewards for transactions whose date is within 3 months of our calculation start date.
In Conclusion
In this article, we explained how to use some of Sales Cookie’s more advanced capabilities to handle challenging sales commission requirements. Whether you have draws, splits, formulas, scoring, or other complex requirements, we’re confident that we can handle it. Visit us online to learn more!
Sales Commissions Explained 