Drilling Down Newsletter # 12 - September 2001
Drilling Down - Turning Customer
Data into Profits with a Spreadsheet
Customer Valuation, Retention,
Get the Drilling Down Book!
Now also available online through
Amazon and Barnes & Noble
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 -
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.
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.
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
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
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
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
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
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" in a positive way.
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
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
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%
% 1 Page Visits 110%
% Downloading 48%
% Bookmarking 72%
% Subscribing 85%
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
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
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
% 1 Page Visits
39.9% 44.4% 51.5%
3.19% 3.42% 3.63%
% Bookmarking 3.72% 5.36%
% Subscribing 3.19% 3.57%
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
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
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,
Questions from Fellow Drillers
Q: Hi Jim,
I've been reading the content on your website and so far it
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
A: Oh sure, you've got "one question". Good
thing I don't have a limit on the number of questions per customer
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:
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
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
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
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
'Til next time, keep Drilling Down!
- Jim Novo
What would you like to
the book with Free customer scoring software at:
Out Specifically What is in the Book
Customer Marketing Models and Metrics (site