Chapter 8

Getting the Most from Your Transaction Data

In This Chapter

Summarizing transaction data at the customer level

Creating behavior-based customer segments

Understanding seasonality and customer purchase cycles

Using transaction data to identify opportunities

You can learn a great deal about your customers by paying attention to what they’re doing. Your company’s transaction systems contain a wealth of data about how customers are behaving. In this chapter, I use the word transaction in a much broader sense than just sales transactions. In essence, I consider any customer interaction with your business to be a transaction.

Certainly purchases are transactions. But so are returns. A phone call to your call center, registering and logging on to your website, and searching for a particular product on your website all count as transactions. When trying to make use of transaction data, you typically run into three obstacles:

Not all your transaction data is tied to individual customers. I address a number of approaches to solving this problem in Chapter 18. Basically, these tricks of the trade amount to giving your customers a reason to identify themselves at various points in their purchase cycle.

Sheer volume. Individual transactions tend to be fairly simple from a data perspective. A purchase transaction contains data about the product, the price, and the date, time, and place where the transaction occurred. But there are a lot of them.

Transactional systems are generally busy running day-to-day business operation. That means the data is changing all the time. It also means your operations folks don’t want you poking around their systems and slowing down the works. They typically want to restrict access to off-peak hours, which usually means nights and weekends.

This chapter covers some ways to wrangle your transaction data into a useful form. It then goes on to talk about some common ways of using transaction data in your marketing programs.

They Bought How Many? Simplifying Transaction Data

A number of years ago I was working for a regional bank. This was before the online revolution, when automated telephone banking was the shiny new technology. The grand idea was to try to move as many transactions as possible out of the branches into lower-cost channels like ATMs and telephone banking. When we actually looked at the data, though, we found branch transactions weren’t going down, despite the fact that we were seeing a great many transactions coming through the telephone banking system.

On closer inspection, we found that the vast majority of these transactions were balance inquiries and inquiries about recent account activity. Not too surprising, given the limited functionality of the telephone banking system.

What was a little surprising was that the vast majority of these transactions were done by a very small number of customers. We expected people to check their balances once in a while. And we found several thousand customers doing just that. They were calling into the banking center once or twice a month. But we also found a much smaller group of customers who were using the system hundreds of times a month. Some of this behavior was remarkably strange. We found one customer who had checked his balance every three or four minutes from 11 o’clock one evening until 2 o’clock in the morning.

The preceding example is typical of transaction data in general. When you make yourself more accessible to customers, they will take advantage of that accessibility — sometimes to extremes. When you begin looking at your transaction data, you’ll often find a small group of customers who account for a disproportionate volume of transactions. These outliers should be ignored when you calculate means and other statistics. If you include them in your calculations they’ll distort your understanding of what’s really going on with the vast majority of customers.

Chapter 6 talks about how to approach such wide variation in data. The next section discusses some ways to summarize high-volume transaction data. There are times when you will need to go back to the detailed transaction data to answer specific questions. But for the purpose of profiling your customers, summarized data is more manageable and can be quite informative.

---

---

Just count ’em: Summarizing transactions at the customer level

Raw transaction data can be difficult to deal with. It’s time consuming and complex to go back to lists of individual transactions every time you want to answer a question. What you really need is summarized transaction data.

It’s certainly important for your company to understand overall sales numbers. Knowing how many widgets were sold at what price and when is central to running your business. These metrics are essentially summaries of transaction data. Your job involves understanding customer behavior. This requires a lower-level summary of your transaction data than is generally provided in accounting and financial reporting. You want to get a sense of what individual customers are doing.

One simple way of knowing what customers are doing is to keep a running tabulation on your customer record of how many transactions they performed in a given time period. If you’re summarizing purchase transactions, you may also want to keep track of how much they spent.

Your monthly bank statement is a classic example of such a summary. You can certainly flip through your statement and view every individual transaction. But the front page tells you your balance, the number and total value of your deposits, the number and total value of your withdrawals, and the interest you earned.

Or consider the grocery store. I have one of those loyalty cards that earns me discounts on gas based on how much I spend each week on groceries. Every time the cashier hands me my receipt, she tells me how much of a discount I’ve earned. This is essentially a summary of my recent grocery purchases.

Another approach to summarization is to restrict your focus to recent transactions. Consumer auto purchases don’t typically happen 200 times a year. A customer’s most recent transactions are often the most informative. What web pages did the customer view this week, for example? In these cases, it’s perfectly reasonable to store transaction details for a few recent transactions on your customer record.

Bucketing transactions into categories

