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
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
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
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:
here to subscribe
View previous newsletters
More Satisfied Customers
Check out what some
recent customers had to say about their experiences with Customer
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
Much of the success of this project is due to Craig's skill and
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
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
VP Sales & Operations, Americas
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
See What Our Customers Say