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

Drilling Down Newsletter # 12 -  September 2001 - Customer Scoring

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

Get the Drilling Down Book!
Now also available online through
Amazon and Barnes & Noble

Prior Newsletters:

Folks, I'm sure you are aware of the terrorist attack here in the U.S. on 9/11/01.  I delayed publication of this newsletter for some time, but upon receiving queries from many of you, have decided to "carry on."  As you said, disruption is the intent of the terrorist, and to alter planned activities only serves to encourage further mayhem. 

Thank you for the advice and support.
Drilling Down Newsletter # 12 - 
September 2001

In this issue:
#  Best of the Best Customer 
    Retention Articles
# Tracking the Customer LifeCycle:
   Advanced Latency Studies
# Practice What You Preach:  Online Ad
   Effectiveness?  Tell Me About It...(#5)
# Questions from Fellow Drillers
Hi again folks, Jim Novo here.  This month we've got great customer retention article links and a further look at customer LifeCycle measurement and tracking.  Plus, yours truly came up with a landing page copy test to shed further light on the Advertising Effectiveness study, and a fellow Driller has a great question on the "capacity" of the Drilling Down customer profiling software.  To aggregate the data or not?  That is his question.

Let's do some Drillin'!

Best of the Best Customer Retention Articles
These articles are on the DM News web site and will move into their paid subscription archive 30 days after the date of publication listed below, so check them out soon!  The URLs are too long for the newsletter, so the following links take you to a page you can link directly to the article from. 

Note to web site visitors: These links may have expired by the time you read this.  You can get these "must read" links e-mailed to you every 2 weeks before they expire by subscribing to the newsletter.

Loyalty Site Will End Cash Rewards Program
August 22, 2001   DM News
Repeat after me: Simple cash rebates do not build  loyalty or increase profitability.  Period.  If you retailers out there using these programs could measure subsidy costs, you would be shocked at how much money you are losing.  In every environment where these costs can be measured, simple rebate programs lose money.  How?  You give up more in margins to best customers than you ever make back on incremental purchases from other customers.  Trust me.  Oh, I know, I know, it's different for your business. 

Borders' E-Mail Promotions Hook Readers
August 24, 2001  DM News
Double Trouble, Folks.  Using e-mail to drive offline sales is working for Borders, and they provide some (limited) stats on the success of the program.

Mercedes-Benz Mailers
Outpace Online Campaign

August 27, 2001   DM News
An absolute ton of response stats on e-mail, rich media, Flash, Superstitials - and direct mail - for a campaign using unified creative in all mediums.  Looking for comps?  You got 'em. 

Tracking the Customer LifeCycle:
Advanced Latency Studies

Last month, I had a bit of a tirade on the bull so called "CRM Experts" throw around like "firing low value customers" and other assorted crap that just serves to confuse the heck out of people.  In the process, I tried to document in a simple way the model at the heart of all data-driven marketing, CRM included.  If you're feeling a bit confused about where this analytical CRM stuff is headed, or just want to join me in beating up on a few revered concepts, give the article a read.  

I hope we're now on the same page - the above tirade was prompted by your flood of questions in response to the first article in this series on Behavioral Marketing.

So let's continue, shall we? 

Latency is one of the simplest of the "trip wire" metrics you can use.  If you know the average amount of time between two customer activity events, you can set up systems to recognize when a customer trips the wire (behaves outside the norm) and activate a response. 

But what if you were to look at an entire series of Latencies?  For example, the average number of days between the first and second purchases, the average number of days between the second and third purchases, third and fourth, fourth and fifth, etc.  You don't have to use purchases, you could use contacts with customer service, visits to a web site, any behavior important to your business.  What would that look like, and more importantly, what can it do for you?

It would look like a snapshot of the customer LifeCycle, that's what it would look like.  And what it can do for you is start you on the path to predicting customer behavior and increasing the value of your customer base. 

Let's say you look at average behavior across all  customers, and end up with a "Latency Sequence" that looks something like this: 

1st - 2nd event:  90 days
2nd - 3rd event:  60 days
3rd - 4th event:   30 days
4th - 5th event:    60 days
5th - 6th event:    90 days
6th - 7th event:   120 days
7th - 8th event:   150 days:

What does this pattern say to you?  Think about it. 

I'll tell you what it says to me.  First, as you probably realized, you are now starting to see something that looks like a "cycle," as in LifeCycle of the customer.  It's a series of events you can graph with a line and make charts of.  If you can measure it, you can try to affect it in a positive way, and determine the results of your efforts.  Second, you now have a series of seven "trip wires" to can use as described in the previous article to more finely sift and screen behavior looking for deviations from the norm.  And third, somewhere around the 3rd or 4th event, something significant happens to change customer behavior in a very noticeable way.  The customer accelerates into the 4th event, then begins to decelerate in terms of behavior.  Depending on your business, this may be a positive or negative event. 

How to use this information?

Regarding the Lifecycle and the trip wires, you could have a series of seven actions ready to take at any point in this LifeCycle where the customer deviates from average behavior.  As long as the customer stays on track, save the money and take no action.  But as soon as the customer misses or "rolls over" past one of these LifeCycle milestones, you know to pull the trigger on your action.  If you follow this model, you will end up maximizing every cent of your budget and driving higher profits, because you don't spend unless you have to, and when you spend, it creates maximum impact.  This is the recipe for high ROI customer management and marketing, folks.  Act only when you have to and always at the point of maximum impact. 

Regarding the behavior change, if I was a retailer, this looks negative, since the "ramp" in buying behavior reversed and went in the other direction.  If I was running a pure service center, this may be a very desirable pattern, perhaps meaning the customer has "learned" the product and no longer needs as much service.  It could be negative though, since opportunities to upsell or cross-sell the customer are decreasing over time. Depends on your business.  The important thing to recognize is there was a change in behavior, and to try and determine how you might affect this change in a positive way.  Reversals in the direction of a behavior like this are almost always significant turning points in the relationship with the customer.  

Human behavior dynamics often take on seemingly "physical" properties.  Inertia is one such property - an object in motion tends to remain in motion unless acted on by an outside force.  This reversal in the direction of the customer "momentum" around the 4th event indicates there is something about your business - a process (or lack of a process), a product (or lack of a product), something - which causes the average customer to "slow down" and reverse their contact momentum.  Your mission (should you decide to accept it) is to find out what it is and try to influence this "something" positively. 

If I was a retailer, this is what I'd do.  Given the information provided here, I would send a promotion to the customer immediately after the 4th purchase - and no sooner.  I don't want to spend money on a promotion or by reducing my margin if I don't have to, and as long as the customer is accelerating, there is no reason to spend any money.  But I would really like the ramp to continue past the 3rd purchase, and any way I can bring that 4th purchase in closer to the 3rd is going to affect my bottom line, not to mention perhaps lengthening the ramp into the 5th or 6th purchase and beyond.

If I was a service center, the fact it takes 3 calls to educate the customer might not be acceptable, and I would look for ways to decrease the length of time it takes.  If I upsell and cross-sell, I would look to weight more of this activity early in the process knowing I am not going to get as many chances as time goes on and the customer becomes more likely to defect.  Success at either of these actions can create incremental profits with very little expense - you're not necessarily changing what you do, just when you do it, to match more closely with the customer LifeCycle.

Of course, you can begin to subdivide the customer base, just as we did in the first article.  The Latency Sequence may look quite different for hardware buyers relative to software buyers, and it certainly will be different by the type of campaign you used to attract the customer in the first place.  Once you are able to compare and contrast different customer LifeCycles by product, campaign, customer source, or any other data point meaningful to your business, you begin to paint a more complete picture of what parameters positively or negatively affect customer behavior.  We will talk more about this idea next month...and get into some specific directions and ideas for implementation.

I can teach you and your staff the basics of high ROI customer marketing using your business model and customer data, and without using a lot of fancy software.  Not ready for the expense and resource drain of CRM?  Get CRM benefits using existing resources by scheduling a workshop.

Practice What You Preach: Online Advertising
Effectiveness?  Tell Me About It...  (Part 5)
If you have just joined us, this entire series can be read on a single HTML page here.

Last month we took a look at the quality of visitors generated by my paid search listing ads on Google and GoTo.  I created a ratio between the Google and GoTo for key conversion metrics on my most popular keyword phrases (RM = Relationship Marketing, CR = Customer Retention, CL = Customer Loyalty):

Google / GoTo Ratio
Avg. Visit Length      65%       125%       308%
% 1 Page Visits     110%       115%         91%
% Downloading       48%       112%       570%
% Bookmarking      72%          44%      160%
% Subscribing         85%         84%       140%

One thing is perfectly clear from this chart - Google dramatically under-performs GoTo for the paid search term Relationship Marketing, and outperforms GoTo on the paid search term Customer Loyalty, across the board, in every category (note a lower number on % 1 Page Visits is better).

Things are less clear-cut for the term Customer Retention, although I'd have to give it to GoTo because Bookmarking and Subscribing to the newsletter are highly correlated to future purchase of a book.

This analysis brings up an interesting question, though.  What is the effect of the content searchers land on when clicking on a search item?  Could the variances above be at least partially explained by a good or poor match of the content with the expectations of the searcher?  How large could this effect be, a double or a triple in response? 

That's what I tried to find out, by sending all these searchers to the same page - my home page, which covered all three subjects in a generic sense, and had prominent links to the same pages searchers were sent to previously - Custom Landing pages written to match the search term used.  Note:  The  current Home Page is different than the one used  when this test was run.  The Home Page used in the  test was similar to this page with links to the Custom landing pages displayed prominently at the top of the page.

The chart below shows the conversion metrics of visitors for my three primary search terms - Relationship Marketing, Customer Retention, and Customer Loyalty - when they are all sent to the Home Page (far left column) and when they are sent to a Custom Page designed to reflect the search term they were using (far right column).  Also provided for comparison are the same metrics generated by All Search visitors and All Google search visitors: 

Search-Driven Visitor Conversion Metrics


Avg. Visit Length      
              3.35        3.15         2.61        2.60
% 1 Page Visits
              39.9%    44.4%     51.5%      52.5%
% Downloading
              3.19%    3.42%     3.63%     6.01%
% Bookmarking
               3.72%   5.36%     7.44%     9.84%
% Subscribing
               3.19%   3.57%    3.82%      3.83%

If you were to read down the Home Page column, this chart says:  "When visitors searched the terms Relationship Marketing, Customer Retention, and Customer Loyalty on Google and GoTo and clicked through to the Home Page, they stayed an average of 3.35 minutes, 39.9% viewed just this page then left, 3.19% downloaded a book sample, 3.72% bookmarked the site, and 3.19% subscribed to the  Drilling Down newsletter" (which you are reading right now).

But check out what happens when they land on a page designed for the topic they were searching.  Shorter visit (bad), higher abandonment (bad), higher download, bookmark, and subscribe (very good, since these stats directly correlate to future purchase of my book).

What does this mean?  Can we reconcile the "bad" and the "good" in terms of the behavioral marketing approach?  Well, sure.  Two possibilities:

1.  When I dump highly targeted visitors on the generic home page, they stay longer and view more pages looking for what they came to find, but a higher percentage then leave without engaging in the desired behavior.  When I take the exact same traffic and dump it to Custom Landing Pages, they stay for a shorter length of time and view fewer pages, but they download, bookmark, and subscribe at a much higher rate, because they found exactly what they were looking for. 

2.  It's also likely the targeting of the Custom Landing page itself is causing shorter visits / higher abandonment.  In other words, a visitor types in "Customer Loyalty," a pretty generic concept, and lands on a page with a specific view on the search term.  It's more likely this specific content differs from what was desired by the visitor relative to the Home Page, which by nature is meant to have a generic appeal.  The generic approach gets the longer visit and deeper site penetration relative to the specific approach, but also ends up driving away the specific visitors I am looking for (those who might want to buy a book on measuring and tracking loyalty metrics) at a higher rate. 

This kind of effect is seen quite frequently in direct marketing efforts; the more targeted you get on the front end, the lower the "initial response" but the higher the "final conversion" to the desired outcome you are looking for.  The results may seem intuitive to you (give them what they want and they respond at a higher rate) but you don't know for sure until you measure the effect.  To maximize the ultimate conversion of the whole site, you have to find the "perfect balance" between the initial response and final conversion to the desired behavior.

Did you notice how the stats get better and better as you read from the left to the right of the chart?  Scroll up and look at it again.  Weird, huh?  Almost mystical in consistency.  I get better performance from natural search traffic than I get from driving highly targeted (and paid for) traffic to the generic Home Page.  And "natural" Google traffic is even better than "All Search" engine traffic.  What does this mean? 

That's right, you guessed it.  I'm going to have to go down another layer and find out what the heck is going on.  Next month we'll have the last Drill Down on this topic, I promise.

If you'd like to see more on web log analysis in future newsletters, let me know.

Questions from Fellow Drillers
Q: Hi Jim,

I've been reading the content on your website and so far it has been very useful. 

A:  Well, that's good!  I was starting to wonder if maybe I was wrong about the whole thing...   Just kidding, thanks for the compliment. 

Q:  I do have one question.  You mention that if a company has less than 65,000 transactions Excel can be used to measure customer Lifecycle metrics.  What was the time frame for the transactions?  Was that 65,000 per year, month, etc.?  Can you still use Excel if you have more transactions by "aggregating" the data?  How would you go about doing this aggregation?

A:  Oh sure, you've got "one question."  Good thing I don't have a limit on the number of questions per customer around here...

It's 65,000 total transactions, the number of "rows" in an Excel spreadsheet (it's actually a tad more).  Access can become unfriendly over 100,000 rows or total records.  It seems logical if you have this many transactions, you would probably be using SQL Server, Oracle, or something else more robust than Excel to hold your customer data.