Many companies sell a large number of different products. In the case of consumer goods retailers, they sell many different brands of each product. Grocery stores, department stores, and retail stores in general have thousands or even tens of thousands of distinct products in inventory at any given time. And these inventories change all the time.

Though not as extreme, this product diversity exists in other industries as well. Banks and other financial institutions have a fairly significant range of products. Six-month, one-year, and five-year CDs are all different products with different yields. Automobiles differ not only by make and model, but by color and optional accessories as well.

Making sense of product diversity requires you to simplify your view of the product world. Think product types. Most companies have different levels of summarization for their products, called product hierarchies. These hierarchies resemble the classification system in biology — order, family, genus, species, and so on. Using such classifications you can generate a picture of your customer’s product needs that’s manageable and actionable.

Banking products fall into a fairly natural hierarchy. At the highest level, banks offer deposit products and loan products. At the next level, a distinction is made between demand deposits like checking accounts and time deposits like CDs, which have a maturity date. On the loan side, installment loans that have a defined payment schedule are distinguished from revolving lines of credit such as credit cards.

At still another level down, products are grouped by product type. Checking accounts, savings accounts, CDs, IRAs, mutual funds, and so on. At this level, there are somewhere around 15 or 20 product types. That’s still a manageable number of buckets. There’s enough detail to get a pretty good picture of the customer relationship.

Grocery stores have a similar set of product types. When you wander through, you pass the produce department, the meat counter, the bakery, and so on. These departments form the basis for a useful product grouping. Again, there are a relatively small number of product types based on these departments.

When summarizing your products into groups at the customer level, you need to balance simplicity and completeness. You want to have a manageable number of buckets, but you also want to capture the full breadth of your business. There is no hard and fast rule for the right number of groups other than you’ll know it when you see it. But typically if you get much over 20, you’ll be sacrificing the simplicity you were after.

Deriving customer attributes from transaction data

In Chapter 7, I talk about the need to distinguish price-sensitive customers from those that are more focused on premium benefits. I focus on the connection between price sensitivity and income. I point out that people that buy top-of-the-line products need to be able to afford them. But I also point out that income is not the whole story. Certainly lower-income households tend to be quite sensitive to price. But there are high-income households that display a high degree of price sensitivity as well.

Determining which customers are price sensitive requires you to look at detailed transaction data. Does the customer only buy your products at your spring clearance sale? Do most of their transactions involve coupons or other discounted offers? Answering these questions takes effort and data crunching.

You’ll use price sensitivity a lot in developing target audiences, offers, and messages. You don’t want to have to send your geek back to the detailed transaction data every time you want to take this trait into account. So don’t. Perform the analysis once and then store price sensitivity as a customer trait. In other words, create a variable that indicates whether a customer is price sensitive and store it on your customer record. This makes the information readily available, without bothering your technical team.

It’s often possible to summarize transaction data into customer traits that are meaningful. If Customers that do X are a core focus, there is no need to re-invent the wheel every time you want to focus on that group. Treat doing X as a customer trait in itself and store that information on your customer record. This way of summarizing transaction data gets you what you really need while insulating you from large transaction files.

Transaction Data from the Web

When a customer interacts with you online, it gives you access to a wide variety of information that can be useful in your marketing efforts. You obviously have access to customer purchases. But beyond that, virtually every click, search, and page view can be recorded and accessed. Furthermore, because you control your web content, you can react to many customer interactions instantaneously. In Chapter 13, I talk in more detail about how to use this data online. In this section, I want to touch on a couple of types of data that are available to you.

Data related to e-mail campaigns

When you send a marketing e-mail, you almost always include a link to your website in the e-mail. Your call to action often involves directing the customer to your website to register, shop, purchase, or go for a discounted offer. Your customer’s response to these campaigns can be measured.

Your e-mail service provider will typically provide you with daily or even more frequent reports that include two key metrics for these campaigns:

How many customers actually looked at your e-mail. That is, how many people actually opened the e-mail and presumably read it.

How many customers clicked on the link in your e-mail and proceeded to your site. This is the more important metric. The click-through rate often forms the basis for judging the success of an e-mail campaign in getting the attention of its audience.

Your e-mail service provider (ESP) can provide you with a good deal more as well. To really understand the success of your campaign, however, you need to know who purchased. Click-through data can actually be tracked to the individual customer. More precisely, it can be tracked to the individual e-mail address. Your ESP can provide you with this e-mail disposition data so that you can load it into your marketing database for analysis purposes.

The customer may click through to your website but not purchase immediately. They may come back to your website later by clicking on the browsing history button in their browser. If they make a purchase during that session, you may want to attribute that purchase to your e-mail campaign. This can be done by tying the e-mail address associated with the purchase transaction back to the e-mail address on the mail file.

