Drilling Down Home Page Turning Customer Data
into Profits with a Spreadsheet
The Guide to Maximizing Customer Marketing ROI

Site Map

Book Includes all tutorials and examples from this web site

Get the book!

Purchase Drilling Down Book

Customers Speak Up on Book & Site

About the Author

Workshops, Project Work: Retail Metrics & Reporting, High ROI
Customer Marketing

Marketing Productivity Blog

8 Customer
Promotion Tips


Customer Retention

Customer Loyalty

High ROI Customer Marketing: 3 Key Success Components

LifeTime Value and
True ROI of Ad Spend

Customer Profiling

Intro to Customer
Behavior Modeling

Customer Model:

Customer Model:

Customer Model:
Recent Repeaters

Customer Model:

Customer LifeCycles

LifeTime Value

Calculating ROI

Mapping Visitor

Measuring Retention
in Online Retailing

Measuring CRM ROI

CRM Analytics:
Micro vs. Macro

Pre-CRM Testing for
Marketing ROI

Behavior Profiling

See Customer
Behavior Maps

Favorite Drilling
Down Web Sites

About the Author

Book Contents

 Productivity Blog
  Simple CRM
 Customer Retention
 Relationship Marketing
 Customer Loyalty
 Retail Optimization
What is in the book?
  Visitor Conversion
  Visitor Quality
Guide to E-Metrics
  Customer Profiles
  Customer LifeCycles
  LifeTime Value
  Calculating ROI

  Recent Repeaters
  Retail Promotion
  Pre-CRM ROI Test
  Tracking CRM ROI
  Tutorial: Latency
  Tutorial: Recency
  Scoring Software
  About Jim

Strategic vs. Tactical LifeTime Value
Drilling Down Newsletter #86  1/2008

Drilling Down - Turning Customer
Data into Profits with a Spreadsheet
Customer Valuation, Retention, 
Loyalty, Defection

Get the Drilling Down Book!

Prior Newsletters:

Hi Folks, Jim Novo here.

Many folks think the topic of Lifetime Value is complex and not worth understanding.  I disagree, of course, but a recent series of interactions got me thinking about how some of this attitude towards LTV might be a result of confusing Strategy and Tactics.  We will speak with a fellow Driller about this same idea, and go through how the two fit together with respect to the LifeTime Value concept.

We also have some material on the role Marketing can play in Operations to drive higher Marketing / Company ROI; an article on VRU optimization and a set by step plan for Marketers who want to raise their Strategic profile by playing in this sandbox.

Let's get to that Drillin'...

Best Customer Marketing Articles

Listen Up! (VRU Optimization)

Speaking of the role Marketing should play in Operations, here’s the first article I have ever seen in a Marketing context about optimizing a VRU / IVR. This challenge is really very similar to optimizing a web site.  You have path, and traffic down branches of path.  You have bounce rates and exits. You have the same “choices with correct context” issue that is the heart of designing good navigation and inline link text.  You have usability.

To access the full article review and links to the articles themselves, click here.

Sample Marketing Productivity Blog Post

Marketing through Operations
January 9, 2008

 Customer-centricity is something companies want to embrace more than ever.  Companies can do this through a Chief Customer Officer, but why isn’t a Marketing exec taking the reins on this issue?  In direct marketing companies - where customer-centricity is not just a fad, but has a decades-long history - the Marketing folks know that Operations typically contains a goldmine of customer-centric Marketing opportunities.  Many of these opportunities come from problems with empathy and context - or for the more technical folks out there, “Usability”....Read Post

Questions from Fellow Drillers

Strategic vs. Tactical LifeTime Value

Q:  I'm working with IT on building a new database and at the same time hope to build in new query capabilities to allow me to understand our customer lifetime value and build it accordingly.  I've been tasked with putting some math examples together for IT so they can understand what I'd be looking at and how CLV would be calculated.

A:  I'm sort of lost on this because it sounds to me like you are looking to forecast LTV; reality is that LTV is really only known after the customer has defected.  So, you can go back in time and say "the LTV of customers 
acquired through this campaign is X" a year or two after the fact and use that info going forward in the business.

Q:  In addition to your Drilling Down book, I also have read Sunil Gupta's "Managing Customer as Investments".  One thing that stands out for me is that Gupta stressed the importance of allowing for a discount rate and I don't see any mention of discount rate in the examples your provide in your book.

