Excel Data Mining: Measuring Customer Support Costs

Posted by Lior Weinstein on Friday, Feb 6th, 2009
Category : Microsoft Excel

In this series we have already looked at Recency, Frequency and Monetary value as metrics for data mining and ranking your customers in Excel. RFM will tell you who the most rewarding customers are, but it will not tell you who is most-likely to be a tough customer, nor will it tell you how expensive those customers are.

To work out these additional factors, you need to record more data, and that is what I will reveal in this article.

Customer acquisition costs are familiar to any business but many companies do not track individual customers support costs and instead see customer service as an aggregated expense. This is a mistake because certain customers could be costing you more money than they bring in, meaning gaining more of these customers would actually hurt your business rather than help it. Another reason why “make it up in volume” is often a bad approach!

Depending on your systems you might be able to record support incidents either by counting “tickets”, or you might even be able to record time spent. Again, just like with the customer value, you want to use recency and frequency, although in this case high recency and frequency are “bad”!

What would you use instead of monetary value or margin? Well, in some cases you can place a cost on the total support a customer required. It’s not just a factor of time, although that is a cost worth recording, but also there may be additional expenses incurred such as travel, postage, returns, waste, custom work or additional purchases. A customer who demands you turn up at their offices hundreds of miles away with a brand new custom widget is going to be more of a financial burden than one who sends one email and receives a stock answer.

Along with customer ID, you will want to record the type of customer or the product/service the support was against. If customers have multiple products then do the exercise against customer initially but also run through another process for product or service.

Often, but not always, you will find the customers with the best RFM scores are also those who cost you least in customer support. The top 20% really are your best customers overall. Over and over the customers who pay you least are also the ones who cause you the most bother. Of course there is always the high-roller exception who is just demanding because, well, they can.

A friend around the millenium had a software product with service levels. Together we turned the business from a net loss into a hugely profitable company by first systemizing customer service, and then by removing the bottom rung of the offering entirely. We found the cheap product attracted customers who were both more likely to circumvent the copy protection, but also generated the bulk of the distractions in the form of groundless complaints, returns, support problems and bad PR.

You do not need fancy systems to keep this information. Using Excel you can record your customer service data very easily. Just make sure you record at least:

  • Customer ID
  • Date/Time
  • Product/Service
  • Problem Type (categories, such as “fault”, “usage”, etc)
  • Problem Description
  • Solution
  • Solution Type (categories, such as “replacement”, “FAQ”, etc)
  • Boilerplate solution available (Y/N)
  • Resolved Date/Time

You might well have suggestions or requirements for additional data, but essentially you want to know what the problem was, the cure, how long you spent solving and who for.

Once you have your data you can see if there is a way to make these problems go away, perhaps the issue is with documentation or customer expectations rather than product quality. If the problems can’t go away entirely, you can then work on making your customer service as easy as possible, with stock responses, procedures, and other systems. Of course failing all that, you are left with dropping the product, customer (or type of customer) or raising prices.

Bottom line, without data you would only be guessing. So long as you have actionable information then you can actually make some decisions. If you are not recording support information, you had better get started!

Latest From Our Blog