Business Intelligence/ Decision Models

1. Business Intelligence/Decision Models Week 4 Lifetime Value

2. Review • Week 2: Data organization in RDBMS, SQL Queries • Week 3: Importing data into SPSS and Data Transformation in preparation for analytics • Week 4: Customers’ Lifetime value • CLV Spreadsheets • SPSS Life Tables and Means • Estimating CLV from SPSS and into Excel

3. How Lifetime Value is used for acquisition and retention • We need to know the value (the equity) of our customers, so as to properly target our sales and retention efforts • We need to discriminate among our customers to acquire and retain the best • More specifically, how much money should be spent on • Acquisitions • Retention

4. What is lifetime value? • Net present value of the profit to be realized on the average new customer during a given number of years. • To compute it, you must be able to track customers from year to year.

5. Customer Lifetime Value nCLV = [NPV Σi=1(Pri X Inci)] – AC0 where Pr is the survival probability for period i Pr X Inc. is the expected income for period I n is the number of time periods NPV is the net present value AC is the acquisition cost

6. LTV • Spreadsheet • Life tables (SPSS)

7. Simple CLV Spreadsheet  Discount Factor = (1 + (.08 x 2))2    or D = (1.16)2 = 1.35. http://www.dbmarketing.com/articles/Art251a.htm

8. How much to invest in retention? During Year 2 • Pr. of cancelling = 30% • Replacement Cost: $35.00 * 30% = $10.50 • Gross profit if surviving: $3,787,500/60,000 = $63.13 • Opportunity Cost if cancelled: $63.13 x 30% = $18.94 • Total Expected Cost: $18.94 + $10.50 = $29.44 • If 100% sure to salvage, investment < $29.44 • If only 10% probability of salvage, investment < $2.94

9. NPV • $1 @ 10% = $1.10 (after 1 yr) • $1.10 @ 10% = $1.21 (after 2 yrs) • $1 x (1.10)3 = $1.33 (after 3 yrs) • PV x (1 + r) n =FV • FV = PV/(1 + r) n

10. Discount Rate First year (0): (1+.06)0 = 1.0 Second year : (1+.06)1 = 1.060 Third year : (1+.06)2 = 1.124 _________________________________ PV = FV in p0 $100/1 = $100 PV = FV in p1 $100/1.06 = $94 PV = FV in p2 $100/1.124 = $89 NPV over all three years =$283

11. Excel for discounting factor • Discount Rate = (1 + r)^n • Discount Rate = POWER((1+r),n)

12. Discount Rate • http://en.wikipedia.org/wiki/Discounting • Discount Rate for period 1 (r): • Interest Rate (e.g. 10%) • Cost of Capital (e.g. 15%) • Hurdle Rate (e.g. ROI = 20%)

13. Simple CLV SpreadsheetStarting Parametres

14. Tutorial • Program a CLV Worksheet (See Excel sheet) • Use SPSS to Estimate CLV • Transform dates using Date/Time Wizard • Use Survival/Life table to estimate cumulative survival rate by time period and customer segment • Use Compare Means to estimate annual purchases • Transfer data into your CLV spreadsheet

16. Topline from Hughes’ Reading

17. Top line from Chapter 3-1Lifetime Value • Subscriber or customer lifetime value is the standard method of measuring the success of mail or e-mail marketing programs. It is defined as the profit you make from a specific number of people over a three-year period. • Knowing the lifetime value lets you know how much you can spend to acquire more customers, or how much you have lost when they unsubscribe or go away. • A LTV table does not include new subscribers or customers. It measures the value of the ones that you already have. • An average open rate for promotional e-mails is about 10 percent. This means that about 90 percent of all promotional e-mails never get read by anybody.

18. Top line from Chapter 3-2Lifetime Value • HTML stands for hypertext markup language. It provides the color and graphics for modern e-mails. • Once they have opened an e-mail, readers can click on a link to get more information. That click sends a packet back to the company that sent the e-mail and lets it know who clicked, when, and on what. • Once opened, about 20 percent of e-mails are clicked on at least once. • Once clicked on, about 3 to 4 percent of promotional e-mails result in a sale within the e-mail. • The discount rate permits you to discount future profits to today’s value so you can add them to get the net present value of all present and future profits. • Typically e-mails produce sales within the e-mail shopping cart and also stimulate readers to buy later from a store, a catalog, or a Web site. These subsequent sales are measured by the off-e-mail multiplier.

19. Top line from Chapter 3-3Lifetime Value • Few companies know the value of the off-e-mail multiplier. An average value might be about 3.76, which means that e-mails produce 3.76 sales in other channels for every sale made in the e-mail itself. • The average return on investment from e-mail marketing is typically about eight times the ROI from direct mail. • LTV is typically computed over three years. This is because it supports investments leading to future profits; but those providing the funding for marketing activities seldom support projects that last longer than three years. • LTV can be calculated for market segments, or can even be attributed to individual customers.

20. Top line from Chapter 7Customer Loyalty • Building and maintaining loyalty today is easier than it used to be because of e-mails and cell phones, and more complex because customers expect that you will maintain a 360 degree knowledge of their purchases from you. • Loyal customers are more profitable for you than other customers. • Calling customers by name on the phone, on Web sites, and in e-mails is essential to maintaining loyalty. • You need a universal ID number like phone numbers or e-mail addresses for all customers. • Reichheld suggested that, “Would you recommend me?” is a good way to measure loyalty. • There are two types of loyalty programs: instant discounts (for lower-income customers) and points (for higher-income customers). • The primary purpose of a loyalty program is obtaining information which helps achieve the goals of the business. • Your actions toward customers can determine their loyalty. Acquiring the right customers is a way to build loyalty.