Rebate Allocations

When transactional data is imported from an external ERP system into a Datamart, information about the rebates is typically missing. But without taking rebates into consideration, the gross margin will be overestimated. Thus, unless rebate data is added to the transactional Datamart, it will not be possible to get the full picture when analyzing the data with the Analytics module.

This article describes how to add rebate data to transactional Datamarts, so that the customer can include rebates in their Analytics charts. This process is often referred to as rebate allocation.

Different companies may implement rebate allocation slightly differently. The sections below demonstrates just one example.

Figure 1. Illustrative price waterfall where the rebates are included.

Figure 2. Screenshot of a transactional Datamart where the information about the rebates is included, and where the rebate is included in the calculation of the margin.

The Mathematics of the Rebate Allocation

Before it is possible to configure a rebate allocation, it is necessary to understand the mathematics behind the rebate component in the price waterfall ([figure_WaterfallRebate]). For this, consider a transaction t, which has a corresponding row in the transaction Datamart.

As discussed in the introduction, customers may want to include the rebate in a price waterfall ([figure_WaterfallRebate]), or include rebates in the calculation of pocket margins. Thus, a field in the transaction Datamart is reserved for a transaction rebate amount t_rebate, which will hold the value of the rebate bar in the price waterfall.

Special Case — A Single Rebate

To figure out how to calculate the transaction rebate amount t_rebate, simplify the problem by considering the special (and unrealistic) case where there only existis a single rebate r. Let r_rebate denote the rebate amount; that is the amount of money which is paid back to the buyer.

One constraint of t_rebate is that the sum of all the transaction rebate amounts, over all transactions, must be equal to the full rebate amount:

r_rebate = ∑t∈T t_rebate (1)

where T is the set of all transactions. This simply means that every transaction where t_rebate > 0 contributes with something to the total rebate amount r_rebate.

The definition of t_rebate that satisfies (1) is

t_rebate = t_invoicer_rate (2)

where r_rate is the rebate rate and t_invoice is the transaction’s invoice price. Let r_Σinvoice denote the total invoice price of the transactions that were included in the calculation of r_rebate. Then, the rebate rate r_rate can be expressed as

r_rate = r_rebate / r_Σinvoice (3)

Put into 2, this yields

t_rebate = t_invoicer_rebate / r_Σinvoice (4)

Interpretation

It is natural for the transaction rebate amount to be proportionate to t~invoice and r_rate: if the rebate rate increases, the transaction rebate amount must increase with it (linearly). And if the transaction invoice price increases, the transaction rebate amount must increase (linearly) too — because r_rate can be considered as a component of r_rebate.

A slightly different way to perceive (4) is by rearranging the factors:

t_rebate=r_rebatet_invoice / r_Σinvoice (5)

The fraction is the transaction’s total contribution to the rebate amount r_rebate. Since this calculation is repeated over all transactions, the situation can be viewed as the rebate being distributed over the transactions — according to how much each transaction contributed to the rebate amount. Because of this, the process is often referred to as rebate allocation.

Since rebates are typically not linearly dependent on the transaction total invoice price, the rebate t_rebate can not be said to actually “exist” for the individual transaction; it is only derived in order to allow end users to include rebates in price waterfalls, to calculate meaningful margins, and similar analyses.

The General Case — Multiple Rebates

A single transaction can also be encompassed by multiple rebates. In this case, t_margin is simply calculated as a sum over all the rebates that includes t_invoice in the rebate calculation. Let R_t denote the set of all rebates that the transaction t is encompassed by. This results in

t_rebate = ∑ t_invoice (5)

or equivalently

t_rebate = ∑ t_invoice r_rate (6)

The factor t_invoice is independent of the rebate, and can thus be broken out of the sum.

Implementing the Rebate Allocation

With (5), a calculation Data Load can be constructed that performs the calculation and stores the output back into the transactions Datamart. The greatest challenge here is to find out which transactions are encompassed by which rebates — that is, which Datamart rows are associated with which Rebate Records. To solve this problem, a Feeder Logic needs to be utilized.

Feeder Logics

On the screen where logics can be configured, it is possible to select a feeder logic.

Technically, a feeder logic is just a generic calculation logic. Therefore, all generic calculation logics will be listed in the drop-down list with the feeder logic.

But what characterizes a feeder logic is that it emits one or more objects — in our case, Rebate Records with api.emitRebateRecords().

