How to exclude contacts from a mailing who share an email with a group of contacts?

Question

If I'm sending out emails asking for donation, I exclude all the contacts who have donated recently with a smart group. Unfortunately, because we have many contacts who share an email (generally couples), people who donate can still receive an email asking for a donation, because only one member of the couple has a recent donation and is in the smart group. The other contact receives the email as normal.

How do other people deal with this problem? Is there some way we can find all contacts who share a primary email with members of a group and exclude them from a mailing as well? We don't want to exclude everyone with a relationship of partner or spouse as we still want to send emails when the email address is different.

Answer 1

You can find all contacts who share a primary email with members of a group, and save those contacts to a Smart Group, using Civi's APIv4. This solution was developed on CiviCRM 5.32.2 and may not work on previous versions.

Navigate to the API4 explorer within your Civi. Then configure a query as in the screenshot below, or paste the following into your URL bar after the word "explorer" and hit enter:

/GroupContact/get?join=[["Email AS contact_A_email",false,null,["contact_id","%3D","contact_A_email.contact_id"],["contact_A_email.id","%3D","contact_A_email.id"]],["Email AS contact_B_email",true,null,["contact_A_email.email","%3D","contact_B_email.email"],["contact_A_email.contact_id","<>","contact_B_email.contact_id"],["contact_B_email.id","%3D","contact_B_email.id"]]]&select=["contact_B_email.contact_id"]&where=[["group_id","%3D","18"],["status","%3D","Added"],["contact_A_email.is_primary","%3D","true"],["contact_B_email.is_primary","%3D","true"]]&limit=0&groupBy=["contact_B_email.contact_id"]

Note you'll need to select your "donated recently" smart group where I have "Email List - CA" in the screenshot. Also, if you'd like to widen the search to include non-primary email addresses, just delete the "where" clauses that refer to "is_primary".

What this is doing is:

1. GroupContact... get... Where group_id = x and status = added: Search for members of a particular group (in this case, it'll be your "donated recently" group).
2. Join Email AS contact_A_email...: get those recent donors' email addresses.
3. Join Email as contact_B_email...: get email addresses that are the same as the ones we just found, but belong to different contacts.
4. Select contact_B_email.contact_id: return the contacts connected to those matching email addresses.

Once everything is set up, you can click the Save button and create your smart group. Once you have that, you should be able to exclude these contacts from mailings.

(P.S. There's a quirk about APIv4 which forces me to include the "x.id = x.id" clauses in the joins. I might even call it a bug -- so maybe it will be fixed in future versions.)

Answer 2

I don't have the solution, but we sometimes have similar issues.

Just so I'm clear, let me lay it out for my simple mind..

A and B are two contacts in a family unit, and they have the same email address; C and D are two contacts in a family unit, and they have different email addresses

For A and B: When you get a donation from A in response to a solicitation, you want to not solicit B with follow-on solicitations because the family already gave, or is it because the two contacts really aren't distinguishable in an email solicitation and A will think you're hitting her up a second time

For C and D: when you get a donation from C in response to a solicitation do you want to: -- not solicit D with follow-on appeals because their family unit already donated OR -- solicit D with follow-on appeals because they have a unique email, and maybe both spouses would want to contribute