Comparing External Lists to Database

Noah Wilson

Looking for ideas of how to efficiently compare a large external list to the database in order to see if anyone on it is already in the database. Our annual fund team will periodically get a large acquisition list from an external source as part of preparations various appeals. The issue arises when they have to screen that list against our database to make sure that there is no one on it who has opted out of receiving communications from us (which we track in RE). (It also helps to make sure we don't create duplicate records.) This is currently a very manual and time-consuming process and I'm trying to come up with a way to streamline it. Thank you for any ideas or insight you can provide!

Karen Diener

@Noah Wilson I've needed to do this in the past, and always had the mail house take care of de-duping between our current data and any acquisition file. Not sure if you're using a mail house for any part of your appeal processes, but they have better tools to do some matching for you.

Trying to compare data between the two will be very difficult. For example, the acquisition may have Thomas Jones on it, but your database may have Tom Jones. Addresses are very likely to have formatting discrepancies too (Avenue vs. Ave., North vs. N.) or Tom Jones may have a different address than Thomas Jones even though they may happen to be the same person.

I know that “use a mail house” isn't necessarily an easy option for everyone. The only other thing I can think of - without knowing how you store your data or how it appears on a mailing list - is to try and set up a unique field for each constituent in each dataset in your export file. For instance, export data from RE of everyone who has opted out of email. Then create some sort of unique ID that you would also be able to create on the acquisition list. For instance, last name + email address or something. You could use the concatenate formula in Excel to do this. Then set up the same formula on the acquisition list and do use VLOOKUP (the best Excel formula ever) to compare the two. Hopefully that will help!

Karen

Dan Snyder

@Noah Wilson @Karen Diener has some good suggestions, the only thing I might add is to set up an import with your acquisition list and just run the validation making sure to check off “Use duplicate criteria for new records”.

It may not catch them all, but might identify some who you can immediately remove reducing the number you need to manually review. Not perfect by any means, but cuts down some of the review and then you could do something like Karen suggested.

Noah Wilson

@Karen Diener Thank you very much for the information! I'll have to check with the annual fund team to see what options are available regarding the mail house. The VLOOKUP could be another possibility. I appreciate the insight!

Noah Wilson

@Dan Snyder Thank you for bringing up import as a suggestion. The thought did cross my mind to use ImportOmatic as a tool to try to filter the lists since it has baked-in matching features. Having you bring it up lends more weight to the idea. Thank you!

Christine Robertson

@Noah Wilson I would definitely use ImportOMatic (if you have it!) or another similar tool (such as Importacular, SMARTthing, etc.) that allows you to match to records based on other criteria than IDs.

Austen Brown

@Noah Wilson - Echoing the others and adding you can also utilize Power Automate to do some of the heavy lifting for you with comparing external lists to the database.

Noah Wilson

@Christine Robertson
Thank you for the names of additional platforms to consider!

Noah Wilson

@Austen Brown
Thank you for the recommendations! Could you please elaborate more on how you would use Power Automate to help? I'm somewhat familiar with it and Power Platform in general, but am still learning about the different modules and capabilities.

Christine Robertson

@Noah Wilson I'm sure that they can all do what you need, but Importacular has a free version that you can use if you just want to see which records are/are not in the Raiser's Edge.

Austen Brown

@Noah Wilson - You can direct Power Automate to utilize an external spreadsheet as a source and then lookup each row within RE NXT from a flow, I recommend having an output where it creates a new file of matched or unmatched constituents.

Noah Wilson

@Christine Robertson
Awesome! Thank you for the information and tip on Importacular!

Noah Wilson

@Austen Brown
I see; that's an amazing idea! Are there any resources you would recommend that would be helpful for learning more about setting something like that up?

Kimberly Dobos

@Austen Brown When you use a flow to compare the database to an external list can you check multiple data points….ie name and degree and employment. We spend endless hours comparing lists in our organization.

Austen Brown

@Noah Wilson - Blackbaud has a free online course you can go through to learn the basics of Power Automate; there are also courses available through BBU:

If you're interested in PA, but would rather someone else build the flows for you check out AutomateGenius.

Austen Brown

@Kimberly Dobos - Correct, you can utilize PA to check multiple items for you - name, contact info, etc.

Faith Murray

@Noah Wilson, I would recommend using multiple check processes. I've never used PA or Importomatic, but we've used an external vendor who specializes in merge-purge processes, and it was a huge help, but they still missed some of the duplicates. I usually use a 3-step approach: run it past a mail house, run it through Import's “Use duplicate criteria” feature, and then after all that, I also pull a query with “Last Name one of and First Name one of”, plugging in the columns from the excel file. This really narrows down the final manual double check.

Karen Diener

@Noah Wilson:

Looking for ideas of how to efficiently compare a large external list to the database in order to see if anyone on it is already in the database. Our annual fund team will periodically get a large acquisition list from an external source as part of preparations various appeals. The issue arises when they have to screen that list against our database to make sure that there is no one on it who has opted out of receiving communications from us (which we track in RE). (It also helps to make sure we don't create duplicate records.) This is currently a very manual and time-consuming process and I'm trying to come up with a way to streamline it. Thank you for any ideas or insight you can provide!

I've had to do this before in a large organization, but we used a mail house to fulfill all mailings and had them take care of it.

We ran segmented solicitation data from RE and sent it to them along with a suppression file of everyone with opt outs. They used the suppression file to bounce against acquisition files and handled it all for us. Not sure if this might be an option for you, but it was well worth outsourcing due to the volume of work. It was well before Power Automate existed, and we didn't have Omatic, so we had fewer options. Frankly, I would still probably do it this way.

Karen

Noah Wilson

@Austen Brown @Faith Murray @Karen Diener Thank you all for the excellent information and advice! I look forward to examining these possibilities in more detail to see if we can apply to them to make our processes more efficient.

Sherry Aldrich

@Noah Wilson Do you have an identifier field in both the list and RE/Nxt that you can compare or do a de-dupe on? If not are there other fields in both that you can de-dupe by? You could export from RE and combine the two (making a field of which names came from which list) and de-duper from there.

Noah Wilson

@Sherry Aldrich We unfortunately do not have an identifier across both lists. Thank you for the idea though!

Salvador Orochena

Hi @Noah Wilson, we recently started using ImportOmatic+ Cloud. This is different than the ImportOmatic Classic List Manager plugin. What is nice about the cloud version is that it can run through the list and identify the possible matches without user intervention. Ex: In Classic, you would have to be in front of your screen reviewing the process as it compares each record. In the cloud version, it will auto match and create a list for you to review. You can process some records and come back to finish the process at a later time. This is great for acquisition lists and other list you may want to compare. List Manager is also nice because it has additional functionality to manage non constituent records. ImportOmatic offers several solutions which we leverage for different processes based on functionality needed.

Noah Wilson

@Salvador Orochena Thank you very much for insight and example! We're looking more into ImportOmatic+ Cloud and some of the benefits it may bring. I do like the idea of being able to let cloud create a list of possible matches on its own and then not have to process the entire list at once.