Sales Promotions – After Challenge Discussion

Blog: Decision Management Community

In this post I’m arguing that the use of database tables to represent sales promotions is not a good idea to compare with the use of rules or decision tables. The promotion example in the Jan-2018 Challenge was a very simple one. Unfortunately, in the real world, business users are often not so accommodating. Towards the end of this document I’ve listed some examples of promotions that are in production at real customer sites. The main thing to notice is the wide variety of the various promotions.

In modeling this kind of problem there are essentially two parts that need to be accommodated:

The Customer-Order-Item data structure i.e. the core business data
The Promotion rules

Almost certainly the Customer-Order-Item structure will already have been designed and expressed as some sort of database table(s). It is still needed to run the business even if we don’t offer rebates or discounts.

Databases are indispensable for the core business objects. And generally the structure is relatively stable. We don’t normally see the core business data model changing every few weeks. In fact making changes to database structures can be quite a slow process requiring the involvement of IT and DBAs for approval.

However, promotions are quite a different kind of animal. The business is typically changing the sales promotions quarterly or even more frequently. If the promotion structure never changed, a table would work just fine. The values in the database can easily be changed at a moment’s notice – and the logic can be coded once in java and since it’s entirely generic it should rarely change. And you wouldn’t need an expensive rule engine.

But modeling promotions in a database may not give the business the flexibility it would like. The whole idea of a sales order promotion is to let the business users frequently and arbitrarily change their rules – and that means not just the values but the entire structure.

Every time they make a change to the structure (as opposed to the values) of a promotion you could be faced with a potential database redesign – or more likely a brand new database. Or worse still the business users simply are told that the database doesn’t support that kind of promotion so it can’t be implemented.

Along with that new database will likely be a new user interface – and that will probably require some programming. And putting the values in the database still means you have to put the semantics in some kind of code or decision table.

So now you have the business rules split into two parts – the values are in the database but the interpretation of those values is coded somewhere else. Very hard to see what’s going on since neither place gives the complete picture.

This means that a business user, looking at the values in some kind of web UI which has to be custom built for the purpose, will need something to explain what the various data values actually mean. And if this explanation is provided in English there is the very real possibility of misunderstanding (is the value in a column interpreted as “greater than” or “at least”. Providing the code probably wouldn’t help most business users.

Similarly if you only look at the code you see something very generic that often gives little clue as to what actual values may be in play. You also have to look at the values in the database to make complete sense of the rules. For example to know that lemons get a discount if you order more than 6.

A compromise is the use of in-memory lookup tables defined inside the rule model and created during the execution of the rules rather than being stored on an external device.

In-memory tables can be more efficient (no need to read an external data source) and more flexible (very easy to change). In practice most database lookup tables are stable for short periods and end up getting cached in memory anyway to improve performance so why not eliminate the overhead entirely?

But what if applications other than the rules need access to that lookup table defined in the rule model?

You simply allow other applications to call the decision service to get the data rather than making a SQL call to a database – it might actually be quicker – and you have a convenient way to isolate the application from the physical database. Note that this approach is most useful for read-only data such as lookup tables for pricing etc. You still need physical databases for the core business data which is typically in a constant state of flux.

In contrast, by putting it all in a decision table (e.g. Corticon, OpenRules and others) you get these benefits:

Everything is one place – values and semantics – no need to cross reference the database when reading the rules. And you can still use in-memory tables for the promotion details if you want to – though then you miss out on the ability of the decision table to automatically detect ambiguities and omissions in the rules. Though it’s certainly possible to write rules to do this.
There are no constraints on what kinds of weird rules you can create (and business users do come up with some pretty crazy rules for their promotions – see examples further on)
The decision table is a familiar concept to most business people; it has been around longer than programming languages (and if you have a natural language form of the decision table like Corticon it’s even easier)
When you update the decision table with new values, ranges or conditions, a good rule engine will be able to tell you if you have missed any combinations of conditions or if you have any conflicting rules – this is something that’s very hard to accomplish when you simply allow business users to change values in a database through a web interface (unless you want to write all the validation logic yourself – in which case you now find yourself replicating in the web UI a lot of the semantics that are already in the rules/code that makes the decision in order to know whether values conflict or are missing.

Summary

While it is certainly possible to define database structures to represent sales promotions it may be better to use rules or decision tables to support the wide variety and frequent change that seems to occur in the real world.

Examples of Actual Promotions

Promotions Based On Historical Data

Rebates for the last quarter of 2017 are given for product sales in certain categories providing they are more than 5% over prior year’s sales. Prior year sales is actually a composite of parts of 2016 and parts of 2017 based on sales date and various other factors. So the logic for determining what prior sales to count is somewhat complex and arbitrary.

Promotions Based on the Customer’s Sales Ranking

Each customer’s total eligible sales are ranked in relation to other customers in the same category. Rebates are given based on the customer’s relative ranking, not on absolute sales figures.

Promotions based on Knowledge of the Customer’s Business

A customer’s segmentation is determined by using information about their business (e.g. the number of X, Y or Z items the customer already has) to calculate an estimate of the sales that will be made in the forthcoming year (for example the sales of new items needed for the maintenance of X, Y and Z). Rebates are then given depending on the customer segmentation.

Goal Based Promotions

Customers are required to provide estimates of their likely purchases in each of several categories for each quarter in the upcoming year.

Then each quarter the actual sales are compared with the estimate. Rebates are given when they exceed the estimate. Any amount over the estimate can be carried forward from the prior quarter to offset any low sales in that quarter. Q1 may not use any excess from the prior year Q4. For any given quarter the number of categories that met the quota is counted. If all categories meet the quota then the customer gets 100% of the rebate. If one or two categories fail to meet the quota then those categories get no rebate but others that meet the quota get 100%. If three categories fail to meet the quota then those categories get no rebate but others that meet the quota get 50%. If more than three categories fail to meet the quota but the aggregate quota was reached then they get 50% bonus. If the aggregate quota was not reached then there is no rebate even if some categories meet their quota. Customers are encouraged to set higher quotas to get better unit prices (otherwise they would all make estimates of zero and always exceed their quotas). The quota is reduced by any backordered amounts.

Variable Item Pricing Promotions

Customer orders 250 items total=$1700

Over 100 is priced at $5 (150×5=$750)

51-100 is priced at $6 (100×6=$600)

1-50 is priced at $7 ( 50×7=$350)

If anyone else has examples of actual promotions used in production it would be interesting to see some details.