Page-use data

Another useful type of data pertains to how your customers use your website. Most websites have a large number of different pages full of various kinds of content. Your web-hosting system can keep track of every one of the pages a user views and even the order in which they view them.

Later in this chapter (and in Chapter 10), I talk about market-basket analysis and groups of products that are frequently bought together — suits and ties, for example. Much of this kind of analysis is based on purchase data. But page-view data can also tell you a lot about the groups of products that customers are interested in.

Websites frequently make suggestions while customers are shopping on the site. “Customers who bought that often bought this too,” for example. The page-view data is critical to understanding how well these recommendations are performing. If no one ever clicks on the recommendation, then you know you’d better find a different recommendation.

You also have access to data about how your customer got to your site. You can know, for example, whether they came to your site from a competitor’s site or from a search engine or sponsored link. You also have access to information about where they went afterwards. Many websites allow their users to perform keyword searches of their websites, and you can analyze that data — in context. In other words, you know what page the user was looking at when they searched for a particular keyword.

Much of the page-use data from your site is anonymous. You may not recognize many of the customers viewing your page. But this data is still incredibly valuable in understanding how customers are using your website and how they shop for, and ultimately buy, your products.

Grouping Customers Using Transaction Data

Chapter 7 talks at length about grouping customers based on a variety of types of data. In that chapter, I explore the basic idea of finding pockets of customers with similar attitudes and needs. The similarities within these pockets, or segments, allow you to identify opportunities that are specifically relevant to customers in a particular segment. They also let you construct offers and messages that will resonate with those customers.

Your customer-level transaction summaries are useful in identifying a different type of customer segment. Transaction data can be used to develop behavior-based customer segments. In what follows, I give a couple simple examples of behavior-based segmentation schemes.

Finding and keeping your loyal customers

In the movie Up in the Air, George Clooney plays a business traveler. One of the ongoing themes in the movie has to do with how he deals with his frequent air travel. Among other things, he had set a goal of reaching 10 million frequent flyer miles. This is an extreme case. But there are customers who are extremely loyal to particular brands and companies. No matter what business you’re in, you will find that you have a core segment of loyal customers, often call high-affinity customers.

Finding these high-affinity customers is a matter of looking at purchase patterns in your transaction data. Affinity is not just a measure of how much business they throw your way. You can combine information from your transaction data with survey research about your customers’ overall spending habits. This gives you a sense of how much of their business you’re getting and how much is going to your competitors. This share of wallet, as it’s called, is a simple measure of affinity.

High-affinity customers tend to contribute disproportionately to your bottom line. For that reason, it’s extremely important to keep them happy. By recognizing who they are and acknowledging their loyalty, you can protect and strengthen the relationship.

A customer my show high affinity for many reasons. Maybe they simply like your brand. There really are Coke people and Pepsi people. But the affinity may be born of necessity or convenience as well. Location and availability may play a large role in driving the customer to your business. You can’t take your best customers for granted. If they’re doing business with you out of convenience, then inconveniencing them may push them elsewhere.

Grocery stores have reward cards to encourage customer loyalty. Mine gives me discounts on gas based on how much I spend. I do the vast majority of my shopping at the store that’s closest to my house. But, all things being equal, I’d rather shop at a different chain. The rewards card does just enough to keep me from driving the extra 8 minutes or so to my preferred store.

Airline frequent flyer programs are designed to do the same thing. Credit cards often offer cash or other rewards based on purchase volume. Almost every large retail business, from department stores to coffee shops, offer some sort of reward program.

You don’t need to offer discounts and financial rewards to reinforce your relationship with your loyal customers. Simply acknowledging that loyalty in your communications is a start. Several companies that I do business with actually send me birthday or Christmas cards every year. I’ve always thought that this was pretty clever, given the connection to buying presents.

Another way to reward loyal customers is to offer them special access to your products or stores. You can allow them to pre-order the latest model. I’ve seen department stores open their doors early to loyal customers “by invitation only.” Some theme parks offer early entry to guests who stay at their hotels so the guests can avoid long lines.

An example from the credit-card industry

The credit-card industry is one that’s awash in transaction data. Marketing and customer profiling in this industry depend heavily on summarized transaction data. One simple but incredibly useful way that credit card companies segment their customers depends fundamentally on only a couple pieces of information.

Everybody knows credit card companies charge interest if you don’t pay them off every month. They also charge a variety of fees for late payments and various other offenses. But credit cards also generate revenue in another way. When you use your credit card to buy something, the merchant you bought it from pays a small percentage of the purchase to the card company.