That said, you can always aggregate data to keep it under 65,000 rows and still use Excel - just be careful what and how you aggregate. Generally, the lower the economic value the transaction has, the more OK it is to aggregate.  So if you had a choice, you would aggregate page views, but not purchases. 

For example, you could aggregate an entire day's page views into one record, instead of keeping them as unique records.  Or a whole week's worth.  Instead of having individual page views, you would have an "activity record" that would look like this: 

Customer ID  
Date Last Activity
Total Page Views

The "date" could be an actual date or any "cut-off" - the last day of the week, or last day of the month.  You lose some useful detail (maybe) but you still retain the most important parameters - date of last activity (Recency) and total Frequency.  As long as you retain these metrics, you can run any of the models in the Drilling Down method.  You can run these "aggregated" transactions through the Drilling Down software and you'll end up with customer scores than will work just fine for LifeCycle profiling.

With purchases and other direct revenue items, I always try to keep as much data as possible, because there are other things you will want to do down the line with the detail once you see how powerful LifeCycle profiling can be.  That said, people are running into "resource limitations" these days, so here is what I would do: prove it out and then beg for money.

In the beginning, you could aggregate purchases, let's say monthly.  Run these aggregate transactions through the software and create your LifeCycle models, which will be of "aggregate buying behavior."  With an eye towards proving out ROI, track your marketing and show how you can double or triple response while lowering costs using LifeCycle profiling.  Then say to the appropriate penny pincher, "This is what we can do with aggregate purchase behavior.  If we could keep more details on each transaction, we could run these LifeCycle models based on category of product purchased, average price paid, time of day or day of week - any piece of data we can afford to store without aggregation.  If we do that, we can begin to really see which products, prices, times of day or days of week create the most valuable, long LifeCycle customers and target correctly."

