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
- 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;