Page MenuHomePhabricator

Silverpop export: move the suppression list to a different job
Open, Needs TriagePublic


The suppression list is a separate file uploaded to Silvepop. It is currently in the same script but it's dependencies are limited. to:

  • If requires the tables generated by the Silverpop job - but if they exist it can run at any time
  • If we upload the full table & then the suppression job fails then we might email someone who should be shouldn't have received it.

I think we can mitigate these by scheduling it to happen a few hours later - although @KHaggard probably needs to confirm that.

The calculation of the suppression list currently takes over an hour so doing it after would reduce the main job time (although I believe I can also reduce suppression list calculation time).

I'm going to work on separating the sql to a separate file & reviewing. UPDATE - see these 2 commits

Setting up a separate python job for it might be something someone else wants to do (mepps & cstone both indicated they were looking for a part to take on, and I'll work on the myqsl separation today - which interacts with the other parts I'm focussed on right now)

Event Timeline

Jumping in since Katie is having a busy week. I want to add a response to
your question along with a few process notes:

  1. We can stagger the runtime of the import jobs (we already treat the two separately). I'm interested in figuring out the best timing, and maybe if we should do the suppression list first? The main file import takes so long, and Acoustic can only perform one job at a time so it makes the other wait in the queue while it runs
  2. It would be unusual for a donor to unsubscribe and get an email the very next day. Most people interact with our emails within 2 days of receiving it, and we almost never send emails with less than a week between them. The schedule allows for a little flexibility
  3. Our official unsubscribe policy is it could take us up to 3-4 business days to process an unsub request--DS knows this and employs it in messaging. We strive for a shorter time frame, but legally, we have leeway
  4. Katie monitors the imports daily and flags when they go down. If the suppression list import fails two days in a row, we pause all campaigns

@CCogdill_WMF thanks for this - that simplifies it - it sounds like the suppression list can run 'any time'

It looks like I cam close to getting the suppression list query time down to 'negligible'' ( I assume the file is already small.), but the suppression list is leveraging the data calculations from the main export so I need to do more work there before we could run it first and get much benefit from running it first.

I'm a bit stuck on this - I can't understand why this isn't passing - & I'm having trouble running the tests locally & my attempt to fix is not working :-). I'm going to focus on another part of the sql for a bit (this this is kind of a discrete portion & I was going to 'knock it off & then look at the address part' ..... 4 hours ago....

If anyone wants to take over figuring this out feel free to grab this ticket. It wouldn't be a bad way to collaborate on the silverpop restructure since it is a task that can be separated from the borg.

mepps added a comment.EditedJun 23 2020, 6:48 PM

@Eileenmcnaughton I added a patch that gets the tests working. I figure we'll squash it with yours but I wanted to separate it to make sure it's the best way (that it reflects the behavior we want). It looks like the error was causes by us not dropping and rebuilding the table, as well as adding the log_date check. What was the next thing you were going to focus on on this ticket?

Eileenmcnaughton removed Eileenmcnaughton as the assignee of this task.Jul 21 2020, 7:57 PM