use SPSS software

CASE #2: Tuscan Lifestyles: Targeting Customers with RFM Analysis Sara Beckman is increasingly confident that careful analysis of the Tuscan Lifestyles customer database will lead to more efficient and effective marketing decisions. As director of marketing for the upscale specialty catalog specializing in cookware, tableware, linens, and decorative home accessories in the spirit of the Tuscany region of Italy, Sara has been pursuing several initiatives in database marketing and customer relationship marketing. Tuscan Lifestyles is proud of its track record in identifying prospects that become customers. Their historical response rate for direct mail to prospect lists is 2.3% - far above the industry average. However, Sara is convinced that there are opportunities to increase the effectiveness of subsequent catalog mailings. Tuscan Lifestyles typically mails catalogs four times a year to their full customer list. Yet, as their customer list grows Sara wonders if it may be more effective to develop a portfolio of catalogs - and through market tests, identify which subset of the full customer list to target. Database marketers have a long history of success with using RFM (for Recency - Frequency - Monetary) analysis to target customers. The fundamental premise underlying RFM analysis is that customers who have purchased recently, have made more purchases and have made larger purchases are more likely to respond to your offering than other customers who have purchased less recently, less often and in smaller amounts. RFM analysis can also be used to target special offers to welcome' new customers, encourage small purchasers to spend more, to reactivate lapsed customers, or encourage other marketing initiatives. Although there are several variations of RFM segmentation, they all begin by classifying customers based on the recency of their last purchase, the frequency of their purchases, and the amount spent. It is typical to consider quintiles - or five groups - when looking at combinations of RFM. Professor Charlotte Mason prepared this case to provide material for class discussion rather than to dlustrate ether of the ineffective handling of a business situation. Names and data may have been disguised to assure Page 2 There are two major variations of RFM classification: Method 1 - "Independent n-tiles' computes quintiles independently for recency, frequency and monetary. Then they are combined to form a RFM index. This approach typically yields unequal numbers of customers across the 125 combinations. For example, some combinations - such as customers who are very recent, very frequent, yet very low on monetary - are less common than others. Method 2 - 'Sequential n-tiles' computes quintiles first for recency. Then, within each quintile for recency, quintiles for frequency are computed - resulting in a total of 25 recency/frequency combinations. Finally, within each of these 25 cells, quintiles for monetary are computed. This method produces approximately equal numbers of customers for each of the 125 RFM cells. Following is an outline of what Sara proposes for using RFM analysis for targeting mailings to current Tuscan Lifestyles customers: Compile the catalog offering . Select a random sample from the full pustomer database and mail the catalog Track the response (% who respond, 3 orders) by RFM classification or 'cell. Assess profitability and return on marketing expenditure by RFM cell. Use results of the test maling to determine which customers from the remaining customer list (i.e. those not in the test) to mail. Sara has asked you to conduct an initial analysis of this approach - using data from a recent Tuscan Lifestyles catalog, Using data from a random sample of 96,551 customers who received this recent mailing (see Exhibit 1 for a summary and definitions of key variables found in the dataset Tuscan.sav), Sara has asked you to prepare a report addressing the following questions: Page 3 IMPORTANT: PROVIDE KEY OUTPUT AND AT LEAST ONE SENTENCE INTERPRETING THE OUTPUT, FOR EACH QUESTION. (You can put your answers on page 8 of this document.) Part 1: Descriptive Customer Analysis This analysis uses the Tuscan.sav data file. 1. How many customers are in this data file (the test mailing)? How many are male? How many are female? 2. How many buyers are in this data file [Hint targbuy? 3. What percent of customers actually responded to (i.e., bought anything from) this catalog? 4. What is the mean response rate of men and women? (Hint: first recode targbuy into a binary format of O=no, 1-yes, then obtain mean of this new variable for men and women you will need this new variable again later in the case.] 5. Of those who bought , what was the average 5 order size (Hint: select cases or split file; analyze targdol variable]? 6. If COGS plus variable costs are 50% of sales, what is the contribution per sale (Hint: Incorporate your answer from question #5]? Part I: RFM Classification Sara is interested in the effectiveness of the 'sequential n-tiles' approach for RFM classification See Exhibit 2 of this case for reminders on how to create RFM cells using the sequential n-tile (.e., nested) approach. 7. Create a sequential RFM Model in Tuscan sav. Generate a report showing the number of customers, number of buyers, and the response rate for each RFM cell. (Hint: use Analyze ... reports... caso summaries and remember that the response rate is the mean of the buyer variable you created in your answer to question #4 request N. sum, mean). Export results from your SPSS output table to Excel, and sort the rows from high to low on response rate Include a small screen shot of your sorted results in Excel to receive credit for this question, Part III: Profitability Analysis Al this firm, MacBook Air See Exhibit 2 of this case for reminders on how to create RFM cells using the sequential n-tile (.e., nested) approach. 7. Create a sequential RFM Model in Tuscan.sav. Generate a report showing the number of customers, number of buyers, and the response rate for each RFM cell. (Hint: use Analyze ... reports... case summaries and remember that the response rate is the mean of the buyer variable you created in your answer to question #4; request N, sum, mean). Export results from your SPSS output table to Excel, and sort the rows from high to low on response rate. Include a small screen shot of your sorted results in Excel to receive credit for this question. = Part I: Profitability Analysis At this firm, the cost to produce and mail catalog = $1 COGS and variable costs - 50% (of sales revenues) 8. What is the breakeven response rate (Hint: use the mailing cost information above along with your answer to question #6]? Now, let's compare the profitability of two scenarios (in questions #9 and #10): mailing to all 96,551 customers (no model approach) mailing only to those customers in RFM cells with a response rate that is equal to or a greater than the breakeven response rate Page 4 Page 4 . 9. Determine (a) the gross profit in dollars, and (b) the return on marketing expenditures as a result of mailing the catalog to all 96,551 customers. (Hint: how many customers are mailed, how many buyers are obtained? Recall that here, ROME = gross profits/mailing costs] 10. Continuing to use the Excel spreadsheet from question #7, Determine which RFM segments have response rates exceeding the breakeven rate. Determine the number of customers belonging to these profitable segments. Determine the number of buyers belonging to these profitable segments. Calculate, what would the (a) the gross profit in dollars, (b) the return on marketing expenditures (gross profit/cost to mail catalogs) from mailing the catalog only to those customers in the RFM segments with response rates at or above the breakeven point? That is, rather than mailing to all 96,551 customers - what would the profitability of this catalog mailing be if we mail to the subset of customers in 'profitablo' segments? In a sentence or two provide a recommendation to your manager about whether to use a mass mailing or RFM sequential model to determine which customers should receive the catalog Page of 9 Page 8 Your Name: Your Team Member Names (if any): Your Answers to CASE #2 Part 1: Descriptive Customer Analysis 1. There are males and customers are in this data file (the test mailing)? There are females 2. There are buyers are in this data file. 3. percent of customers actually responded to (ie, bought anything from) this catalog, 4. The mean response rate of men is and of women is 5. Of those who bought, the average order size was $ 6. The contribution per sale is $ Part II: RFM Classification 7. Below is a screen shot of my sorted RFM sequential cells from Excel. Part Profitabir Analysis Page B of 9 - 1 6. The contribution per sale is $ Part II: RFM Classification 7. Below is a screen shot of my sorted RFM sequential cells from Excel Part III: Profitability Analysis 8. The breakeven response rate is and ROME will be 9. If we mail to all customers in this data set, we will make $ %. 10. If we mail to those customers at or above break-even using the RFM sequential model, we will make $ and ROME will be % Therefore, I recommend we CASE #2: Tuscan Lifestyles: Targeting Customers with RFM Analysis Sara Beckman is increasingly confident that careful analysis of the Tuscan Lifestyles customer database will lead to more efficient and effective marketing decisions. As director of marketing for the upscale specialty catalog specializing in cookware, tableware, linens, and decorative home accessories in the spirit of the Tuscany region of Italy, Sara has been pursuing several initiatives in database marketing and customer relationship marketing. Tuscan Lifestyles is proud of its track record in identifying prospects that become customers. Their historical response rate for direct mail to prospect lists is 2.3% - far above the industry average. However, Sara is convinced that there are opportunities to increase the effectiveness of subsequent catalog mailings. Tuscan Lifestyles typically mails catalogs four times a year to their full customer list. Yet, as their customer list grows Sara wonders if it may be more effective to develop a portfolio of catalogs - and through market tests, identify which subset of the full customer list to target. Database marketers have a long history of success with using RFM (for Recency - Frequency - Monetary) analysis to target customers. The fundamental premise underlying RFM analysis is that customers who have purchased recently, have made more purchases and have made larger purchases are more likely to respond to your offering than other customers who have purchased less recently, less often and in smaller amounts. RFM analysis can also be used to target special offers to welcome' new customers, encourage small purchasers to spend more, to reactivate lapsed customers, or encourage other marketing initiatives. Although there are several variations of RFM segmentation, they all begin by classifying customers based on the recency of their last purchase, the frequency of their purchases, and the amount spent. It is typical to consider quintiles - or five groups - when looking at combinations of RFM. Professor Charlotte Mason prepared this case to provide material for class discussion rather than to dlustrate ether of the ineffective handling of a business situation. Names and data may have been disguised to assure Page 2 There are two major variations of RFM classification: Method 1 - "Independent n-tiles' computes quintiles independently for recency, frequency and monetary. Then they are combined to form a RFM index. This approach typically yields unequal numbers of customers across the 125 combinations. For example, some combinations - such as customers who are very recent, very frequent, yet very low on monetary - are less common than others. Method 2 - 'Sequential n-tiles' computes quintiles first for recency. Then, within each quintile for recency, quintiles for frequency are computed - resulting in a total of 25 recency/frequency combinations. Finally, within each of these 25 cells, quintiles for monetary are computed. This method produces approximately equal numbers of customers for each of the 125 RFM cells. Following is an outline of what Sara proposes for using RFM analysis for targeting mailings to current Tuscan Lifestyles customers: Compile the catalog offering . Select a random sample from the full pustomer database and mail the catalog Track the response (% who respond, 3 orders) by RFM classification or 'cell. Assess profitability and return on marketing expenditure by RFM cell. Use results of the test maling to determine which customers from the remaining customer list (i.e. those not in the test) to mail. Sara has asked you to conduct an initial analysis of this approach - using data from a recent Tuscan Lifestyles catalog, Using data from a random sample of 96,551 customers who received this recent mailing (see Exhibit 1 for a summary and definitions of key variables found in the dataset Tuscan.sav), Sara has asked you to prepare a report addressing the following questions: Page 3 IMPORTANT: PROVIDE KEY OUTPUT AND AT LEAST ONE SENTENCE INTERPRETING THE OUTPUT, FOR EACH QUESTION. (You can put your answers on page 8 of this document.) Part 1: Descriptive Customer Analysis This analysis uses the Tuscan.sav data file. 1. How many customers are in this data file (the test mailing)? How many are male? How many are female? 2. How many buyers are in this data file [Hint targbuy? 3. What percent of customers actually responded to (i.e., bought anything from) this catalog? 4. What is the mean response rate of men and women? (Hint: first recode targbuy into a binary format of O=no, 1-yes, then obtain mean of this new variable for men and women you will need this new variable again later in the case.] 5. Of those who bought , what was the average 5 order size (Hint: select cases or split file; analyze targdol variable]? 6. If COGS plus variable costs are 50% of sales, what is the contribution per sale (Hint: Incorporate your answer from question #5]? Part I: RFM Classification Sara is interested in the effectiveness of the 'sequential n-tiles' approach for RFM classification See Exhibit 2 of this case for reminders on how to create RFM cells using the sequential n-tile (.e., nested) approach. 7. Create a sequential RFM Model in Tuscan sav. Generate a report showing the number of customers, number of buyers, and the response rate for each RFM cell. (Hint: use Analyze ... reports... caso summaries and remember that the response rate is the mean of the buyer variable you created in your answer to question #4 request N. sum, mean). Export results from your SPSS output table to Excel, and sort the rows from high to low on response rate Include a small screen shot of your sorted results in Excel to receive credit for this question, Part III: Profitability Analysis Al this firm, MacBook Air See Exhibit 2 of this case for reminders on how to create RFM cells using the sequential n-tile (.e., nested) approach. 7. Create a sequential RFM Model in Tuscan.sav. Generate a report showing the number of customers, number of buyers, and the response rate for each RFM cell. (Hint: use Analyze ... reports... case summaries and remember that the response rate is the mean of the buyer variable you created in your answer to question #4; request N, sum, mean). Export results from your SPSS output table to Excel, and sort the rows from high to low on response rate. Include a small screen shot of your sorted results in Excel to receive credit for this question. = Part I: Profitability Analysis At this firm, the cost to produce and mail catalog = $1 COGS and variable costs - 50% (of sales revenues) 8. What is the breakeven response rate (Hint: use the mailing cost information above along with your answer to question #6]? Now, let's compare the profitability of two scenarios (in questions #9 and #10): mailing to all 96,551 customers (no model approach) mailing only to those customers in RFM cells with a response rate that is equal to or a greater than the breakeven response rate Page 4 Page 4 . 9. Determine (a) the gross profit in dollars, and (b) the return on marketing expenditures as a result of mailing the catalog to all 96,551 customers. (Hint: how many customers are mailed, how many buyers are obtained? Recall that here, ROME = gross profits/mailing costs] 10. Continuing to use the Excel spreadsheet from question #7, Determine which RFM segments have response rates exceeding the breakeven rate. Determine the number of customers belonging to these profitable segments. Determine the number of buyers belonging to these profitable segments. Calculate, what would the (a) the gross profit in dollars, (b) the return on marketing expenditures (gross profit/cost to mail catalogs) from mailing the catalog only to those customers in the RFM segments with response rates at or above the breakeven point? That is, rather than mailing to all 96,551 customers - what would the profitability of this catalog mailing be if we mail to the subset of customers in 'profitablo' segments? In a sentence or two provide a recommendation to your manager about whether to use a mass mailing or RFM sequential model to determine which customers should receive the catalog Page of 9 Page 8 Your Name: Your Team Member Names (if any): Your Answers to CASE #2 Part 1: Descriptive Customer Analysis 1. There are males and customers are in this data file (the test mailing)? There are females 2. There are buyers are in this data file. 3. percent of customers actually responded to (ie, bought anything from) this catalog, 4. The mean response rate of men is and of women is 5. Of those who bought, the average order size was $ 6. The contribution per sale is $ Part II: RFM Classification 7. Below is a screen shot of my sorted RFM sequential cells from Excel. Part Profitabir Analysis Page B of 9 - 1 6. The contribution per sale is $ Part II: RFM Classification 7. Below is a screen shot of my sorted RFM sequential cells from Excel Part III: Profitability Analysis 8. The breakeven response rate is and ROME will be 9. If we mail to all customers in this data set, we will make $ %. 10. If we mail to those customers at or above break-even using the RFM sequential model, we will make $ and ROME will be % Therefore, I recommend we