From @Eileenmcnaughton, once they're opted out, we stop pushing the contacts up to silverpop. The fix here would be to push them up but add suppression data to update their records. So, we'd add a silverpop filled called "suppressed" and we'd have to add these contacts to the regular export with suppressed=true.

Silverpop has a suppression list but it's not visible to the email team when looking at an individual contact.

Explore: Could we do this as part of the suppression list export?

This commit might be worth reviewing before going too far down this https://gerrit.wikimedia.org/r/c/wikimedia/fundraising/tools/+/620802 - it removes code that I now think is doing more harm than good & might make it all a bit less confusing too (it's been sitting around a while as it didn't have it's own phab)

Thanks @Eileenmcnaughton I've stared at this a bit today and it looks safe to merge but it has a merge conflict.

@mepps so I think the merge conflict is with another open (wip) rather than what is merged - a new feature I guess

@Eileenmcnaughton are we not able to delete contacts from silverpop databases? I guess if I'm understanding the problem, once contacts are added to the suppression list we won't email them, but they're still in the main database. Are these separate enough that they could be on the suppression list but not in the main db?

"Are these separate enough that they could be on the suppression list but not in the main db?"

@mepps - yes I think that is possible

I would expect there IS an api to delete contacts from silverpop.... not one we've explored

Hmm here's why that doesn't work: https://help.goacoustic.com/hc/en-us/articles/360043570933-Opt-in-an-opted-out-CRM-synchronized-contact.

I wonder if this is helpful for this: https://help.goacoustic.com/hc/en-us/articles/360042857374-View-opted-out-contacts-in-queries?

Does this field not reflect what's on the suppresssion list? Sorry I don't know much about silverpop.

@mepps No that doesn't reflect suppression list contacts. We have people labeled as "Opted In" in our database that also have their email on the suppression list so they are always suppressed, but that field doesn't notate it. I'm not aware if it's being used for something else, but if not, it would be super helpful if that field updated when people are put on the suppression list!

@mepps -looks like you asked the right question there - so would us updating that field solve a lot of the problem?

@Eileenmcnaughton @KHaggard :). It's exciting to find a possible way forward! Is there anyone else we should ask about how the status field is being used before moving forward?

@Ejegg might know more.

One thing is that I expect we would set opted out to be yes if they opted out or the last time they had a chance to opt in they didn't. (saying that for clarity since that is how the silverpop export works now)

As an aside I'm nervous that we use highest email id as proxy for latest version of the donor in the sillverpop code - I tried to change that before but it kinda died - just something to keep on your radar as a possible gremlin in the code

@Ejegg has suggested we might not be able to update this field. I'm reading through https://help.goacoustic.com/hc/en-us/articles/360042685114-Opt-out-and-suppression-settings and https://help.goacoustic.com/hc/en-us/articles/360042858454-Import-a-database to try to figure out if we can or if there's a way for contacts on the suppression list to be opted out.

Ooh! From https://help.goacoustic.com/hc/en-us/articles/360042858094-Master-suppression-list

What happens to the contact record in the database when a contact email address is added to the master suppression list?
Adding a contact's email address to the master suppression list has numerous consequences.

When a contact is added to the master suppression list, a copy of the record is created and added to the list. The contact record within the database remains with the status of opted in. The contact is added to the database suppression list and their status on the Suppression List Summary page shows as opted in, because they are 'opted in' to the suppression list. This opt-in date shows when the contact unsubscribed from the email list.

When emails are sent to the database, the contact is suppressed based on the master suppression list at the Org level.

If you want to change the contact status within the database you can perform an import update. You can change the opt in status of a contact within your master database by using a further import update and selecting Opt out contacts, to update the status.

While a contact's email address is on an organization-level master suppression list, he or she does not receive email from any database in the organization. If a contact's email address is on a database-level suppression list, he or she does not receive email from that particular database.

I'm wondering if this means this could be done during import with the data as is?

Oh, that's helpful - thanks @mepps

So basically, every contact on the Master Suppression List also has a copy on the _all_wikimedia database? That's what I originally thought, but I spoke with one of our Trilogy reps today and he mentioned something interesting:

