Page MenuHomePhabricator

List all emails that may have been mistakenly opted out
Closed, ResolvedPublic

Description

As described T223935, for the last few years, contacts that were created during the first almost two hours of the silverpop export were added to the unsubscribes export on their first day. They would be exported to the regular mailing list on their second day, but according to @CCogdill_WMF their addresses stay on the opt out list on IBM's side.

  • Figure out when we deployed the tools (silverpop_export) update that started calculating all the opt outs from the log tables.
  • Check on timing of export job and look at logs to determine general timing of window between start and select to set up the 'excluded' table.
  • Select all email addresses created between those hours, and delete those that have any opt-out status

Event Timeline

Ejegg created this task.Jun 25 2019, 8:28 PM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptJun 25 2019, 8:28 PM

Thanks, @Ejegg!

A related question has been bumping around in my mind: which contacts
*were* considered
opted out and are no longer? I.e. were there *on hold* people who were once
opted out and should be opted back in? I don't actually know if these cases
exist, but wondered if combining those into this request would allow us to
accomplish two goals at once.

@CCogdill_WMF it occurs to me that the main nightly mailing list export includes everyone that should not be opted out.

The only issue I can see with using that list to purge is that it will include people who have opted out via Silverpop-hosted pages in the hours following the export. Can you think of a way to get around that?

Hmm, I guess I could do all of this with what we have in IBM. I could create a copy of the main database in silverpop and then purge all the people who have opted out using silverpop forms from it. I will then purge the remaining list from the master suppression list, and then the nightly unsubscribe import will re-import anyone we missed. Whew! @MNoorWMF does that all make sense to you? Would like to sanity check the method.

So let us know when T223935 is resolved and we can start this process!

Ejegg added a comment.Jun 26 2019, 3:22 AM

@CCogdill_WMF your mentioning 'on hold' makes me think of one more thing. Since that can be a temporary status in Civi, we might want to drop it from the list of fields that get someone on the unsubscribes list. Tomorrow I'll look at that logic again to see how often it's temporary, and what kind of TY letter bounce errors put an address on hold.

Sounds great, thank you! :)

Ah, duplicating my comment from T223935 here:

Last week, we performed the process I outlined above in silverpop and purged anyone from our Master Suppression List (MSL) who had been opted-out by the unsubscribes import. That removed 1.25M records. Then, the nightly import imported 936k new rows, which suggests that we successfully removed about 310k records from the MSL that were not supposed to be there. This feels like a big win!

We did notice through this process that Civi does not have a record of all unsubscribes made on the IBM side. I think these are probably unsubscribes that predate the APIs Eileen set up between IBM > Civi.

Is there anything else we should check or QA on the email side? If this all looks good to you, I think we can close this task.

Ejegg closed this task as Resolved.Jul 10 2019, 5:29 PM
Ejegg claimed this task.

Sounds good to me! Let's make a new task for finding the old opt-outs and getting them into Civi.