Page MenuHomePhabricator

Small number of contacts being inappropriately put on the suppression list
Open, Needs TriagePublic

Description

In checking my deployment I found 4 contacts added to the suppression list on the given day who I think should not be there.

I have 2 theories

  1. timing - ie we built the staging table 3 hours before the suppression list. We have stuff to mitigate this but it looks at max(email_id) - my theory is that these were not caught due to the id being lower (but having been edited not created). There are a couple of fix possibilities - link back to the latest data or leverage modified date

2)t we are suppressing them due to 'inconsistent opt in' - ie in at least one case they had seen the opt in and not chosen it and more recently seen it and chosen to opt in. They remain on the suppression list. For others the order was reversed

It might be worth purging & rebuilding the suppression list when we get to the end of the refactor

SELECT new.email, e.id, contact_id, SUM(on_hold) as on_hold,

->        SUM(do_not_email) as do_not_email,
->        SUM(is_opt_out) as is_opt_out,
->        SUM(opt_in) as opt_in,
->        COALESCE(SUM(do_not_solicit)) as do_not_solicit
-> 
-> FROM new_silverpop_excluded new
->   LEFT JOIN old_silverpop_excluded old ON old.email=new.email
-> LEFT JOIN civicrm.civicrm_email e ON e.email = new.email
-> LEFT JOIN civicrm.civicrm_contact c ON c.id = e.contact_id AND c.is_deleted = 0
->   LEFT JOIN   civicrm.civicrm_value_1_communication_4 v ON c.id = v.entity_id
-> 
-> WHERE old.id IS NULL
-> GROUP BY new.email
-> HAVING on_hold = 0 AND do_not_email = 0 AND is_opt_out = 0 AND (opt_in IS NULL OR opt_in = 1) AND do_not_solicit = 0;

Event Timeline

Option #2 shouldn’t be a possibility... we want it to be easy for someone
to switch their opt-in preferences, which means we handle them on the
Acoustic side. If they are added to the suppression list, how will they get
removed from the Acoustic list if they opt back in?

Le ven. 26 juin 2020 à 9:17 PM, Eileenmcnaughton <
no-reply@phabricator.wikimedia.org> a écrit :

Eileenmcnaughton created this task.
Eileenmcnaughton added projects: Fundraising-Backlog, FR-Email,
Wikimedia-Fundraising-CiviCRM, Fundraising Sprint Lazy Loading Life,
Fundraising Sprint M 2020. View Task
https://phabricator.wikimedia.org/T256522
*TASK DESCRIPTION*

In checking my deployment I found 4 contacts added to the suppression list
on the given day who I think should not be there.

I have 2 theories

  1. timing - ie we built the staging table 3 hours before the suppression list. We have stuff to mitigate this but it looks at max(email_id) - my theory is that these were not caught due to the id being lower (but having been edited not created). There are a couple of fix possibilities - link back to the latest data or leverage modified date

2)t we are suppressing them due to 'inconsistent opt in' - ie in at least
one case they had seen the opt in and not chosen it and more recently seen
it and chosen to opt in. They remain on the suppression list. For others
the order was reversed

It might be worth purging & rebuilding the suppression list when we get to
the end of the refactor

SELECT new.email, e.id, contact_id, SUM(on_hold) as on_hold,

-> SUM(do_not_email) as do_not_email,
-> SUM(is_opt_out) as is_opt_out,
-> SUM(opt_in) as opt_in,
-> COALESCE(SUM(do_not_solicit)) as do_not_solicit
->
-> FROM new_silverpop_excluded new
-> LEFT JOIN old_silverpop_excluded old ON old.email=new.email
-> LEFT JOIN civicrm.civicrm_email e ON e.email = new.email
-> LEFT JOIN civicrm.civicrm_contact c ON c.id = e.contact_id AND c.is_deleted = 0
-> LEFT JOIN civicrm.civicrm_value_1_communication_4 v ON c.id = v.entity_id
->
-> WHERE old.id IS NULL
-> GROUP BY new.email
-> HAVING on_hold = 0 AND do_not_email = 0 AND is_opt_out = 0 AND (opt_in IS NULL OR opt_in = 1) AND do_not_solicit = 0;

*TASK DETAIL*
https://phabricator.wikimedia.org/T256522

*EMAIL PREFERENCES*
https://phabricator.wikimedia.org/settings/panel/emailpreferences/

*To: *Eileenmcnaughton
*Cc: *Aklapper, DStrine, CCogdill_WMF, EYener, jgleeson, mepps, KHaggard,
Ejegg, Eileenmcnaughton, EBjune

mepps added a comment.Jul 1 2020, 3:20 PM

@Eileenmcnaughton can you add some CIDs?

mepps these 4

email_idcontact_id

+----------+------------+

2030847520327220
3534130620324897
3466733015378088
2687206111045397

+----------+------------+

Although they may have been edited.

I am hoping to make changes to the script such that we can get the most recent opted in, not 'any' but there is also the question as to whether we have the right big picture approach here -ie putting a non-opt-in on the suppression list doesn't really match the requirements @CCogdill_WMF describes above very well but it does address legal concerns that we are very careful not to email them.... too careful probably. I think there is a conversation in there

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