Argh staging is down so I can't see the various calculated tables

Using this ticket as a mental notepad:

I did some digging and found that the secondary email exists twice in our DB, which might be a factor.

First record:

id: 68124209
created_date: 2025-07-25
source: Silverpop Added by WebForms
is_delete: 1
is_primary: 0
do_not_email: 0
do_not_phone: 0
do_not_mail: 0
do_not_sms: 0
is_opt_out: 0

Second record:

id: 39778356
created_date: 2019-12-14
source: online donation
do_not_email: 0
do_not_phone: 0
do_not_mail: 0
do_not_sms: 0
is_opt_out: 0
is_deleted: 0
is_primary: 0

I believe there are 35 affected contacts

SELECT c.id, MAX(c.modified_date) FROM silverpop_export_staging s LEFT JOIN civicrm.civicrm_email e ON s.id = e.id LEFT JOIN civicrm.civicrm_contact c ON c.id = e.contact_id GROUP BY e.email HAVING MIN(is_deleted) = 1;

+----------+----------------------+

35 rows in set (17 min 20.338 sec)

Actually it is less than 25 - the cluster modified date of 13-08-25 were ones that would have been removed overnight -

I ran a one off query

DELETE s FROM silverpop_export_staging s  INNER JOIN civicrm.civicrm_contact c ON contact_id = c.id WHERE c.is_deleted = 1 AND c.modified_date < DATE_SUB(now(), INTERVAL 10 DAY) ;

That picked up the contact & 59 other rows - I expect her to move onto the suppression list overnight - I can't see why she would not have been removed in late July as the script is trying to & as seems to work for most

Note this issue affected 2-3 dozen other contacts

@Eileenmcnaughton, I can confirm that the affected donor is now on the suppression list.

@SHust, this donor should no longer receive emails to their secondary email, as expected. Let us know if it comes up again!

@Eileenmcnaughton, will the affected donors' secondary email be opted out? I checked a few of the CIDs from the list you shared above, and they are still appearing as opted-in at Acoustic!

@Eileenmcnaughton, below are a few CIDs I spot checked, and the secondary email is still not opted out at Acoustic:
CIDs where the secondary email is still opted in at Acoustic: 66444930
CIDs where both emails remain opted in, even though the secondary email wasn’t saved during the merge (primary is visible on the CID summary page, and the secondary can be found at Acoustic opted in): 67478837, 66068936, 66434235, 64987363

Change #1180709 had a related patch set uploaded (by Eileen; author: Eileen):

[wikimedia/fundraising/crm@master] Fix is_deleted failing to update on delete

https://gerrit.wikimedia.org/r/1180709

So one thing I noticed is that the last modified date was not updated on the first deleted contact I investigated - this seemed to possibly play into it & I figured out how to address that

https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/1180709

I dug into the first one of the new ones & I can see how the out-of-date modified_date could lead to that contact not being added to the suppression list

Thanks for the update, @Eileenmcnaughton. Please let me know if there's anything we need to do differently when merging to avoid issues!

Change #1180709 merged by jenkins-bot:

[wikimedia/fundraising/crm@master] Fix is_deleted failing to update on delete

https://gerrit.wikimedia.org/r/1180709

@Eileenmcnaughton and/or @jgleeson, today, CID 4420172 received email #2, even though they donated on 09/16. The message was sent to their 3rd email on file, which shouldn’t have happened.

That email address is active in Acoustic and is also listed as the primary email for an older CID (12922062) under a potential family member’s name, who last donated in 2015 and has not received communications from us.

Could you please look into why the merge process didn’t opt out all non-primary emails in Acoustic? We’re concerned this issue may be affecting others as well. Thank you!