A:  Yes, a case in point for my comments above.  The reason I don't mention NPV is I favor a relative LTV approach; I just think it's more practical, and in my experience management is not really interested in the NPV approach when discussing Marketing, they want to know about "cash I make now", not cash you are forecasting in the future.

Said another way, to use NPV you have to have something to discount to the present, meaning you are making a forecast of absolute LTV, and this forecast will probably be wrong because there are too many unknowns that will occur, at least in B2C.

Q:  This is the request from my IT rep....which leads me to believe that she may have missed that Absolute vs. relative LTV conversation which we had - or maybe I'm missing something.  Once I have these examples we will be ready to begin our modeling.  Here is the formula I was going to provide IT for LTV:

<bunch of equations and data>

A:  Again, far from me to play IT expert here, but what I would concentrate on when creating a new database is making sure you have all the data you need, making the acquisition of that data as clean as possible, and creating a database that is as flexible as possible.  I don't really understand why you need to provide any "LTV formula", because if the data and structure are right, you can run any formula / query any time you want.  You can both go back in time and forward in time, and use different equations for each, the big difference being back in time is "actual" and forecast is "predicted" and adjusted with NPV.

What I am thinking here is there may be some confusion on both sides about the difference between using customer data / valuation for Strategic versus Tactical purposes, so perhaps some more info on that will help you and IT communicate on this project more effectively.

A Strategic use of customer valuation would probably be fairly stable and something that could have static formulas created for reporting purposes at the CEO level.  You would probably use the NPV approach and forecast absolute (a number) LTV.

For Tactical use of customer valuation in  campaign management and driving Marketing ROI, you are better off not messing around with NPV and looking at LTV from a relative (is it getting better or worse) perspective.

Let's see if I can give you some practical examples of what I mean.

The Gupta formula:

CLV = m(r/1+i-r)

m=customer margin or profit per period (year)
r= retention rate 
i=discount rate

is a marvelous macro equation to describe LTV when you are trying to make strategic customer investment decisions.  

He also provides modifications to this model for different scenarios where retention rate or profit are variable, with a final result that on average, the LTV of a customer is between 1 and 4.5 times the current period profit.  

This is probably shockingly low to some people, but you have to realize that at a 60% defection rate only 2.8% of customers are left after 7 years; put another way, with a 12% discount rate and 90% retention, the multiple on current period profit is just 4 for LTV.

This is a perfectly competent analysis and especially powerful in it's simplicity; I use this kind of thing when talking to CFO's, CEO's and other financially-oriented, strategically-focused individuals.  The plain fact of the matter is because of the discount rate, any arguments about "how long a customer is a customer" become moot, because the incremental value of the out years is very small.  This realization generally kicks C-Level people in the gut and gets them thinking about "doing something".

Strategic customer value, in case it's unclear, would be used for decisions like this:

* What lines or services should we add?
* What marketing mix is optimal?
* Should we implement a loyalty program?

Formulas like Gupta's are also often used to value companies for sale or acquisition.

However, on a practical, tactical level, we know the dangers of making database marketing decisions using the "average customer", knowing there is a lot of behavioral skew, and that there is a huge profit difference between best and not so good customers.

So, when you're developing targeting ideas and just trying to see where you make the most money so you can optimize customer  campaigns, I think his model is really overkill.

When I look at campaigns, I basically look at 
"breakeven" - will the net margin after all costs pay for the customer acquisition or the incremental sales generated from the customer as a result of the campaign?  Once the customer segment hits breakeven, then it's all upside profit from there, right?  As long as you don't do something to destroy value (which is certainly possible), LTV should remain the same or increase and since you are past breakeven, financial liability is zero.

And that's when you start looking at the relative value - one segment beats breakeven by $2 per customer, another beats by $20 per customer, then all else equal, I invest more in the campaign that beats by $20 and let it go - 
it really doesn't matter what the end or "terminal" LTV of the customer is.  I made the right decision by allocating towards higher ROI so I don't need to know absolute LTV, do I?  

Put another way, I'm optimizing a system, trying to make it better and better at generating profits.  The end, absolute numeric value number is really not relevant to me.  If what I'm doing is always adding incremental value, absolute LTV will take care of itself
and can be measured as a separate exercise - if you have resources.

Next, we know these relative valuations can change over time.  To monitor further, you use a simple "sales to date" analysis to see if LTV is still growing and a "% Recent" index to predict if sales will continue to grow.  Based on this longer-term idea, you make further adjustments to your campaign allocation.

