Page MenuHomePhabricator

Deleted contacts and silverpop export questions
Open, Needs TriagePublic

Description

from @EYener

For an email segmentation project, I'm finding that I might have to associate donor attributes with now-deleted contacts. Here is an example (with a real CID for reference).

Donor 5011493 was sent an email during Big English. This CID has now been deleted and associated donor attributes are tied to CID 1618203. To complicate things, the email address associated with 5011493, which received this email back during the campaign, is not the primary email address for CID 1618203.

I would want to know things about this contact which are kept in the silverpop_export table: their largest donation, first donation date, etc. so that I would be able to determine what donor segment criteria they would have fallen into in past years had we segmented emails in 2018 (for example).

Is there any way I can associate these two CIDs or their associated email addresses together? I would like to be able to pick up CID 5011493 from a send in civicrm.civicrm_mailing_provider_data and associate it with attributes in silverpop.silverpop_export_staging or wmf_donor - if this is possible.

Event Timeline

DStrine created this task.May 18 2020, 8:12 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptMay 18 2020, 8:12 PM
EYener added a subscriber: jrobell.May 18 2020, 8:14 PM

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