How Can I Specify a Custom Formula (Advanced)?

Sometimes, you want to customize attainment and require a formula to do so.

Do You Need A Custom Formula?
In most cases, calculating a transaction's value based on its revenue or profit is sufficient.

When using revenue or profit as an attainment metric, Sales Cookie will try to calculate them automatically for you.

Also, you can certainly use reward formulas to calculate payouts without requiring a per-transaction custom value.

However, there are situations where you may need to "value" transactions in a more flexible way. For example, you could decide that:

• You want to count transactions, so you can't use revenue or profit
• Attainment should be based on the sum of two fields
• Attainment should be based on the average revenue of all credited transactions
• A transaction's value should be zero if the revenue is under a certain value
• A transaction's value depends on the type of product sold
• A transaction's value should be based on previous commission amounts
• Etc.

For those cases, you can use a custom formula. Once calculated, your custom value can be re-used by other formulas. You can also use custom values as an attainment metric.

Creating a Custom Formula

First, review this article about formulas to understand how they work, and which constructs are available.

Second, choose whether you just want to a/ calculate a custom value per-transaction (which you can then refer to in other formulas), or b/ want to use a custom value as an attainment metric. 

If you want to use a custom attainment metric, choose this:

Otherwise, you can still calculate a per-transaction custom value "on the side", and refer to this custom value in other formulas (ex: reward formulas). 

Next, create a custom formula:

• Edit your plan
• Click on the "Calculations" tab (if needed)
• Enter a custom formula (examples below)

Your custom formula could be very simple - for example, to count each credited transaction as 1 point, you would use this:

If use your custom value as an attainment metric, enter a metric name and unit - for example:

If use your custom value as an attainment metric, you can either specify a per-transaction value (ex: each transaction is worth 1 point). For more complex scenarios, you can choose to calculate a global attainment value. 

For example, if you wanted to calculate attainment as the average revenue of all transactions credited to each target, you could specify:

       

Your formula's output should be a numeric value.

Custom Formula Examples
Assume that you uploaded transactions with the following fields:

• Quantity (a number field)
• City (a text field)

Here are some examples of formulas you could use to configure custom values.

Calculate A Custom Value Based On A Count

1

Calculate A Custom Value Based On Quantity

var $quantity = [Transaction].[Quantity (Imported)]

--------------------------------------------------------------------

2.5 * $quantity + 4

Calculate A Custom Value Based On Revenue And Profit
[Transaction].[calculated_revenue] - 3 * [Transaction].[calculated_profit]

Calculate A Custom Value Based On City And Quantity 

var $quantity = [Transaction].[Quantity (Imported)]

--------------------------------------------------------------------

var $city = [Transaction].[City (Imported)]

--------------------------------------------------------------------

IFEX($quantity >= 100 AND $city = 'Osaka', 2 * [Transaction].[Quantity (Imported)])

--------------------------------------------------------------------

IFEX($quantity >= 400 AND $city = 'Madrid', 3 * [Transaction].[Quantity (Imported)])

--------------------------------------------------------------------

0

Calculate A GLOBAL Custom Attainment Based On Average Revenue

SELECT AVG([Transaction].[calculated_revenue]) FROM Transactions

• Related Articles

• How Can I Specify A Dynamic Crediting Formula (Advanced)

  Sometimes, you want to credit users or teams based on dynamic crediting rules. Do You Need A Crediting Formula? In most cases, it's sufficient to rely on aliases and system mapped transaction fields to credit users or teams. The following crediting ...

• How Can I Specify a Dynamic Quota Formula (Advanced)?

  Sometimes, you need quotas to be calculated dynamically using a quota formula. Do You Need A Quota Formula? In most cases, it's sufficient to use available quota options (such as "As Percentages of a Per-Target Dynamic Quota" or "As Percentages of a ...

• How Can I Specify A Dynamic Transaction Filtering Formula (Advanced)

  Sometimes, you want to filter transactions based on dynamic rules. Do You Need A Filtering Formula? In most cases, it's sufficient to rely on saved queries to filter transactions. For example, you could define a transaction filter such as this: And ...

• How Can I Specify a Scoring Formula (Advanced)?

  Sometimes, you want each transaction's value to be calculated using dynamic rules. Do You Need A Scoring Formula? In most cases, calculating a transaction's value based on its revenue or profit is sufficient. When using revenue or profit as an ...

• How Can I Deal With Ever Changing Hierarchies (Advanced)?

  This article applies if you've configured teams to represent a single hierarchy, but ran into issues because your team hierarchy is changing all the time. Let's say that you have configured a fixed hierarchy like this: One option to deal with change ...