I've decided to delay the send to go out Monday Sept 7 so there is more time to fix this issue.

[I'm going to leave the priority as "Unbreak Now" but leave it up to fr-tech to change it to "High"]

some more context provided by Kristie (and tagging in @MBeat33 if you can provide even more), this issue happened before a few years ago and a fix was put in place. could the updates to Civi have caused this fix to break in some way?

Just added another note from investigating: Looks like CID 16801314 has a matching CID and email address between civi and Acoustic, but Acoustic is listing AF_latest_donation_date as Nov 2016. Looks like this contact was not updated from the donor's Aug 26 donation, so something must be up with the civi to Acoustic nightly export.

More examples:
CID 35353995 / donated 08/25 / received email today in French
CID 7748626 / donated 08/28 / received email today in French
CID 16626014 / donated 08/25 / received email today in French

@krobinson Confirming that those three CIDs you commented do not have updated latest_donation_date criteria within Acoustic. CID 16626014 was found via email address, which had CID 26931866 instead in Acoustic.

The nightly import is not broken and running "successfully" on my side, so we are getting files from civi every night, but I think something is amiss within the data migration itself.

@KHaggard thanks for the update. Let me know if you need more examples.

silverpop_export_view_full.modified_date is not updated correctly so the final update is too narrow

Change 623855 had a related patch set uploaded (by Eileen; owner: Eileen):
[wikimedia/fundraising/tools@master] Fix bug where MAX modified date not used

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

Ok so this appears to be happening for contacts where the contact with the higher email id is not the most recently modified. I've put up a patch with uses an aggregate function to get this

Cool, thanks @Eileenmcnaughton - will we need to do a full database update tonight or tomorrow to get all contacts situated?

@KHaggard yes - we could do just the past month or so since our last one, or a full one. We'll probably just do a full one. The bug is that some contacts were not updated when they should have been, rather than any incorrect updates

Ah, ok. Thanks for clarifying and working on this. @MNoorWMF moved our next email send day to Monday, so if you want to send over the full database file tonight, it'll process as scheduled and I can check it in the morning to make sure the contacts we listed in this task are updated. Does that sounds good?

Change 623855 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Fix bug where MAX modified date not used

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

I outlined the steps / sql I'm going to use here
https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/crm/+/623694

I expect it to take up to 2 hours

It's on track - it has just started the cvs upload part

Thanks @Eileenmcnaughton I see it processed early this morning. Checking the database now.

Yay

Hi all,

We saw what looks like a related bulk email complaint today. The donor in Zendesk 802792 is in Civi with the same email address as both CID 16933895 and CID 35236198. They got an enUSC2021_E1 email today, despite donating on August 26th, 2020 as CID 16933895. The email address is listed as Home in both CIDs.

The ancient history ticket that may be related is T120897: Silverpop data inaccurate - two donors with the same contact ID. Question, is this recent donation one that the Acoustic scan should have caught?

Hi @MBeat33 - Thanks for flagging. I'm looking up this contact in Acoustic and their information looks updated to me, latest_donation_date is the 2020 donation and it's saying that the last time we sent them an email was in Feb 2020 (Thank You Campaign). The same is reflected in "mailing events" in Civi.

I see they forwarded the email to us - I'm looking into this and will check the queries we have.

Thanks, I fwd-ed the original email from fundraising@, does that give you the visibility you'd like for this one, Katers? We can also ask the donor for their copy but that may take a little lobger.

No need, I see the Sent Date info in the forwarded email and it looks like the control from this morning's send.

@MBeat33 I found the problem - they have another email address 13435973

It's possible they have this forwarding to their "active" email. If we dedupe them it should fix the issue.

Ah, thank you for seeing that @KHaggard. Sorry I didn't do a name search for an alt address! I will merge them and close this Task, sorry for the alarm, all.