He said that people who unsubscribe should be put on the suppression list and removed from the main database, but there are two instances where that doesn't happen and the suppressed contact remains in the main database:

1. Hard bounced contacts
2. Unsubscribes in Civi done manually

Does this information help?

It does @KHaggard and this would cover any contacts that were put on the supression list from Civi. Basically, what needs to happen next is for trilogy to have another scheduled import of the suppression list (assuming they import it?) to the main db where they just check "Opt-out contacts".

Sorry, I'm a bit confused. Fr-tech sends the Master Suppression List export file to us and we have it mapped to update nightly. Would the fix be to change the settings of this data job?

I'm also going to add one of our Trilogy reps in case he knows more about this: @bsisolak

A few notes to add. You can do anything mentioned here with the API, it's just up to you how you want to structure it. The link above about CRM integration does not apply here (that is for Acoustic accounts that sync with SFMC or MS Dynamics).

fr-tech already updates the Master Suppression List nightly. The issue here is that fr-tech is adding people to the main Acoustic database on a nightly basis that are in the Master Suppression List. So not all records are marked do not email (not sure the exact flags/terminology you use) in Civi correctly. Maybe this is a Civi dupe issue where the same email is on multiple records, one is marked do not email and one is marked emailable?

Once you determine what you want to do, I can help point you in the direct of the needed API calls.

@bsisolak Thanks for the quick response! At this time, we remove all contacts on the suppression list from our upload to the main db. The issue is that it does not go back and update the pre-existing contacts. My understand of the linked documentation is that there's a way to import the list of suppressed contacts to the main db with opt-out selected so that it just updates the contacts that are already in the main db. We do dedupe by email already for the export to silverpop. The other option would be to delete the contacts from the main db. It sounds like @DStrine has set up time for us to talk about this further.

"there's a way to import the list of suppressed contacts to the main db with opt-out selected so that it just updates the contacts that are already in the main db" - Are you referring to Civi or the Acoustic database? For sure you can keep this all clean in Acosutic, but over time it will get out of sync again.

"We do dedupe by email already for the export to silverpop." - are the two files deduped againt each other? Or is each file pulled by unique email?

Addressing Brian's questions:

• I think @mepps is talking about Acoustic, but @bsisolak is right about how it will slowly get out of sync again and we will have to make a routine of opting out folks in the main database. This is synonymous to what we currently do in Acoustic twice a year: purge the suppressed contacts out of the main database (instead of re-labeling). I guess the solution we want is to figure out an ongoing way to keep suppressed contacts out of the main database regularly, removing the need to manually purge when we happen to remember.

• I'm still learning about how dedupe works on the backend, but what I know is that if we manually dedupe a contact in Civi that had different email addresses, the email address listed as 'primary' updates with the correct data but the 'old' email remains in the database and is automatically suppressed. @Eileenmcnaughton mentioned wanting to purge and rebuild the suppression list because of this. Not sure what would be entailed to do this, but would it be tricky to tackle during en6C?

Rebuilding the Master Suppression List is very risky, and I would not recommend it. Anyone on that list should never be emailed. If Civi were missing any data, that could be catastrophic to your deliverability.

There are two dupe scenarios (and I was asking about the other, where two records have the same email address), but your note made me think of another question.

One a single Civi account with multiple email addresses emails, where do you store the subscription date? On the record or the email address?

I’m guessing by your note you store it on account. Also, I'm not sure what you call an account in Civi (record, account, user), so likely using the wrong term there.

@bsisolak a bunch of us met and I believe you have the recording of that. @mepps had a suggestion there for the opt in field. Let us know what you think.

Can we take one step back and sanity check that the Civi database has all the data from the Master Suppression List? I'm assuming (and this might be wrong) that there are emails on the Master Suppression List that are not marked as opt-outed in Civi. When you export the MSL, select the “Export Contacts, Opt-Outs, Snoozes and Undeliverables” option.

Where does Civi get all its data about who is opted-out/hard bounced? Is that all from the mailing event data?

For sure you can run a nightly import that would mark records as opted-out in the main database. But I'm not sure that will resolve the issue as I think the root issue is that Civi thinks there are emails that are opted-in, when they are not.

