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

Loyalty Program Structure & Tracking
Drilling Down Newsletter #102 7/2009

Drilling Down - Turning Customer
Data into Profits with a Spreadsheet
Have a question on Customer Valuation, Retention, Loyalty, or Defection?  Go ahead and send it to me here.

Get the Drilling Down Book!

Prior Newsletters:

Hi Folks, Jim Novo here.

Lots of discussion out there about customer Loyalty Programs, given the economic situation.  Not a surprise, since (unfortunately) most firms do not analyze customer retention until there is a slowdown, and then often choose a Loyalty Program as the answer.

But, how do you structure and track a Loyalty Program?  A complete answer to that question takes an entire book; here we'll throw a life preserver to a fellow Driller in need today.

Over on the blog, we were talking about the  impact simple but universal concepts from Sociology and Psychology should be having on Marketing decision making.  If you can predict behavior, why not use it to your advantage?

The Drillin' awaits...

Sample Marketing Productivity Blog Posts

Do you think the study of Psychology and Sociology might provide some clues to creating successful businesses, campaigns, products, and services?  The two posts below detail classic behavioral science concepts that can dramatically affect Marketing outcomes.

Norms of Reciprocity
June 28, 2009

Post-Action Dissonance
July 10, 2009

I will respond to any comments you leave.

Questions from Fellow Drillers

Loyalty Program Structure and Tracking

Q:  I'm involved in a loyalty program analytics project.  This client is a local pharmacy. All sales are done directly in store, the web site is just for communication purposes.  The general problem we are trying to solve is the manager doesn't have any detailed ideas about shoppers behavior apart from observation.

The idea is to launch a card-based loyalty program which will track sales activity and give insight into customer behavior.  The program will be points-based calculated on amount spent.  Points can be redeemed as rebates, coupons, gift certificates, or use points to buy items in loyalty program catalog.

The task is to segment customers according to their recent purchase behavior and determine the customer lifecycle.  I've been able to do some basic analysis using the R package and MySQL database, but am unable to detect customer lifecycle.

Can you please give me guidance on this?

A:  What is the Objective of detecting the LifeCycle, to create a more "active" customer retention program?  Loyalty programs can be quite "passive" and often benefit from a more active overlay.  But there can be many reasons to want to understand the LifeCycle...

Q:  My 2nd task is to use the behavioral data with demographics to build a direct marketing strategy and provide management with insight into the customer base, for example: percent new customers, % of Gold customers who passed to Silver in last quarter.

A:  Again, it would be helpful to understand how management would take action on this data.  But I suppose you are in the common position of not knowing the tactical approach, and nobody will lay it out for you (a.k.a. they are clueless)...and you don't know the right questions to ask or how to ask them.

Let's infer that the Objective for the loyalty program is to increase customer retention, the Strategy is to customize communications based on behavior to increase relevance and response.  This is typical for a loyalty program.

So, the metrics required for the Objective would show which customers are active and which are in the process of defecting.  To execute the Strategy, you need segments such as New, Silver, and Gold customers.

I use LifeCycle Grids for this purpose. See this post on Measuring Customer Engagement for examples of what this approach looks like.  You can use % instead of counts in the grid segments if you wish.

Customers tend to move from right to left in the grid over time if they are inactive and defecting.  If active, they stay over on the left. Generally, the customers of most concern are those in the orange upper left Quadrant (Q3), best customers who are becoming former best customers.  These are the folks who should be targeted with double-points offers and so forth to reactivate them.

The most profitable grid cells to target for reactivation can only be found by testing, but I suspect for a pharmacy it's generally in the middle of the Recency scale - 60 days no activity, 90 days no activity, 120 days no activity.  You can't wait too long, but you don't want to do expensive promotions to customers who would buy anyway.  Wait for them to show signs of lapsing, then promote.

As far as "how many Silver turn into Gold", if you line up the Frequency parameters in the grid for that boundary, you can use the grid for that as well.  Or, create a custom grid to reflect just movements between levels by using a selective population input rather than "all customers" in the grid.

The great thing about using the LifeCycle Grid method is it's the same analysis for any population or segment, so management gets used to it and this drives consistency in judging the value of Marketing programs.  So you could run the same grid for Men, or  people 50+, or within 5 miles of the store.

