@MDemosWMF After discussion with Eileen, here's the situation: The imports do handle merged contacts — as long as the old contact hasn't been fully deleted, which should be fine for our purposes here as long as we aren't sending letters with cids that were merged more than a year ago (we delete contacts from the trash after one year). So let's try to address the problem of sending out mail with old cids and that should address this problem in the future.

@JMando or @MDemosWMF Do you know what kind of records we have of the list of cids we have sent to Faircom? If we wanted to send them a list of merges can we select those based on cid from data we have somewhere or would we need to get this from Faircom?

@Lars I'm not sure I understand what you mean. Faircom creates the full list of CIDs as far as those that are chosen to mail to for each campaign. So if you wanted to look at those ahead of time they would need to send them over so they could be updated if needed with a new CID before finalizing.

@MDemosWMF The question is more do we have a full list of the contact IDs we have sent to Faircom, so that we could check if any of those cids have been merged and send that information to them so they can merge them on their end.

The full list @JMando would be able to weigh in on. I believe they have a static universe of CIDs they are working with, but not all are pulled in for each mailing. So not sure if it makes sense to run the whole universe to check for merges at a certain frequency or if we would rather run the specific lists for each mailing as we plan who is being pulled in.

@MDemosWMF I've also disabled the job that deletes trashed contacts after a year until we resolve this issue. So the problem won't get any worse, at least.

We are sending Faircom a complete list of US-primary-address contacts every month. Will need to ask them if they could just delete from their universe the contacts who are no longer in the list or do they actually need to do the deduping on their end as well. This also brings up the question of if they are removing contacts who no longer are included in the file Joseph sends because they have moved outside the US (probably pretty minor but would be good to handle this as well).

Here is the list we send:

Select 
	cc.id AS contact_id,
    prefix.label as prefix,
    cc.first_name,
    cc.middle_name,
    cc.last_name,
    suffix.label as suffix,
    cc.organization_name,
    cc.job_title,
    a.street_address,
    a.supplemental_address_1,
    a.supplemental_address_2,
    a.city,
    state.abbreviation as state,
    a.postal_code,
    a.postal_code_suffix,
    p.phone,
    e.email,
    cc.addressee_display,
    cc.postal_greeting_display,
    cc.email_greeting_display,
    cc.contact_type,
    cc.birth_date,
    cc.is_deceased,
    cc.do_not_email,
    cc.do_not_phone,
    cc.do_not_mail,
    cc.do_not_sms,
    cc.is_opt_out,
    d.donor_status_id,
    d.donor_segment_id,
    comm.no_direct_mail,
    comm.no_paper_ty,
    manager.label as relationship_manager,
    tag.label as exclude_from_direct_mail_campaigns
FROM civicrm.civicrm_contact AS cc
LEFT JOIN civicrm.civicrm_option_value prefix ON CAST(cc.prefix_id AS UNSIGNED) = CAST(prefix.value AS UNSIGNED) AND CAST(prefix.option_group_id AS UNSIGNED) = CAST(6 AS UNSIGNED)
LEFT JOIN civicrm.civicrm_option_value suffix ON CAST(cc.suffix_id AS UNSIGNED) = CAST(suffix.value AS UNSIGNED) AND CAST(suffix.option_group_id AS UNSIGNED) = CAST(7 AS UNSIGNED)
LEFT JOIN civicrm.civicrm_email AS e ON (cc.id = e.contact_id AND e.is_primary = 1)
LEFT JOIN civicrm.civicrm_phone AS p ON (cc.id = p.contact_id AND p.is_primary = 1)
LEFT JOIN civicrm.civicrm_address AS a ON (cc.id = a.contact_id AND a.is_primary = 1)
LEFT JOIN civicrm.civicrm_state_province as state on a.state_province_id=state.id
LEFT JOIN civicrm.civicrm_country AS country ON a.country_id = country.id
LEFT JOIN civicrm.wmf_donor AS d ON cc.id = d.entity_id
LEFT JOIN civicrm.civicrm_value_1_communication_4 AS comm ON cc.id = comm.entity_id
LEFT JOIN civicrm.civicrm_value_1_prospect_5 AS prospect on prospect.entity_id = cc.id
LEFT JOIN civicrm.civicrm_option_value AS manager ON CAST(prospect.relationship_manager_284 AS UNSIGNED) = CAST(manager.value AS UNSIGNED) AND CAST(manager.option_group_id AS UNSIGNED) = CAST(45 AS UNSIGNED)
LEFT JOIN civicrm.civicrm_entity_tag AS entity_tag ON entity_tag.entity_id = cc.id AND entity_tag.entity_table = 'civicrm_contact' AND entity_tag.tag_id = 200
LEFT JOIN civicrm.civicrm_tag AS tag ON tag.id = entity_tag.tag_id AND tag.label='Preference: exclude-from-direct-mail-campaigns'
WHERE country.iso_code = 'US';

