How Can I Calculate a Complicated Global Reward (Advanced)?

How Can I Calculate a Complicated Global Reward (Advanced)?


Sometimes, you want to calculate a total (gross, lump sum) commission amount based on complex rules.



Do You Need A Global Reward Formula?
In most cases, it's sufficient to rely to use pre-canned rewards such as "% of Revenue", "% of Profit", or "Cash Reward". Those options do not require formulas.

​

However, you may want to calculate commission amounts using formulas. This can be achieved by defining either a/ a global reward or b/ a per-transaction reward. This article describes option a/ (global reward). Click here for the other option, and here to determine if you are using the right approach.

Creating a Global Reward Formula
First, review this article about formulas to understand how they work, and which constructs are available.

Next, create a global reward formula:
  • Edit your plan
  • Click on the "Incentives" tab (if needed)
  • Click on the "+ Reward" button
  • Select "Global Reward"
  • Enter a global formula (examples below)

Your formula's output should be numeric.

For each triggered reward, we provide two virtual data set you can refer to in your reward formula:
  • A virtual set of transactions credited to target user / team triggering the reward
    • Ex: calculated reward = 2 times all transaction revenue credited to the user
      • SELECT 2.0 * SUM([Transaction].[calculated_revenue]) FROM Transactions
  • A virtual set of rewards previously assigned to the current beneficiary
    • Ex: calculated reward = half the amount of all other rewards
      • SELECT 0.5 * SUM([Rewards].[value]) FROM Rewards

This is powerful as you can perform complex calculations using:
  • System-calculated values
  • Any field of credited transactions
  • Other rewards previously assigned to the beneficiary

If your query refers to "Transactions", your SQL-like statement will be evaluated against a virtual set of transactions credited to each target user / team for this calculation.

If your query refers to "Rewards", your SQL-like statement will be evaluated against a virtual set of all previous rewards assigned to the beneficiary.

Getting Help
If you are not familiar with SQL-like queries, we recommend reaching out to our support for assistance. Creating your own query is an advanced topic and you may need our assistance.

Global Reward Formula Examples
Here are some examples of formulas you could use to configure global rewards.

Assume that you uploaded transactions with the following fields:
  • Quantity (a number field)
  • City (a text field)

Pay A Fixed Amount Which Depends On The Payee
250 + [Beneficiary].[@@Bonus] * 0.5

Pay $45 Per Credited Transaction Whose Quantity Is Greater Than 25
SELECT 45 * COUNT(*) FROM Transactions WHERE ([Transaction].[Quantity (Imported)] >= 25)

Pay 10% of Revenue For Credited Transactions At The Current Attainment Level Only
SELECT 0.10 * SUM([Transaction].[calculated_revenue]) FROM Transactions WHERE ([Transaction].[attainment_threshold] = [Credited].[attained_threshold])

Pay 8% Of Profit For Credited Transactions Having Osaka As A City
SELECT 0.08 * SUM([Transaction].[calculated_profit]) FROM Transactions WHERE ([Transaction].[City (Imported)] = 'Osaka')

Pay A Fixed Amount Which Depends On Credited Transaction Cities
SELECT SUM(
            CASE
                  WHEN [Transaction].[City (Imported)] = 'Paris' THEN 10.5
                 WHEN [Transaction].[City (Imported)] = 'London' THEN 9.5
                 WHEN [Transaction].[City (Imported)] = 'Berlin' THEN 8.5
                 ELSE 0.0
            END)

FROM Transactions WHERE [Transaction].[City (Imported)] IS NOT NULL

Pay 2X Rewards From Other Released Calculations Within The Same Period
SELECT 2 * SUM([Reward].[value]) FROM Rewards WHERE [Reward].[in_period] = 1 AND [Reward].[released] = 1

    • Related Articles

    • How Can I Calculate a Complicated Per-Transaction Reward (Advanced)?

      Sometimes, you want to calculate per-transaction commission amounts based on complex rules. Do You Need A Per-Transaction Reward Formula? In most cases, it's sufficient to rely to use pre-canned rewards such as "% of Revenue", "% of Profit", or "Cash ...

    • When Should I Mark A Reward As Non-Deductible?

      Some plans use strategies where commissions are always re-calculated YTD, and deductions applied based on what has been paid so far YTD. This way, if past data changes (dates or amounts), the re-calculation will catch this, because we'll constantly ...

    • 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 ...

    • How Can I Credit Multi-Dimensional Hierarchies (Advanced)?

      This article applies if you've configured teams to represent a single hierarchy, but ran into issues because you want to use different hierarchies to credit users or teams depending on the plan or scenario. Let's say that you have configured a single ...

    • 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 ...