Decision Model for Sales Order Promotions

promoDMCommunity.org Jan-2018 Challenge is looking for a decision model that can define promotions for different sales orders. It provides a simple example of promotion: reduce the total cost of the order by $3.50 if it contains at least 5 items 1108 and 4 item 2639. Let’s build the proper decision model.

The problem formulation sounds simple. However, to determine if an order is eligible for a promotion we need to check if an order’s items cover a promotion’s requirements that may involve embedded iterations. To better understand the problem, let’s start with test data that usually a good methodological approach.

So, we will define a set of promotions each of whom consist of several promotion items. We will use Excel Data tables in the OpenRules format. Here is our first table that defines the array “promotionItems”:

 And now we can define two promotions that contain some of these items:

So, the first promotion will give a customer a $3.50 discount if his/her order contains at least 5 items 1108 and at least 4 items 2639.  The second promotion will give a customer a $5.00 discount if his/her order contains at least 3 items 1112, 4 items 1723, and 2 items 1610.  Here is an example of customer’s orders:

We will use different combinations of these promotions and orders to test our future decision model that is supposed to determine if an order is eligible to a promotion. We set an initial value of the target decision variable “Order Is Eligible To Promotion” to NO, while our future rules may modify it to YES. Actually we already may specify a few test cases:

As you may guest, the Test-1 will check that the very first order (orders[0]) is eligible to the very first promotion (promotions[0]). Similarly other 3 test cases will validate the expected values for the pairs Promotion 2 – Order 1, Promotion 1 – Order 2, and Promotion 2 – Order 2.

Now, when we already have test data, we may start an implementation of our decision model that for each pair order-promotion should determine the decision variable “Order Is Eligible To Promotion”. It will be only natural to call our decision model “DetermineOrderPromotionEligibility”.

In plain English the decision logic may be expressed in a straight-forward way:

For each promotion item inside the promotion
validate that the actual total quantity of this item inside the order is more or equal to the minimally required quantity.

As a programmer, I immediately start thinking about two embedded loops with possible breaks… However, we need to represent this logic in a way that will be understandable to business people. It seems reasonable if we split the above logic in two separate steps:

  1. For each promotion item calculate its total quantity inside the order
  2. For each promotion compare the calculated quantity with the minimally required quantity and if it’s not reached set the variable “Order Is Eligible To Promotion” to NO.  Otherwise, it should remain YES.

We may describe these two steps as sub-decisions of our top-level decision:

The first sub-decision “CalculatePromotionItemQuantitiesInsideOrder” can be implemented using the following decision tables:

