Removing Duplicates from Databases

Removal of duplicate records is a perennial challenge among database marketing professionals, either for purposes of mailing or analyses.  Keep in mind that not everyone can afford postal software to remove duplicates from address databases.

Of course, using MS-Access, one can create a solution using the “Find Duplicates Query Wizard” that comes with the software, but what if the data is NOT EXACTLY the same?  The tips below may help in the cleansing of databases, and hopefully save some dollars and frustration in the end.

1. Using the MS-Access Query “Find Duplicates” Wizard, looking for EXACT MATCHES
   1. Always remember the simple stuff, like Social Security Numbers.  Each number is unique to all and thus can be a simple solution
   2. The Birthday/Last Name combination also may work, but care must be taken before any deletions are done.  I would probably suggest a tighter Birthday/Last Name/ZIP Code matching be done first.
   3. The First Name/Last Name/ZIP Code may bring some results when the Birthday field is not strictly filled, although this may be the least effective
2. Creating your own query
   1. Create a field in the table and name it UNIQUER.  This will contain the database that will identify a unique field for each record.
   2. Think of what applies to your particular needs as far as what to place in the UNIQUER field.  Most of the time, for address databases, I use the following combinations
      1. First 3 letters of the first name + the first 3 letters of the last name + the first 5 digits of the ZIP code
      2. First 3 letters of the last name + first 3 digits of the ZIP code + birth month + birth day
      3. You get the picture; play with what works best with your needs.

Always ensure that all your records follow the same format rules; in other words, ZIP codes must follow the same rules for spaces, numerics are formatted in the same manner, and the dates as well.  Use an Update Query to fill the Uniquer field with the selection and combinations that have been selected.

Once the fields have been populated, it is only a matter of creating a duplicate table shell of the original database by copying the original database and pasting “Structure Only”, edit that shell to Index the Uniquer field, and then create an Append Query to that shell to eliminate the duplicates.

Prior to doing the Append Query, one may go through several steps and create temporary tables to ensure that the records are placed first in the order in which the preferred ones may be selected.  An example may be that the records are sorted by Total Sales in descending order.  When appending this information on the table shell, then at least one is assured that the records that are most deserving actually are the ones that are used.

Manipulating Data in MS-Access

Manipulating data has always been a source of constant challenge with so-called “propeller-heads;” a big part of the fun of trying to outsmart yourself.  At first, I try to figure this out for myself, without having to “cheat” and find the answer on the Internet.  But sometimes you just have to give up, pop up Google, and do the search and figure out how someone else outsmarted you.

In my world, where nothing is perfect, we sometimes have to find ways to transform data to what we need them to be. Take ZIP codes, for example. Some distance software uses only the first 3, some only the first 5, hardly the entire 9 characters of US ZIP codes. And don’t get me started on Canadian Postal Codes, where many Americans are not aware of the proper format.

I use the following commands in MS-Access to format data as I need them, as an UPDATE QUERY for FIELD [Data]:

