Data Segmentation Analysis (Excel Table).

 The tables in the attached file show a cross-tabulation of frequent -flier mileage categories (high, med, low) by Age-category (younger, older). A travel offer was made to 5000 people in the analytic sample •Table 1 shows the counts of the 5000 people in the analytic sample. •Table 2 shows the counts as a percentage of the 5000 people in the analytic sample •Table 3 shows the number of orders placed for the travel offer. •Table 4 shows the response rate for the travel offer HW: Create a new table using Excel showing indexes of observed response rates, relative to the overall rate. Then based on the index table you created, create 3 segments labeled as follows: Yes (index >= 120), Maybe (index between 100-120), No (index <=100) – If the entire customer file is 100,000 customers, how many customers are there in each of the “Yes”, “Maybe”, and “No” categories? – What might be the observed response rate for the “Yes” segment that you want to market to? – Past marketing efforts (before this new segmentation) showed an overall response rate of 5.24%. If new offers are to be sent and targeted to the “Yes” segment, what is the expected response rate for the “Yes” segment? And what is the response rate index? – How many orders to the travel offer would you expect?