Alternative Method for NCOA update

Robert Viggiano

We're running RE 7.92.5508, but do not have AddressFinder in our support contract. Our mail house just returned an NCOA file with over 400 address changes. Is there any other way to import these changes into RE and update the addresses in the constituent records? Thanks.

Melissa Graves 2

Robert Viggiano:
We're running RE 7.92.5508, but do not have AddressFinder in our support contract. Our mail house just returned an NCOA file with over 400 address changes. Is there any other way to import these changes into RE and update the addresses in the constituent records? Thanks.

Unless you submitted to the vendor and they returned BOTH the constituent Import ID (or Cons ID) AND the Address Import ID to have in your file - this will be very difficult. If you do not have both of these IDs then the questions to ask include:

• Do you expect to put this address in as primary and change the old one to former?
• If an address is linked, do you want the linked addresses to also update in the same fashion?

Unfortunately, if the answers to the above are yes, unless you have Import-O-Matic, you may need to do these manually. Frankly, AddressFinder is much less expensive than IOM so I would invest in that for future NCOA runs if you possibly can.

As far as a possible process to get these in. You COULD just go one by one and update them by hand (400 is not that bad) OR add these IDs to the import file (export all records and use VLOOKUP is one easy way). But even if you have these IDs the importing process is twofold. You have to a) import the OLD address as a former address on these records using a brand NEW address import ID (not the existing one) THEN upload the new address using the OLD address import ID so it can update the record and all linked addresses.

IMHO, AddressFinder is worth the price not to have to go through this hassle.

Nicole S

Melissa Graves:

Unless you submitted to the vendor and they returned BOTH the constituent Import ID (or Cons ID) AND the Address Import ID to have in your file - this will be very difficult. If you do not have both of these IDs then the questions to ask include:

• Do you expect to put this address in as primary and change the old one to former?
• If an address is linked, do you want the linked addresses to also update in the same fashion?

Unfortunately, if the answers to the above are yes, unless you have Import-O-Matic, you may need to do these manually. Frankly, AddressFinder is much less expensive than IOM so I would invest in that for future NCOA runs if you possibly can.

As far as a possible process to get these in. You COULD just go one by one and update them by hand (400 is not that bad) OR add these IDs to the import file (export all records and use VLOOKUP is one easy way). But even if you have these IDs the importing process is twofold. You have to a) import the OLD address as a former address on these records using a brand NEW address import ID (not the existing one) THEN upload the new address using the OLD address import ID so it can update the record and all linked addresses.

IMHO, AddressFinder is worth the price not to have to go through this hassle.

As Melissa says, AddressFinder is very worth it.  Without it, the process for updating these addresses is layered and you need to have to import ID (or constituent ID) in the CSV file.  If you don't then I don't know what to tell you aside from manual.

BB26213 (How to add a new preferred address, update a linked spouse (or relation) with the new address, and retain the old address as an alternate) offers an over-simplified solution.  Before you try this, you need to take into account things like to/from dates, send mail to this address (for old address I assume no?), updating the address type, or any other steps that your organization updates during an address change.  It is not for the faint of heart.

Shiri Pressman

Nicole S.:

As Melissa says, AddressFinder is very worth it.  Without it, the process for updating these addresses is layered and you need to have to import ID (or constituent ID) in the CSV file.  If you don't then I don't know what to tell you aside from manual.

BB26213 (How to add a new preferred address, update a linked spouse (or relation) with the new address, and retain the old address as an alternate) offers an over-simplified solution.  Before you try this, you need to take into account things like to/from dates, send mail to this address (for old address I assume no?), updating the address type, or any other steps that your organization updates during an address change.  It is not for the faint of heart.

Hi,

We're looking into purchasing Address Finder for the NCOA--does anyone have any issues with running NCOA when it comes to donors who have multiple residences/Seasonal Addresses? Since we're based in South Florida it is hard to keep track of these addresses especially during the summer and fall 5/1-10/1. Does anyone else have these issues? Also, what is your strategy especially when you're running the NCOA.

Your thoughts and input would be greatly appreciated.

Thanks!

Marlena Mason 2

