INITIAL SALE 1ST YEAR

RENEWAL 2ND YEAR RENEWAL 3RD YEAR Initial customers _{50,000 35,500 28,045} Renewal rate 71% 79% 85% Total revenue $30,710,500 $21,804,455 $17,225,519

Policy maintenance & claims $9,738,150 $8,004,309 $7,184,680

Gross profit $20,972,350 $13,800,146 $10,040,839

Discount rate _{1.00 1.15 1.32}

Net present value _{$20,972,350 $12,000,127 $7,592,317}

Cumulative net present value $20,972,350 $32,972,477 $40,564,794

if infd_ag2 < 40 then risk_adj = 1.05; else if infd_ag2 < 50 then risk_adj = 1.01; else if infd_ag2 < 60 then risk_adj = 0.92; else risk_adj = 0.78; end;

end;

The next step assigns the average net present value of the product profitability, prodprof. And finally the average net present value (npv_3yr) is derived by multiplying the probability of becoming active (pred_scr) times the risk adjustment index (risk_adj) times the sum of the discounted profits from the initial policy (prodprof) minus the initial marketing expense:

prodprof = 811.30;

npv_3yr= pred_scr*risk_adj*prodprof - .78;

run;

proc sort data=acqmod.test; by descending npv_3yr; run; data acqmod.test; set acqmod.test; smp_wgt=1; sumwgt=5000; number+smp_wgt;

if number < .1*sumwgt then val_dec = 0; else if number < .2*sumwgt then val_dec = 1; else if number < .3*sumwgt then val_dec = 2; else if number < .4*sumwgt then val_dec = 3; else if number < .5*sumwgt then val_dec = 4; else if number < .6*sumwgt then val_dec = 5; else if number < .7*sumwgt then val_dec = 6; else if number < .8*sumwgt then val_dec = 7; else if number < .9*sumwgt then val_dec = 8; else val_dec = 9;

run;

proc tabulate data=acqmod.test; weight smp_wgt;

class val_dec;

var records pred_scr risk_adj npv_3yr; table val_dec='Decile' all='Total',

records='Prospects'*sum=' '*f=comma10.

pred_scr='Predicted Probability'*(mean=' '*f=11.5) risk_adj = 'Risk Index'*(mean=' '*f=6.2)

npv_3yr = 'Total 3-Year Net Present Value' *(mean=' '*f=dollar8.2)

/rts = 9 row=float; run;

TIP

If you have a very large file and you want more choices for determining where to make a cut-off, you can create more groups. For example, if you wanted to look at 20 groups (sometimes called twentiles), just divide the file into 20 equal parts and display the results.

A model is a powerful tool for ranking customers or prospects. Figure 7.5 shows the expected active rate, average risk index, and three-year present

Figure 7.5 Decile analysis of scored file.

value by decile for the new file based on the sample that was scored. The model, however, does not provide the rules for making the final name selections. The decision about how deep to go into a file is purely a business decision.

In Figure 7.6, I plug the expected active rate for each decile into the NPV formula. The columns in the table are used to calculate the NPV projections necessary to make an intelligent business decision:

Prospects. The number of prospects in the scored file.

Predicted active rate. The rate per decile from Figure 7.5.

Risk index. The average risk based on a matrix provided by actuarial (see Table 4.1).

Product profitability. The expected profit from the initial policy discounted in today's dollars. The values are the same for every prospect because the model targets only one product. The calculation for discounting will be explained in chapter 12.

Average NPV. The average three-year profit from the one product offered, discounted in today's dollars.

Average cumulative NPV. The cumulative of average NPV.

Sum of cumulative NPV. The cumulative total dollars of NPV.

Figure 7.6 NPV model gains table.

Determining the File Cut-off

Once the file has been scored and the financials have been calculated, it's time to decide how many names to select or how much of the file to solicit. This is typically called the file cut-off. Figure 7.6 does an excellent job of providing the information needed for name selection. It, however, doesn't give the answer. There are a number of considerations when trying to decide how many deciles to solicit. For example, at first glance you might decide to cut the file at the fifth decile. The reason is rather obvious: This is last decile in which the NPV is positive. There are a number of other good choices, however, depending on your business goals.

