how to merge old mailing list into new list without overwriting data?

January 9, 2023 11:40 AM

Our org is doing some housecleaning of our databases and mailing lists, and I've got a kludgy old email list that I am being asked to merge into our organization's existing mailchimp list, so that everything is consolidated there. Initially at least, I need to be able to reach just the people who came from the old list, and not already on the mailchimp list, but with two lists of that size, I'm not sure how to compare/sort them to get them into mailchimp wiithout causing damage.

The idea was to import the old list into mailchimp and tag them, so I could send an explanatory email just to addresses with that tag. However, a decent portion of the people on the old list (OL) will already be on the mailchimp list (ML), so there will be some duplicates, and the ML data is surely more up to date. If I import the old addresses with the tag, I could very well end up overwriting someone's (or a lot of someone's!) current address with some aol address they were using in 2007. Our ML has 13K contacts; the OL has 5K contacts.

How do I resolve this? I have been trying to figure out how to solve this and thinking this through is breaking my head. I'm thinking there's got to be a way, but I'm stumped.

posted by leticia to Computers & Internet (6 answers total)

IMHO, you should download both of them locally and load each into Excel or a database, where you can add additional fields to separate them once you merge them together. THEN once you've merged them, you should be able to separate out the duplicates or near duplicates.

It won't be easy, because machine won't be able to do this for you. What we consider duplicates are often "near misses" for machines. But if you play with it locally, you can mess up and the original data is not disturbed. THEN you can upload a "new merged list" once you've cleaned up the data.
posted by kschang at 12:07 PM on January 9, 2023 [4 favorites]

Best answer: I would also use Excel for this, but do you have another field that you can use to determine if someone is on both lists with different email addresses? Name alone is not enough. In databases, this is called a primary key. Often times a user is given one when they are created and it should be reused on any table that holds user information, even if they never use it themselves. If your data doesn't have this, then you will need to figure out a way to decide what to do when you have "Henry Smith" in both lists with different email addresses.

If you do, then what you want to do is get each list into its own tab in Excel and then find the ones that exist in OL that do not exist in ML. COUNTIF is a good formula so you can count how many times the user id that is on the old list shows up in the Mailchimp list. If it is more than one, you can delete the record from the old list.
posted by soelo at 12:17 PM on January 9, 2023

Response by poster: Unfortunately, no primary key. When I said OL was kludgy, what i didn't elaborate on was how this OL came to be. It was originally the exec. director's Rolodex, converted to an email list many, many moons ago. It has since been added to and added to in Outlook, and the ED has continued to use it for outreach while at the same time a formal newsletter was developed and people subscribed and unsubscribed themselves to that. In the last year the formal newsletter was migrated to mailchimp, and now we have thls list of contacts exported from Outlook that ED wants to incorporate into mailchimp, so that we have a single source list for mailings. A good goal, but getting there... Manually comparing the 5K in the OL to the 15K in the ML is no joke, even with using excel to highlight repeated names.

(Strategy for the repeated "Henry Smiths" is to send the *new* Henry Smith the initial email, giving him a chance to unsubscribe if it is a legit duplicate and not his preferred address, or, more likely, hard bounce.)
posted by leticia at 1:33 PM on January 9, 2023

Best answer: Understood about the lack of primary key! That sounds frustrating.

Excel should be removing the manual part of this for you though. That is what the COUNTIF formula will do. If you have to use full names, get them all into one cell using a concatenate formula so that |Anne|Wilson| becomes |Anne Wilson|. Then you compare the two FullName columns and remove the duplicates from OL. You do this by sorting the results and deleting the entire row that has the record for the FullName on the OL tab. This is the reason you want the lists on two different tabs, so you can delete from one easily. I would not merge the two lists and then reload everything into Mailchimp if you have the ability to upload just the revised OL and tag them at the same time. You will not have to worry about overwriting anything because you have already removed the ones in ML.
posted by soelo at 1:51 PM on January 9, 2023

Best answer: The strategy hinted at in your updates is the one I would explore. I haven't used MC in a while but roughly what I remember:
- upload OL names into a completely separate list. (Say "no" if MC asks if you'd like to update existing records.)
- email that OL list inviting them to subscribe to the NL if they want to continue hearing from you.
- possibly send one or two more reminders, ending with "guess it's goodbye"
- any OL members who are also on NL, if they go to opt in to NL, should get some sort of "you're already subscribed, update preferences?" message.

Now NL has only those OL names who opted in to NL.
You may get pushback from ED that you will lose a lot of the OL names who don't bother to opt in, but I think that's the best way.
posted by evilmomlady at 2:02 PM on January 9, 2023

How familiar are you with Access? Most packages (I don't use Mailchimp specifically) will allow you to export contact info to a comma-separated values (.CSV) file. If you can get the OL that way, and the MailChimp that way, you could load each into an Access table and do various JOIN operations between two tables to do what you need.
posted by TimHare at 9:02 PM on January 9, 2023