Optimizing Marketing Investment

Table Functions of our .NET libraries finaquant® protos (non-commercial) or finaquant® calcs (commercial) can be used to formulate evaluation function (also known as objective or fitness function) and constraints in any optimization scenario quite easily without cumbersome database programming; following model example shows how.

The output of an evaluation function can be measures like profit, time or costs, depending on the figure you want to minimize or maximize. In the following example, we want to get optimal marketing investment amounts per brand (value drivers) in order to maximize total profit from sales.

We will use Nelder-Mead Solver from Microsoft Solver Foundation (free .NET library) to find optimal investment amounts in this example.

You may download the Visual Studio project file with all the C# code required to run this example here.

Business Scenario

An online digital products dealer wants to increase its sales through advertisements on internet. Each ad will promote a certain brand (i.e. ads at brand level). The increase in sales for each product depends on (1) investment amount, and (2) sensitivity α of the product.
Marketing Investment as Value Driver

Formulas

Profit formula
Lift Factor Curve
Goal: Finding optimal marketing investment amount per brand to maximize profit

Input Tables

There are two input tables:

  1. Product table with key figures like price, cost, sensitivity and quantity (normal expected quantity without any advertisement)
  2. Marketing Investment table with initial (sub-optimal) investment amounts.

Input Tables
Following C# code shows how input tables are created:
Create Input Tables

using FinaquantCalcs;
 
// define metadata
MetaData md = MetaData.CreateEmptyMetaData();
MetaData.AddNewField(md, "brand", FieldType.TextAttribute);
MetaData.AddNewField(md, "category", FieldType.TextAttribute);
MetaData.AddNewField(md, "product", FieldType.TextAttribute);
 
MetaData.AddNewField(md, "price", FieldType.KeyFigure);
MetaData.AddNewField(md, "cost", FieldType.KeyFigure);
MetaData.AddNewField(md, "profit", FieldType.KeyFigure);
MetaData.AddNewField(md, "sensitivity", FieldType.KeyFigure);
MetaData.AddNewField(md, "quantity", FieldType.KeyFigure);
MetaData.AddNewField(md, "advinvest", FieldType.KeyFigure);
MetaData.AddNewField(md, "advfactor", FieldType.KeyFigure);
MetaData.AddNewField(md, "liftedqty", FieldType.KeyFigure);
 
// create master product table
var ProductTableFields = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(ProductTableFields, "brand");
TableFields.AddNewField(ProductTableFields, "category");
TableFields.AddNewField(ProductTableFields, "product");
TableFields.AddNewField(ProductTableFields, "price");
TableFields.AddNewField(ProductTableFields, "cost");
TableFields.AddNewField(ProductTableFields, "sensitivity");
TableFields.AddNewField(ProductTableFields, "quantity");
 
MatrixTable ProductTable = MatrixTable.CreateTableWithElements_A(ProductTableFields,
	"HP", "Notebook", "HP Notebook H200", 900.0, 650.0, 0.8, 22.0,
	"HP", "Notebook", "HP Notebook H400", 1200.0, 800.0, 0.7, 12.0,
	"Asus", "Notebook", "Asus Notebook A100", 700.0, 550.0, 1.2, 25.0,
	"Asus", "Notebook", "Asus Notebook A200", 900.0, 700.0, 0.9, 22.0,
	"Samsung", "Notebook", "Samsung Notebook S100", 900.0, 700.0, 0.9, 32.0,
	"Samsung", "Notebook", "Samsung Notebook S500", 1100.0, 850.0, 0.8, 30.0,
 
	"HP", "PC", "HP PC H800", 1200.0, 1000.0, 0.7, 24.0,
	"HP", "PC", "HP PC H900", 1500.0, 1200.0, 0.6, 15.0,
	"Asus", "PC", "Asus PC A500", 1600.0, 1400.0, 1.0, 18.0,
	"Asus", "PC", "Asus PC A600", 1800.0, 1500.0, 0.9, 16.0,
	"Samsung", "PC", "Samsung PC S100", 900.0, 700.0, 0.8, 35.0,
	"Samsung", "PC", "Samsung PC S500", 1100.0, 800.0, 0.7, 30.0,
 
	"HP", "Server", "HP Server H1500", 2500.0, 2000.0, 0.5, 14.0,
	"HP", "Server", "HP Server H3000", 3500.0, 2500.0, 0.6, 9.0,
	"Asus", "Server", "Asus Server A1500", 2600.0, 2200.0, 0.4, 12.0,
	"Asus", "Server", "Asus Server A1600", 2900.0, 2400.0, 0.3, 9.0,
	"Samsung", "Server", "Samsung Server S3100", 3900.0, 3000.0, 0.6, 15.0,
	"Samsung", "Server", "Samsung Server S3500", 4500.0, 3300.0, 0.5, 18.0
	);
