Astute Planning, Flawless Execution,
Delighted Customers

Issue #131

Tuesday, October 21, 2008

Help the Fight Against Hunger
Please support me in my fight against hunger. This weekend, I'll be participating in the CROP Hunger Walk and I could use your help to reach my goal of raising $4,000. Some of you have already donated, getting me to over 15% of my goal already - THANK YOU!

To make a secure online credit card donation, visit Craig's CROPWalk Personal Page.

Customer Data - Getting Well
by Craig Bailey

In our prior article in the series Customer Data Are You Neglecting a Key Corporate Asset, we covered approaches to "stop the bleeding" to minimize the potential for bad customer data getting into your systems. Now that you have accomplished this, it is time to embark on the effort to clean up customer data.

In this article we will cover 4 steps to cleaning up your customer data.

1. Scope the effort
2. Define the approach(es)
3. Engage necessary resources
4. Standardize and de-duplicate addresses

Scope the effort

The first step in cleaning up your customer master data is to determine the scope of the effort so that you can subsequently define the approaches to take and the resources needed to complete the cleanup effort. Scoping involves determining how many records need to be reviewed, how bad the data is and the scenarios of uncleanliness (duplicates or extraneous records, etc.). A simple way to accomplish this is to get an extract of your customer file consisting of customer number, name and address, and import it into MS Excel or MS Access. From there, you can sort and sift the customer data looking for patterns, opportunities and low-hanging-fruit to clean up your customer data.

An approach to identify duplicate customer records would be to sort your customer file by Customer Name. Upon scanning the list you will likely find separate customer records that have similar names but are spelled slightly differently. This may result from key stroke errors or differing approaches to inputting abbreviations.

An approach to identify extraneous information in your customer master would be to search on the customer name, address line 1, 2 or 3 for terms such as:

  • Variations of "Attention Accounts Payable" It would be more appropriate to setup your billing system to automatically print "Attn: Accounts Payable" on invoices and statements to avoid cluttering address data.

  • Variations of "Attention Receiving" It would be more appropriate to setup your system to auto print "Attn: Receiving" on shipping documents to avoid cluttering address data.

  • Variations of "Do Not Use" It would be more appropriate to deactivate or delete these records to avoid cluttering your customer list with bogus records.

The above are just a few examples of information that we find in customer data that serves to clutter up your customer master with duplicates and extraneous information that minimizes its utility for the enterprise.

Note: As you are likely aware, there are numerous utilities and service providers that can help you accomplish the above. While the method outlined above can be very pragmatic and effective for smaller volumes of data (<20,000 records), we'd suggest that if you are dealing with large volumes of customer data (>20,000 records), you might consider leveraging these tools. Be aware that they are typically very aggressive, requiring much tuning and QC'ing of rules to "get it right." In fact, we have found that these tools are most effective for marketing data; for "real-live" customer data, much more care must be taken to ensure that you continue shipping product and sending invoices to the correct location. Call us if you'd like to learn more.

Define the approaches

Once you have scoped the effort and identified the various scenarios that need to be cleansed, you can now determine the best approach to perform the cleanup. The choices will involve some combination of automated and/or manual (brute force) effort.

As an example of an automated cleansing approach, you can provide a list of common variations of "Attn: A/P" to your IT department, and have them strip it from customer address lines. However, prior to performing this in production, you'll want to make sure that you have updated your system to auto print "Attn: Accounts Payable" on invoices and statements (if your system is not already doing this).

As an example of brute force cleanup, you can provide the sorted customer list to a team of analysts to research those records with similar customer names to determine if they are in fact duplicates and can be merged. Note that there will likely be several guidelines and criteria to consider as relates to your company's "definition of a customer," prior to merging accounts, which could include, but is not limited to: payment terms, credit limit, sales rep assignment(s), etc. In essence, you'll need to determine "if" the records can be merged, which record should survive and any post-merge handling that will need to be performed.

Engage necessary resources

Assuming yours is like most companies we have worked with, you will likely have a sizable "brute force effort" to undertake. If that is the case for you, you will now want to reach out to temporary staffing agencies to obtain pricing and profiles for customer data quality analysts. The number of resources and duration of their engagement will depend on the scope of effort identified above and how quickly you wish to get through this effort. Your customer data quality team leader will want to have direct oversight of the selection of these resources and day-to-day activities, ensuring that the research and cleanup effort is happening according to the plan and guidelines.

