Introduction
When transactional data is imported from an external ERP system into a data mart, information about the rebates is typically missing. But without taking rebates into consideration, the gross margin will be over estimated. Thus, unless rebate data is added to the transactional data mart, it will not be possible to get the full picture when analyzing the data with the PriceAnalyzer module.
This article describes how to add rebate data to transactional data marts, 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 data mart 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. For this, consider a transaction t, which has a corresponding row in the transaction data mart.
As discussed in the introduction, customers may want to include the rebate in price waterfall, or include rebates in the calculation of pocket margins. Thus, a field in the transaction data mart is reserved for a transaction rebate amount trebate, 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 trebate, simplify the problem by considering the special case where each transaction is only associated to a single rebate r. Let rrebate denote the rebate amount (the amount of money which is paid back to the buyer).
One constraint of trebate is that the sum of all the transaction rebate amounts, over all transactions, must be equal to the full rebate amount:
rrebate=∑trebate
t∈
where T� is the set of all transactions. This holds true for the assumption that there is only a single rebate.
The definition of trebate�rebate that satisfies (1)(1) is [1]
trebate=tinvoicerrate (2)�rebate=�invoice�rate (2)
where rrate�rate is the rebate rate and tinvoice�invoice is the transaction’s invoice price. Let rΣinvoice�Σinvoice denote the total invoice price of the transactions that were included in the calculation of rrebate�rebate. Then, the rebate rate rrate�rate can be expressed as
rrate=rrebaterΣinvoice (3)�rate=�rebate�Σinvoice (3)
Put into (2)(2), this yields
trebate=tinvoicerrebaterΣinvoice (4)�rebate=�invoice�rebate�Σinvoice (4)
Interpretation
It is natural for the transaction rebate amount to be proportionate to tinvoice�invoice and rrate�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 rrate�rate can be considered as a component of rrebate�rebate.
A slightly different way to perceive (4)(4) is by rearranging the factors:
trebate=rrebatetinvoicerΣinvoice (5)�rebate=�rebate�invoice�Σinvoice (5)
The fraction is the transaction’s total contribution to the rebate amount rrebate�rebate. Since this calculation is repeated over all transactions, the situation can be viewed as the rebate is 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.
Figure 3. Visual representation of a rebate allocation that only involves a single rebate. The rebate amounts can be said to be distributed over the transactions.
Since rebates are typically not linearly dependent on the transaction total invoice price, the rebate trebate�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, tmargin�margin is simply calculated as a sum over all the rebates that includes tinvoice�invoice in the rebate calculation. Let Rt�� denote the set of all rebates that the transaction t� is encompassed by. This results in
trebate=∑r∈RttinvoicerrebaterΣinvoice (5)�rebate=∑�∈���invoice�rebate�Σinvoice (5)
or equivalently
trebate=∑r∈Rttinvoicerrate (6)�rebate=∑�∈���invoice�rate (6)
Figure 4. Visual representation of rebate allocation that involves multiple rebates — for the general case where each transaction may be part of multiple rebates. Note that the transaction T2 occurs in the calculation of two rebates, thus the transaction rebate field in the data mart will contain a sum of two contributions.
Figure 5. Visualization of how multiple rebates are distributed over rows in a transaction data mart. Transaction 1 is only encompassed by a single rebate, while Transaction 3 is encompassed by two rebates.
The factor tinvoice�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 data mart 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.
Figure 6. A data load can be set up to include the execution of a feeder logic. Note that the StartDate input field has been generated by the feeder logic.
Technically, a feeder logic is just a generic calculation logic. [2] Therefore, all generic calculations logics will be listed in the drop-down list where 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() [3]:
The feeder logic is optional for data loads, and is executed only once for each job — before the PA 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 PA 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 data mart consists of N� rows, M×N�×� comparisons are made. But for real-world data, only a fraction of these combinations results in the execution of the PriceAnalyzer logic (in the row calculation context phase).
Example 1. Code to emit rebate records in a feeder logic.
api.emitRebateRecords(
'InvoiceDate',
'startDate',
*rrFilters
)
Specifies the name of the data mart 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 PriceAnalyzer logic is executed once in the init calculation context phase.
The PriceAnalyzer logic is executed repeatedly in the row calculation context phase.
The first rebate record is compared to all transaction rows in the data mart. If the transaction is encompassed by the rebate, the logic is executed.
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 variableinput.The previous step is repeated for all other matches between transaction and rebate record.
The previous step is repeated for the other emitted rebate records.
The PriceAnalyzer 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 data marts. During the processing of a calculation data load job, the calculation base of the emitted rebate records are used to filter out rows from the transaction data mart. The rows that matches the calculation base filter will result in executions of the PriceAnalyzer logic in the row calculation context phase.
Click to see example
The calculation base is be 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 data mart which will be matched against the date range in the calculation base (see the property includedTimePeriods in the example above).
Example 2. Specifying the name of the data mart field that will be compared against the calculation base date range.
calculationBase.setDateFieldName("InvoiceDate")
Strictly speaking, the property |
The date range, that will be compared against the data mart rows date field, can be changed from the default settings by calling calculationBase.include():
Example 3. Setting the date range of the calculation base explicitly. This will be matched against the dates in the data mart. dateFrom and dateTo are of the type java.util.Date.
calculationBase.include(
new TimePeriod(
dateFrom,
dateTo,
TimeUnit.DAY
)
)
include() has been overloaded, to accept other types of arguments:
Example 4. Setting the filters for the customer group and product group explicitly.
calculationBase.include(out.CustomerGroup) calculationBase.include(out.ProductGroup)
The by-default inherited values can easily be removed from calculationBase completely:
Example 5. Removing the default values that are otherwise inherited from the rebate record.
calculationBase.clear()
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 PriceAnalyzer 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 was derived in the above section (see The Mathematics of the Rebate Allocation):
trebate=∑r∈RttinvoicerrebaterΣinvoice (5)�rebate=∑�∈���invoice�rebate�Σinvoice (5)
Where
t� is the transaction, trebate�rebate is the amount that should be added to the transaction row, and tinvoice�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).
rrebate�rebate is the rebate amount (in money), and rΣinvoice�Σinvoice is the total invoice price of all transactions that were used in the calculation of the rebate amount (rrebate�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 data mart, 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 trebate�rebate. In the example that follows, the name of this field will be Rebate.
Figure 7. Datamart with where trebate�rebate is stored in the Rebate column.
Row Calculation Context Phase
Firstly, the transaction invoice price (tinvoice�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, otherwise the row values can’t be mapped to the logic inputs:
Example 6. Reading the two values from the transactions row that are needed for the calculation. In case the value is null, default to 0.
BigDecimal tRebateSum = api.userEntry("Rebate") ?: 0.0
BigDecimal tInvoicePrice = api.userEntry("InvoicePrice") ?: 0.0
if (api.isSyntaxCheck()) {
return
}
Next, the rebate amount (rrebate�rebate) and the total invoice price of all transactions encompassed by the rebate (rΣinvoice�Σinvoice) are read from the rebate record:
Example 7. Reading the rebate record.
def rebateRecord = api.currentItem() BigDecimal rTotalInvoicePrice = rebateRecord?.attributeX BigDecimal rRebateAmount = rebateRecord?.attributeY
The number of the attribute may vary between different pricefx implementations/solutions. |
Finally, the term is calculated and added to the sum:
Example 8. Rebate.groovy: Calculating one term from (5) and adding it to the sum.
return tRebateSum + rRebateAmount * (tInvoicePrice / rTotalInvoicePrice)
The output of the element in Example 8 needs to be mapped back to the same column that |
To improve readability, the mandatory null checks were omitted from the example. |
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 was 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 that resets the Rebate amount to 0 for all data mart rows.
Example 9. Rebate.groovy: Calculating one term from (5) and adding it to the sum.
def source = api.getDatamartRowSet('source')
def target = api.getDatamartRowSet('target')
while(source.next()){ //(1)
def row = source.currentRow
row.Rebate = 0.0
source.updateRow(row) //(2)
target.addRow(row) //(3)
}
1 | Iterate over all the transaction lines, in the source table. |
2 | Update the source row. The source rows must be updated, because these are the values that will be mapped to the |
3 | 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 data mart rows would remain unchanged — unless they are updated in the init phase. |
Summary
For Pricefx solutions that involves the Rebate Manager, it is common to include data about the rebates in the transaction data mart. 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 data mart). 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 PriceAnalyzer module. The amount can be expressed as follows:
trebate=∑r∈RttinvoicerrebaterΣinvoice (5)�rebate=∑�∈���invoice�rebate�Σinvoice (5)
The rebate allocation is performed with a calculation data load that utilizes:
A feeder logic.
A PriceAnalyzer 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 data mart. The allocation logic is executed with the row as calculation context for each match between transaction and rebate record. The transaction is available as logic input, while the rebate record is available via api.currentItem().
The association between data mart 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 data mart rows. The calculation base is defined in the rebate logic, during the rebate record calculation context execution phase.
1. This can easily be proven by inserting (2)(2) into (1)(1): rrebate=∑t∈Ttinvoicerrate=rrate∑t∈Ttinvoice=rraterΣinvoice�rebate=∑�∈��invoice�rate=�rate∑�∈��invoice=�rate�Σinvoice, which is equivalent of the definition of rrebate�rebate in (3)(3). ■
2. A generic calculation logic is a calculation logic with the formula nature set to null.
3. Groovy API documentation of IRebateRecordManager.add()