@SHust In this case, the cause of the issue is that there was a duplicate contact (10567076) with 12801204's non-primary email address as their primary email address. I have now merged these two contacts, so the immediate issue will be resolved.

However, there are two issues here:

1. Because of the complex way in which the Acoustic export works, we ended up with the same cid attached to two contacts with different emails in Acoustic (and the second contact had donation data for the merged contact before the merge happened). We ended up in this state because we merged two contacts with different email addresses, setting one of the email addresses to secondary, while a third contact with the same email as the one we just made secondary also existed. That's a pretty specific set of circumstances that I think would happen only very rarely, so I'm not sure this is worth fixing given the complexity in making that change. For reference, we have only 30k email addresses that are secondary for one contact and primary for another (query below) and I would guess only a small proportion of those would have ended up in that state through merging.
2. It seems that our deduping doesn't fully cover the case where two contacts share an email address, but it is primary for one contact and non-primary for another. A quick check suggests that most of the 30k contact pairs above are actually the same person. Would it be worth trying to dedupe these contacts? I don't have a good sense how we decide if a particular dedupe task is worth the cost.

select count(*) from (select email from civicrm_email group by email HAVING count(distinct contact_id) > 1 and count(distinct is_primary) > 1) as sq;

@Lars, thanks for looking into this so quickly and for sharing your findings! Do you happen to have a link to a search kit so I can take a look and try to figure out the best next steps?

@SHust unfortunately, this isn't searchkit-able. Would you like me to send you a sample csv with names, cids and emails?

That would work, tysm!

@SHust I spoke to soon, someone on CiviCRM chat did manage to find a way to get this into a SK (but sadly without links to the contacts). But this should give you the general idea:

https://civicrm.wikimedia.org/civicrm/search#/display/Emails_shared_by_two_contacts_where_one_is_primary_and_one_is_not/T407541_Emails_shared_by_two_contacts_where_one_is_primary_and_one_is_not_Table_1

Note that this is slow to load, but it will get there eventually if you just leave it.

Thanks for the link, @Lars — you weren’t kidding about the slow loading! Do you think there’s a way to split this massive group into smaller ones so the admin team can work on deduping them? I’m seeing a lot of bogus emails (CID examples: 20605649, 64765224, 10202889, 2180527).
Maybe a group could be created based on “(List) Contact Display Name = no more than two” to help narrow things down?