Configuring Promotion Optimization for Predictive Trade Planning

This chapter covers the following topics:

• Overview of the Configuration Process
• Set Up Promotion Optimization Without Using the Installer
• Configuring the Optimization Step
• Other Important Notes

Overview of the Configuration Process

To configure Promotion Optimization, you use the following general process:

1. First do one of the following:

   • Run the Demantra installer, choosing the option to install Promotion Optimization.

   • Or manually do the same work that the installer does; see "Setting Up PO Without Using the Installer".

2. Then see "Configuring the Optimization Step".

3. Before using Promotion Optimization for the first time, run the Analytical Engine in batch mode so that your machine has access to the engine's cached results. See "Using the Engine Administrator and Running the Engine" in the Demantra Analytical Engine Guide.

Set Up Promotion Optimization Without Using the Installer

If you did not use the Demantra installer to set up Promotion Optimization, complete the steps in this section.

Setting Environment Variables

Promotion Optimization requires the following Windows environment variable be set:

PATH	Must include the directory Demantra_root\Collaborator\virtual_directory\optimization\dll For example: F:\Demantra Spectrum\Collaborator\demantra\optimization\dll

Registering the Analytical Engine

The installer registers the engine. If you do not use the installer, you may need to register the engine manually.

To do so, double-click the batch file Demantra_root/Demand Planner/Analytical Engines/bin\RegEngine.bat.

Configuring the Optimization Step

For Promotion Optimization, the primary configurable element is the Optimization step. Because this requires details about your installation location, you must configure this step manually.

To configure the optimization step

1. Click the menu item Workflow Manager.

2. Click the Workflow Manager tab.

   Note: Only this user can make changes to the optimization workflow.

3. In the row for the Call Promotion Optimizer workflow, click Edit.

4. Right-click the Call Promotion Optimizer step, and then choose Properties.

5. In the Parameters section, review the following parameters in case any changes are necessary. Typically, these parameters do not require modification. This step is required in order to make optimization work in your environment.

   Parameter Name	Purpose
   MODEL_PATH	Complete path and filename of the promoopt.opl file.
   dbms_type	The database type, one of the following: odbc oracle81 The database type information is automatically received from the application server.
   dbms_connect	Database connection information in the form database_user/database_password@databasename. For example: demantra/d@alexish The dbms_connect parameter is automatically received from the application server but modifications may be required if the servername and dbname are not the same.
   JAR_FILE	Relative Path of OPLtoJava.jar file.
   DRIVER_JAR	Relative Path of ojdbc14.jar file