Let's say you're a young company going after market share. Management might decide that you are willing to spend $0.25 to bring in a new customer. Then you can cross-sell and up-sell additional products. This is a very reasonable approach that would allow you to solicit eight deciles. (In chapter 12, I will develop a lifetime value model that incorporates cross-sell and up-sell potential.)

Perhaps your company decides that it must make a minimum of $0.30 on each customer to cover fixed expenses like salaries and building costs. In this situation, you would solicit the first four deciles. Another choice could be made based on model efficiency. If you look at the drop in NPV, the model seems to lose its high discrimination power after the third decile.

It's important to note that any decision to cut the file at a certain dollar (or cents) amount does not have to be made using deciles. The decile analysis can provide guidance while the actual cut -off could be at mid -decile based on a fixed amount. In our previous example, the average for decile 4 is $0.30. But at some point within that decile, the NPV drops below $0.30, so you might want to cut the file at an NPV of $0.30. The main point to remember is that selecting the file cut-off is a business decision. The decile analysis can provide guidance, but the decision must be clear and aligned with the goals of the business.

Champion versus Challenger

In many situations, a model is developed to replace an existing model. It may be that the old model is not performing. Or perhaps some new predictive information is available that can be incorporated into a new model. Whatever the reason, it is important to compare the new model, or the ''Challenger," to the existing model, or "Champion." Again, depending on your goals, there are a number of ways to do this.

In Figure 7.7, we see the entire file represented by the rectangle. The ovals represent the names selected by each model. If your "Champion" is doing well, you

Figure 7.7 Champion versus Challenger.

might decide to mail the entire file selected by the "Champion" and mail a sample from the portion of the "Challenger" oval that was not selected by the "Champion." This allows you to weight the names from the sample so you can track and compare both models' performance.

At this point, I have calculated an expected net present value for a single product. This is an excellent tool for estimating the long-term profitability of a customer based on the sale of a single product. We know that one of our company goals is to leverage the customer relationship by selling additional products and services to our current customer base. As mentioned previously, in chapter 12 I expand our case study to the level of long-term customer profitability by considering the present value of future potential sales. I will integrate that into our prospect model to calculate lifetime value.

The Two-Model Matrix

I decided against using the two -model approach because of instability. However, it may be preferred in certain situations because of its flexibility. Because the models have been built separately, it is possible to manage the components separately. This may be very useful for certain business strategies. It can also make the model performance easier to track. In other words, you can monitor response and activation separately.

The code is similar to the one -model code. The difference is that the decile values have to be calculated and blended together. The first step is to sort the

validation data by the response score (predrsp), create deciles called rsp_dec, and output a new data set. The steps are repeated to create deciles in a new data set based on activation called act_dec.

proc sort data=acqmod.out_rsp2(rename=(pred=predrsp)); by descending predrsp;

run;

proc univariate data=acqmod.out_rsp2(where=( splitwgt = .)) noprint; weight smp_wgt;

var predrsp;

output out=preddata sumwgt=sumwgt; run;

data acqmod.validrsp;

set acqmod.out_rsp2(where=( splitwgt = .)); if (_n_ eq 1) then set preddata;

retain sumwgt; number+smp_wgt;

if number < .1*sumwgt then rsp_dec = 0; else if number < .2*sumwgt then rsp_dec = 1; else if number < .3*sumwgt then rsp_dec = 2; else if number < .4*sumwgt then rsp_dec = 3; else if number < .5*sumwgt then rsp_dec = 4; else if number < .6*sumwgt then rsp_dec = 5; else if number < .7*sumwgt then rsp_dec = 6; else if number < .8*sumwgt then rsp_dec = 7; else if number < .9*sumwgt then rsp_dec = 8; else rsp_dec = 9;

run;

proc sort data=acqmod.out_act2(rename=(pred=predact)); by descending predact;

run;

proc univariate data=acqmod.out_act2(where=(splitwgt = .)) noprint; weight smp_wgt;

var predact active;

output out=preddata sumwgt=sumwgt; run;

data acqmod.validact;

set acqmod.out_act2(where=( splitwgt = .)); if (_n_ eq 1) then set preddata;

retain sumwgt; number+smp_wgt;

if number < .1*sumwgt then act_dec = 0; else if number < .2*sumwgt then act_dec = 1; else