Do any of you have experience using an outside vendor instead of AddressFinder?  The more research I do, the more I think it makes sense to use AddressFinder, but i've been told it's cost prohibitive.  From all the different things that we need to consider in order to make sure the address processes correctly via the import updates makes me think that it would be money well spent, b/c the staff time it has taken to get close to discerning all of the special considerations has been extensive.



The original file we sent to the vendor to review our data did not have import IDs or relationship info, so we need to generate another file, however, i'm at an impasse on what the two imports i'm looking at using to update (Constituent Address and Individual relationship address imports) these records will actually do, based on the various things i need to evaluate on each record.  I called BB support and they haven't told me anything new, but have underscored my fears with going this route.  I don't know if that's to steer everyone toward AddressFinder - but at the same time, i think AddressFinder is a good tool for this and one we need to use.



I about exhausted the support rep on all the different scenarios i was explaining that i needed to evaluate in order to make sure i'm processing the record in the right import, so I won't bore anyone with those details unless you want them.  As you can imagine, our data is far from clean and has so many issues.



I am really trying to build a case for the use of AddressFinder over an outside vendor - b/c of the process involved to get the records updated - and would appreciate anything anyone is willing to share.



thanks!

Nicole S

As a general rule, I try to include the IDs every time I have an export.  You never know what you'll want to import back in to RE.  If you include the IDs and instruct the mailhouse to retain them when they run NCOA, you can import them back in to RE and keep all of the relevant info.  at least for 7.93 -- I have not upgraded to 7.94.  However, it is not for beginners.  You need to be very confident in your import skills before you try it.



I used to do this in between AddressFinder updates.  But then we got to where we had fewer than 100 updates so we stopped. 



It can be a complicated import to set up, but once you have it it's easy to recreate for subsequent runs.  AddressFiner is definitely easier, but it's not an option for everyone.  I used it for years and loved it, but then I moved to an organization that didn't want to spend the money on it.  (Pennywise, pound foolish, if you ask me.)



I have not worked with or really looked at other vendors.  I think the convenience of having a system integrate with RE is worth the cost.  If you are already using a mailhouse and are confident in your RE skills, then you can try using that.  A quick google search shows some options -- http://www.melissadata.com/dm/data-services/ncoa.htm -- but depending on the size of your database you may not be saving any money over using AddressFinder.  It looks like you could end up paying as much as $3 per returned address!  And, of course, you would still need to update the addresses with an import either way.

Marlena Mason 2

Nicole, thank you so much for your reply.  What we're doing is actually different than going through a mailhouse - we're working with a vendor that does address cleanup and NCOA Link.  They are not producing mail files for us.  The IDs were exported with the original file that was sent, however no relationship info was exported (and neither were any import IDs), which we need to evaluate the data, so people can be updated correctly.  And even still, that's only part of what we need - since there's no such thing as a table that shows what addresses are shared (beyond the field "Link?" that only provides yes/no info) that can be queried or exported, i have to think our best first step isn't to go down this path.  it's to update our relationships.



As you mention, creating the import, even if our data is perfect, would be quite the challenge, and while i am reasonably proficient with import, i haven't come to a comfortable place as to how we would actually approach this, knowing that our data is in a degraded state.  However- do you have any suggestions for building the import that you would be willing to share?



I am going to push again for AddressFinder, as I know it doesn't offer everything we need (what does?), but it comes so much closer to managing the process, which is already frought with potential issues.

Nicole S

My earlier response --

BB26213 (How to add a new preferred address, update a linked spouse (or relation) with the new address, and retain the old address as an alternate) offers an over-simplified solution.  Before you try this, you need to take into account things like to/from dates, send mail to this address (for old address I assume no?), updating the address type, or any other steps that your organization updates during an address change.  It is not for the faint of heart.

Try it in the sample database or with one address as a test.  I can look for the import file I used in the past and I will share it with you if I find it.

Peter McKay 2

Does anyone have advice for how to handle "invalids"?  I find that many of the addresses I get back from the mail house that are tagged invalid just lack a last-four zip code or have nothing discernably wrong with them at all, and they've been given no Return Code. What's the most efficient way to update these?  I'm afraid to just mark them invalid.



