How Do Custom Variables Work?

How Do Custom Variables Work?


Custom variables (also known as custom properties) help you define values which change over time. Custom variables are essential to Sales Cookie's ability to calculate commissions en-masse. Custom variables also help you manage various commission-related parameters such as quotas, rates, start dates, etc.

Time-Dependency
In this example, is a custom variable called MonthlyQuota was defined:



Note that there are two entries for the same custom variable (each with different effective dates). If you refer to this custom variable in a monthly commission plan, and run a calculation for the month of February, the value will be 100,000. However,  if you run a calculation for the month of May, the value will be 120,000. 

This is because Sales Cookie automatically pulls the appropriate value based on the period you are calculating for.

Defining Custom Variables
You can define custom variables:
  • On users
  • On teams
  • On plans

To view custom variables, edit the corresponding record, and navigate to the "Custom" tab. Here are some examples of valid values:
  • -127.44                        (number)
  • 454,33 USD                 (number with currency code)
  • 454,33 (EUR)               (number with currency code and parenthesis)
  • 454,33€                        (number with currency symbol)
  • 31.56%                         (number with percentage)
  • #2020-12-31#             (date)
  • 'hello'                            (string)

Referring to Custom Variables
You can refer to custom variables in a number of ways.

Sometimes, the Sales Cookie user interface lets you enter the name of a custom variable like this:



You can also reference custom variables in formulas like this:



As you can see, there is a prefix such as [Credited], [Beneficiary], [Plan] - followed by the variable name within brackets prefixed with @@.

How Custom Variables Are Replaced 
Suppose that you referred to [Beneficiary].[@@MyVariable] in a reward formula within your plan. 

When you run a calculation for say January, Sales Cookie will:
  • Determine who the payee (user) should be
  • Lookup custom variables on the user
  • Check the end date of your calculation (ex: January 31)
  • Replace [Beneficiary].[@@MyVariable] by the value with an effective date of January 31

When To Use Custom Variables
You should use custom variables when:
  • Values can change over time
  • Values are fixed but could change  
 
For example, let's say you always pay a 10% commission. You could use a formula like this:



However, it may be a good idea to define a custom variable on your plan called "Rate" and use something like this instead:



This way, if you change your mind about the plan-level commission rate, you can another entry for the same custom variable (with a different value & effective dates). Now, you can calculate commissions for any period, and the correct rate will be applied - always. You're also tracking how values changed over time, which is useful for auditing purposes.

Cumulative Custom Variable Behaviors (Advanced)
Most of the time, you will use the @@ prefix to refer to your custom variable. 

However, the following are valid for numeric custom variables:
  • @@X - looks up a single value of custom variable X based on the calculation's end date
  • ##X - sums quarter-to date values of custom variable X (up to the calculation's end date)
  • #+X - sums values of custom variable X within the calculation's end date's quarter
  • !!X - sums year-to date values of custom variable X (up to the calculation's end date)
  • !+X - sums values of custom variable X within the calculation's end date's year

For example, suppose that you defined a value of 1000 for January, February, and March. If you run a February calculation and use ##, the value will be 2000 (quarter-to-date). If you run a February calculation and use #+, the value will be 3000 (all values within the quarter). This is useful if you have both a monthly plan and a quarterly plan, but only want to input monthly values. 

Performing Custom Variable Lookups (Advanced)
In some advanced scenarios, you can't use the convenient [Prefix].[@@MyVariable] approach. However, you can then use the Lookup() function. 

Here are some simple examples:
  • Lookup('John', @@MyVariable)
    • This retrieves the value for MyVariable from the user or team with an alias of "John"
  • Lookup('NorthWest', @@MyVariable)
    • This retrieves the value for MyVariable from the user or team with an alias of "NorthWest"

Lookup Tables Within Custom Variables (Advanced)
Some of your custom variables may be defined as lookup tables. 

In this example, MyVariable takes different values depending on the input. For example, from 0 (inclusive) to 10 (exclusive), the expected value is 1. From 10 (inclusive) to 15 (exclusive), the expected value is 2.5. From 15 (inclusive) to 20, the expected value is 3.5. From 20 (inclusive), the expected value is 5.


In this other example, MyVariable takes different values depending on the input. For example, if the value is 'Denmark', the expected value is 4. Note the use of a wildcard in the lookup key ('Den*').


To query lookup table in formulas, first load the custom property (a lookup table) into a variable. Second, call the Lookup() function, passing a/ the value to lookup, and b/ the variable containing the lookup table.
  • var $lookupTable = [Credited].[@@MyVariable]
  • Lookup(12.35, $lookupTable) 

If table keys and values are numeric, you can also request a blended rate calculation. Below, because the specified value is 12, we will get a blended rate, which is based on the two attained tiers (from 0 to 10 and from 10 to 15). We're also passing -999 as a default value (ex: if you lookup a negative value, you will get -999 as the lookup table starts at zero).  
  • var $lookupTable = [Credited].[@@MyVariable]
  • Lookup(12, $lookupTable, -999, true)

Bulk-Uploading Custom Variables 
It would be quite tedious to repeat this process if you have 100 users:
  • Edit each user
  • Click on their "Custom" tab
  • Enter values with different effective dates

The bulk edit tool lets you enter values for one custom variable across many users (or teams) using a live spreadsheet. 
  • Go to Settings > Tools
  • Select one of the following



In this example, we input a custom variable called "MonthlyQuota" over multiple users.



To learn more about bulk-upload of custom variables, click here.

    • Related Articles

    • What Is A Web Endpoint - And How Does It Work?

      Applicability Deploying a web endpoint allows automated import of transactions from any sales or accounting system. You should deploy a web endpoint when: You want to automate transaction upload (so as to avoid manual uploads) Your sales transactions ...

    • What Type Of Support Is Available For NetSuite?

      This article assumes you've already setup a connection to NetSuite. Next, click on Transaction > Add Transactions and select NetSuite. Here is a basic SuiteQL query: SELECT T.*, TO_CHAR(T.lastmodifieddate, 'YYYY-MM-DD HH24:MI:SSxFF') AS ...

    • What Type Of Support Is Available For Snowflake?

      By adding a Snowflake connection to your workspace, you can: Automatically import and sync sales transactions to your workspace We support any SQL query as long as we can incrementally retrieve data (details below) Newly created Snowflake records ...

    • What Type Of Support Is Available For PostgreSQL?

      By adding a PostgreSQL connection to your workspace, you can: Automatically import and sync sales transactions to your workspace We support any SQL query as long as we can incrementally retrieve data (details below) Newly created PostgreSQL records ...

    • What Is The Difference Between Team Managers And Users' Direct Managers?

      Given a user: This user may have one direct manager (specified directly on the user's page) This user may also belong to different teams, each having a different team manager There are a few differences between the two: Each user can only have 1 ...