De-Duping Exported Mailing Lists from multiple queries

Melissa Eschweiler

Hi!  We use queries to identify multiple sources for a mailing list.

We use a third party mail house for large mailings and need to provide 1 list.

I am manually combining queries exported to excel spreadsheets.

I am using the Head of Household to eliminate spousal duplicates in individual queries-export.

My hiccup is identifying duplicates between sources.

Query 1: Individuals with criteria 1,2,3

Query 2: Organizations with relationship Contact names with criteria 1,2,3

Query 3: Individuals with criteria 4


If an individual from query 1 is also the relationship contact for an orgniaztion in query 2, I am seeing lots of duplicates.

The individual name appears two separate columns in the output.  Query 1 is the Primary Addressee   Query 2 is the Relationship Contact.

Other than manaully de-duping, I am looking to see if anyone out there has had similar experiences or found a solution?

Lauren Schler CFRE bCREPro

Are you familiar with the Query Lists function?That tool would
handle this for you without your needing to dedupe outside of RE.
After you combine all of your lists into a query list, you would
then connect the query list to your export to remove non HOH.

Best,

Lauren 

Sent from my
Verizon, Samsung Galaxy smartphone

-------- Original message --------

Melissa Eschweiler

Hi

Thanks for the response!

I am familiar with query list, but wonder
since the duplicate individual names are in different fields
(Primary Addressee and Primary Relation Name) if that will
work?

 

Worth a try.

 

Best Regards,

 

Melissa

 

Melissa Eschweiler

Development Specialist

Friends of McHenry County College
Foundation

(815) 479-7529

 

Gina Gerhard

Melissa - I'm reposting this information but I think it might help with your situation of an individual who is also a contact at an organization. 

I've just copied/pasted from prior forum postings:

In Mail, there's this really great 'hidden gem' of a function -- exactly for the situation when you have the same individual on your mailing list as an individual AND as a contact at an organization.  It doesn't really have a name but here's the instructions (if you're patient to go through these steps):

• Go to Mail and create a Quick Letter.
• On tab 6: Org Address, in Step 2 hit the Contact Information button.
  • Here's where you specify which contact types you want to include.
• There's a line of text towards the bottom that says 'If a contact also receives this mailing as an individual' and you have these choices:
  • Mail to individual address only
  • Mail to contact address only
  • Mail to both addresses separately
• So you can specify here how you want to handle these 'duplicate' situations
  • BUT your decision applies to your entire mailing list (you can't pick and choose which ones you want to go one way or another, it's for ALL).
• If Quick Letter doesn't give you enough 'oomph' for your mailing, run your query through Mail with this option selected and then on the 1: General tab, choose to create an output query.
  • Then use this output query in Export to get a more robust mailing out.  But it will have done this 'special processing' for you.

I've found a few issues with this:

• If you choose to mail to both separately and the individual actually has their business address as preferred, you'll generate what essentially are two exact duplicate mailings (because they're both going to the business).
• If you choose to mail to the contact address only, you're probably pulling in the single addressee version (Mr. John Smith).  If you really want to mail to 'Mr. and Mrs. John Smith" then you should probably choose to mail to the individual address and choose the joint salutation.

I've been a very vocal advocate for making this very useful functionality also available in Export, not just in Mail. Please consider supporting this in IdeaBank with your votes:

• Make options for mailing to contacts in Mail also available in Export:  http://rediscovery.uservoice.com/forums/137015-raiser-s-edge-discovery-topics/suggestions/5874230-make-options-for-mailing-to-contacts-in-mail-also

Lauren Schler CFRE bCREPro

Hi Melissa,

 

Query List won’t dedupe your individual names and Org Contact
names. If your mail file needs are basic (ID, Name, Address) you
could accomplish this with the Quick Letter mail parameter file.
Use the export option to generate your list. There are settings on
the Org tab that allow you to select how contacts are handled if
they also exist as constituent records.

 