Leaving fees aside, this two-source revenue stream leads to an important distinction between credit-card customers. Customers who carry balances and pay interest every month are profitable. Customers who pay off their balances ever month can also be quite profitable if they make a lot of purchases. But they are profitable in a very different way.

The “industry terms” for these segments are revolvers and transactors. Customers who revolve, or carry a balance forward, pay interest every month. Transactors, with a lot of purchases, generate income from merchants. If you throw in a third group comprised of inactive cards, you have a simple behavior-based segmentation.

This segmentation is really based on only a couple of pieces of information. How much interest does the customer pay each month, and how much do they spend on purchases each month? If you put yourself in the card companies’ shoes, you’ll arrive at very different marketing strategies for these segments.

In the case of the transactors, you probably won’t have a whole lot of success with balance-transfer offers. If they’re paying off your credit card every month, they’re probably paying off their other cards as well. The more effective strategy is to encourage them to keep spending. This is exactly what “cash back” offers and rewards programs are designed to do.

On the other hand, revolvers may be very good candidates for balance-transfer offers. Again, the way they do business with you is a pretty good indication of the way they do business with your competitors. You can entice some of them away by offering low rates on transferred balances.

In the case of the inactive credit cards, you would probably take a hybrid approach. For these customers, the marketing challenge is to get them to use the card. Since you don’t know how they would be likely to use the card, you might use both purchase and balance-transfer incentives.

Timing Is Everything: Understanding When Customers Purchase

It’s important for you to understand the timing of your customers transactions. Certain products are seasonal, some products are bought impulsively, and others require time and careful consideration. Some transactions may be signals to you that you need to take action. In all these cases, knowing when the customer did something will help you to communicate appropriately with your customer.

The Christmas rush: Seasonal purchase patterns

Many retail businesses live and die according to their performance in December. When I worked for a credit-card company years ago, I was absolutely astounded at the large percentage of purchase volume that occurred late in the year. December was bigger than any three-month period combined.

But even in the consumer goods industry, seasonal patterns need to be taken into account. As I mention in Chapter 7, school schedules dictate the timing of many purchases for families with children. Back-to-school sales are a staple of the late summer, for example. Prom dresses are a staple of spring.

This dependence on school schedules extends far beyond consumer goods. Summer vacation, spring break, and other holidays are busy times in the travel industry. Test-preparation services revolve around standardized test dates. Limousine rentals and hair stylists are in high demand at prom time.

Don’t forget the actual seasons. Weather affects the demand for many goods. Snow shovels, air conditioners, and bathing suits all have seasonal appeal.

When you recognize a seasonal pattern, you need to understand what’s driving it. Your database marketing campaigns begin by identifying your target audience. It’s not enough to recognize that limousine rentals spike in May. You need to get to the fact that it’s families with high school children that are driving the demand. From there you can craft offers and messages that will help you to take advantage of the seasonal opportunity.

Transactions and seasonal patterns

Many, if not most, of the examples I just gave may seem intuitively obvious. But obvious or not, you still need to examine your transaction data to be precise about exactly when and for how long a particular window of opportunity may be open.

In Chapter 7, I talk about the relationship between school schedules and geography. It turns out that these schedules vary quite a bit from state to state. The back-to-school time window is actually four to five weeks long, depending on geography. Careful examination of your transaction data can inform the timing of your back-to-school campaigns.

You may also find some surprises when you look at your transaction data. As I point out throughout this book, in database marketing, surprises mean insights. Last summer, my wife came home one day complaining that the air conditioning in her office was freezing her half to death. I suggested that she get a space heater to put under her desk and volunteered to go buy one for her. Little did I know what I’d signed up for. Seven stores later I finally found one lonely space heater at the back of an empty shelf.

Apparently, space heaters are considered a seasonal item. Who needs a space heater in July? Well, I’m certain that my wife is not the only person on the planet who has a dislike of air conditioning. I suspect that an industrious data analyst could discover an untapped opportunity in transaction data related to small appliances.

Responding to customer behavior

In Chapter 2, I mention that some campaigns are based on event triggers. Essentially this means that a particular customer behavior triggers a marketing communication or a series of communications. Often these triggers are based on customer transaction data.

I get regular service reminders from the auto dealership where I bought my car. This is a simple example of an event-triggered campaign. The trigger is actually the date of my last service appointment, along with the mileage at that time. The dealership lets the appropriate amount of time go by and then reminds me that I’m due for an oil change or my 50,000-mile service. The timing and the content of this communication depend on the details of my last service transaction.

