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

Optimizing for Customer Value
Drilling Down Newsletter 114: 2/2011

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

Get the Drilling Down Book!

Prior Newsletters:

Topics Overview

Hi again folks, Jim Novo here.

I assume most analysts have a finite amount of time to do their jobs.  This means we all have to make trade-offs to optimize our own time towards highest productivity.  Or at least that's what most employers would hope for:  given an hour of your time spent in analysis, how can you maximize your value?

For most, optimizing your own analytical productivity boils down to this question:

Will you go wide, or will you go deep?

In other words, can you generate more value  by analyzing hundreds of scenarios only at the surface?  Or are you more productive by  concentrating on a dozen or so high value scenarios and going deep, tracing out how they directly impact the financial statements?

Lots of analysts talk about how people at the company don't take their work seriously, will not make changes based on their analysis. Nine times out of ten, these people have decided "going wide" is the answer to creating value for the company, more is better.

I politely suggest they go deep instead.

Let's do some deep Drilling, shall we?

Questions from Fellow Drillers

Optimizing for Customer Value

Q:  Thank you for creating this useful website!

A:  You're welcome!

Q:  When figuring out retention rate for an annual or a 8 months life time cycle period, how do I pick the starting period?  Do I look at their first orders on a date?  Or I pick a time frame such as one month?

A:  It depends on:

1. What kind of "retention" you are talking about, the definition, which is probably impacted by the audience for the data

2.  What you will do with the retention data, what kind of decisions will be made and actions be taken because of the data

You should always ask these questions above  when someone requests "retention data" - or any other kind of analysis, for that matter!

For example, there probably is a huge difference in what you would provide to the Board of Directors for an annual benchmark and what you would provide to Marketing people for executing campaigns.  

In the first case, the data would probably be used to inform Strategic decision making, for example, should we change our product mix or approach to pricing given the market?

In the second case, the data would probably be used in a Tactical way, for example, to target new customers who are predicted to defect because of the campaign they responded to or the product they bought.

