As part of our Monday morning meetings, I like to review with our task leaders the technical support calls we had from the previous week and discuss ways to avoid similar problems in the future.  One such morning, I noted with particular interest a call from one of our users who was trying to exclude non-mailable addresses from a campaign.  While this is routine for direct mail campaigns, what made this situation stand out is that he was specifically examining the street address to make sure it contained a house or building number, rather than referencing a bad address indicator as is the norm. 

I find cases like this fascinating, because at times we're all guilty of doing things the hard way.  Remember the old joke about the guy pounding his head against a wall?  When asked why, he says, "because it feels so good when I stop."  I'll bet if we totaled the hours spent getting around problems (or the effects of a problem) instead of fixing them, we'd all have a lot more time to lay on the beach and drink margaritas.  But alas, I'm not so naïve as to think this will ever change; in fact I'll confess that once even I was in a hurry and made a mistake.  My discussion for today is a look at the tendency we all have to do things the hard way, and fix the effects of a problem rather than the problem itself.  Here are some examples of database design "opportunities" I've seen because of this mentality. 

Non-Unique Keys

Ever since Bill Gates killed all the dinosaurs and founded modern civilization, database projects have suffered from data problems.  I've seen supposedly unique files contain duplicate account numbers, order numbers, and keycodes.  The easy way out when building a database is to say the heck with it, and add some IT-generated but marketer-mystifying unique key to the table.  As such, the problem (which is the fact that you have dups where you shouldn't) is neatly circumvented.  But guess what?  You get to pay for this cleverness for the rest of eternity (or at least until you quit), because for some odd reason your business reports are based on people, orders, and marketing campaigns, not on meaningless unique keys.  And until such time as you put forth due diligence and fix the problem, these reports are either not accurate or they're horrendously difficult to produce. 

Duplicate Names

Given the industry we're in, you'd think if there's one thing we know, it's that marketing databases should contain unique customer and prospect names and addresses.  Think again.  On more than one occasion I've seen merge/purges done AFTER a campaign generated the solicit file.  Now repeat this several times: garbage in, garbage out, now let's fix the garbage.  Sounds kinda' snappy, eh?  But not very efficient to do, and it sure makes it hard to find out who's responding to what offer.  Please, unless you really like your merge/purge vendor, clean and unduplicate names and addresses before you put them in your database.  That way you only have to do it once. 

Changing Account Numbers

As part of your name/address unduplication, make sure you always use the same account number for the same person.  I know this seems like common sense to most of us, but apparently it's not as common as we think.  I've seen databases where customer numbers are reassigned with each update.  This makes it awfully hard to do unimportant stuff like calculate lifetime value and perform response analysis.  To avoid changing customer numbers, maintain a clean, unduplicated master file and bounce all new names against it.  When there's a match, simply use the account number from the master file and you're all set.  Most modern address unduplication packages allow you to do this. 

Value Lists

I've seen many cases where users key in lengthy lists of codes - such as product codes - in order to run a particular campaign or report.  When questioned, often I find that the given list has a business meaning that is not accounted for in the database design.  For example, perhaps the same manufacturer made all the products, or maybe they fall into a particular category such as "furniture."  Wouldn't it be easier if you had a manufacturer or category code to choose from?  That way you can just ask for Manufacturer = 'Acme', rather than entering all the product codes that Acme makes.  Another common example I've seen is identifying military bases by entering lists of ZIP codes, rather than simply adding a military indicator to the file. 

Exceptions

If there's one word that marketers love and databases don't understand, it's except.   Here's a real life example from a company I work with: "effort 3 is always telemarketing, except for marketing programs 100 and 200, in which case it's effort 4."  While this may be easy for us to understand, try writing a query that produces totals for direct mail vs. telemarketing by using this business rule.  There are times (and this is one of them) that a marketing database design should not attempt to mirror the way people think.  I recommend that you take exceptions such as this and develop a field or code for it.  In this case, we added a Media field to the database, coding it with a "T" for telemarketing and "D" for direct mail. 

Inferred Meanings

Much like that person we know who's the last to laugh at every joke, databases can be pretty dense.  If you often find yourself using a set of conditions to derive a given value, consider adding a code just as you do for exceptions.  I've seen situations where values such as a customer's status can be quite difficult to derive, for example:  "active customers are those who have bought within the last six months.  Inactive customers are those who have bought within the last twelve months, OR their spending level has decreased by more than 15 percent as compared to the prior year."  Personally I'm a believer in "dumbing down" the database design, adding fields as necessary to make it easier to ask questions, and placing complicated rules like this into the load and update process.  As such, I'd advise adding a Customer_Status field to the database, and populating it according to the rules. 

As you can see, I'm a big believer in fixing what's broke.  So now that I've solved all these problems, let me go dump a few more quarts of oil in my car, then clean off the driveway where it keeps leaking.  And I guess I should also have my carpet cleaned where I've tracked oil in the house.  Hmmm, my head's starting to hurt again.  Happy trails.

Copyright 2002, PRIMEDIA Business Magazines & Media Inc.  All rights reserved.