The feeder logic is optional for Data Loads, and is executed only once for each job — before the Analytics logic init calculation context phase. With a feeder logic, the execution during the row calculation context phase then behaves differently:

Each emitted Rebate Record is compared to each row in the transaction Data Mart — one by one. If the given transaction row is encompassed by the given Rebate Record, the Analytics logic is executed, and the Rebate Record is available with api.currentItem(). But if the Rebate Record doesn’t match with the transaction, the logic will not be executed for that combination of Rebate Record and transaction. Thus, if M Rebate Records were emitted, and if the transaction Datamart consists of N rows, M x N comparisons are made. But for real-world data, only a fraction of these combinations results in the execution of the Analytics logic (in the row calculation context phase).

Code to emit Rebate Records in a feeder logic.

api.emitRebateRecords(
    'InvoiceDate',// ❶
    'startDate',  // ❷
    *rrFilters    // ❸
)

❶ Specifies the name of the Datamart field that will be matched against the validity of the Rebate Record.
❷ Specifies in which order the Rebate Records will be processed — when being consumed.
❸ Filters that are used to filter out Rebate Records.

The sequence of events is as follows:

The feeder logic is executed and Rebate Records are emitted.
The Analytics logic is executed once in the init calculation context phase.
The Analytics logic is executed repeatedly in the row calculation context phase.
1. The first Rebate Record is compared to all transaction rows in the Datamart. If the transaction is encompassed by the rebate, the logic is executed.
  1. The logic is executed for the first match between transaction and Rebate Record. The Rebate Record is accessible by calling api.currentItem(), and the row is accessible with the binding variable input.
  2. The previous step is repeated for all other matches between transaction and Rebate Record.
2. The previous step is repeated for the other emitted Rebate Records.
The Analytics logic is executed once in the summary calculation context phase.

But how does the application determine whether a given transaction is encompassed by a given Rebate Record? This problem is solved with a so-called calculation base.

Calculation Base

Each Rebate Record has a property called calculationBase; which, in short, is a filter for rows in Datamarts. During the processing of a calculation Data Load job, the calculation base of the emitted Rebate Records is used to filter out rows from the transaction Datamart. The rows that match the calculation base filter will result in executions of the Analytics logic in the row calculation context phase.

{
  "includedCustomerGroups": [],
  "includedProductGroups": [],
  "excludedCustomerGroups": [],
  "excludedproductGroups": [],
  "includedTimePeriods": [
    {
      "timeUnit": "DAY",
      "startDate": "2020-09-01",
      "endDate": "2020-09-30",
      "single": false
    }
  ],
  "excludedTimePeriods": [],
  "otherFilters": [
    {
      "_constructor": "AdvancedCriteria",
      "criteria": [
        {
          "fieldName": "CustomerId",
          "value": "CID-0008",
          "operator": "equals"
        }
      ],
      "operator": "and"
    },
    {
      "_constructor": "AdvancedCriteria",
      "criteria": [
        {
          "_constructor": "AdvancedCriteria",
          "criteria": [
            {
              "fieldName": "ProductId",
              "value": "BR1000S-JP",
              "operator": "equals"
            },
            {
              "fieldName": "ProductId",
              "value": "BR1200S-JP",
              "operator": "equals"
            }
          ],
          "operator": "or"
        }
      ],
      "operator": "and"
    }
  ],
  "dateDimFieldName": "PricingDate"
}

The calculation base is defined in a rebate logic during the Rebate Record calculation context phase, with the binding variable calculationBase. By default, the calculation base inherits some Rebate Record’s properties — such as: startDate, endDate, customerGroup and productGroup. Thus, many properties do not necessarily have to be set explicitly in the logic.

However, the system will never be able to infer the value of the property dateFieldName from anywhere, thus it always has to be set explicitly. This property specifies the name of the column in the transaction Datamart which will be matched against the date range in the calculation base (see the property includedTimePeriods in the example above).

Specifying the name of the Datamart field that will be compared against the calculation base date range.

calculationBase.setDateFieldName("InvoiceDate")

The date range that will be compared against the Datamart rows date field, can be changed from the default settings by calling calculationBase.include():

Setting the date range of the calculation base explicitly. This will be matched against the dates in the Datamart. dateFrom and dateTo are of the type java.util.Date.

include() has been overloaded, to accept other types of arguments:

