Address Line Global Cleanup

Bob Wiebe

I would welcome some advice on a cleanup task.  In our addresses we have been inconsistent with the use of the abbreviation for Avenue. We have about as many “Ave”’s as “Av”’s. I am looking for a way to change all the abbreviations to Av without going through one by one. Here are my thoughts so far

• A global change would have a hard time avoiding changing the spelling of words which include the syllables in question, e.g. Lavin Rd to Lavein Rd
• I could pull a list using an Excel Formula of only the addresses ending in Av, and could probably make the change in Excel and import them back in, but I am not that comfortable with importing.

Is there a way to replace only the “Av’s” at the end of the line?

 

Elaine Tucker

I would do the change via import, as the address field is not an option in global change. Create  a query for the records affected. Then create your import file from the Import screen in RE. Dump into Excel or Access, and then import back in. Just be sure to include the address import ID in the export.

Jen Claudy

I personally would export everything, find a mailhouse (the one I use could probably do this for you, since it would be all electronic anyways) to do a NCOA screening (ask for a 4-year screening, not 2) and import the results back, whether or not there's a change.  This *should* give you clean, USPS-standards, abbreviations and formats (but you can request not to get your data back in all caps).  Bonus of the NCOA (National Change of Address) updates.  As Elaine said, be sure you include Constituent ID (or Constituent Import ID) AND Address Import ID.  Otherwise, importing back to RE is much more difficult.

William Winkler

I agree with Jennifer! We frequently have the mail house do data hygiene on lists. This usually involves NCOA check, CASS formatting, adding salutation based on gender. Oh! And check for deceased constituents. It is a small price for the time it saves.

Ryan Hyde

Doing this through a mail house would be very wise. You could also pay for AddressFinder in Raiser's Edge itself - I don't know if you can pay for a one-off process though. If you're hosted and were upgraded to 7.94 against your will, you should have a free instance of AddressFinder which would fix the problem for you in much the same way as having a mail house doing an NCOA report for you would, and it's more automated, so that's nice.



If you can't afford it, then exporting all affected addresses to Excel and cleaning it up there would be best practices. I would first make sure that there aren't any addreses with "av" somewhere in their aprt from the word "avenue" or one of your abreviations. I'd then do a find and replace of "Av" for "Ave," then a global replace for "avee" for "Ave" (because anything that was "Ave" before th first change would now be "avee", and then a global replace for "Aveenue" for "Ave" (for the same reason as the previous change). That's just thinking off the top of my head though. There may be a cleaner way to do it. 

Jen Claudy

As Ryan mentioned, you can use BB's AddressFinder...but be aware that it will only check one address per constituent record.  I export all addresses, delete those that are Address Type: Previous Address, and have the remainder of the list screened.  We do this every 90 days so we can use our bulk mail permit without worrying about when the data was last screened.  It costs us $25 each time.



As Eddye mentioned, there is a Deceased Record screening (also thru USPS, I believe), but that cost more thru our mailhouse because they don't use it for anyone else and couldn't spread out their expense very much, so we've only done that once.

Faith Murray

I suggest the NCOA as best practice. However, your post is unclear whether you are changing to Av or Ave; NCOA will change your Av to Ave, because Ave is what the post office uses.



If you are set on using Av instead of Ave, or if you plan to go the cheap route and not pay for NCOA you will need to import and edit in Excel. In Excel you can reduce lookalike Ave's by selecting only the Address 1 column and include spaces in your "find and replace" command. Also, under "Options" on the Find box, select "Match case" so that only Av with a capital A will be isolated in the search. Example:



Find " Av" (note the space between the first quotations and letters)

Replace with " Ave"



You will still have to watch for words like "Avenida St", but at least it will reduce incidents by ruling out "Lavern St". Hope this helps.

Bob Wiebe

Thanks for the comments to date. In Canada I don't believe we have an equivalent to the NCOA, so the suggestions regarding using and manipulating within Excel are especially helpful.

Ryan Hyde

You do have a national NCOA database (National Change of Address), but I'm certainly not sure what the laws pertaining to mailhouses giving you an NCOA report. 



Here's some info: https://www.canadapost.ca/cpo/mc/business/productsservices/atoz/ncoa.jsf



And it looks like Canada Post offers data cleaning services directly: 

https://www.canadapost.ca/cpo/mc/business/productsservices/dms/datamanagementservices.jsf

Debra Holcomb

Hey Bob,



Just to provide a different perspective. . . .  Does this matter to the donor?  Will it help you raise more money (or are you losing gifts because of it)?  Is the address still deliverable?  Is it affecting your ability to provide reports?



If the mail is being delivered corrrectly and donors aren't complaining, I would put my resources towards a different project and not worry about this detailed level of consistency.  What's the benefit of having them all consistent?  The risk of creating new errors seems too great when there really isn't a problem (unless the mail isn't being delivered).  



Debra

Martie Kaczmarek

I would encourage you to purchase Blackbaud's Address Accelerator. It is not expensive and is always available for your use. This product verifies that you entered a correct address, meaning it is a valid address (not confirmation that it is the person's current address) and it edits the address to USPS postal preferred format. So it will change all of your Av or Ave or Avenue to AVE. You don't have to worry about it changing St. John Avenue to Street John Avenue. It also enters the DPC, CART, LOT, and County.



You can use this feature on one address or you can run a large group through the software.



It is worth the cost, especially given how much staff time it would take to do the clean up.  Just note, this is not an NCOA tool, but useful nonetheless.

Bob Wiebe

Debra Holcomb:

Hey Bob,



Just to provide a different perspective. . . .  Does this matter to the donor?  Will it help you raise more money (or are you losing gifts because of it)?  Is the address still deliverable?  Is it affecting your ability to provide reports?



If the mail is being delivered corrrectly and donors aren't complaining, I would put my resources towards a different project and not worry about this detailed level of consistency.  What's the benefit of having them all consistent?  The risk of creating new errors seems too great when there really isn't a problem (unless the mail isn't being delivered).  



Debra

Thanks for the good reminder to make sure data work is end driven, not an end in itself.  My interest arose because these little abbrevations, when internally inconsistent, can reduce one's effectiveness when checking for duplicates. That in turn leads ot higher mailing cost, or annoyed recipients.