Searching constituents with duplicate addresses

Millie Chen

Is there a way to search or run a report for constituents who have the same addresses as each other? For example, a couple or a household with two or more members living at the same address are not registered in Altru with those primary relationships. I know the full/incremental duplicate constituent report/process finds constituents with similar names and addresses, but nowadays many people keep their own last name while living with a partner, so Altru doesn't pick up on this.

This would help immensely when I only want to send one letter per household for appeal mailings.

Margaret Matteson

The best I've come up with is exporting a query and looking at duplicates in Excel using conditional formatting. Then I create households or try to puzzle out who actually lives where.

Millie Chen

Thank you!

What are the parameters you would set up for the ad-hoc or smart query? E.g. Primary address is equal to not blank?…

Margaret Matteson

I have a couple different processes. Every month, I run a query on constituents added by webforms last month. These constituents typically need a lot of clean-up anyway, and they are prime suspects for people living at the same address but not in the same household. One thing that is tricky and you have to sort of decide on a case by case basis, is that if I buy tickets for my three best friends to a Scheduled Program, the friends will each get constituent records populated with my address even though we don't live together. So that's something to think about. Sometimes I just delete the address off the records because we really just don't have their mailing address, just the person who purchased the sales order.

Then, after that, I would just prioritize the records you mail to the most. So I'd review a typical mailing list and take that as an opportunity to clean things up. I usually pull mailing lists a few days before they're due and take it as an opportunity to look for people who should be in the same household. If they have the same last name, I create a household. I do cursory google search (a good idea is to search for “John Doe and Jane Smith wedding registry”) or run a Wealth Engine search to see if they're actually living in the same household (both on the deed of a house for example). It's time consuming but I just do my best.

Millie Chen

Thank you so much for the detailed response! I really appreciate it. Hopefully in the future Altru will come out with a feature that will make this process easier for us.

Chris Nungesser

Hi @Millie Chen!

I agree with @Margaret Matteson. Exporting to excel and using conditional formatting is good. You may want to manipulate your data a little bit to try to attack the problem from multiple angles. Meaning that you might choose to only review the first X-number of characters in an address. Or removing the last word from the address line 1 field. Or maybe doing some find and replace work before doing your matches. Basically, just pointing out ways to avoid missing matches like “123 E. Main St.” and “123 East Main Street”. That those are the same, but they won't register as matches in excel since they aren't exactly the same text. I hope that makes sense on some level.

With regards to your query, you might want to make sure your query includes Address End Date is blank (basically if there is an End Date then it might be an old address).

You'll probably want a way to work out if the folks are in the same household. This way you aren't reviewing scenarios where the people are already in the same household. So including a person's HH lookup ID could be good. I'm not sure if you could do this in the same query or if you'd want to do a Household Export and then pair them up in excel using a match/index formula or X-lookup. I can go into more detail about how to do that if you aren't familiar with that process (or we could jump on a quick zoom call and share a screen, it might be easier to see it if you aren't familiar with the process).

Chris ?

Margaret Matteson

You can set up a query to exclude anyone who is in a household already (using “Household Belonging To \\ Household Record ID is blank”) and you can have criteria like “Primary address is not Do Not Mail”.

Regina Holten

Would searching for duplicate emails be fast? I was curious more for your web form origination query. I'm certain lots of dupes and clean up need to be done on this…how do you differentiate b/w children and adults - this is probably more a problem for us in that we at most ONLY list two adults in a household. But of course five kids get their own record and are not tied into one household. I am hoping it isn't as scattered a job as I keep thinking it is…

Gail Vincent

The other method I use in Excel is to Find/Replace parts of addresses that I want to standardize (Street to St, etc.), then sort the entire list by address. This brings similar addresses close together where I can see them. I also do the conditional formatting for duplicates so that I don't miss any.