The first table of the type DecisionTableIterate iterates over the array “Promotion Items” that represents promotion items inside the given promotion. For each promotion item in this array it executes the rule “CalculateTotalQuantityForOneItem” defined in the second table. This table first initializes the decision variable “Qty of Promotion Item Inside Current Order” with 0 and then uses ActionIterate to iterate through all order items applying to them the rules “IncrementQtyInsideCurrentOrder”. These rules are (defined in the third table which for every for order items that have the same Id as the current promotion item increment its quantity using “Order Item Qty”.

By now, we calculated “Qty of Promotion Item Inside Current Order” for each promotion item. We can use the values of these variables defined inside promotion items to implement the sub-decision “ValidateCalculatedQuantities” using the following two tables:

The first table executes two actions:

  1. Initializes the decision variable “Order is Eligible to Promotion” with YES
  2. Iterates through promotion items and for each item executes the rules “CompareActualAndMinimalQuantites” defined in the second table.

The second table for the current promotion item checks the condition

Qty of Promotion Item Inside Current Order < Promotion Item Minimal Qty

and if it is true, it overrides the default value YES for the decision variable “Order is Eligible to Promotion” with NO. This completes our processing logic.

To be able to execute our decision model we need to put all introduced decision variables in the glossary:

As we don’t want to use Java (yet), to complete our decision table in Excel we need to define data types for all PromotionItem, Promotion, OrderItem, and Order:

Now we can execute our decision model against the above 4 test cases. Here are the produced results:

RUN TEST: Test-1
Decision DetermineOrderPromotionEligibility: 
Calculate Qty Of Promotion Items inside Order
Iterate over Promotion Items using rules 
CalculateTotalQuantityForOneItem
  Conclusion: Qty of Promotion Item Inside Current Order = 0 [0.0]
Iterate over Order Items using rules 
IncrementQtyInsideCurrentOrder
Conclusion: 
Qty of Promotion Item Inside Current Order += Order Item Qty [5.0]
  Conclusion: Qty of Promotion Item Inside Current Order = 0 [0.0]
Iterate over Order Items using rules 
IncrementQtyInsideCurrentOrder
Conclusion: 
Qty of Promotion Item Inside Current Order += Order Item Qty [6.0]
Decision DetermineOrderPromotionEligibility: 
Validate Calculated Quantities against minimally required
  Conclusion: Order is Eligible to Promotion = YES [YES]
Iterate over Promotion Items using rules 
CompareActualAndMinimalQuantities
Validating results for the test 
Test-1 was successful
Executed test Test-1 in 33 ms

RUN TEST: Test-2
Decision DetermineOrderPromotionEligibility: 
Calculate Qty Of Promotion Items inside Order
Iterate over Promotion Items using rules 
CalculateTotalQuantityForOneItem
Conclusion: Qty of Promotion Item Inside Current Order = 0 [0.0]
Iterate over Order Items using rules 
IncrementQtyInsideCurrentOrder
Conclusion: Qty of Promotion Item Inside Current Order = 0 [0.0]
Iterate over Order Items using rules 
IncrementQtyInsideCurrentOrder
Conclusion: Qty of Promotion Item Inside Current Order = 0 [0.0]
Iterate over Order Items using rules 
IncrementQtyInsideCurrentOrder
Decision DetermineOrderPromotionEligibility: 
Validate Calculated Quantities against minimally required
Conclusion: Order is Eligible to Promotion = YES [YES]
Iterate over Promotion Items using rules 
CompareActualAndMinimalQuantities
  Conclusion: Order is Eligible to Promotion = NO [NO]
  Conclusion: Order is Eligible to Promotion = NO [NO]
  Conclusion: Order is Eligible to Promotion = NO [NO]
Validating results for the test 
Test-2 was successful
Executed test Test-2 in 11 ms

RUN TEST: Test-3 
Decision DetermineOrderPromotionEligibility: 
Calculate Qty Of Promotion Items inside Order
Iterate over Promotion Items using rules 
CalculateTotalQuantityForOneItem
Conclusion: Qty of Promotion Item Inside Current Order = 0 [0.0]
Iterate over Order Items using rules 
IncrementQtyInsideCurrentOrder
Conclusion: 
Qty of Promotion Item Inside Current Order += Order Item Qty [5.0]
Conclusion: Qty of Promotion Item Inside Current Order = 0 [0.0]
Iterate over Order Items using rules 
IncrementQtyInsideCurrentOrder
Conclusion: 
Qty of Promotion Item Inside Current Order += Order Item Qty [6.0]
Decision DetermineOrderPromotionEligibility: 
Validate Calculated Quantities against minimally required
Conclusion: Order is Eligible to Promotion = YES [YES]
Iterate over Promotion Items using rules 
CompareActualAndMinimalQuantities
Validating results for the test 
Test-3 was successful
Executed test Test-3 in 9 ms

RUN TEST: Test-4
Decision DetermineOrderPromotionEligibility: 
Calculate Qty Of Promotion Items inside Order
Iterate over Promotion Items using rules 
CalculateTotalQuantityForOneItem
Conclusion: Qty of Promotion Item Inside Current Order = 0 [0.0]
Iterate over Order Items using rules 
IncrementQtyInsideCurrentOrder
Conclusion: Qty of Promotion Item Inside Current Order = 0 [0.0]
Iterate over Order Items using rules 
IncrementQtyInsideCurrentOrder
Conclusion: Qty of Promotion Item Inside Current Order = 0 [0.0]
Iterate over Order Items using rules 
IncrementQtyInsideCurrentOrder
Decision DetermineOrderPromotionEligibility: 
Validate Calculated Quantities against minimally required
Conclusion: Order is Eligible to Promotion = YES [YES]
Iterate over Promotion Items using rules 
CompareActualAndMinimalQuantities
  Conclusion: Order is Eligible to Promotion = NO [NO]
  Conclusion: Order is Eligible to Promotion = NO [NO]
  Conclusion: Order is Eligible to Promotion = NO [NO]
Validating results for the test 
Test-4 was successful
Executed test Test-4 in 9 ms
All 4 tests succeeded!
Executed all tests cases in 66 ms

The above decision tables can be easily extended to also calculate the complete and promotional costs for the given order and to define the discount as a percentage instead of an a fixed dollar amount. Here are Excel files which contain the complete implementation:

Decision.xls  Rules.xls  Glossary.xls  Data.xls

Our readers can download them and use it with an evaluation version of OpenRules to run this decision model and/or to extend its functionality.

Advertisements

One comment on “Decision Model for Sales Order Promotions

  1. Pingback: Decision Model for Order Promotions |

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.