// view table
MatrixTable.View_MatrixTable(ProductTable, "1. Input: Product Table");
 
// create advertisement investment table
var AdvInvestTableFields = TableFields.CreateEmptyTableFields(md);
TableFields.AddNewField(AdvInvestTableFields, "brand");
TableFields.AddNewField(AdvInvestTableFields, "advinvest");
 
MatrixTable AdvInvestTable = MatrixTable.CreateTableWithElements_A(AdvInvestTableFields,
	"HP", 1500.0,
	"Asus", 2000.0,
	"Samsung", 2500.0
	);
// view table
MatrixTable.View_MatrixTable(AdvInvestTable, "2. Input: Marketing Investment Table with initial sub-optimal amounts");

Formulating Profit with Table Functions

Following C# code shows how profit can be formulated with table functions.

Note: Row Transformer (MatrixTable.TransformRowsDic) is used to apply user-defined formulas on each row of extended product table.
Calculate Profit

using FinaquantCalcs;
 
public class OptimizeInvestment
{
	// global parameters
	private static int ctr = 0;
	private static MatrixTable _ProductTable;
	private static MatrixTable _AdvInvestTable;
	private static double _MaxTotalInvest = 30000;
 
	// Optimal Advertisement Investment for each brand
	public static void MarketingInvestment()
	{
		// Create input tables
		...
 
		_AdvInvestTable = AdvInvestTable.Clone();
 
		// insert new key figures into product table
		ProductTable = MatrixTable.InsertNewColumn(ProductTable, "advfactor");
		ProductTable = MatrixTable.InsertNewColumn(ProductTable, "liftedqty");
		ProductTable = MatrixTable.InsertNewColumn(ProductTable, "profit");
 
		_ProductTable = ProductTable;
 
		// Add advertisement investment into product table
		var ProductTable2 = MatrixTable.CombineTables(ProductTable, AdvInvestTable);
 
		// view table
		MatrixTable.View_MatrixTable(ProductTable2, "ProductTable with adv investment");
 
		// row-by-row processing: apply formula(s) on each row of product table
		// to calculate adv factor, lifted quantity and profit
		ProductTable2 = MatrixTable.TransformRowsDic(ProductTable2, CalculateProfit);
 
		// view table
		MatrixTable.View_MatrixTable(ProductTable2, "ProductTable with profit");
 
		// total net profit
		double GrossProfitSales = KeyVector.SumOfAllElements(ProductTable2.GetColumnKeyFigure("profit"));
		double AdvCosts = KeyVector.SumOfAllElements(AdvInvestTable.GetColumnKeyFigure("advinvest"));
		double NetProfit = GrossProfitSales - AdvCosts;
 
		Console.WriteLine("Gross Profit from Sales = " + GrossProfitSales);
		Console.WriteLine("Advertisement Costs = " + AdvCosts);
		Console.WriteLine("Net Profit = " + NetProfit);
		Console.ReadKey();
	}
 
	// Calculate adv factor, lifted quantity and profit for a product 
	// Method called by row transformer
	private static void CalculateProfit(
		ref Dictionary<string, string> TextAttribDic,
		ref Dictionary<string, int> NumAttribDic,
		ref Dictionary<string, double> KeyFigDic)
	{
		// adv factor
		KeyFigDic["advfactor"] = LiftFactor(KeyFigDic["advinvest"], KeyFigDic["sensitivity"]);
 
		// lifted quantity
		KeyFigDic["liftedqty"] = Math.Round(KeyFigDic["advfactor"] * KeyFigDic["quantity"]);
 
		// profit
		KeyFigDic["profit"] = KeyFigDic["liftedqty"] * (KeyFigDic["price"] - KeyFigDic["cost"]);
	}
}


Console output: Net Profit
Extended Product Table with Profit