If providing data to the Board, "annual retention rate" would probably make the most sense (again, you should ask, what's it for?). If that's what they want, you would pick a starting period, probably aligned with the fiscal year (Jan - Dec?), and find out what percent of people who purchased Jan - Dec 2009 also purchased Jan - Dec 2010. 

That's the annual retention rate.  Useful information, perhaps leading to the Board requesting action of some kind.  But by itself, you really can't "do" anything with this data, there's no source or targeting information, there's no customer value information.

However, if you segment by campaigns, product of initial purchase, price points, offers, or other actionable variables, the retention rate could be just about any formula, e.g. what is the retention rate:

a. Today, of people who made their first purchase in 2005?
b. End of 2009, of people who made their first purchase in 2005?
c. Today, of people who ever bought Product X as their first purchase?
d. Today, of people who bought Product X as their first purchase in 2009?
e. Today, of people who had at least 2 service calls in 2010, who became new customers in 2009, who used a 50% off promotion?

and so on.  Retention rate for anything tactical almost always requires and audience and time frame to be defined.

Q:  You mention in your article, "Total number of customers" as the denominator for calculating the customer retention rate, do you mean the total customers at the end of the period?  Or those total customers came in on the first date of a fixed period?  Or the first fixed period that I'm observing?

A:  Whatever definition is the correct definition depending on the need of the audience.  There is no standard, other than perhaps the very first one, the Strategic "reporting" idea of year over year retention.  This is commonly used in reporting to Wall Street, for example.

While discussing this particular idea of "customers", one might encounter the common problem of not knowing the definition of a customer, at least in terms of retention.

When does the company declare a customer is no longer a customer?  Is a customer  "everyone" who has ever purchased?  If the company has been around 10 years, and you are calculating retention rate "today", as in how many of these total customers purchased in the last year, you may find you have a very low number, one that won't mean much to anybody, and is not actionable.

On the other hand, if your definition of "customer" includes a level of activity, for example, "must purchase at least twice, one of those purchases in the past 3 years", now you are talking about a highly actionable kind of retention definition.  Why? 

Because there is some hope that people who have purchased at least 2x (Frequency), at least once in the past 3 years (Recency) could actually still be customers, as opposed to defected customers.  If I am calculating a "serious" retention rate, something to be used to take Marketing action, or pay out bonuses, or revise policies, I want to measure against people who actually have some Potential Value, some Value to the company in the future.  That's how I define a customer.  To me, there isn't any point in calling someone a customer who is unlikely to contribute to profits in the future.

If you define as a customer "anyone who purchased over the past 10 years", you just have a dead metric that really does not reflect the reality of what taking action might produce.  In other words, you are including people who are extremely unlikely to still be customers, so what's the point of the "customer retention metric" you created?

Does the above help answer your question? 

Q:  I wasn't expecting you to reply me so fast and in such detail!!!  Thank you so much!  I'm calculating this retention rate for marketing and your answer is very helpful for me!!!

A:  Great!  So maybe ask them specifically how they want to look at it, and if they seem puzzled, suggest to them various options.

I can tell you from experience with businesses like yours is the buying behavior tends to peak early and you have to act quickly if you want to extend the lifecycle.  Perhaps not quite as time-critical given your "triple bottom line", but probably not too different.

This argues for a tighter leash on the definition of a customer, perhaps purchased at least twice, one of those past 6 months.  You could also do 2x purchase, at least once in past 3 years, and compare, it will give them a 
feel for customer defection trend / rate.

The next step would be the Lifecycle map, which uses Recency and Frequency in a more actionable way, like this example.

Marketing people should be able to use this map to target specific groups of customers, e.g. purchased 4 - 9 times, but not in the past 90 days.  These are good customers who are in the process of defecting, and require special 
attention to keep them on board.

After all, the point of measuring retention is not retention rate itself, it's about increasing the productivity and profitability of the business system.  Just as you can optimize for conversion, you can optimize for retention, and sometimes you discover they conflict.

For example, one company I worked with featured certain products on their home page because those products had a high conversion rate on visits to the home page; they had "optimized" the home page for this scenario.

However, a very quick and simple calculation showed these products generated customers  with terrible repeat purchase rates relative to just about every other product with volume.   A quick survey of these customers found out why the repeat purchase rates were so low - almost all customers disliked the product and thought the company deceived them.  Turns out the company "over-sold" the product - and that's why the high conversion rates.

In another case, PPC campaigns had been optimized for conversion without regard to customer retention.  Under a budget crunch, the lowest converting campaigns were killed, but overall sales volume over the next 3 months dropped much more than the sales generated by these campaigns.

Reason?  These low converting campaigns generated the company's very best customers in terms of 30-day, 90-day, 180-day value, while most of the highest converting campaigns generated low value, single purchase customers on the same time frames.

This kind of analysis is simply not that difficult to set up and execute, relative to the extreme amounts of value that can be created:

1.  Pass campaign codes / info with the order to the backend order processing.  If you are not doing this yet, start right now!

2.  Select a campaign, choose a time frame.  If you want to match up to financial statements (a good idea if you will be talking to C-Level folks), say January 2010.

3.  Grab all new customers who came in on Campaign X during Month Y - what is their average value 1, 3, 6, 12 months later?  This is a Lifecycle by Campaign analysis, similar to the LifeCycle map example mentioned above.

The new customer experience (channel, offer, product) is one of the most powerful predictors of future customer value, and the value of these new customers relative to each other tends to remain stable regardless of how many other generic campaigns (weekly email) you throw at the customer over time.

Across all campaigns, about 60 - 80% of these new customers will have the same value at 12 months they had at 1 month.  The question to answer, as with any optimization, is this: knowing the customer value created by these campaigns varies widely, are we allocating the acquisition spend optimally?  For example, are we spending 70% of the budget to generate  20% of the annual customer value?  Are we willing to pay more for clicks that generate new customers with 10X higher annual value?

Retention rate isn't just some mystical number, retention rate quickly turns into profit dollars and can have incredible financial impact!


Need help with creating action-oriented customer analysis 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 2011, 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