So for example, let's say the net $2 customers above are late bloomers and the net $20 customers above are fast starters; you do this sales to date / Recency analysis at 3 months or 6 months and find out the $2 folks are now $30 with great Recency and the $20 folks are still $20 with terrible Recency.  So you adjust your campaign allocations to reflect this change based on the real data, not a forecast that has been discounted with NPV.

I mean, you have the actual data, why mess with a forecast?

The above is an example of why I'm not clear on why a "formula" needs to be solidified or why you would want to strap yourself into that kind of inflexibility - unless, of course, there is something going on system-wise that I am (obviously) not competent to comment on. None of these queries are complex formulas, but you do need all the source data to keep track of the campaign populations and go back and query them.  That's why I stressed the data and flexibility above.

Now, if we're talking about a resource issue, as in you live in a is a "set it and forget it" type of environment where the ability to run custom (though very simple) queries on the data is lacking, and you need a "formula" approach, I would use the LifeCycle Grids from my book to replicate the idea above.  If you don't recall this idea, the Grids are similar to the pix I used in this blog post.

Once the data collection and output for one of these Grids is programmed, all you need is the ability to run a Grid for each campaign - and that should be super-easy to set up, even as a self-service thing.  For example, you go into a menu system and select:

Run LifeCycle Grid --> Choose Campaign Code --> Choose Date Range

and run your own reports.  They could be set up to run overnight or on weekends if system resources are an issue.

Then, you could just visually compare the Grids or have them output to spreadsheets and perform calculations on them.  It should be very obvious which campaigns generate higher relative LTV using this method - over both the short term and the long term.  If dollars are more important than number of 
transactions, use dollars instead of Frequency.

If you really want to get to a "net net net" LTV number (and you should!), have that number carried in the database cumulatively as opposed to created in the query each time and use that number instead of Frequency in the Grids.  This further reduces the need for custom work.  In other words, if the system knows margin and cost of campaign, have the net net net calculated during updates and stored in the customer record.

Basically, all you need to know is the margin on the product and the cost of all campaigns, which would come from a promotional history table - cost of all promotions (and any discounts) the customer received.  This net LTV number could be updated on a weekly or monthly basis.  If you want extra credit, also net out the cost of customer service calls using the customer service history or use an average per customer during updates.  Then when you run the Grids, you will be working with net cash, which is always a good thing.

Look, I think the Gupta book is fabulous and have reviewed it on my blog.  But you always have to ask yourself, why am I doing this analysis, and what kind of action will be taken?  His book is largely about strategic customer analysis (the idea of a "portfolio" of customers), and I'm guessing you are looking for something more tactical because of your focus on campaigns in the data.

His approach is long-term and planning oriented; mine is short-term and results oriented.  Neither conflicts with the other, they operate on different time horizons for different purposes, and ultimately the short-term tactical results for relative LTV should roll up to the long-term strategic forecasts of absolute LTV.  That is, if the forecasts are accurate!

Does the above make sense to you?  Hope it helped to form the approach to the IT side of this better in your mind.


If you are a consultant, agency, or software developer with clients needing action-oriented customer intelligence or High ROI Customer
Marketing program designs, click here

That's it for this month's edition of the Drilling Down newsletter.  If you like the newsletter, please forward it to a friend!  Subscription instructions are top and bottom of this page.

Any comments on the newsletter (it's too long, too short, topic suggestions, etc.) please send them right along to me, along with any other questions on customer Valuation, Retention, Loyalty, and Defection here.

'Til next time, keep Drilling Down!

- Jim Novo

Copyright 2008, The Drilling Down Project by Jim Novo.  All rights reserved.  You are free to use material from this newsletter in whole or in part as long as you include complete credits, including live web site link and e-mail link.  Please tell me where the material will appear. 


    Home Page

Thanks for visiting the original Drilling Down web site!

The advice and discussion continue on the Marketing Productivity Blog
Twitter: @jimnovo

Read the first 9 chapters of the Drilling Down book: download PDF

Purchase Book



Slow connection?  Same content, less graphics, think Jakob Nielsen in Arial - Go to faster loading website

Contact me (Jim Novo) for questions or problems with anything on this web site.  

The Drilling Down Project.  All rights reserved, all media.



Ask Jim a Question


Get the book with Free scoring software at Booklocker.com

Find Out Specifically What is in the Book

Learn Customer Marketing Concepts and Metrics (site article list)


This is the original Drilling Down web site; the advice and discussion continue on the Marketing Productivity Blog and Twitter.

Download the first 9 chapters of the Drilling Down book here: PDF
Purchase Book          Consulting