This example shows how a company uses the Simple Scenario-Based Data Processing Engine Edits for Rebates prebuilt app to group and aggregate Rebate Member Product Aggregate records.
Required Editions
| Available in: Enterprise, Unlimited, and Developer Editions that have Rebate Management enabled. |
Business Requirements
The Rebate Member Product Aggregate object stores each member’s aggregated transaction records for a specific payout period and rebate type in a single cumulative record. By default, the records show the summed up value of Transaction Quantity and Transaction Amount for all eligible Transaction Journal records. And, based on the Data Processing Engine definition used to update these records, the records can be grouped by member, product, year, and so on.
A company wants to group the transaction data of rebate program members by country of sale. Channel managers want to track and reward members based on country-specific sales.
Program managers want to track the aggregate value of discounts offered for all qualified member transactions. To calculate the summed up discount percentage, the calculation must consider the filters for eligibility criteria and the list of included or excluded products.
Program managers also want to track the aggregate value of return order quantity for all member transactions. To calculate the summed up return order quantity, the calculation must not consider the filters for eligibility criteria and list of included or excluded products.
Rebate Program Specifications
The program manager creates a rebate program called Quarterly Rebates and adds multiple partners as members to the program. The payout frequency is quarterly, with the payout date on the first of each quarter. The program is effective for one year.
The manager associates one rebate type to the program. Here are the key details.
- Name: Revenue-Based Payouts
- Calculation Type: Aggregate Based
- Aggregation Object: Rebate Member Product Aggregate
- Calculation Definition: Main Aggregate by Member with Aggregate Item Details (cloned from the Aggregate by Member with Aggregate Item Details template)
- Measure Type: Percentage of Revenue
- Measure Field: Total Transaction Amount
- Benefit Qualifier Field: Total Transaction Amount
- Product Filter Type: Include Products
The manager adds these two benefit tiers to the rebate type:
- Silver tier offers a 7% rebate when the total transaction amount is between $25,000 and $100,000.
- Gold tier offers a 15% rebate when the total transaction amount is above $100,001.
The manager adds these products to the Included Products list.
- Compactor 3500
- Compactor 8000
The manager defines these eligibility criteria:
- Select Records When: All Conditions Are Met
- Resource: Member Priority
- Operator: Not Equals
- Value: Low
Transaction Journal Records
Here are the sample transaction records from Q1, FY’24 for all the members enrolled in the rebate program.
| Record Number | Member Name | Country | Product | Product Price Per Unit | Transaction Quantity | Return Order Quantity | Discount (%) | Member Priority |
|---|---|---|---|---|---|---|---|---|
| 1 | Acme | UK | Compactor 3500 | $5000 | 4 | 2 | 6 | High |
| 2 | Acme | UK | Compactor 8000 | $7000 | 10 | 3 | 7 | High |
| 3 | Acme | USA | Compactor 3500 | $5000 | 10 | 2 | 5 | High |
| 4 | Meridian | USA | Compactor 8000 | $7000 | 5 | 1 | 7 | Medium |
| 5 | Meridian | USA | Compactor 3500 | $5000 | 6 | 2 | 5 | Medium |
| 6 | Neogen | UK | Compactor 8000 | $7000 | 2 | 0 | 7 | Low |
| 7 | ABC Corp | India | Compactor 3500 | $5000 | 4 | 1 | 5 | High |
Custom Field Specifications
The manager creates these fields:
| Object | Field Name | Data Type | Purpose |
|---|---|---|---|
| Transaction Journal | Country | Text | Grouping Criteria |
| Rebate Member Product Aggregate | Member Country | Text | Grouping Criteria |
| Transaction Journal | Discount | Number | Aggregation Criteria |
| Rebate Member Product Aggregate | Total Discount Offered | Number | Aggregation Criteria |
| Transaction Journal | Return Order Quantity | Number | Aggregation Criteria |
| Rebate Member Product Aggregate | Total Return Quantity | Number | Aggregation Criteria |
Data Processing Engine Edit App Specifications
The manager specifies these values in the Data Processing Engine Edit prebuilt app:
- Data Processing Engine Name: Main_Aggregate_by_Member_with_Aggregate_Item_Details
- For aggregating data
- Transaction Journal Field: Discount
- Rebate Aggregate Object Field: Total Discount Offered
- Select the checkbox Aggregate with Eligibility Criteria
- Transaction Journal Field: Return Order Quantity
- Rebate Aggregate Object Field: Total Return Quantity
- Deselect the checkbox Aggregate with Eligibility Criteria
- For grouping data
- Transaction Journal Field: Country
- Rebate Aggregate Object Field: Member Country
Data Processing Engine Definition Updates
The program manager downloads the JSON file of the modified Main_Aggregate_by_Member_with_Aggregate_Item_Details DPE definition that now contains the node changes for the field mappings and the grouping and aggregation logic.
The program manager opens the Main Aggregate by Member with Aggregate Item Details definition in the DPE builder, and uploads the modified JSON file.
The manager can see the changes to all the required nodes.
The manager can run the definition either from within the builder, or via a scheduled flow.
Rebate Member Product Aggregate Results
These records are created in the Rebate Member Product object based on the eligibility filters, grouping, and aggregation logic. The transactions for Neogen are eliminated based on the eligibility criteria.
| Member Name | Member Country | Total Transaction Quantity | Total Transaction Amount | Total Discounts Offered (%) | Total Return Quantity |
|---|---|---|---|---|---|
| Acme | UK | 14 | $90,000 | 13 | 5 |
| Meridian | USA | 11 | $95,000 | 12 | 3 |
| Acme | USA | 10 | $50,000 | 5 | 2 |
| ABC Corp | India | 4 | $20,000 | 5 | 1 |
These values are updated whenever the scheduled flow runs. The flow triggers the Main Aggregate by Member with Aggregate Item Details data processing engine definition. The aggregate values on the payout calculation date for Q1 FY21 are used to update the final payout amounts in the Member Payout object.
Final Payouts
The members receive the following payouts for the first quarter, based on the benefit tiers defined by the program manager:
- Acme qualifies for the Gold tier benefit, and receives a payout of $9800 (7% of $14,0000)
- Meridian qualifies for the Silver tier benefit, and receives a payout of $6650 (7% of $95,000)
- ABC Corp doesn’t qualify for the payouts because the total transaction amount ($20,000) is less than the minimum eligible value for the Silver benefit tier.