4

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.

3

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"]

screenshot of API explorer with filled-in fields

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.)

5
  • Thanks Noah, this looks like a great solution. I can't get it to work though. I've pasted what I have in the next comment. It seems to always return nothing. For testing, I have a group with one contact who definitely shares an email with someone else, but I've tried other groups as well. I'm on 5.28.4, if it matters. If you have any idea what I might be doing wrong here, please let me know. – Lars SG Dec 23 '20 at 18:02
  • CRM.api4('GroupContact', 'get', { select: ["contact_B_email.contact_id"], join: [["Email AS contact_A_email", false, ["contact_id", "=", "contact_A_email.contact_id"], ["contact_A_email.id", "=", "contact_A_email.id"]], ["Email AS contact_B_email", true, ["contact_A_email.email", "=", "contact_B_email.email"], ["contact_A_email.contact_id", "<>", "contact_B_email.contact_id"], ["contact_B_email.id", "=", "contact_B_email.id"]]], groupBy: ["contact_B_email.contact_id"], where: [["group_id", "=", 443], ["status", "=", "Added"]] – Lars SG Dec 23 '20 at 18:02
  • I can confirm, after looking at the code history, that there were significant changes to APIv4's handling of joins in 5.29. I'm editing my answer to make clear what version I was testing this solution on. – Noah Miller Dec 23 '20 at 19:18
  • Thanks. I'll come back to this when we upgrade to 5.29. – Lars SG Dec 23 '20 at 20:45
  • Very cool use of the API explorer! – Coleman Dec 24 '20 at 2:22
0

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

3
  • There's definitely some grey area here. I think in many cases, we probably don't want to ask people whose partners have donated to donate, but in some cases we do (so for C&D, sometimes yes, sometimes no). I'm not sure I can figure that out programatically, so I figure it would be best to at least subtract all the people with the same email address, though I would consider removing all with relationships instead. – Lars SG Dec 17 '20 at 3:24
  • For A & B, it's mostly because A will think we're hitting them up again. We do use tokens to insert their first name as a greeting, but I think when people see an email at a shared address, they really just see it as us asking again and aren't thinking, oh, well, that's OK, because it has a different name. In other words, people have an expectation that their email address will be taken off the list after they donate, but they don't have an expectation that their partner's email will be taken off the list. When the partner's email and their email are one and the same, we run into this issue. – Lars SG Dec 17 '20 at 3:27
  • In other words, people have a mental model of our database as a list of email addresses, while the model we're actually using is contacts that can share an email address—and that's where we run into trouble. – Lars SG Dec 17 '20 at 3:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.