The next sound you hear should be the cash drawer opening...ka-ching!

Aggregation can also be thought of as relative to the frequency of profiling.  If you want to profile customers intra-day (why?), then you need all the individual page views.  But if you are only going to profile customers once a week, you could use daily totals, or once a month, use weekly totals.  Whatever the next smaller logical unit is relative to the profiling cycle is a good place to aggregate. 

Since what is most important is not a customer's LifeCycle score, but a change in LifeCycle score, you have to pick a time frame that makes sense for the natural cycle of your business to profile customers. 

A lot of times biz owners have a pretty good feel for this.  If it "feels like" (or you know for sure) your best customers buy 2x a month, then run profiles 2x a month.  If they buy 2x a week, running profiles every 2 months may not help you much.  You want to try to synch up your profiling with what you  perceive to be the behavior, or better yet, measure the behavior first.  If you synch to your best customers, you'll be on top of the rest of your customers, because their behavior is not likely to change as rapidly as it might among best customers.  And after all, you want to be paying the most attention (from a tracking standpoint) to your best customers.  Whatever changes you may implement based on LifeCycle Tracking for them should "trickle down" to the rest of your customers in a positive way.

Hope that answers your question; feel free to continue asking until it makes sense to you.  And thanks again for the kind words on the site - be sure and tell your friends! 

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 at the top and bottom.  If you would like me to teach you these concepts using your own business model and customer data, check out my workshops and project-oriented services.  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 to me.

'Til next time, keep Drilling Down!

                               - Jim Novo

Copyright 2001, 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 attribution, including live web site link and/or 
e-mail link.  Please tell me where and when 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