Setting the filters for the customer group and product group explicitly.

The by-default inherited values can easily be removed from calculationBase completely:

Removing the default values that are otherwise inherited from the Rebate Record.

Allocation Logic

With an understanding of how feeder logics and calculation bases can be used to associate Rebate Records with its transactions, it is now possible to construct the Analytics logic that will perform the allocation. This logic is referred to as an allocation logic. The logic will perform the calculation according to the formula that has been derived in the above section (see [section_math]):

t_rebate = Σ t_invoice r_rebate / r_Σinvoice (5)

Where

t is the transaction, t_rebate is the amount that should be added to the transaction row, and t_invoice is the transaction’s invoice price.
R is the set of all Rebate Records, and r is a rebate in R (represented by a Rebate Record).
r_rebate is the rebate amount (in money), and r_Σinvoice is the total invoice price of all transactions that were used in the calculation of the rebate amount (r_rebate).

The allocation logic will be executed in two of the three (calculation context) phases: in the init phase, and in the row phase.

In the row phase, the logic is iteratively executed for each combination of Rebate Record and transaction (see [feeder_logics]). Only one of the terms in (5) will be available at the time; thus, the full sum cannot be calculated in a single row-phase execution, but each will result in the calculation of a single term. The terms will be added to one of the fields in the transaction Datamart, where it accumulates until it has reached full sum (5). This field will thus be the one that was reserved for the transaction rebate amount t_rebate. In the example that follows, the name of this field will be Rebate.

Row Calculation Context Phase

Firstly, the transaction invoice price (t_invoice) and the incomplete sum (Rebate) will have to be read from the transaction row. The input parameters will also have to be declared during the input generation mode, otherwise the row values can’t be mapped to the logic inputs:

Reading the two values from the transactions row that are needed for the calculation. In case the value is null, it defaults to 0.

Next, the rebate amount (r_rebate) and the total invoice price of all transactions encompassed by the rebate (r_Σinvoice) are read from the Rebate Record:

Reading the Rebate Record.

❶ The number of the attribute may vary among different Pricefx implementations/solutions.

Finally, the term is calculated and added to the sum:

Rebate.groovy: Calculating one term from (5) and adding it to the sum.

Init Calculation Context Phase

When the job enters the row calculation context phase, it assumes that the amount stored in the field Rebate is 0. But if the logic has been executed at least one time previously, the Rebate field may already store a value greater than 0. Therefore, the execution of the row phase element must be preceded by the execution of an init calculation context element, which resets the Rebate amount to 0 for all Datamart rows.

Rebate.groovy: Calculating one term from (5) and adding it to the sum.

❶ Iterate over all the transaction lines in the source table.
❷ Update the source row. The source rows must be updated, because these are the values that will be mapped to the input in row calculation context phase. (Note that the key of the Datamart row must be embedded in the map that is provided as an argument to updateRow(), but this is not the primary key that is displayed in the end-user interface).
❸ Those transactions that cannot be matched with a Rebate Record will be “skipped” by the allocation logic (i.e., the allocation logic will not execute in the row calculation context phase for these transactions). As such, for these rows, the row calculation context elements will not add rows to the target table. This means that these Datamart rows would remain unchanged — unless they are updated in the init phase.

Summary

For Pricefx solutions that involve the Rebate Manager, it is common to include data about the rebates in the transaction Datamart. In a process that is often referred to as rebate allocation, a value that represents the transaction’s total proportional contribution to all rebates is added to the transaction row (in the Datamart). This allows the rebates to be taken into account when calculating the pocket margin, and it allows the end users to analyze the rebates in the Analytics module. The amount can be expressed as follows:

t_rebate = Σ t_invoice r_rebate / r_Σinvoice (5)

The rebate allocation is performed with a calculation Data Load that utilizes:

A feeder logic.
An Analytics logic, often referred to as an allocation logic.

The feeder logic emits Rebate Records. The emitted Rebate Records are then compared against the transactions in the Datamart. The allocation logic is executed with the row as calculation context for each match between transaction and Rebate Record. The transaction is available as a logic input, while the Rebate Record is available via api.currentItem().

The association between Datamart row and Rebate Record is made by comparing the transaction row with the Rebate Record calculation base. In essence, the calculation base is a filter for Datamart rows. The calculation base is defined in the rebate logic, during the rebate record calculation context execution phase.