There are a few more wrenches to throw into this. You will have people who fill out the RML who are opted out. Let’s put this on the back burner for now. But… as a I think about this, you don’t have RML records in Civi do you? What do you do with mailing events that don’t map to an email in Civi?

Just wanted to update that we had a great conversatoin with @bsisolak, @KHaggard, @Eileenmcnaughton, @Ejegg, and @DStrine about this. How did it doing the import to load the field for the suppressed contacts?

Hi @mepps - thanks for following up. @bsisolak and I have a one-on-one meeting set up for tomorrow to go through the process. Will update how it goes afterwards.

Thanks @KHaggard! I'm putting this in done on this sprint board but not closing it for now to reflect that fr-tech has finished our side within this sprint.

Thanks @mepps - that makes sense. I just got a training from Brian on how to run this job. In the spirit of being overly cautious, I will execute the suppressed contact opt-out from the main database job this Friday, so that it doesn't run while we are sending emails. Will ping here after it runs so we get a sense of how long it takes to process and if it's viable to set up a nightly recurring job for it.

Is it also the plan that going forwards we will update recently-opted-out contacts?

I think after I run the test on Friday we will have a good understanding of how many contacts in general are opted out, and it might be a good practice to have this be a 3rd recurring import job if it doesn't take too long to run. I'll report back on how long the job takes and how many contacts update.

Hey all - I just ran the test trial of this with a MSL file from Tuesday. The opt-out job took 3 minutes and the _all_wikimedia recalculation job took 11 minutes. I'm adding the job results below:

Contact Source Name: _all_Wikimedia
File Name: Data Sep 29 2020 09-00-09 39 AM 1

Job ID: 172483608
Job Type: Contact Source Import @ Friday, October 2, 2020 at 5:57:37 PM GMT
Job Status: Complete
Total Rows: 4648307
Total Valid Rows: 4646640
Total Invalid Addresses: 1667
Total Duplicate Addresses: 4524822
Total Disallowed Addresses: 0
Total Bad Data: 0

To test how these affect queries, I recreated a query from T254304 to compare list size and it's dropped from being 6000 to 123 contacts. This definitely seems to have helped me narrow down rows to investigate data discrepancies within.

Great @KHaggard! I think our final step would be to send two of opt-out lists going forward?

@mepps Yes I think so! Would it be possible for fr-tech to send a duplicate Unsubscribes file under a different name than "Unsubscribes" so I can differentiate/organize the recurring jobs in Acoustic?

@KHaggard That should be doable! Moving back to Doing so we remember to do this.

No rush at all on this task. In fact, we have a small break in sending emails late next week so setting up a new recurring import would be more convenient next week :)

Change 634026 had a related patch set uploaded (by Mepps; owner: Mepps):
[wikimedia/fundraising/tools@master] Add opt out file for suppression list upload

https://gerrit.wikimedia.org/r/634026

Change 634026 merged by jenkins-bot:
[wikimedia/fundraising/tools@master] Add opt out file for suppression list upload

https://gerrit.wikimedia.org/r/634026

Patch is merged through to deploy branch. Not deployed as yet - is any co-ordination needed?

@KHaggard @bsisolak the opt out file should upload in the next hours & then daily. Filename is something like Optout-20201015131007.csv

Thanks, Eileen! Will attempt setting up the recurring import now.

Ok, just set up the recurring update and ran the recent file from the FTP. Here's the "receipt" from the job:

Contact Source Name: _all_Wikimedia
File Name: Optout-20201015033618.csv

Job ID: 173087539
Job Type: Recurring Contact Source Import @ Thursday, October 15, 2020 at 1:23:40 PM GMT
Job Status: Complete
Total Rows: 4778145
Total Valid Rows: 4776475
Total Invalid Addresses: 1670
Total Duplicate Addresses: 4709926
Total Disallowed Addresses: 0
Total Bad Data: 0

I have it set up to run daily, 9am UTC. Mostly I just check to make sure bad data is zero and total rows matches the unsubscribes total rows. Please let me know if you think I need to monitor more than that for this job.

Thanks!

Sounds good -