Multiple constituents at the same address

Carolyn Moatz

My boss is looking of a list of our alumni who we still have as living with their parents. Not all addresses that are the same are marked as shared, so I can't use that. Does anyone have any ideas of how to do this?

Joanne Felci 2

Carolyn Moatz:

My boss is looking of a list of our alumni who we still have as living with their parents. Not all addresses that are the same are marked as shared, so I can't use that. Does anyone have any ideas of how to do this?

I'd start with the Duplicate Constituent Managment Tool under Admin - this will create a query of potential dupes


You can then sort results by address amd export into excel


If you do not believe this tool would help, depending on the size of your database you might want to do an export of all records and then use conditional formating to highlight duplicates


Good luck!

Aaron Rothberg

Hi Carolyn-


My approach would change depending upon how the data is stored in your database, but assuming you have three constituent records, one for each parent and one for the child, I would output every constituent in your entire database (you could also constrain the query by Constituent Code to get just parents and children) with their Preferred Address Street 1, City and State. I would then concatenate Street 1 with City and State so that one Excel cell will contain [Street 1, City, State], then sort by that column, then use Excel's Exact function to find folks at the same address. If you include the primary Constituency Code as a column that will help you locate parents and students that live at the same address versus two parents where there is no child. Add in some conditional formatting with colors to quickly find your Exact matches.


The formula in cell B98, for example, would read =EXACT(A98,A97).



 

Alan French

Aaron Rothberg:


The formula in cell B98, for example, would read =EXACT(A98,A97).

Unless the address data is incredibly clean then I might not use Exact here; it's case-sensitive, so it wouldn't recognise "1 Canal Street" as being the same as "1 canal street" and it may therefore miss some duplicates. The formula "=A98=A97" would give the same true/false result without being case-sensitive.

Jill Freidmutter

If you're data is not super clean you might want to do looser comparisons. Once the data is downloaded to Excel, you can concatenate the first 6 or 8 characters of Address Line 1 and the first three characters of city. Then you could match records like: "1020 West State Street" and "1020 W State St".

Jen Claudy

You might consider using AddressAccelerator or having your entire database screened with NCOA and update the address formatting.  Then your Excel formulas will work better, and your database will be cleaner!

Alicia Barevich

All great ideas. My caution with using NCOA would be shared addresses. If the child still "shares" their address, but NCOA picks up that they moved, the parents' address could be changed in RE as a result. Before running NCOA, I click through and clean up any shared addresses I can to minimize these issues.

Alan French

Alicia Barevich:

All great ideas. My caution with using NCOA would be shared addresses. If the child still "shares" their address, but NCOA picks up that they moved, the parents' address could be changed in RE as a result. Before running NCOA, I click through and clean up any shared addresses I can to minimize these issues.

I loathe shared addresses for this reason, and it's not just parent/child relationships that are affected - for example my parents are divorced and my mum regularly receives mail for my dad, despite the fact that he's never lived at that address. This probably contradicts most people's policies but unless I know for certain that both constituents have moved, I prefer to add the incoming address as a new one rather than updating both shared addresses.

Alicia Barevich

When I manually update addresses, I am careful about making sure it is shared logically. However, NCOA doesn't have a human brain, so can end up changing any shared addresses it finds.

Jen Claudy

NCOA looks at both the address and the name at the address.  So if the child moves out of the parents' home and files a Change of Address with the post office (which many times I don't think they do, since they can still receive mail at the parents' address), NCOA should only return a change for the child's record, not the parents' record(s).  Unless the child is John Jr. and the father is John Sr. in which case the system might get a bit confused.


It does depend on whether you use Blackbaud's AddressFinder or another vendor and use RE Import.  If the second, you would want to set it up to save a copy of the old address anyways.  In either case, the updated/new address for the child should no longer be shared with the address for the parents.


In Alan's parents' situation, it's more likely that the individual lists used to send mail have been updated incorrectly.  I.e. if a married couple is in a database as a "household" and the wife is HOH, they get divorced and the wife moves, but the database doesn't get updated with a new record for the husband, so NCOA updates the record with the wife's new address.  Later, the database gets updated and the husband gets a new record, but whoever makes that change doesn't realize that the address is now incorrect.  But if that database list was shared or sold before the record was split, then all the databases & lists with the incorrect information will be incorrect until they each realize the error.  Not likely to happen.


 

Faith Murray

Jen Claudy:

NCOA looks at both the address and the name at the address. It does depend on whether you use Blackbaud's AddressFinder or another vendor and use RE Import.  If the second, you would want to set it up to save a copy of the old address anyways.  In either case, the updated/new address for the child should no longer be shared with the address for the parents.

I'm not sure on other vendors, but on the enhanced ncoa that I just imported (yesterday, yippee) the vendor output contained a separate column to identify if the move was an Individual move or a Family move, when known -- ie, did just one person move or the whole household.

Christine Cooke 2

Carolyn Moatz:

My boss is looking of a list of our alumni who we still have as living with their parents. Not all addresses that are the same are marked as shared, so I can't use that. Does anyone have any ideas of how to do this?

Have had this challenge a few times and my director has wanted to cut down on the numbered mailed of certain publications/items.  The HoH is given an additional Addressee that is for the Household:  The Smith Family or The Smith Jones Family (if each parent uses a different last name).  And the children still living at home are marked with a Solicit code of Do Not Mail-Duplicate in Household.  For those times when it is not necessary for every person to get their own, like a quarterly magazine/newsletter and the like, it works great and significantly cuts the number mailed, thus the cost of printing and postage.  And then, when you have other pieces, like appeals or things specifically to alums you can ignore the Solicit Code and include them in the list.