Comparing what the grids look like for different populations produces insight you can use to further sharpen your targeting ability.

Q:  How do I determine the value of a point for the different segments, say Gold versus Silver?  How do I optimize the ROI in each different segment given differing point values, what is the formula I should use?

A:  Hmm, you're really out in the cold on this, aren't you?  Are there any Marketing people involved in this, or are you supposed to use the "magic of analytics" to tell the Marketers what they should be doing?  Don't tell me, let me guess - this is a "MarCom" project and the person who normally buys online banners and sends out press releases will be in charge of loyalty communications...

Here's the problem.  The question you are asking is a Marketing question, not an Analytical one; there is no "formula".

First, a good loyalty program budgets 3 - 5% of sales to points / overhead, meaning a point could be worth in real money between 2 cents and 4 cents, or you are planning to award double / triple points in certain segments.

By a "good program" I mean a proactive effort that changes behavior, as opposed to running as a faith-based initiative, the old "we have a loyalty program so people are more loyal" approach to these programs.

Companies try to do loyalty programs in the 1 - 2% range but it's rare they actually work because there's not enough leverage.  You can't really provide decent rewards that truly motivate behavior in that range, unless you can provide a lot of intangible benefits though other arrangements outside the point formula.

For example, you have a movie star under contract and part of that contract is to attend company functions.  You then arrange for such a function and customers bid points for one of 10 available slots.  That's an intangible benefit; there's not a 1:1 financial correlation between the value to the customer and cost of points. It's "insider access", recognition that is emotionally rather than financially driven. Inviting best customers to sporting events has long been used the same way.

If your budget is limited to a 1 cent a point, you really need to think through how you will create excitement for the program by adding these intangible benefits.  Not that you have access to movie stars, but how about unique discount programs?  Exclusive product Previews?  Paid services provided free?

Second, you really can't change point values across program segments without getting into a lot of trouble on the finance side, it becomes too difficult to audit and you lock yourself into all kinds of execution problems.  For example, what happens when a Gold Member drops to Silver if the point values are different?  Further, what if an item is bought when the member is Gold and returned when the member is Silver? How will the accounting work when the net differential in point value creates a value gap?

If you want to boost offer value for certain segments, you award more points - points are still worth the same amount, but you give double or triple points, not change the value of a point.  Points end up on the balance sheet as a liability so this is deadly serious stuff, you do not want the value of a point to fluctuate.

Ask any CFO.

What level is selected generally depends on the margins in the business, and how the program will be paid for.  Example: you decide to kill all promotional coupons and use the savings from those discounts to fund points, which would tend to improve your margins, because point discounts are targeted, coupons are not.

But there is no way to determine a point value that is optimum for ROI, it's how the points are applied and used in the program, the Marketing Tactics - the Targeting, Messaging, and Timing driving behavioral change - that determine what the program ROI will be.

The best advice I can give you on this is to TEST before you decide on anything - point levels, budgets, segments, etc.  Use the LifeCycle Grid to find out what works in terms of offers and behaviors.  Can you induce loyalty and repeat purchase from lapsed buyers?  What level of discounting or special services is required to change behavior?  Then translate these results into budgets, point values, and programs.

I realize you probably don't control this decision, but I would raise the question if you can: what if we don't really need a Loyalty Program?  What if a less complex and less expensive Retention Program (based on testing results with the LifeCycle Grids) would be more appropriate for our business?

For example, what if we could get best customers to raise annual spend 10%, and at the same time induce just one more shopping trip before a customer defects?  How much would that be worth annually?  How much can we afford to pay for these sales increases?

The LifeCycle Grid gives you a "roadmap" for testing your Targeting, Messaging, and Timing consistently across different segments as your customer population churns over time.  You can use the results to develop either a simple Retention Program or a more complex Loyalty Program, whichever the test results seem to indicate is the best direction to go.

Loyalty programs do not create "Loyalty" - your products and service create Loyalty.   Loyalty programs are an incentive to change behavior, and if analyzed and executed that way, can be very profitable, as can simple ongoing Customer Retention campaigns.


Have a question on Customer Valuation, Retention, Loyalty, or Defection?  Go ahead and send it to me here.

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 2009, 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