@Lars does delete_deleted_contacts still need to be off?

@Eileenmcnaughton Yes, until we figure something out here. I believe we have a meeting in the near future with Faircom to discuss.

See Asana for work on this.

Per discussion with @CHudson-WMF, we will provide a csv with merged cid, merged into cid, timestamp. We'll get these from the Contact merged activities, by source_record_id, joining to a group that will be created in Civi based on the list provided by Faircom at some interval that includes all contacts in their database. We assume that placing the merged contact in the group will end up with the merged into contact in the group, but Lars to confirm with DR that we always move groups when merging contacts, just in case that isn't true for some reason.
Also T431604: Update getMergedTo to use source_record_id if not found in first pass and the backfill in T429532: Add source_record_id to merge activities will make the importing to deleted contact problem go away independently.

@CHudson-WMF All current and future Contact Merged activities now have source_record_id, so once we have a list of current contact ids from Faircom, we can send them back the merge mappings going back as far as necessary. I think we could just get all the contact ids in their list that aren't non-deleted contacts in Civi and then get the new contact id for each by looking up the old contact id in the source_record_id field (and then getting the new contact id from the ActivityContact).

I continue to work on fixing the problem from the Civi end so that importing onto deleting contacts will find the contact they have been merged to.

@MDemosWMF Through the work in the above linked phabs, we have now solved this problem from our end. We are now storing the data required to match deleted contact ids to the contacts they have been merged into, including across multiple merge/deletes. We've backfilled this data, so we should be covered for past merges as well. Before we go ahead and re-enable to job that hard deletes contacts a year after they are put in the trash, would you be able to confirm that your next DM activity import doesn't hit any missing cids?

We'll still want to fix the problem that Engage has stale contact ids in their database as we've discussed above, pending a full export from their end, but the immediate problem that prompted this investigation should be fixed.

@Lars Sounds good. We can keep you updated if we hit anything during the next DM activity import. Looks like we should get that list around 8/25. As a heads up I will be training @Elbar53 on these imports and she will be taking over moving forward.

@Elbar53 @MDemosWMF Can you confirm if you had any issues or not with deleted contacts in DM activity imports since Aug 10?

I haven't noticed any deleted contacts. Sometimes the CID is incorrect, but it's only because two or more profiles were merged.

@Elbar53 So there were cases since Aug 10 where the cid was incorrect because of merges and you had to intervene manually? If so, do we still have the import with errors that I can look at? Ideally we'd handle these all automatically, so if some are not being handled I'd like to find out why.

I only knew it was incorrect because I needed to move it to the Endowment file. In that instance I check the CID because I need to make an adjustment in the donation .Otherwise I wouldn't have known. I don't get rejections anymore based on the CID.

Great, that's what we want to hear @Elbar53

I've re-enabled our delete deleted contacts job.

fr-tech's work on this is done, but leaving this open until the analytics solution for sending merges to Engage is complete.