6. Optionally edit the following parameters.

   Caution: Do not edit parameters that are not listed in this chapter.

   Parameter Name	Purpose
   opti_level_item	Name of the item level for optimization. This is case-sensitive. For example: SKU
   opti_level_location	Name of the location level for optimization. This is case-sensitive. For example: Ship to
   opti_level_promo	Name of the promotion level for optimization. This is case-sensitive. For example: Promotion
   promo_max_budget_attr	Name of the field in the Promotion table that stores the maximum budget.
   promo_used_budget_attr	Name of the field in the Promotion table to which the method writes the optimized budget.
   opti_value_steps	Number of variations of the causal factors that the optimizer should try. Use a value from 29 to 500. 29 is the default.
   MAX_PROMO_ON_PROD_ACC	Maximum number of concurrent promotions on a given account. Suggested value: 1.
   MAX_LENGTH_OF_PROMO	Maximum permitted length of any promotion, measured in base time buckets.
   MIN_RET_MARGIN	SQL expression that returns the margin that the retailer requires. Do not set equal to 0. Promotion Optimization computes the retailer margin as follows: (sale price + buydown)/list price - 1
   MIN_MAN_MARGIN	SQL expression that returns the minimum margin that the manufacturer requires. Do not set equal to 0. Promotion Optimization computes the manufacturer margin as follows: (list price - buydown)/cost of goods - 1
   MAX_BUY_DOWN	Maximum permitted buydown. Use a very large number such as 100000.
   RET_CONSUMER_PRICE_EXPRESSION	SQL expression that returns the everyday price seen by the consumer.
   MAN_LIST_PRICE_EXPRESSION	SQL expression that returns the list price seen by the retailer.
   MAN_COGS_EXPRESSION	SQL expression that returns the cost of goods to the manufacturer.
   MAN_VEHICLE_COST_EXPRESSION	SQL expression that returns any fixed costs associated with running the promotion. See also Other Important Notes > Vehicle Cost Expression and Event Cost Columns.
   FIXED_BUYDOWN_YN	Specifies whether to use buydown as an input (1) or to calculate the optimal buydown (0). Use 1.
   BUYDOWN_EXPRESSION	SQL expression that returns the buydown per unit.
   MIN_RET_REVENUE	Minimum retailer revenue for any promotion.
   MIN_RET_DEMAND	Minimum retailer demand (unit count) for any promotion.
   MIN_MAN_PROFIT	Minimum manufacturer profit for any promotion.
   MIN_RET_INC_PROFIT	Minimum retailer incremental profit for any promotion.
   MIN_RET_INC_REVENUE	Minimum retailer incremental revenue for any promotion.
   STATUS_OPTI_CHANGE_ANY	If the promotion status is below this level, the optimizer modifies the promotion. Set this value to be the same as parameter STATUS_OPTI_LOCKED.
   STATUS_OPTI_LOCKED	If the promotion status is at this level or higher, the optimizer does not modify the promotion. Set it to the number of a status_id..
   USE_FINANCIAL_CONSTRAINTS	Turn certain financial constraints on and off. Valid values are: - 0 = Off (default) - 1 = On When you set this to 0, these constraints are off: - MIN_RET_REVENUE - MIN_RET_INC_REVENUE - MIN_RET_DEMAND - MIN_RET_INC_PROFIT - MIN_MAN_PROFIT - MIN_INC_DEMAND - MIN_RET_MARGIN - MAX_BUY_DOWN If you turn on these financial constraints, review them to make sure that the optimizer can find a solution In some cases, incremental profit or retailer revenue receive negative values. The promotion is profitable and meets retailer margin constraints but is less profitable for the retailer than not running the promotion. If you want the optimizer to ignore these constraints, set them to large negative numbers.
   BASE_DEMAND_EXPRESSION	If you want the optimizer to accept and include manual overrides to the baseline forecast, set this expression to include any override columns that you want. For example, sum(nvl(branch_data.fcst_override,branch_data.#FORE#)). Use this if you allow the optimization to change promotion dates, the optimizer needs to pick up any baseline overrides for the adjusted date range. If you do not want it to, set this expression to sum(nvl(branch_data.#FORE#,0)).
   GOAL_FOCUS	Specifies if Promotion Optimization should optimize from the manufacturer's perspective (0; recommended) or the retailer's perspective (1).
   COEFFICIENT_RANGE_FACTOR	Controls the range of possible coefficient values to be searched. For any given causal factor, Promotion Optimization by default tests a discrete set of coefficient values, ranging from 0 to the largest value observed in history. This parameter specifies the additional percentage to add to that range of values. For example, to allow Promotion Optimization to search values 20% larger than historically seen, set this parameter to 0.20.
   DEFAULT_RET_MARGIN	SQL expression that returns the default retailer margin.
   DEFAULT_MAN_MARGIN	SQL expression that returns the default manufacturer margin.
   RETAILER_LEVEL_ID	ID of the level (from group_tables) that corresponds to the retailers.
   OPTI_BUYDOWN_COLUMN	Defines which column the optimized buydown is written to. Refers to a column on the promotion_data table.
   OPTIMIZATION_RANGE_START	SQL expression that returns the start of the date range which is considered by optimization. Expression references promotion table.
   OPTIMIZATION_RANGE_END	SQL expression that returns the end of the date range which is considered by optimization. Expression references promotion table.
   BUYDOWN_EXPRESSION	SQL expression that returns the actual buydown for promotion being optimized. Expression references promotion_data table. As a default, it incorporates bill back, off invoice and scan values.

Other Important Notes

The Promotion Optimization module uses certain fields in the Promotion and promotion_data tables; do not change the names of any fields.

Also, the Promotion Optimization methods do not check for all the required inputs. In particular, you must make sure that the following information is available before running these methods:

• List price

• Buydown

Additional information regarding degrees of freedom

The actual possible solutions the optimization engine will evaluate depend on the status of the promotion being optimized. The internal ID of the promotion's status will be compared with the system parameter PromotionOptiStatusThreshold.

• If Promotion Status < PromotionOptiStatusThreshold, the optimizer can modify the promotion's timing, promotion type and values of promotional causals.

• If Promotion Status = PromotionOptiStatusThreshold, the optimizer can modify the promotion type and values of promotional causals.

• If Promotion Status > PromotionOptiStatusThreshold, the optimizer can modify the values of promotional causals only.

Since the comparison is based on whether the status is above or below the parameter, it is strongly recommended that promotion statuses be ordered with increasing internal IDs from least fixed to most fixed promotions.

Note: The optimization engine only evaluates the direct effects generated by causal factors. Therefore, optimization results are focused on providing an optimized outcome for the direct effect of the promotion and not the indirect affect. The indirect effects of the promotion can be viewed by accepting the optimized promotion and then executing a simulation.

Solution Exclusion Rules

Use solution exclusion rules when you have a set of conditions and you don't want them to occur together in an optimized promotion. You can:

• Make as many rules as you need

• Put as many conditions in each rule as the number of Oracle Predictive Trade Planning analytical engine causal factors that are in the engine profile configuration

To define the rules and conditions, manually insert rows into table TPO_EXCLUSION_RULES table. Each table row has the rule name and the condition. There is not one table for the rule name and another table for the conditions. These are the table columns:

Column	Description
RULE_ID	The name of the rule. Unique for each set of rows defining a rule.
CAUSAL_ID	Causal_ID from m3_causal_factors, for example, the causal_id for Promotion Type.
TRANSPOSE_ID	A specific value of the transpose_function from the m3_causal_factors table transpose_column sql-expression, for example TPR. If the causal has no transpose, then define this as 0.
MIN_VAL and MAX_VAL	The optimizer makes sure that the variable is outside the ranges defined. If these parameters are 0, the optimizer excludes all values for the causal.
LOC_DIM_ID	Location dimension ID. For each rule, make this the same value for every condition of the rule.Valid values are: - NULL - A valid ID from the members of the level in parameter opti_level_location. Find the IDs in workflow Call Promotion Optimizer, step OPLStep. If you: - Set a value, the optimizer restricts the rule to any combination having that ID for the opti_level_location dimension - Don't set a value, the exclusion rule is global across all locations.
ITEM_DIM_ID	Dimension ID. Make it the same value for every row of a rule.Valid values are: - NULL - A valid ID from the members in the opti_level_item parameter. Find the IDs in workflow Call Promotion Optimizer, step OPLStep. If you: - Set a value, the optimizer restricts the rule to any combination having that ID for the opti_level_item dimension - Don't set a value, it is global across all items.

This example shows rows of table TPO_EXCLUSION_RULES that tell the optimizer:

• As location above, never combine (1) causal with ID 105 and transpose value 46 and (2) causal with ID 125 and transpose value 16. This could represent the rule of never having a promotion with type TPR and a promotion with an Ad Type, since TPR type promotions are price reductions.

• To exclude discounts in the range of 0.15 to 0.30

INSERT INTO "SPECIAL_CUSTOMER"."TPO_EXCLUSION_RULES" (RULE_ID, CAUSAL_ID, TRANSPOSE_ID, MIN_VAL, MAX_VAL, LOC_DIM_ID, ITEM_DIM_ID) VALUES ('1', '105', '46', '0', '0', NULL, NULL)

INSERT INTO "SPECIAL_CUSTOMER"."TPO_EXCLUSION_RULES" (RULE_ID, CAUSAL_ID, TRANSPOSE_ID, MIN_VAL, MAX_VAL, LOC_DIM_ID, ITEM_DIM_ID) VALUES ('1', '125', '16', '0', '0', NULL, NULL)

INSERT INTO "SPECIAL_CUSTOMER"."TPO_EXCLUSION_RULES" (RULE_ID, CAUSAL_ID, TRANSPOSE_ID, MIN_VAL, MAX_VAL, LOC_DIM_ID, ITEM_DIM_ID) VALUES ('1', '115', '0', '0.15', '0.30', NULL, NULL)

Optimized Buydown

When you optimize a promotion, you can specify either a fixed buydown or calculated buydown. Buydown is the list price discount that the manufacturer needs to offer to the retailer during the promoted period.

When you select calculated buydown, you instruct the optimizer to minimize costs, for example buydown and vehicle, but not to override retailer constraints, for example minimum margin, or manufacturer constraints, for example, maximum buydown allowed. It saves the result in the optimizer buydown result.

Valid values are:

• Maximize revenue

• Maximize profit

• Maximize units

See also Configuring the Optimization Steps > To configure the optimization steps > parameter opti_value_steps.

Vehicle Cost Expression and Event Cost Columns

Use the vehicle_cost expression (parameter MAN_VEHICLE_COST_EXPRESSION) to specify retailer event cost overrides per causal and as well as per transpose. A procedure creates the columns on the Retailer level.

You define this parameter in workflow Call Promotion Optimizer, step OPLStep. The default expression uses a case statement to map event costs stored on promotion_data:

MAN_VEHICLE_COST_EXPRESSION = "avg(nvl((case #TRANSPOSE# when 25 then branch_data.FIXED_COST_OVER_1 else branch_data.FIXED_COST_OVER end) ,nvl(t_ep_lr2a.EVENT_COSTS_#TRANSPOSE#,0)))";

To conserve columns, the configuration of the SQL Expression handles the mapping between causal/transpose and promotion_data cost override. Modify the expression based on the availability of vehicle cost information and its actual location.

Optimizer Log

The optimizer logs its activity to the collaborator log. If you want it to log to a file instead, go to workflow Call Promotion Optimizer, step OPLStep, and set at least one of the debug parameters' value to FILE. The optimizer writes the file to directory <Demantra_Collaborator_Home>/optimization/opl.