Standardize and de-duplicate addresses

This section is most applicable for dealing with large volumes of domestic (US) customer data (>20,000 records, which is still relatively small in terms of what address standardization vendors are used to dealing with). One of the "holy grails" that you will hear from address standardization service or utility providers is that they can quickly and easily churn through your customer file, standardize addresses per USPS standards and identify duplicates. They are 100% correct in their claim. However, we'd suggest that if we are only talking about marketing data (for mailing large volumes of promotional materials, etc.) you are good to go. However, if you are talking about real-live customer data, there is MUCH more to it than that. The reality is that:

  • You have customers that "demand" certain instructions or information in their address lines, or that want separate account numbers for each type of product they order. Yes, these are real life scenarios for those of you not in Customer Service or Sales. And, no matter how hard you try to explain to the customer other options (i.e., use of a PO#) to meet their need, they simply "want it their way."

  • You have system and business policy limitations or constraints that require you to maintain duplicate customer records in very specific scenarios (separate payment terms or credit limits by division or product line, separate sales rep assignment and commissions by product, etc.).

  • You have downstream limitations that may prevent making mass updates and/or merges of customer records because manual intervention is required to keep things in sync (i.e., ensuring that your Sales territory assignments remain balanced in your CRM system).

We share the above not to discourage your consideration of these tools or services, but rather to make sure that when you do engage these resources, you go in with eyes wide open. And, you'll want to make sure that you devote sufficient (a.k.a. significant) time to defining and QC'ing the various rules, scenarios and exclusions that need to be baked into the address standardization and de-duplicate algorithms to properly handle YOUR customer data. In parallel, you'll want to determine what exception scenarios (outlined above) you might remove so that managing and standardizing your customer data becomes less complicated.

Now that you have begun cleaning up customer data, the next steps we will discuss include: prevention and inspection.

In the meantime, if you are fed up with your customer data quality issues, please don't hesitate to give us a call. We'd be happy to expand on the strategies we've discussed here and explore how we might be of assistance to you.



+ Help the Fight Against Hunger

+ Customer Data - Getting Well



If you have received this newsletter from a friend and would like to subscribe: Click here to subscribe

View previous newsletters

More Satisfied Customers
Check out what some recent customers had to say about their experiences with Customer Centricity:

Our organization needed to move from a process-centric to a customer-centric focus. We engaged Craig Bailey and Customer Centricity to assist us in moving in the right direction while simultaneously controlling costs and preserving resources. Phase I of the project was an organization assessment and implementation plan. We are very pleased with the outcome of Phase I and feel that the results were well worth the investment.

Much of the success of this project is due to Craig's skill and approach. The
fact that the staff responded so enthusiastically is a testament to his ability to make them feel comfortable, ask them the right questions and to express the intention of improvement rather than criticism.

The true proof of our satisfaction with Craig's services is that we will be engaging him to oversee our Phase II implementation project. I'm looking forward to our next steps and to moving forward to customer centricity!

Kristy Wright
VNA, Western Pennsylvania

Customer Centricity has been instrumental in making the Sophos customer survey program a reality. From concept to launch, the Customer Centricity team guided us quickly and expertly through the process. Their years of customer experience were instrumental in analyzing critical customer feedback allowing us to take swift and decisive action in a very short period of time. Our organization highly recommends Customer Centricity and will continue to leverage their expertise in our customer-facing programs.

Dick Faulkner
VP Sales & Operations, Americas
Sophos, Inc.

About Customer Centricity, Inc.
We strengthen overall company performance through better service delivery and management.

We boost efficiencies in front-line customer service and technical support teams, order processing, fulfillment, field service, logistics and other key operations functions.

In short, we align the resources of your organization to exceed your customers' expectations in the most effective and efficient manner possible.

See What Our Customers Say

Quick Links

About Us

Contact Us


Previous Newsletters

Copyright 2008 Customer Centricity, Inc. All Rights Reserved

5 Old Coach Road Hudson, NH 03051 (603) 491-7948