Thanks for adding this task @DStrine! Adding @jrobell as as subscriber.

Hi @Eileenmcnaughton - I ran the CIDs (both deleted and kept) above through activities yesterday as a test, and I'm not getting the expected match.

Querying for the deleted CID returns an empty set:
select deleted.contact_id as deleted_contact_id, kept.contact_id as kept_contact_id FROM civicrm_activity_contact as deleted INNER JOIN civicrm_activity a ON a.activity_type_id = 87 AND deleted.record_type_id = 2 AND a.id = deleted.activity_id INNER JOIN civicrm_activity_contact as kept ON kept.activity_id = a.id AND kept.record_type_id = 1 and deleted.contact_id = 5011493;

Querying for the kept CID returns two values, but neither are the CID present in the civicrm_mailing_provider_data table:
select deleted.contact_id as deleted_contact_id, kept.contact_id as kept_contact_id FROM civicrm_activity_contact as deleted INNER JOIN civicrm_activity a ON a.activity_type_id = 87 AND deleted.record_type_id = 2 AND a.id = deleted.activity_id INNER JOIN civicrm_activity_contact as kept ON kept.activity_id = a.id AND kept.record_type_id = 1 and kept.contact_id = 1618203;

@EYener you are right - I missed the fact it joins through the parent activity

SELECT deleted.contact_id as deleted_contact_id, kept.contact_id as kept_contact_id, kept.record_type_id
FROM civicrm_activity_contact as deleted
INNER JOIN civicrm_activity a ON a.activity_type_id = 87 AND deleted.record_type_id = 3
AND a.id = deleted.activity_id
INNER JOIN civicrm_activity parent_activity ON a.parent_id = parent_activity.id
INNER JOIN civicrm_activity_contact as kept ON kept.activity_id = parent_activity.id
AND kept.record_type_id = 3

WHERE deleted.contact_id = 5011493;

This activity doesn't link to the kept contact. - but it does link to another activity which does