Thanks! 

Faith Murray

I usually use the NCOA from our mail houses, rather than AddressFinder. Since we don't create mail merges off our Relationship records anyway, this is a no-problem for us. I just include the Constituent ID in the mail list and request the mail house to retain the ID# in the returned files. Then I create an Import file, upload the correct columns, and import.



We always keep old addresses in the system, for reference, becuase let's face it, Big Data is only accurate most of the time! If a new address proves funky, as has happened, we like to keep the old one on file. So, we don't require Address Import ID, because that would only be required if you were over-writing the original Preferred or Linked address. Instead, we simply import the new addresses, being sure to include fields for Source and Date From, then create a query from the import file, and - using that query - will export and import once more to clean up the "Send Mail", "Preferred Address", and "Start/End Date" fields of the new and old addresses (this time including Address Import IDs).



I suppose that if linked records were an issue, you could simply 1) import the new addresses without an Address Import ID, and not Preferred, but indicate that it is the new address by coding its Source; 2) create a query from your import; 3) export these Constituents once again, but include new and old address with both sets of Address Import IDs, and 4) swap the Address Import IDs for both addresses (done by adjusting the column headings). Haven't tried this out, but it seems logical, and you coudl get your new addresses uploaded onto your relationship-linked Address Record. Be worth trying out with one or two Constituents to see if it works.



Peter, for Invalid Addresses, these are worth a manual review to discern if they have repsonded to your mailings in the past year. We have had a few addresses marked Invalid which, in fact, were still perfectly good and belonged to long-time major donors. Definitely don't want to lose those! If, after manual review, you decide they are good to go, you can follow the same process to import, but then from your import query follow up with Global Change to mark the records as Inactive or No Valid Address, delete Attributes, etc.



Honestly, I don't think Imports are all that scary. You just have to be willing to try them out and learn, and of course, have your IT people back up the database first in case you made a mistake. I did make a mistake once. That back-up saved my ***. If AddressFinder is anything like PeopleFinder, you still have to know your Imports process fairly well in order to download the new data, so one may as well take the time to learn.

Nicole S

I have used   more than once to update addresses returned by the mailhouse through import. 



As far as marking the addresses the mail house denotes as invalid... You could try asking the mailhouse to send anyway and see if they are returned.  Or it is possible that they are missing a suite or apartment number.  I've had donors not give me their apartment number because they have some kind of community drop box and everyone just gets their mail from one bin, but they still techinically have a unit number and the address can still get flagged as invalid with out it.

Rachel Webster

Peter McKay:

Does anyone have advice for how to handle "invalids"?  I find that many of the addresses I get back from the mail house that are tagged invalid just lack a last-four zip code or have nothing discernably wrong with them at all, and they've been given no Return Code. What's the most efficient way to update these?  I'm afraid to just mark them invalid.


Thanks! 

Peter I know this answer is coming long after your original question but I recently went through this myself. I found that most of these "invalids" came from data entry errors. There might be only one number in the street number that needs to be editted. I used a combination of the White Pages and looking at my local deed/property search page to find the correct addresses. After you get a large chuck of them done, it becomes more manageable to keep up on. 

Jen Claudy

I developed a system, using MS Access, to enable me to have complete control over our NCOA screenings several years ago.  At that time, we had AddressFinder, and it cost us $2400 for quarterly screenings of one address per record.  Our mailhouse charges $25 per screening.  Since we were, at that org, mailing out 3-4 jobs of 500-2000 pieces each every month, we wanted to screen our database more frequently.  So I set up my system (it's been tweaked since, many times) and we went from $2400 for 4 screenings to $150 for 6 screenings.  I am at a different org now, and have AddressFinder included in our NXT package, but I still prefer my system.  It flags addresses that I know I need to manually check (i.e. Seasonal) and screens every current address (I exclude those of Address Type: Previous Address) in the database.


I can also access the data file I sent out to the mailhouse and the file they returned from every screening, so if something goes awry, I can fix it...even though it would be a big pain to have to do so.  I personally don't care for processes that are changing data in the database behind the scenes when I don't know exactly what is happening or how it's happening, and can't monitor or alter the process.