Duplicate email and phone entries for a Constituent

Debby Herold · May 2023

We have quite a few constituents who have the same email address and/or phone number duplicated in their record. Is there an easy way to remove the duplicate entries?

Joe Moretti · May 2023

@Debby Herold None that I am aware of. And this goes to why there should be proper procedures for inputting and why if someone does not know what they are doing, then they should not be doing it. Many of the RE problems would be solved by this simple step. Us database folks waste more time fixing up others messes because some folks have no idea what they are doing and non-profits have a bad habit of hiring incompetent people and keeping them. I worked with a person who imported alumni emails, but put all the wrong emails into the records because they did not have the import set up properly. Good Grief.

Debby Herold · May 2023

@Joe Moretti what a nightmare!

You would think this would be a standard/easy query to set up for cleaning up the database. I have been driving myself crazy trying to find a way to do a comparison between phone numbers and emails to no avail. If I manually see one I clean it but we have THOUSANDS of records (some imported from our old system). We also use the RELO sync to sync records from Luminate Online and that process frequently adds existing (and duplicate) phone numbers and emails to constituent records. It drives me bonkers.

There has to be an easy way to scan for these duplicate entries within a constituent's record.

Alan French · May 2023

@Debby Herold yes! There absolutely is a way to do this using the Delete Phones plug-in

You'll need to do an import first to either change the phone number to blank (as described in the first article above) or change the type to something that's not in use on any other records (I had a phone type set up for this purpose called “DELETE”).

You will need to identify the duplicates in Excel but this is pretty straightforward:

Export a list of all phones/emails - make sure you include phone import ID. You could also do it for only a subset of constituents if you wanted, as long as you include all their phones/emails, but the deduplication process is no more complicated for 100,000 records than it is for 10 so you might as well include them all!
Open the list in Excel and copy it so you now have 2 identical sheets.
Make sure it's sorted so that the phone number you would prefer to keep is higher up than potential duplicate numbers, e.g. ones marked as primary or whatever. You may not care which one you keep, if they're both identical, so you might be able to skip this step.
Using Excel's Remove Duplicates tool, deduplicate ONE of the sheets by constituent ID and phone number. Make sure you untick the phone import ID field since those will all be different so it won't identify any duplicates!
On the unduplicated sheet, add an xlookup formula (or vlookup or index/match or countif, whatever floats your boat!) next to every phone number to check whether that phone import ID still exists on the deduplicated sheet.
If not, that number was removed during the deduplication and is a duplicate. Those are the ones you're going to want to include in your import and then remove with the plug-in.

Debby Herold · May 2023

Thank you @Alan French … it seems a bit intimidating but I'll give it a try.

Alan French · May 2023

@Debby Herold happy to talk you through it on a video call or write out more detailed instructions if needed! ?

Alex Wong · May 2023

@Debby Herold
Great info Alan has already provided, here's just a few “breakdown” so hopefully make it “easier” to consume:

The Delete Phone plugin can either delete ALL phone/email of a certain “Phone/Email Type” or delete all phone/email that has a “blank” phone number/email address.
It is probably easiest, (without dealing with adding Phone/Email type into configuration by using the “Delete blank phone” option
So if you are to go this route, all you need to do is “identify” which specific phone/email record you want to delete, and use the “import phone" function to change the phone/email record to blank (by using ^ character as the phonenumber/emailaddress field to update to.)
Once you have updated all the ones you want to get rid of, then you need to run the Delete Phone plugin using the delete blank phone option and make sure no one is logged into the RE database view. (best done at off hours)

df6cabbb7c0401d27a18e58408e5e11e-huge-im — You can do All records or use a constituent query to do a subset first

0d0a398a81e708f2481bf67145176725-huge-im — At a minimal, you output these fields to create import file. You MAY not need to care about any phone/email that is already “inactive” as they normally won't show up anyway, so you can filter and delete all the inactive=yes first

641391bf2fc5a6faa2fd1b3cc3088543-huge-im — Assuming this is the resulting file and you are ready to “blank” out the email (PhoneNum), see next screenshot

38fad4edc8657854db18dc7fb3df5e1b-huge-im — enter ^ replacing the dup email in the PhoneNum column for the dup you want to blank out (removing row 27 and row 94 as you are not making change to the email you keeping).

Debby Herold · May 2023

Thanks so much @Alex Wong!

Duplicate email and phone entries for a Constituent

Comments

Categories