Setup

[aisza]

My company does wedding announcements. We attend multiple bridal shows and collect contact information from thousands of brides. Many brides attend multiple bridal shows and we want to make sure we don't duplicate our marketing efforts (coupons, incentives, etc.)

Each bridal show organizer gives us an excel spreadsheet with contact information as well. I need to pull all of these spreadsheets into one database and eliminate duplicates. what is the best way to go about doing that?

[SteveF]

My company does wedding announcements. We attend multiple bridal shows and collect contact information from thousands of brides. Many brides attend multiple bridal shows and we want to make sure we don't duplicate our marketing efforts (coupons, incentives, etc.)

Each bridal show organizer gives us an excel spreadsheet with contact information as well. I need to pull all of these spreadsheets into one database and eliminate duplicates. what is the best way to go about doing that?

Duplicate elimination is an art in itself; and IMO would be a complex thing to build yourself due the potential variations in names, addresses, etc.

So here are some questions that may seem out of place, but do have relevance to your unique situation:

Are you located in the US?
Are your mailings large enough to qualify for quantity discounts through your Postal Service?
Do you perform all of your mailings yourself, or are these outsourced to a mailhouse?

If you are located in the US, please PM me with contact info -- the company I work for might be able to partner with you to solve some of your issues.

Steve

[aisza]

Yes, we are in the US and 99% of our clients are in our local area. Depending on the type of mailing we choose to do, we may or may not qualify for bulk mail prices. We do all of our own printing and mailing as well, since we are a mailhouse in addition to the print shop.

[SteveF]

In that case, don't re-invent the wheel.

Use Access to collect the info from the various lists, then feed the relevant information to your postal presort software for dupe removal. Be sure to CASS-certify the addresses prior to dupe removal to standardize the addresses as much as possible.

After the duplicates are removed, pull it back into Access for analysis & generating lists for the actual mailings.

In case you haven't figured it out by now, I work for a mailhouse/digital print shop in NW Washington, and am involved with mailing-list processing on a daily basis.

Steve

[aisza]

Hmmm... well my question was how. what is the best way to set it up in access? I am not concerned about mailing at all. mailing is a miniscule piece of our marketing strategy and, as you mentioned there are several ways to eliminate duplicate mailings. I want to eliminate duplicate brides in general, and track every data point regarding that bride. What shows she went to, which drawings she entered, what prizes she won, when her consultation is, whether or not she showed up to her consultation, how much she spent with us, etc.

[SteveF]

Hmmm... well my question was how. what is the best way to set it up in access? I am not concerned about mailing at all. mailing is a miniscule piece of our marketing strategy and, as you mentioned there are several ways to eliminate duplicate mailings. I want to eliminate duplicate brides in general, and track every data point regarding that bride. What shows she went to, which drawings she entered, what prizes she won, when her consultation is, whether or not she showed up to her consultation, how much she spent with us, etc.

OK, rather than getting into specifics right away, I think it’s worthwhile stepping back and taking a look at the needed tasks on a more general basis. It sounds like you probably want to check the new lists you receive against existing data, to see if someone you already entered is showing up again on the new list. The question is, how sophisticated do you want to be with your duplicate check?


A simple check, one that is relatively easy to set up in Access, is an “exact match”. This means that the new entry must match, letter-for-letter, against the existing list to be flagged as a dupe. If you have “Catherine Middleton” in your existing list, a new entry of “Cate Middleton” will not match (sorry, I couldn’t resist!). Typos or other misspellings (“Catherine Middelton”) will throw things off as well.


More sophisticated dupe checks, which can find less-than-exact matches, are much harder to build; probably well beyond what you’re willing to try. That’s why I suggested throwing your Postal Presort software at the task. The one we use at my current workplace has very sophisticated duplicate-removal algorithms, beyond what even I’d want to build.


With that in mind, I think the next best thing to do is to identify new potential duplicates strictly by address match, then manually compare the new possible dupes to the existing entries using an on-screen form. The confirmed duplicates can have the new information appended to what already exists, and the non-dupes can just be added normally.


To keep things as simple as possible and fairly easy to build, I recommend the following workflow:

1. Take your entire existing data file, and CASS-certify the addresses with your Postal Presort software. This gives you a starting baseline to use for comparison when importing new lists.
2. import the new Excel file into a semi-temporary table in Access, and assign an artificial Primary Key to the data in that process.
3. export the Primary Key, Name & Address info only, for CASS certification by your Postal Presort software.
4. Re-import the certified list, and match it back up with the original import using the Primary Key.
5. Use an exact address match (street + city + state + zip) against the existing address table to identify potential new duplicates, and proceed to handle these as described above.

The reason I keep getting back to CASS-certification on the addresses is that this process will go a very long way toward eliminating typos and other variations in the street address, so you’ll have a reliable starting point to do an address match and catch duplicates. Remember, to a computer a street address of “880 Penny Lane” is not the same as “880 Penny Ln”. CASSing the lists will change the first one so that it matches the second one, and so make an exact-match possible.


Does this make sense? Yes, it’s complicated! What you want to do is not a simple thing, unfortunately. The outline above is about as simple as I think you’ll be able to get without spending a lot of time figuring out how to deal with name and address variations on your own.

After you get past the potential-duplicate issue with new entries, the rest of the project is a specialized CRM (Customer Relationship Management) application. Which brings up the question: have you looked to see if there are any existing off-the-shelf CRM apps for your rather unique business? Not that I don't think Access is up to the task, but you need to take into account the time investment of building your own -- using an off-the-shelf package frees you up to spend time more productively in other areas.

If you feel this is beyond your abilities, I would consider developing this system for you on a freelance basis. I have over 15 years’ experience developing Access applications, with over seven years working on address-list processing in the bulk-mail business, including building address-cleanup and duplicate-identification methods to handle situations where the Postal software being used at the time (at a previous employer) was not able to do it by itself. I've also been discussing the basic concepts of CRM apps with a buddy who's looking at building a business that supplies CRM tools and services for a specific vertical market (not yours). Please PM me with contact information if you want to go down this road.


Steve

[aisza]

PM? not sure how... email me at info@audixconsulting.com thanks.