So these are Acoustic system fields, not anything we store in Civi or export to Acoustic, right? And since Acoustic has just one record per email address, these must be merges of two records with different emails in Civi?

When we merge two records with different emails in Civi, I think we end up unsubscribing the email that does not get saved as primary on the merged record. From Acoustic's point of view, there isn't any clear link between the merged records - after the merge it just sees one email record get suppressed and the other email record's contact data get updated.

Did @bsisolak mention how we should copy the Acoustic system fields to Civi and then re-send them to the nightly export job? Can they just be included in the regular CSV? Or is there some way to tell Acoustic that two records (with different emails) have been merged so it can merge the whole mailing event history (including these fields) in its own database?

OK, so I guess we can calculate these from the raw mailing event data that we pull into Civi. So we would just want to add these dates for the few emails each night that are the 'winner' of a merge between two contacts with different email addresses.

Should we export these in a different file, or add the three extra columns onto the main DatabaseUpdate and only populate them for these specific rows?

Those three fields are updated by Acoustic nightly (under Settings -> Organization Settings -> Automated Behavior Updates). You can export these fields as you can any other field.

You can either pull down the value from Record A to put into Record B. Or you could use your raw data to compute that yourself and then update Record B.

@bsisolak I see there is some native merge functionality inside Acoustic. Searching help for 'merge' gives this promising-sounding article:
https://help.goacoustic.com/hc/en-us/articles/22198041899033-CRM-Merge-Recipient-Jobs-in-Acoustic
Is that CRM_SYNC_ID something we could use to indicate CRM-side merges to Acoustic and get the full event history merged in the Acoustic DB? I think some campaigns might also use targeting like 'has not received XXX email', and we wouldn't get that if we just did post-merge surgery on those date fields.

Thanks @Ejegg - that's an interesting link, would that replace the need for adding more custom fields? Ideally, it would be awesome if we didn't have to add more fields to the nightly export, but if that's the only way / best way, we can just add them to the existing file DatabaseUpdate next time we're going to remap.

In theory, yes you could do this, but it would require updating to a non-emailed keyed database. This would be a huge undertaking. Might be worth an investigation on your part, but would require a significant re-architecture of your integration and email program.

Ok. I'll chat with Danny next week about this, but most likely I don't think we will be re-architecturing anytime soon. I'll circle back next week.

Hi @KHaggard let's connect after you speak with Danny to figure out next steps.

Thanks @AKanji-WMF for your patience. Danny is back in office today and I spoke with him in a 1:1. He wants to chat with Brian about timeline if we approached this in the more streamlined way vs. our usual add-and-export-over way and I think that conversation is happening on Thursday. We should have a clearer way forward after that, I'll touch base here again next week on the decision point. Thanks all!

@AKanji-WMF and @bsisolak The right way to do this is to retain the information from both records so that the information that carries over into the merged record in Acoustic is correct. Is this a solution that would be a huge undertaking or fairly simple?

Moving back to triage for discussion with FR-Tech

@DBu-WMF in talking to the team we've affirmed it would be a huge undertaking to execute this, even to scope it - let's talk about the objectives/outcomes and the frequency of us seeing this next time we chat?

So you will be moving to an non-email keyed database as yo are adding SMS/MMS to Acoustic. That won't change much about this project, but I might wait until that is complete. The only real difference for this part is you will sync on your Civi CRM, not email address.

@bsisolak we should figure out what else we need to change on the Civi side to accommodate that change. I guess we can stop deduping by email address in the nightly exports? That will be a big change as we have email as the primary key in many tables we use to transform for exports, but the SQL will probably be a lot cleaner.

@KHaggard Will the email team be OK with having multiple rows exported to Acoustic with the same email address? Is it clear how Acoustic decides which contact's info to use for the tokens?

@Ejegg Honestly, to me it's not super clear how that would work but I think it's possible to re-key the database with CID instead of email. I think it will be quite a process though that we'll need to work with Brian and Trilogy on. Assuming that we will need to re-key Acoustic before we set up any data flows from Civi. Is that correct, @bsisolak ?

Let's move that conversation to another ticket, once the texting contract is signed. Then get back to this ticket after that.

Moving to near future - will create desperate phabs for issues related to non email keyed database change.