Power of simple tools for customer mining

The genius of free market competition is that the customer gets to decide who wins and who loses. And ultimately, the customer is the biggest winner. - Donald J Carty, CEO, AMR / American Airlines

While lots has been written about why CRM projects succeed and some fail, ANKUR RASTOGI and AMIT SAXENA say that using simple tools could also help a firm in generating anlysis that could provide vital pointers for the future

What do companies actually want?

The high-value, loyal, returning, satisfied, profitable customer is the key focal point for profitable organisations throughout the world. All managers need to identify and focus on those customers, who are the most profitable, while possibly, withdrawing from supporting customers who are unprofitable. Companies want to leverage technology to deliver better and faster than competition, at each and every point of contact. It can’t be that hard, can it? Well it is and there are many surveys and statistics to prove it, showing that upwards of 70, 80 or even 90 percent of CRM implementations don’t deliver on their original promise, even though CRM has been around for over 15 years with hundreds of packaged applications available.

There have been several reports on this but CRM continues to be a tough nut to crack. And every time a different reason crops up. The Gartner Group attributes failure to poor or ignored data, excessive politics, lack of planning, automating flawed processes and ignoring needed skill sets. In a survey of 219 information technology experts, the CMR Consulting Group found that 62 percent of companies that implement CRM products aren’t customer-focused and only 22 percent have CRM metrics deployed across sales, marketing and customer services.

Anyway, the aim of this article is not to churn out one more report on CRM hype and jump into a success / failure debate.

The objective is to help you know more about your customer.

Before seeking a CRM package to implement, you should know whether you are at least ready with your basics. After spending millions of dollars on a CRM package or even on consulting advice before it, you are always wondering how much ROI will be generated (if it ever occurs). But first try to find out the readiness of your organisation and whether it is all set for the big leap? We will elaborate on simple tools to get 80 percent effectiveness with less than 20 percent of the cost of the main implementation.

The crux of our article is shown in Figure 1, which describes the entire process, inputs and the outputs.

This is the simple stepwise framework that can be implemented before you go for the big thing.

Put your house in order
Typically organisations have multiple data islands existing either in the form of spreadsheets or small databases. In your organisation, data might belong to various periods, from various zones / territories and through various channels. The biggest problem that your organisation can have is inconsistency in the format of data entry across the organisation. Most organisations change the format of entry of data every year without realising that this loss in consistency can lead to improper analysis.

The solution
We propose an approach, which we term as ‘virtually integrated databases’. We don’t propose you spend a fortune in integrating and centralising your databases, which may take forever. Rather we suggest that you establish a corporate policy about data structure as well as about its format. All the database structures must be documented and maintained at an easily accessible place (may be Intranet, shared files, etc.).

The benefit in this approach is that when an employee wants to design a new data island, he knows the design and the structure of the database. So in this way, you are designing a template and have a ready reference for creating ad hoc databases.

In future, whenever data needs to be integrated, you will need lesser time, effort and most important lower investments.

GIGO Menace
Salespeople feed in most of the customer data. They either feed it in an Excel format or in a desktop database. There is no harm in this approach, but the format for the entry of the data is never clear. Just to give an example, one salesperson might feed your customer name as Vivek Kumar Mittal and the other might feed it as V K Mittal. Now for the person who analyses the data, these are two separate customers and the error starts at the first stage itself. It is always said that if data is fed properly, the job is half done. It is the perfect example of GIGO (garbage in garbage out). It takes a huge effort to get rid of such inconsistencies. This process of data cleaning is called data scrubbing. The most dirty, yet the most important stage in this entire process.

The way out

• Set a corporate policy for entering data. Vivek Kumar Mittal should be entered as Mittal Vivek Kumar. i.e. in the surname, first and the middle name format
• No short forms should be allowed.
• Ask the salesperson to confirm the spellings of each customer twice before they send in the files.
• Not just the rules, but also the implications of entering wrong data should be explained to salespeople. In short, make them aware of the analysis problems and involve them at the first step itself.
• All this can be accomplished by a small training exercise. A training programme where someone senior in the organisation can give a short pep talk and explain the problems and implications of incorrect data entry in data analysis. The talk can be followed by a practical demonstration of data entry in a sample database.

Consolidation
Consolidate all the data files, whether they are in Excel sheets or in a database format into one combined database. This should also include data of previous years. To predict the future, it is important to understand the past first.

Compare all the sheets and merge them with matched formats. If any column is non-existent in either the new format or the old format, include those columns with ‘blanks’ added in non-existent ones.

Don’t delete any customer data whether they are existent or non-existent in either the past or the current data files.

The earlier stages of data consolidation and data scrubbing take maximum amount of time. If this is complete, then believe us, you will not face any further problems in your analysis.

Analysis
On the consolidated database, let us start our simple techniques of customer value mining.

80 / 20 principle

The most famous, yet the most neglected tool. It is said that 20 percent of customers bring in 80 percent of your revenues. Find out those 20 percent of the customers for each year and also for the gross total of the last five years.

These are the customers that deserve special attention. Companies that do not follow this principle spend 80 percent of their effort on customers who are giving them a mere 20 percent of revenues.

Not just revenues, it is also said that these top 20 percent revenue contributors are also the ones that generate the entire profits of your organisation. The rest 80 percent are usually loss making because of the effort spent on them.

Find losing and the winning customers