If your export file needs go beyond the fields that Quick Letters
provide, you’ll need to set up a system of Contact Types and/or
attributes and then use an Individual Relationship Query coupled
with an Relationship export to extract your files from RE. You’ll
be querying on contact type = xyz. You will be running your Org
list separately from your Individual list but the two can easily be
combined outside of RE. If you’ve applied Contact Type/Contact
attributes correctly, you won’t need to worry about duplicate
names.

 

Best,

 

Lauren

 

Lauren Schler CFRE bCREPro

I will totally vote for this in the Idea Bank!

 

Melissa Eschweiler

Wow,  You are genius.

Thanks I will give it a go.

 

Best Regards,

 

Melissa

 

Melissa Eschweiler

Development Specialist

Friends of McHenry County College
Foundation

(815) 479-7529

 

Gina Gerhard

Melissa - Blackbaud is the genius here for creating some really useful functionality BUT then burying it in lowly Mail (which many of us don't really use because it's somewhat underpowered).  Hope you'll consider voting for the idea to bring this great functionality more visibility so more people will know how to use it!

Lauren Schler CFRE bCREPro

Hi Gina, I tried to vote for it but got an message saying voting is
closed. L

 

Best,

 

Lauren

 

Gina Gerhard

Oh, didn't know they closed votes. Wondering if it's because they decided to not do it?  Maybe Blackbaud will respond here and clarify?

Lauren Schler CFRE bCREPro

This would be a really great addition to Export. I hear about the
need from this all the time from RE clients.

 

Best,

 

Lauren

 

Jarod Bonino

Gina Gerhard:

Oh, didn't know they closed votes. Wondering if it's because they decided to not do it?  Maybe Blackbaud will respond here and clarify?

Ask and you shall receive!


Quick clarification, Blackbaud migrated all Idea banks to a different platform about 8 months ago. The ideas are all still there, we just no longer have access to the old platform to take down the old posts. We did prevent voting but unfortunately there is no way to prevent comments. The new ideas can be found (and searched) here:


https://community.blackbaud.com/products/raisersedge7/ideas


Or by just clicking the "Ideas" tab in the Community.


I apologize for the confusion. There is a message on the main page of the old community here explaining/redirecting:


https://rediscovery.uservoice.com/forums/137015-raiser-s-edge-discovery-topics


And an announcement was posted on the blog when the work was done back in March:


https://community.blackbaud.com/forums/viewtopic/147/27091?post_id=96986#p96986


But again, sorry for the confusion!



Thanks,

Jarod Bonino

Product Manager, Raiser's Edge

Gina Gerhard

Thanks for answering this, Jarod!  

For anyone who wants to vote, here's the new link to the old idea:  https://re7.ideas.aha.io/ideas/RE7-I-87


 

Jarod Bonino:

Gina Gerhard:

Oh, didn't know they closed votes. Wondering if it's because they decided to not do it?  Maybe Blackbaud will respond here and clarify?

Ask and you shall receive!


Quick clarification, Blackbaud migrated all Idea banks to a different platform about 8 months ago. The ideas are all still there, we just no longer have access to the old platform to take down the old posts. We did prevent voting but unfortunately there is no way to prevent comments. The new ideas can be found (and searched) here:


https://community.blackbaud.com/products/raisersedge7/ideas


Or by just clicking the "Ideas" tab in the Community.


I apologize for the confusion. There is a message on the main page of the old community here explaining/redirecting:


https://rediscovery.uservoice.com/forums/137015-raiser-s-edge-discovery-topics


And an announcement was posted on the blog when the work was done back in March:


https://community.blackbaud.com/forums/viewtopic/147/27091?post_id=96986#p96986


But again, sorry for the confusion!



Thanks,

Jarod Bonino

Product Manager, Raiser's Edge

 

 

Daniel Noga 2

Gina Gerhard:

Thanks for answering this, Jarod!  

For anyone who wants to vote, here's the new link to the old idea:  https://re7.ideas.aha.io/ideas/RE7-I-87


 

Jarod Bonino:

Gina Gerhard:

Oh, didn't know they closed votes. Wondering if it's because they decided to not do it?  Maybe Blackbaud will respond here and clarify?

Ask and you shall receive!


