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.

Customer Segmentation – Leveraging Your Data For Success

In my post “Clean Customer and Product Data – Your Pot of Gold”, I talked about the importance of customer and product data hygiene and maintenance.  In this post, I would like to take you through the next steps with your clean customer database… customer segmentation. 

The first step in customer segmentation is to analyze your customer data.  Any marketer will tell you that you need to collect as much information about your customers as possible.  The more data you have, the more segmentation you can do.  One of the key things you look for are patterns – similarities and differences in the data that you collect.  Here are some things to look for.

• Geography
• Lead, prospect or customer
• Gender
• Age
• Method of entry to your company (email, print, banner ads, Adwords, social media, call center, fax, walk-in)
• Method that your customers purchase (phone, web, walk-in)
• Products purchased
• Frequency of purchases
• Products viewed but not purchased (You might detect a pricing or product content issue)

 The next step is to group and flag this information into your database.  You can create and name categories that your customers fall into.  For example, if you segment your database by “XYZ widget buyers”, then you can target market to that group only and upsell certain accessories to those customers.

The next step is to segment your customer database.  Be careful here.  You don’t necessarily want to pigeon-hole customers into one group.  You will find customers overlap into multiple categories.  Make sure that you don’t hit the same customers that fall in different groups at the same time.  Verify that the simultaneous marketing campaigns you are launching include different customers.  Over-marketing is easy to do if you aren’t careful.

Once you have flagged your database with the appropriate segments, do some A/B testing of your marketing campaigns.  See what works for those segments and what does not.

Measure everything you do and make adjustments to continuously improve your campaigns.  Implement the programs that work and stop the ones that don’t.

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

Does business intelligence on the web increase sales?

When Amazon launched, everyone seemed to be amazed with their business intelligence system that offered other products they might want to purchase.  This isn’t rocket science and anyone can build a database that offers related products.  If you purchase a hammer, the upsell could be nails. What the business intelligence system and the company of the same name that they originally implemented was called Net Perceptions or Net P.  This system did not offer related product, although it could. The system would analyze purchasing data of customers and provide recommendations of products that are purchased when certain other products are purchased.  I’m not certain if Amazon is still using the Net P engine or something else today, but they still use upsells, bundles and product referrals.

Using the same example as above, customers that purchase hammers might also purchase men’s dress slacks.  They have nothing to do with each other, but they are often purchased together.  If you think about it, it makes sense.  When you go to the grocery store to buy milk, you don’t always buy cereal, although they go together.  You might buy tomato sauce, apples and hot dogs.  The Net P engine would offer those products.  This technology is called “collaborative filtering”. There were some other whistles and bells included in the software like monitoring purchases of customers and when they were due to reorder, the engine would alert the sales rep so they could ask the customer if they wanted to reorder product.

At one place where I was employed, we integrated Net P into our web site, call center and direct sales smartphones.  We increased sales by $4.5 million dollars that year due to upsells.  It wasn’t quite as easy as it sounds.  We also had a unified effort to get more customers purchasing online, offered incentives for the customer service and sales personnel and our executive management “encouraged” everyone to be on board.

I moved on in my career and had another opportunity to integrate the same technology at another place of business several years later.  The businesses were similar, but not the same. Interestingly enough, the business intelligence engine failed miserably the second time.  Why? One reason was the economy.  When we implemented the BI engine the first time, the economy was doing very well.  Spending money on upsells wasn’t a problem.  Also, upselling on  the web was a relatively new thing.  Not many companies were doing it well, so we took advantage of the new technology.  The second implementation had not as favorable economic conditions, customers were getting upsold every place they went and customer fatigue became a factor.

Does business intelligence work on the web?  Absolutely.  Just ask Amazon.  Does it work all the time?  No.  You need to keep all of the factors in play including the economy, customer buying sentiment, and upsell fatigue when deciding to invest and implement online business intelligence. There are newer, more sophisticated toys in the business intelligence toy box today.  If you look at all of the economic implications and customer buying behavior, you can increase your sales using business intelligence.