We will classify the entire lot of customers into 4 sets; namely, winning, losing, sustainable and vacillating. We will call this as Trend Analysis.

Let us see what these terms exactly mean.

• For each customer, calculate their share as a percentage of total revenues for each year.
• Now if customer X’s share as a percentage of total revenues is increasing every year, say from 5 percent to 10 percent in a span of five years, then this customer is a winning customer for you. This is because the customer’s spending is inceasing every year. Keep this customer happy. Keep him interested in your products and he will do the rest for you. He has built a strong bond with your firm. Just don’t let this opportunity pass by.
• Just the opposite, if customer X’s share as a percent of total revenue is decreasing every year then he is a loss making customer. This is an alarming trend for you especially if this customer happens to be in your top 20 percent. Find out the cause and take corrective action fast.
• If the share as a percent of total revenues is constant i.e. if customer X’s share is 5 percent every year, this is a sustaining customer. His percentage share might not be increasing, but his absolute share is increasing with increase in revenues. Try to find out where he gets his needs fulfilled. Are you his only supplier or are there others too? Try to increase the share by building relationships with him.
• Last are the inconsistent ones. If the share keeps on fluctuating i.e. the trend is unpredictable, then we will call him vacillating. Try to find out the reasons. Is he a seasonal type or he is more governed by promotions? There could be several reasons that have to be explored

If you keep monthly records of your revenues, than the above analysis might prove very cumbersome. So we suggest that you club data into either half-yearly or yearly sets. This will give a more realistic analysis. You can also draw graphs after the clubbing to provide a visual output and easy viewing of data.

If you still feel that doing analysis on the entire set is not possible, do it on the top 20 percent. You must see what the trend is among your profit-giving customers. Any customer that you lose from this group can lead to a significant loss of profit.

Find out the new and the lost customers in the period of five years.

• On the yearly-clubbed data, find out the top 20 percent for the current year.
• Compare it with the top 20 percent of customers five years ago.
• How many customers are still with you?
• Now compare this with the top 20 percent of the gross five-year revenue data.
• If any of the customers that are not present now and still exist on the final list of gross (sum total of five years’ revenues generated by customers) top 20 percent, they are loss making. Since their past revenues are high, it forces itself into the gross list. Find out the reasons.
• Same goes for the new ones, the customers who were not present five years ago in the top 20 percent list but are appearing in the gross list are significant gains for you.

Seasonality analysis

As we mentioned the seasonality factor earlier, let us see what it means, and how does it help you to study it. Seasonality is generally observed sector-wise.

• We are going to club customers according to their sectors.
• Take the gross sum of each sector’s revenues month-wise for five years.
• Plot all the years on the same graph (Figure 2):

We have plotted monthly revenue figures for three years for a particular sector whose code for the analysis is say, X.

Now this chart is a very clear depiction of the seasonality factor. The revenue increases in the months of December to March, dips sharply in April, tries to stabilise in the months of May to August, and so on. To explain this, we have taken a simple example, which shows such a clear pattern. It might not occur with every sector. But still some patterns will be visible. Now the benefits of doing this analysis are manifold:

• We can identify the sectors whose revenues go down in a particular month, and find out reasons for that so that corrective action can be taken beforehand.
• We can identify complimentary sectors that help keep the top line healthy. By complimentary sectors, we mean that if we can find out two sets of sectors whose revenues complement each other i.e. the crest of X falls with the trough of Y, then we can make our business free from such steep falls by promoting the other when the first is not doing well.

Territory mining tool

Now let us move to our next technique—the territory mining tool. Till now we have analysed customers. In this step, we will see if the efforts spent on a customer are worth the returns coming from him. Usually we have salespeople who go out acquiring and retaining customers. The entire business area is divided into zones / territories and a salesperson is assigned to each zone. Classify your customers into territories they are coming from and do the same analysis zone-wise as we have mentioned above.

Costs incurred on each territory is equal to salary of each salesperson plus other incentives claimed.

Match costs with the revenues coming from each territory. There might be territories that are not giving any revenues. And there might be territories that are giving the bulk of the revenue, although efforts / costs incurred on each territory might be the same. Now this is a management decision to decide as to what is supposed to be done for the loss making territories. They might still like to stick to the territories anticipating future potential or may be just need presence in all areas. But clearly, this analysis will signal the attention level required for each territory separately and how each territory needs to be handled.

Profit Analysis

Last but not the least, it is very essential to do a profit analysis. Because it is profits that form the bottom line for the company. Till now all our analysis has been focused on revenues. This was because they are the easiest to capture and easiest for the analyst to gather from the finance department. Profits are tough to analyse. The reason being that organisations rarely capture the cost incurred on a per customer basis.

If your organisation has been intelligent enough to capture costs incurred on each customer, then repeat the entire analysis with revenues replaced by profits. This will be the real reflection on the company’s performance and will single out the steps needed for future growth of the company.

Instead of a huge report to the top management try making a bullet point report of not more than 2-3 pages. Then send it to the top management and to the salespeople so that it acts as a performance dashboard for them. This will also infuse a sense of competitiveness and responsibility in them when they see the practical results of their efforts in the field.

Ankur Rastogi (Rastogi.Ankur@indiatimes.com) and Amit Saxena (amitsax@yahoo.com) are alumni of S P Jain Institute of Management & Research, Mumbai.