Saturday, May 29, 2010
Thursday, February 4, 2010
Sunday, January 17, 2010
DATABASE SCORING OVERVIEW
If you want to trust your analysis first of all you should trust your data. In order to increase your data quality first you should know the noisy ones.
I. Null or Blank Values
Is Null functions of every query language works. Score should be considered 1, means that it is definitely wrong.
II. Definetly Wrong Values:
- Comparison with Other Databases
There are databases that you can trust more like government’s databases, and when you compare your data with them if the value does not match then should be considered as a wrong value and scored as 1.
- Comparison with Internal Databases
If two data from your multiple databases do not match means that one of them should be considered as wrong. Initially scoring both of them 0,66 is logical.
(
We know that the result is false so;
There are three possibilities here so the Truth Table:
I. Column & II. Column Result
True False False
False True False
False False False
Assumption: All of these possibilities should be considered as having the same probability.
So both of these two columns have %66 possibility of being wrong.
After some cleansing activities these possibilities can be changed with observed possibilities, this is an initial assumption.
)
- Comparison of different Columns
There are related data in every databases like a person’s whose job column is doctor and whose education column is high school is not possible. At least one of these values should be considered as wrong. So initially scoring both of them 0,66 may be logical.
III. Values that have possibility of being wrong
This is the prediction part of database scoring.
There may be used two methods here:
1. Clustering-Segmentation
Cluster would have some dominant characteristic like most of the customers is married or income mean 2.000 so going away from these values makes it most probable for being a wrong value.
2. Modeling
A model is built for every single column. This is a much more expensive method then first.
IV. Values That Can Not be Predicted
There always remain a data that can not be predicted. So these values have the same possibility of being wrong or right. So initially these kind of values should have 0.5 score.
As it is stated above these are the initial scores. All of these segments should be re-scored after data cleansing feedbacks.
Güven Kızıltaş
I. Null or Blank Values
Is Null functions of every query language works. Score should be considered 1, means that it is definitely wrong.
II. Definetly Wrong Values:
- Comparison with Other Databases
There are databases that you can trust more like government’s databases, and when you compare your data with them if the value does not match then should be considered as a wrong value and scored as 1.
- Comparison with Internal Databases
If two data from your multiple databases do not match means that one of them should be considered as wrong. Initially scoring both of them 0,66 is logical.
(
We know that the result is false so;
There are three possibilities here so the Truth Table:
I. Column & II. Column Result
True False False
False True False
False False False
Assumption: All of these possibilities should be considered as having the same probability.
So both of these two columns have %66 possibility of being wrong.
After some cleansing activities these possibilities can be changed with observed possibilities, this is an initial assumption.
)
- Comparison of different Columns
There are related data in every databases like a person’s whose job column is doctor and whose education column is high school is not possible. At least one of these values should be considered as wrong. So initially scoring both of them 0,66 may be logical.
III. Values that have possibility of being wrong
This is the prediction part of database scoring.
There may be used two methods here:
1. Clustering-Segmentation
Cluster would have some dominant characteristic like most of the customers is married or income mean 2.000 so going away from these values makes it most probable for being a wrong value.
2. Modeling
A model is built for every single column. This is a much more expensive method then first.
IV. Values That Can Not be Predicted
There always remain a data that can not be predicted. So these values have the same possibility of being wrong or right. So initially these kind of values should have 0.5 score.
As it is stated above these are the initial scores. All of these segments should be re-scored after data cleansing feedbacks.
Güven Kızıltaş
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.
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.
Subscribe to:
Posts (Atom)