How to Exclude Deals Closed When the Rep Wasn't On A Plan

Reps often get compensated on a set of deals closed during a given period.  For example, you may have a rep that gets a quarterly accelerator payment.  To calculate the accelerator you need to sum up all of the deals that were closed during the quarter and then see if the rep qualifies for an accelerator payment.

But now let's assume that the rep, Jane, gets moved from the plan she was on with the accelerator, let's call it Plan A to a new plan, let's call it Plan B, without an accelerator.  Let's say Jane hopped from Plan A to Plan B on July 15th.  

How do you get Spiff to include deals toward Jane's accelerator for the time she was on Plan A but exclude deals closed after the time she switched to Plan B?

To do so we need to create a new Variable that filters out deals that were closed when Jane wasn't on a given Plan.


Let's assume that Jane is an SDR and the only thing we need to understand her commissions is the count of the deals she has closed.  Further, let's assume we've already created a Scope called DealsClosed that returns all of the deals Jane has closed during a period.  Your Spiff instance comes with several pre-built conditions that make building this kind of Scope quite easy. 

Your first thought might be why don't we just create a condition called BookedWhileOnPlan.  Something like this:

deal.booking_date >= line.effective_as_of AND deal.booking_date <= line.effective_until

It's a great idea but unfortunately sometimes line.effective_as_of returns NULL and the way databases work, this condition will always return false in that case.  So if we don't want that, we have another alternative.

We can create a new Variable called DealsClosedWhileOnPlan.  We can then use the filter function to filter out deals that weren't booked at the right time.

Here's how we will do that:

count(filter(DealsClosed, date_between?(deal.booking_date, line.effective_as_of, line.effective_until)), Id)

You'll notice that we are making use of the special date_between? function.  This allows us to see if a deal's date closed between to other dates.  So the syntax above counts the number of deals that closed while this rep was on their current plan.

Using Default Condition

Spiff anticipated that many companies would only want to count deals toward a rep's commission if the rep was actively assigned to the given Plan when the deal closed.  So we added this condition to the pre-built conditions available to every company in Spiff.

So if you use the pre-built Scope called "ClosedByRep" it automatically uses a pre-built condition called "ClosedWhileRepOnPlan." which means that Spiff will automatically run this filter for all deals using the "ClosedByRep" Scope.

Did this help answer your question?

thumbs up
thumbs down

Thanks for the feedback! 🙏🏽