Quick clarification, Blackbaud migrated all Idea banks to a different platform about 8 months ago. The ideas are all still there, we just no longer have access to the old platform to take down the old posts. We did prevent voting but unfortunately there is no way to prevent comments. The new ideas can be found (and searched) here:


https://community.blackbaud.com/products/raisersedge7/ideas


Or by just clicking the "Ideas" tab in the Community.


I apologize for the confusion. There is a message on the main page of the old community here explaining/redirecting:


https://rediscovery.uservoice.com/forums/137015-raiser-s-edge-discovery-topics


And an announcement was posted on the blog when the work was done back in March:


https://community.blackbaud.com/forums/viewtopic/147/27091?post_id=96986#p96986


But again, sorry for the confusion!



Thanks,

Jarod Bonino

Product Manager, Raiser's Edge

 

 

 

I'd definitely vote for this, but the link resulted in a 404 error for me.

Daniel Noga 2

Gina Gerhard:

Melissa - I'm reposting this information but I think it might help with your situation of an individual who is also a contact at an organization. 

I've just copied/pasted from prior forum postings:

In Mail, there's this really great 'hidden gem' of a function -- exactly for the situation when you have the same individual on your mailing list as an individual AND as a contact at an organization.  It doesn't really have a name but here's the instructions (if you're patient to go through these steps):

• Go to Mail and create a Quick Letter.
• On tab 6: Org Address, in Step 2 hit the Contact Information button.
  • Here's where you specify which contact types you want to include.
• There's a line of text towards the bottom that says 'If a contact also receives this mailing as an individual' and you have these choices:
  • Mail to individual address only
  • Mail to contact address only
  • Mail to both addresses separately
• So you can specify here how you want to handle these 'duplicate' situations
  • BUT your decision applies to your entire mailing list (you can't pick and choose which ones you want to go one way or another, it's for ALL).
• If Quick Letter doesn't give you enough 'oomph' for your mailing, run your query through Mail with this option selected and then on the 1: General tab, choose to create an output query.
  • Then use this output query in Export to get a more robust mailing out.  But it will have done this 'special processing' for you.

I've found a few issues with this:

• If you choose to mail to both separately and the individual actually has their business address as preferred, you'll generate what essentially are two exact duplicate mailings (because they're both going to the business).
• If you choose to mail to the contact address only, you're probably pulling in the single addressee version (Mr. John Smith).  If you really want to mail to 'Mr. and Mrs. John Smith" then you should probably choose to mail to the individual address and choose the joint salutation.

I've been a very vocal advocate for making this very useful functionality also available in Export, not just in Mail. Please consider supporting this in IdeaBank with your votes:

• Make options for mailing to contacts in Mail also available in Export:  http://rediscovery.uservoice.com/forums/137015-raiser-s-edge-discovery-topics/suggestions/5874230-make-options-for-mailing-to-contacts-in-mail-also

 

I'm getting inconsistent results with this. This morning, I ran the query for our Dinner list through mail per these instructions, then ran the output query for that through the Export we normally use for the Dinner. I checked through the results and found that one of our Foundations (we'll call them "Donor A" to allay confusion) pulled along with the Individual record linked to it via relationships--according to the guidelines above, that should not have happened.


Miffed, and assuming I might have done something wrong, I tried one more time. This time, Donor A appeared only once in the list produced via Mail--in the form of their Individual record as I had specified. I then ran this second output query through Export, and again, Donor A only appeared once as an Individual. I shrugged my shoulders, convinced that it really was my error and that I had made some kind of mistake the first time around.


I continued routine list cleanup, assured that this was the best version of the list I could pull. Imagine my surprise when I noticed that another person--Donor B--was appearing under one of their several Organization records, as well as their personal record. This should not be the case, since the two are properly linked and everything. I re-opened the file that was produced by Mail--the second one that I thought was fixed--and to my dismay, I found that Donor B only appeared once in that file, just like I had wanted. However, for whatever reason, when I ran the output query through Export, they were appearing under both in the resulting file.


So, to recap, suddenly Donor A was pulling from the Export the way I wanted--as an Individual ONLY.