• Deleting Spaces:  REPLACE ([DATA],” “,”")
• Changing characters (usually for Canadian Addresses):  REPLACE ([DATA],”O”,”0″)
• Adding a space (again, for Canadian Postal Codes):  LEFT([DATA],3)&” “&MID([DATA],4,3)

I hope this post has been helpful.

The demise of traditional marketing tactics?

This question was posed to our group on LinkedIn:  

“I have been hearing buzz about traditional advertising tactics being abandoned by companies in favor of using social media (SM) and “real-time” conversations to promote businesses. I would like to hear thoughts on social media as the only area of growth in the advertising/marketing arena. Are traditional methods being abandoned and replaced by the “Twitter-type” tactics? Are there any markets where SM tactics are better suited and vice versa? How are you integrating SM into your marketing mix?”

The following is my response:

“I strongly disagree with this statement.

The emergence of new technology and conveniences do not necessarily coincide with the adaptation of your target market to these. Traditional marketing tactics are still alive and well, especially when my target demographic consists of patrons who are 56 years old or more, with no great distinction in income level and location of residence. We are using standard direct mail and telephone blasts heavily; and although the methods are the same, the tactics have changed with results from tests and analyses. Our patrons clamor and complain when they do not receive that mail piece before the beginning of the month. We have invested heavily in the education of our patrons that I believe even if we adopt new methods to reach out to them, they will still be looking at their mailboxes at home.

Also, there is somehow a prevailing distrust of anything electronic among our loyal patrons.

It is not to say that new marketing tactics cannot be adopted; however, for our patrons, it will probably take several years before we can fully replace our delivery methods electronically.

I am curious, though, to hear results coming from other industries on their adoption of social media marketing.”

My Everything Folder

When you’re in a position where you’re expected to know EVERYTHING, you can’t possible KNOW everything, and if you’re in a bind trying to find the subject matter expert who reports to you for information you need NOW, you may think that you’re out of luck.

Don’t fret–there is a solution:  I call it my EVERYTHING FOLDER.

It is a legal-sized, 6-partition folder that contains all the itsy-bitsy details that I’m supposed to carry around in my brain, but otherwise need for thinking of new ideas to make more money or save on expenses.  I carry this around with me EVERYWHERE; you just can’t be sure when you’ll get asked the population of your VIP patrons who live within 240 miles of your property!

Each partition contains current reports or facts, along with the two previous versions, just in case you need to refer to it or need to see a trend.

My version contains the following:

1. Marketing Calendar
2. Patron Segmentation
3. Event/Response Analysis
4. Mail Drop Calendar/Direct Marketing Fact Sheets
5. Promotions/Event/Entertainment Fact Sheets
6. Advertising Media Plan

You can, of course, tailor your folder to your own needs.

Let me know how you plan on having yours look.

Formula for Rounding Up in Multiples of Five

Sometimes you just need a simple formula to just give you what you need without having to strain your brain.

This came out of trying to increase reinvestment by 25%, rounded up, and at the same time keeping the values in multiples of five using MS-Excel and MS-Access:

Assuming Cell A1 contains the original value

=INT((A1*1.25)/5+0.99)*5

I’ll leave it up to you to convert it for MS-Access.

What is GTD and How Do I Get One?

GTD is short for Getting Things Done, which comes from a productivity book by author David Allen, “Getting Things Done, The Art of Stress-Free Productivity.”  

There are countless sites that could explain the philosophy better than I can, so please feel free to click on this site: http://zenhabits.net/2007/02/beginners-guide-to-gtd/ for a collection of the websites that explain GTD.  I would also suggest searching for GTD on the Image Search of your favorite search engine so you can get a visual of the flow that is GTD.

This is by no way a silver bullet; you still have to be disciplined to review your inbox daily, but the dividends are worth it.

For me, the biggest advantage of doing GTD is that my mind is kept clear–this way, I am able to process the work that I need to do better, faster, stronger.  Okay, well not stronger, but I do feel more confident of my skills and that nothing else is in my head to muck up my thoughts.

Getting Started

I have been doing GTD on and off for the last couple of years; I seem to be going through the same cycles, and it was always getting back on the wagon that was always the hardest. The question always has been trying to gather everything from your inbox and start putting them into projects and contexts, and that task alone needs a day after several months of not doing the system. This last time, I decided to try something a little bit radical, and it seems to work just fine. The first thing I did was prioritize those in my inbox by requestor. My superiors had certain expectancies that cannot be ignored. After choosing these tasks, I then sorted what was left by date, and set aside (ie IGNORED) anything that was older than 2 weeks. 

At first I was nervous, not really knowing what to expect. I organized my recent inbox items and it gave me a sense of accomplishment, but I was still careful to look at those that were set aside, just in case something came up. After the first week, some of these tasks resurfaced, but I treated them as a new item on my inbox. This became the case for the next several weeks, and I was able to get by just fine.

I was able to get back to doing GTD in a manageable way, without being overwhelmed by the seemingly endless to-do list that was facing me just before, and now I feel that I have more control on my days and getting things done.

 

iPhone Apps I Use the Most

I’ve found that my iPhone has absolutely changed the way I organize my life and work. It has proven to be an invaluable tool, and now I can’t imagine life without it. I don’t even really consider it a “phone,” but rather a computer that just has the capability of receiving and making telephone calls.

Listed below are the iPhone Apps that I use most of the time, and which I think is crucial to getting my life in order:

Default Applications

Calendar & Email - I use these to keep mainly my work in order.  Support for MS-Exchange was greatly appreciated (and needed!), and I was glad to find out that I did not have to subscribe to AT&T’s Enterprise subscription to use it!  I also found out that I had to change my FETCH options so that the battery on my iPhone does not run out.

 Google Maps – When searching for places and businesses, nothing beats this application.  Not only does it locate Points of Interest, but it provides you with their telephone number, directions, and any restaurant ratings if it is applicable.

 Downloaded Applications (Free)

Evernote – This is the do-it-all note-taking application, hands down.  My favorite feature is being able to take a picture of words and the application actually making those words searchable.  Of course, I would recommend installing the application on your home computer as well.

 vlingo - The feature that iPhone forgot.  With the press of a button, you can use your voice to dial anyone from your contact list, a telephone number, search the web, or locate on Google Maps.  It is truly invaluable to me, especially when my hands are not free to type.

 iDicto – Need to jot down a quick note while on the run?  Just record it for now and then recall it later once you’re at your desk.  Although calling your own voicemail also works, you wouldn’t have to wait for all the prompts to go through before recording.

 Free RSS – Free RSS has a feature that automatically seeks the RSS feed just by letting it know the main webpage that you are interested in.  If the page has more than one feed, it allows you to select which feed you would like to add on your RSS.

 Facebook – Even though Facebook can prove to be a time-consumer, it has also been a valuable resource for me when I am in need of assistance.  As most of my friends in FB are former co-workers, this gives you an avenue to post a question or concern and receive responses in a minimally invasive environment.

 Darkroom – I have replaced the default Camera application with this.  Darkroom prevents any blurred images by actually waiting for the iPhone to be steady before actually taking the photograph.  This is important, especially when I have to take a picture of the contents of my whiteboard.

 Speed Dial (1-4) – Place this on the first page of your iPhone and you save several keystrokes when trying to contact your top 4 contacts!  Set up was very intuitive and easy.

 Downloaded Applications (Fee)

Toodledo – I have used several To-Do applications, both web- and application-based, and so far, this is the closest I have ever used that follows the needs that most GTD’ers have for their Inbox, Next Actions, Waiting For, and Someday lists.  It costs $2.99, which I believe is a steal!