This sort of communication is useful in cases where the relationship between you and your customer does not end with their purchasing a product. Even after I drive my car off the lot, I still need maintenance. Vacation planning is like this as well. I often don’t buy airline tickets, make hotel reservations, and arrange for a rental car at the same time, even though I may use the same website to do all of this.

Some events may trigger real-time messages. Fraud alert calls from your credit-card company happen within minutes of a transaction. Confirmation e-mails for online transactions are sent almost instantaneously.

Your customer’s transactions are full of information that can inform your communications. From service calls to targeted offers, understanding what your customer is doing allows you to be in the right place at the right time with the right message.

In Chapter 10, I talk about triggered-event marketing in much greater detail.

History Has a Way of Repeating Itself: RFM Models

When database marketing was first coming into prominence, analysts developed a relatively simple targeting technique that is still widely used today. The technique was first developed for the catalog sales business. The motivation was that catalogs are expensive to print and ship, so it’s important to mail them to people who might actually use them.

Recency, frequency, and monetary value: The RFM framework

The technique, known as RFM modeling, is based on looking at three facts about customer transactions.

R is for recency. How long ago did the customer last buy from you?

F is for frequency. How often or how many products did the customer buy?

M is for money. Well, actually it’s for monetary value but it means money. How much did the customer spend?

The basic idea is that each one of these factors individually is somewhat predictive of response rates. Combining them makes those predictions even better.

RFM models are developed using the summarized transaction data discussed earlier in this chapter. Transaction counts, purchase totals, and recent transaction dates are grouped into ranges. A simple RFM model might only distinguish high, medium, and low transaction volumes, for instance.

Each customer is ranked on each of the three attributes. Customers are then segmented based on their combined ranking. For example, one segment is made up of customers who fall into the low category on all three attributes. There is another segment for very recent, low volume, and high monetary value. And so on.

The number of segments gets big fast. If each attribute is split into three ranges, you end up with 27 distinct RFM groups. If you split them ten ways, you end up with 1,000 segments.

Building the model

The real insight comes when you apply these segments to customers who have received marketing campaigns from you in the past. You look at the response rates for each of the RFM segments. Typically, some segments dramatically outperform others.

Like all models, you should test RFM models before using them in defining target audiences. The standard way of testing a model involves splitting up the customers you’re analyzing into two randomly defined groups.

You might be analyzing the response rates of 100,000 customers who received your spring campaign. You want to randomly split that group in half. You use the first half to do your analysis and define your high-performing segments. Then you use the second half to confirm (or not) that those segments really do perform better than the others.

You can accomplish this random split with a random number generator. Database software, analytic software, and even spreadsheets have functions that will produce random numbers between 0 and 1. The idea is that you generate a random number for each customer record. If the number is less than .5, you put the record in your analysis file. The rest of the records go into your confirmation or test file.

By focusing future campaigns on the high-performing segments, you can achieve higher response rates while reducing campaign costs. You should consider some technical issues when implementing any type of analytic model. For one thing, you don’t want to assume that your segments will perform as well in the future as they did in the past. I address these issues, as well as the notion of testing models, in Chapter 16.

Beer and Diapers: Market-Basket Analysis

Another strategy that has been around for a while involves trying to understand what products are purchased in bundles. There are obvious product bundles. You need paper when you buy a new printer, for example. But there are sometimes not so obvious bundles. Increasingly sophisticated analysis techniques are being used to cull through large amounts of transaction data to find those less obvious bundles. This type of analysis is known as market-basket analysis.

Whenever the subject of market baskets comes up, you’re likely to hear someone bring up a famous story on the subject. In the early days of this sort of analysis, a grocery store chain began analyzing its data to look for products that were purchased together. Much to their surprise, so the story goes, they found that when men bought baby diapers very frequently also bought beer.

I’m pretty sure this story is more urban legend than truth. I’ve never seen a diaper display next to the beer cooler in a grocery store. But it does illustrate the potential element of surprise inherent in data analysis. Unexpected patterns do exist and can be used to your advantage.

But grocery stores do tend to be pretty good at this sort of analysis. When I do my shopping for Thanksgiving dinner, I rarely have to actually walk down an aisle. The supplies for various traditional side dishes are all laid out at the ends of the aisles. One display has the green beans, mushroom soup, and fried onions for the green bean casserole. Another has the canned pumpkin, brown sugar, and condensed milk for pumpkin pie.

You often see the results of market-basket analysis when you shop online. Whether it’s books, clothes, or appliances, you frequently get prompted with a message about what other customers bought along with a particular item. “People who bought this also bought these things...”