Meanwhile, Donor B was pulling as both an Individual and as one of their linked Organizations, despite the fact that only the Individual appeared in the output query from Mail.


Any ideas why, or is RE: being quirky?

Gina Gerhard

DISREGARD (posted to incorrect issue.....)

Jarod - Is this user error on my part regarding this link?  I have users who want to vote!!

Gina Gerhard

Here's one idea as to what might be happening.


With Donor A, in Mail when you ran the process and created the Output query, it might have eliminated Organization A (where Donor A works) entirely from the query.

Can you check this?


With Donor B, it might have left Organization B in IF THERE WERE OTHER CRITERIA for Organization B (like if you exported multiple contacts for Organization .  Can you check to see if perhaps Organization B was left in this output query?  

• If the organization was left in, then in Export you might actually be outputting the same contact Donor B AGAIN for this organization.

Daniel Noga 2

Gina Gerhard:

Here's one idea as to what might be happening.


With Donor A, in Mail when you ran the process and created the Output query, it might have eliminated Organization A (where Donor A works) entirely from the query.

Can you check this?


With Donor B, it might have left Organization B in IF THERE WERE OTHER CRITERIA for Organization B (like if you exported multiple contacts for Organization .  Can you check to see if perhaps Organization B was left in this output query?  

• If the organization was left in, then in Export you might actually be outputting the same contact Donor B AGAIN for this organization.

 

You're pretty awesome.


Organization B does appear in the output query, and in fact, there are multiple Contacts eligible to pull from Organization B (as I set Mail to export all qualifying Contacts, not just one).


Things are still weird, though: The output query only pulled two of the Contacts, but there are three that should be included. "Print one for each Contact found" is checked, they are all the same type, they all have valid addresses and wouldn't be excluded that way, "Do Not Mail to this Contact" is unchecked--not sure why only two of the three showed up.







 

Gina Gerhard

Are the contacts all of the type that were specified to pull?

Daniel Noga 2

Gina Gerhard:

Are the contacts all of the type that were specified to pull?

All three are the same,yes--so I decided not to test this with the "Print one for each Contact found" box unchecked; if I did that, I would end up just getting one of them, and it would be whichever one RE: grabbed first. That wouldn't help diagnose the issue.

Gina Gerhard

Gina Gerhard:

Are the contacts all of the type that were specified to pull?

Sorry, just read again and you did say they were of the same type.  I would think it should include as many as are eligible.


So this whole thing with processing through Mail first may only be good if you specify ONE contact only -- if in your case you have multiple contacts, you may run into the issue of re-pulling in the contact you were trying to filter out in the first place.

• I used to do this by exporting out the contact IDs for the organization, and then doing a VLOOKUP to compare with the constituent IDs for individuals on the list. It would find matches, and then I would decide which I wanted to include - the individual or the org contact.  
• But it took a lot of time and was quite messy -- so I prefer to try to use Mail when possible to eliminate the dupes.  
• But if you are pulling multiple contacts, you may have to look at something like that process??

Daniel Noga 2

Gina Gerhard:

Gina Gerhard:

Are the contacts all of the type that were specified to pull?

Sorry, just read again and you did say they were of the same type.  I would think it should include as many as are eligible.


So this whole thing with processing through Mail first may only be good if you specify ONE contact only -- if in your case you have multiple contacts, you may run into the issue of re-pulling in the contact you were trying to filter out in the first place.

• I used to do this by exporting out the contact IDs for the organization, and then doing a VLOOKUP to compare with the constituent IDs for individuals on the list. It would find matches, and then I would decide which I wanted to include - the individual or the org contact.  
• But it took a lot of time and was quite messy -- so I prefer to try to use Mail when possible to eliminate the dupes.  
• But if you are pulling multiple contacts, you may have to look at something like that process??

 

Yup. Fortunately, I do have something in place--an Attribute that I add to both the Organization and Individual records that are linked via relationships. I make sure to include this in the output, and I can sort by that column and highlight the dupes to decide which one to send to. I liked the idea of having Mail handle this for me, but it looks like I'll revert to handling this manually. Thanks for helping me out with this, though!