ANALYTIC CROSS SELL CAMPAIGN PLANNING

CROSS SELL CAMPAIGN PLANNING
In order to maximize your Campaign Profits you can use a linear modeling method. Also this model will show you the shadow values which will help you to make your sensitivity analysis.
First of all for every kind of product you should have data selection queries which are either model or criteria set. Sample data for each data selection method should be leaded to every possible campaign channel.
Number of Reached Customers of every sample campaign
OVERDRAFT CASH LOAN MUTUAL FUND
OUTBOUND $500 $500 $500
BRANCH $500 $500 $500
SMS $500 $500 $500

After this, you should measure every campaigns’ profit.
Net Profit of the Campaigns
OVERDRAFT CASH LOAN MUTUAL FUND
OUTBOUND 5000 1500 3000
BRANCH 1500 1200 5000
SMS 350 1600 700

By using these two tables you can calculate net profit of a customer for each sample campaign.
Net Profit of A Customer
OVERDRAFT CASH LOAN MUTUAL FUND
OUTBOUND 10 3 6
BRANCH 3 2.4 10
SMS 0.7 3.2 1.4

Now the next step is to find the capacities.
There are two things to be considered when calculating the capacities.
1. Your company’s growth plan for gaining new customers.
2. Historical data, last year’s growth rates for every single product.
This one is so important because every product can be an input to the other models. You can boost every single data by using the possible growth rate.
For example assume that there are 50.000 cash loan customers at the end of the year and you expect it to rise %1 every month(there may be seasonal effects but we will discuss it later).
This means you should boost your data about %1 every month and the other models should use this when modeling.
This should be done for every single product of which variables are input to other models.
If the company’s intend is to grow %3 in that product %1 will be omitted and %3 should be used.

Now we have calculated the capacities for every possible campaign.
Net Profit of A Customer
OVERDRAFT CASH LOAN MUTUAL FUND CAPACITY
OUTBOUND 10 3 6 10000
BRANCH 3 2.4 10 15000
SMS 0.7 3.2 1.4 50000

Now we have constraints and resources and our aim is to maximize the total profit. So it is a linear programming problem that waits to be solved.
Campaign Selection
OVERDRAFT CASH LOAN MUTUAL FUND Channel Sum Capacity
OUTBOUND 0 <= 100000
BRANCH 0 <= 15000
SMS 0 <= 50000
Total 0 0 0

We expect the linear model to calculate the total customers of each product from each channel. (empty cells)
“Channel sum” represents the sum of all campaigns leaded to that channel.
“Total” represents the number of customers to whom that product will be offered.
Now we should write a sum product which maximizes the total profit.
Total Profit $0

Total profit cell is Sum Product of Net Profit Customer Matrix and Campaign Selection Matrix.


Constraints
1. Channel sums <= Capacity
2. (OB,KMH) : (YF,SMS) >= 0 – you can not lead a campaign which has negative number of customers 
3. # of Cash Loans >= 70.000
Variable Cells
(OB,Overdraft) : (SMS, Mutual Fund)
Target Cell
Total Profit cell

Then let the solver solve this problem for you.
Campaign Selection - Number of Customers
OVERDRAFT CASH LOAN MUTUAL FUND SUM OF CHANNEL CAPACITY
OUTBOUND 80000 20000 0 100,000 <= 100.000
BRANCH 0 0 15000 15,000 <= 15.000
SMS 0 50000 0 50,000 <= 50.000
Total 80000 70000 15000


Total Profit 1.170.000 $

The food is ready 
Changing Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$C$29 OUTBOUND OVERDRAFT 80000 0 10 0.600000001 4
$D$29 OUTBOUND CASH LOAN 20000 0 3 7 0.600000001
$E$29 OUTBOUND MUTUAL FUND 0 -4 6 4 1E+30
$C$30 BRANCH OVERDRAFT 0 -7 3 7 1E+30
$D$30 BRANCH CASH LOAN 0 -1 2.4 0.600000001 1E+30
$E$30 BRANCH MUTUAL FUND 15000 0 10 1E+30 0.600000001
$C$31 SMS OVERDRAFT 0 -9 0.700000001 9.499999999 1E+30
$D$31 SMS CASH LOAN 50000 0 3.2 1E+30 8.800000001
$E$31 SMS MUTUAL FUND 0 -9 1.399999999 8.800000001 1E+30

Changing the Coefficient
Allowable Increase/Decrease columns tell us that, provided the coefficient of Outbound Overdraft in the objective function lies between 10+0,6 = 10,6 and 10 - 4 = 6, the values of the variables in the optimal LP solution will remain unchanged. Similar things can be said for the other variables.
This means that when the profit per customer for Overdraft Campaign from Outbound is not in the [6;10,6] range you will expect the solution to change.
Forcing a variable which is currently zero to be non-zero
If we take the variables of which Final Value equals to 0:

Variable Reduced Cost New Value Estimated objective function change
OUTBOUND MUTUAL FUND 4 =A or >= A 4A
BRANCH OVERDRAFT 7 =B or >= B 7B
BRANCH CASH LOAN 1 =C or >= C C
SMS OVERDRAFT 9 =D or >= D 9D
SMS MUTUAL FUND 9 =E or >=E 9E

First of all reduced cost means opportunity cost. So the larger A,B,C,D,E the worse the solution will be.
Mutual Fund Offer from Outbound should have 4 $ more profit in order to be non-zero means that this campaign will be logical. Similar things can be said for the other variables.
Changing the right-hand side of a constraint
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value: Price R.H. Side Increase Decrease
$F$29 OUTBOUND SUM OF CHANNEL 100,000 10 100000 1E+30 80000
$F$30 BRANCH SUM OF CHANNEL 15,000 10 15000 1E+30 15000
$F$31 SMS SUM OF CHANNEL 50,000 10 50000 20000 50000
$D$32 Total CASH LOAN 70000 -7 70000 80000 20000

Shadow Price tells us exactly how much the objective function will change if we change the right-hand side of the corresponding constraint within the limits given in the Allowable Increase/Decrease columns.

For example for the Outbound Capacity constraint, provided the right-hand side of that constraint remains between 100.000+ 0=100.000 and 100.000 – 80.000 = 20.000 the objective function change will be 10.
And also this means that an extra call capacity of OB yields an increase in profit of $10. So you can decide to hire more employee to OB 
Similar things can be said for the other variables.

New Campaign Offers
This model should be updated and executed every month in order to have flexibility.
When a new campaign demand comes this diagram will be useful.

Assume that an Overdraft Campaign had the chance to pass the “Solve Linear Model” process the new problem would be:

* Assume that this month you should sell 10.000 overdrafts.




To summarize linear modeling may be a good way to guess your capacity needs and maximize your Campaign Profits.