How to clean a dirty customer database

How many people do you know that enjoy cleaning dishes after a party or family get together?  Not many.  To me, cleaning a customer database is very similar.  It needs to be done, but not many people like doing it.  At one company where I worked, the executive management team didn’t believe in cleaning customer data.  They would just continue to buy lists without de-duping.  They would throw tens of thousands of dollars away every time they mailed a flier, brochure or catalog.  Keeping a database clean isn’t cheap, but well worth the investment.  As I suggested in my article “Clean Customer and Product Data – Your Pot of Gold”, your customer data is one of the most valuable assets that you have and you must protect it.

Here’s a process that has worked well for me that helped keep our customer database clean.  This is by no means the only database cleansing method, but it works well.

1. Create a “Key” using zip code (3 or 5 digits) and primary address (sometimes all, sometimes the first 14 characters or so).
2. The Key record will look something like “505011234MAINSTSW” while the zipcode field still reads “50501-1578” and the primary address record still reads “1234 Main Street S.W.”.  (We could still use the address information when we generated the address labels, since we know that the list will get CASS and NCOA (National Change of Address) processing by the mailer if we wanted to get any sort of postage discount.)
3. If the list hasn’t been CASS certified (Coding Accuracy Support System) yet, we would standardize the Key by doing a global search and replace on things like “Road”, “Street”, and “North”, and change them to standard postal abbreviations like “Rd”, “St”, and “N”. Then we would strip special characters from the Key: dashes, periods, commas, pound signs, and spaces.
4. When you sort by the Key, then by contact name, a formula can be written in Excel to compare addresses, and use segments of the contact name and/or company name to identify duplicates.
5. By doing visual checks of a few hundred records, you can usually tell if the formulas need to be tweaked and if additional processing of the records needs to be done.
6. Concerning demographics and customer value, we use SIC (Standard Industrial Classification) and NAICS (North American Industry Classification System) to help target customers, and tallying sales activity of defined time periods help classify the accounts.  Because we had the luxury of a SQL database, that information was stored within the database and updated as needed. We could identify the SIC or NAICS hot spots in the customer database for target marketing.
7. Phone or e-mail contact with the customer helped keep the contact list up-to-date.  Because the customer service reps would associate an order with a caller by leveraging a SQL database, we could use the data to help identify active contacts, (who placed orders, how often were orders placed, and the date of their last activity).  This activity helped pinpoint when a contact went cold, and helped us identify who to ask for when calling to clean up the list.

After cleaning up the database and before we mailed an expensive piece like a 1,000 page catalog, we would do a smaller mailing to that same list to see what got returned.  We would clean up the list using that information and then we would be ready for our mailing of a more expensive piece.

If you want to learn more about me, please visit my LinkedIn profile, my website and my blog.

Follow these guidelines to build a successful ecommerce website

There are six key things that you can do to ensure that you build a powerful and effective ecommerce website.

Ask your customers to help you design your ecommerce website

This may sound like a crazy idea, but it works.  I wish I had followed this advice before spending months building an ecommerce website that our customers really didn’t like.  We thought we did everything we needed to in order to build the best site. We benchmarked our major competitors who had significantly more web traffic than we had and rebuilt what we thought was a better website than our competitors.    After we launched our new site, our sales were flat.  We solicited customer feedback and the customers kept saying “I wish your site was more like competitor X, Y or Z.”  What exactly does that mean?  I was determined to find that out.  We contacted every customer who sent us feedback to find out exactly what they meant.

Our customers didn’t want a clone of our competitors’ sites.  We quickly discovered that our new website wasn’t what the customer wanted or needed.  They wanted to find products on the website quickly and easily.  They didn’t really care about promotions and specials. Benchmarking told us what our competitors were doing, but it didn’t tell us what our customers wanted in an ecommerce website.  We completely rebuilt the new website we just launched based on what our customers wanted, not what we thought they wanted.  We also made our customers beta testers of our newly rebuilt website.  They had ownership and a vested interest in making our site successful.  Our site became their site.

Make it extremely easy to use

There is nothing more frustrating than going to a site that you know has the product that you want, but you can’t find it.  In an ecommerce world where every second or millisecond counts, build a site that is logical, intuitive and very easy to use.  You want your customers to find what they are looking for as quickly as possible.  That means that your product names and descriptions need to be accurate and what the customers call the product.  I have found that many manufacturers call their product “X” but the customer calls it “Y”.  Kentucky Fried Chicken discovered that their customers called their company “KFC” not “Kentucky Fried Chicken” so instead of “re-educating” the customer, they changed their signage and promotional materials to mirror their customer’s perceptions.  That was a very smart move. When customers Google a product that you carry, you want your products to show up in their search results.

Be price competitive

Do your homework.  Make sure your products are the ones that the customers want and that they are competitively priced.  Competition is tough in the ecommerce world.  Your value proposition should include competitive pricing.  Don’t let price be a hindrance for customers to buy from you online.  That doesn’t mean you need to be the cheapest online, but you do need to be competitive.

Optimize your site

Before you launch your site, make sure that it is search engine optimized.  You want Google and the other search engines to index your site because you have great content with keywords that reflect that you are an expert in the products that you carry.   When I was hired at one company, we had 6 optimized pages on Google.  Within the year, we had over 3 million optimized pages and our products showed up many times on page 1, position 1 in Google search results.  SEO works when you do it correctly.

Market your URL everywhere and build credibility

You can have the best ecommerce site in the world, but if your customers don’t know about you and the products that you are selling, you won’t be very successful.  New customers are much more likely to check out your ecommerce site if they know more about you.  With all of the fraud that takes place online, people need to believe their credit card information is safe on your site.  I strongly recommend advertising online (on websites and social media) and in print, as well as sending out email messages about your company and your new website.  The more visible your company and URL become, the more likely your online sales will increase.  People like to do business with people they know and trust.  Building credibility with the marketplace is very important.

Manage your expectations

We would all love to build a successful ecommerce website like Amazon.com, however Amazon hasn’t made a meaningful profit in the nearly 20 years it has been in business.  It takes time to get your ecommerce site to take hold in the marketplace.  Be patient.

If you…

1. Have your customers help you build the ecommerce website that they want,
2. Make it very easy to use,
3. Ensure that your product names are what your customers call your products,
4. Be price competitive,
5. Optimize your site so you are indexed well with major search engines and
6. Get the word out in the marketplace about your products and services on your ecommerce site using effective advertising to build awareness and credibility. If you do all of these things, your site will be successful.

If you want to learn more about me, please visit my LinkedIn profile, my website and my blog.

Ecommerce Image provided by Maria Elena.