Finding Optimal Investment Amounts with a Constraint

Following C# code shows how optimal investment amount for brand each can be obtained with the Nelder-Mead Solver for non-linear optimization models without constraints.

In the code below, objProfit2() is the evaluation function.
Constraint: Total Marketing Investment <= $30000

Note: Nelder-Mead Solver can solve optimization models without constraints, but there is a way to outtrick this solver: Adding a high penalty to evaluation function if constraints are not satisfied. In this example, this penalty is formulated as follows:

double ConstraintPenalty = (Math.Sign(totInvest - MaxTotalInvestment) + 1) * 500000;
Find Optimal Investment Amounts
#region OPTIMIZE WITH MS SOLVER FOUNDATION NelderMeadSolver
 
int dim = AdvInvestTable.RowCount;
 
var solverParams = new NelderMeadSolverParams();
// var solverParams = new HybridLocalSearchParameters();
 
var solver = new NelderMeadSolver();
// var solver = new HybridLocalSearchSolver();
 
int TotalProfit;
int[] MarketingInvestment = new int[dim];
 
//add variables
for (int i = 0; i < dim; i++)
{
	solver.AddVariable(null, out MarketingInvestment[i]);
	solver.SetBounds(MarketingInvestment[i], 0, _MaxTotalInvest);
}
 
//add a row and set it as the goal
solver.AddRow(null, out TotalProfit);
solver.AddGoal(TotalProfit, 0, true);
 
// initial investment amounts (starting point)
for (int i = 0; i < dim; i++)
{
	solver.SetValue(MarketingInvestment[i], AdvInvestTable.GetKeyFigValue("advinvest", i));
}
solver.FunctionEvaluator = objProfit2;
 
//Solve the model
// goto t1;
var solution = solver.Solve(solverParams);
Console.WriteLine("========= OPTIMIZE ADV INVESTMENT PER BRAND WITH NelderMeadSolver ==========");
Console.WriteLine("Max Profit: " + solution.GetSolutionValue(0));
 
Console.WriteLine("========Values=========");
for (int i = 0; i < dim; i++)
{
	Console.WriteLine("Xopt[{0}] = {1}", (i + 1), (double) solver.GetValue(i+1));
}
Console.ReadKey();
 
#endregion OPTIMIZE WITH MS SOLVER FOUNDATION NelderMeadSolver  
 
// objective function, minimize negative profit
private static double objProfit2(INonlinearModel model, int rowVid, ValuesByIndex values, bool newValues)
{
	MatrixTable AdvInvestTbl = _AdvInvestTable.Clone();
 
	for (int i = 0; i < AdvInvestTbl.RowCount; i++)
	{
		AdvInvestTbl.SetKeyFigValue("advinvest", i, values[i+1]);
	}
 
	// calculate net profit
	// Add advertisement investment to product table
	var ProductTable = MatrixTable.CombineTables(_ProductTable, AdvInvestTbl);
 
	// row-by-row processing: apply formula(s) on each row of product table
	// to calculate adv factor, lifted quantity and profit
	ProductTable = MatrixTable.TransformRowsDic(ProductTable, CalculateProfit);
 
	// total net profit
	double GrossProfitSales = KeyVector.SumOfAllElements(ProductTable.GetColumnKeyFigure("profit"));
	double AdvCosts = KeyVector.SumOfAllElements(AdvInvestTbl.GetColumnKeyFigure("advinvest"));
 
	// Total Investment
	double totInvest = 0;
	for (int i = 0; i < AdvInvestTbl.RowCount; i++) totInvest += values[i + 1];
	double ConstraintPenalty = (Math.Sign(totInvest - _MaxTotalInvest) + 1) * 500000;
 
	return (0 - (GrossProfitSales - AdvCosts) + ConstraintPenalty);
}

 
Optimal Investment Amounts
Marketing Investment Table with optimized amounts

Conclusions

This example demonstrates that table functions can be used to implement complex evaluation functions based on data tables (i.e. input tables) in optimization problems. Similar optimization problems may arise in many business fields including operational research, risk analytics and financial planning. That is, table functions can be used to support better and smarter business decisions.

Copyright secured by Digiprove © 2014 Tunc Ali Kütükcüoglu
This entry was posted in Calculation engine and tagged , . Bookmark the permalink.

Leave a Reply