Page MenuHomePhabricator

Reflect all undeliverables via Silverpop in CiviCRM
Closed, ResolvedPublic

Description

Why?
Save manual entry, improve our data

Store how?
Update email addresses that have bounced to on-hold, possibly add an activity.
These will be excluded from future silverpop exports

Other notes:
Do we know that an undeliverable will never be deliverable - ie. not a mailbox full - I’ll check with Brian

Details

Related Gerrit Patches:
wikimedia/fundraising/crm : masterProcess unsubscribes: omit blank contact_id

Event Timeline

DStrine created this task.Mar 29 2017, 9:32 PM

I feel like we agreed NOT to do anything with these other than put them in the mailing events tab on the contact record / in the civicm_mailing_provider_data table - but I can’t find that decision recorded. Here are the statuses. @CCogdill_WMF please confirm which statuses, if any, should cause us to mark an email as on_hold (& set the on_hold date) in CiviCRM.

select event_type, count(*) FROM civicrm_mailing_provider_data GROUP BY event_type;
+------------------------+----------+

event_typecount(*)

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

Click Through61244
Clickstream293714
Hard Bounce326163
Open16292592
Opt Out164634
Reply Abuse18936
Reply Change Address7252
Reply Mail Block268159
Reply Mail Restriction1537
Reply Other282
Sent26651805
Soft Bounce399523
Suppressed645951

@Eileenmcnaughton I think we discussed this over chat? But recording here:

These can be marked on-hold

  • Hard Bounce
  • Mail Block

Abuse/Suppressed/Unsubscribe should be unsusbcribed.

Just making sure--is there anything else you need on this task?

We are pulling this in just to check if it's done. If there is more work to do here we might not be able to get to it right now.

I took a look at this and we have a script to 'process unsubscribes' which takes our silver pop data and l

  • looks for 'Opt Out', 'Reply Abuse', 'Suppressed'
  • for contacts with those it
    • creates an unsubscribe activity
  • sets the contact to is_opt_out = 1
  • if there are any matching email addresses (on that contact or another) sets is_bulk_mail to 0

These steps are consistent with manual DS unsubscribes.

HOWEVER, @Ejegg disabled the job in Nov 2017 and I'm not sure why - I'm thinking it might have been load going into BE & it never got re-enabled? Maybe @Ejegg
can recall....

The script is currently not configured to take action on these...

'Hard Bounce' => 'Hard Bounce',
'Reply Change Address' => 'Reply Change Address',
'Reply Mail Block' => 'Reply Mail Block',
'Reply Mail Restriction' => 'Reply Mail Restriction',
'Reply Other' => 'Reply Other',
'Soft Bounce ' => 'Soft Bounce',

Based on your comment above It seems maybe we should create a second job to update civicrm_email.on_hold = 1 for Hard Bounce and Reply Mail Block and we would not take action on the others. That would not be a lot of work to create.

@DStrine @CCogdill_WMF per above I've re-enabled the cron that got turned off - it has a little catch up to do - about 400k rows but it will get there.

However, the outstanding question is whether there should be a follow on job for other bounce types or whether we are happy with the existing job (see prev comment)

Hey Eileen, I'm in agreement with what you proposed in your earlier comment!

Change 493423 had a related patch set uploaded (by Ejegg; owner: Ejegg):
[wikimedia/fundraising/crm@master] Process unsubscribes: omit blank contact_id

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

Change 493423 merged by jenkins-bot:
[wikimedia/fundraising/crm@master] Process unsubscribes: omit blank contact_id

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

OK this is running although it has some catch up to do -

select count(*) FROM civicrm_mailing_provider_data WHERE contact_id > 0 AND is_civicrm_updated = 0 AND event_type IN('Opt Out', 'Reply Abuse', 'Suppressed');
+----------+
| count(*) |
+----------+
|  4266509 |

I've also broadened the scope of the duplicate entry cleanup in https://phabricator.wikimedia.org/T215865 to remove duplicate rows of the above types

I think we can close this given we have a follow on to deal with the other types. I'll just re-run the above query later today to check it's dropping & if so I'll close this

OK - it is dropping

``
select count(*) FROM civicrm_mailing_provider_data WHERE contact_id > 0 AND is_civicrm_updated = 0 AND event_type IN('Opt Out', 'Reply Abuse', 'Suppressed');
+----------+

count(*)

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

4265295
Eileenmcnaughton closed this task as Resolved.Mar 5 2019, 12:16 AM

Just also merged a fix by @Ejegg for the Non-numeric ids & we added a 'meeting' to do a quick query check on this in 2 weeks

Upstream fix for bug Elliott hit (& worked around ) https://github.com/civicrm/civicrm-core/pull/13766

@Eileenmcnaughton, does this mean there are 4.2 million records with one of
these event_type? That seems very high to me.

Eileenmcnaughton added a comment.EditedMar 7 2019, 12:24 AM

@CCogdill_WMF I refined the query - it's still high but perhaps not inaccurately so.....

 select count(DISTINCT contact_id) FROM civicrm_mailing_provider_data WHERE contact_id BETWEEN 0 AND 99999999 AND is_civicrm_updated = 0 AND event_type IN('Opt Out', 'Reply Abuse', 'Suppressed');
+----------------------------+
| count(DISTINCT contact_id) |
+----------------------------+
|                     737390 |

(the first query now gives 3827886 so some reduction due to processing & also due to the duplicate row removal process)

@CCogdill_WMF Hmmm....

select count(DISTINCT contact_id), event_type FROM civicrm_mailing_provider_data WHERE contact_id BETWEEN 0 AND 99999999 AND is_civicrm_updated = 0 AND event_type IN('Opt Out', 'Reply Abuse', 'Suppressed') GROUP BY event_type;

count(DISTINCT contact_id)event_type
2Opt Out
1Reply Abuse
737390Suppressed

3 rows in set, 38 warnings (23.27 sec)

I guess that is because the suppressed info is coming from silverpop whereas we likely already processed the other data?

This shows it better - ie all the 'Opt Out' contacts are already Suppressed

   select count(*), event_type FROM civicrm_mailing_provider_data WHERE contact_id BETWEEN 0 AND 99999999 AND is_civicrm_updated = 0 AND event_type IN('Opt Out', 'Reply Abuse', 'Suppressed') GROUP BY event_type;
+----------+-------------+
| count(*) | event_type  |
+----------+-------------+
|    32432 | Opt Out     |
|     3418 | Reply Abuse |
|  4319890 | Suppressed  |

That 737k number makes sense to me. The distinct seems important.

Le mer. 6 mars 2019 à 16:37, Eileenmcnaughton <
no-reply@phabricator.wikimedia.org> a écrit :

Eileenmcnaughton added a comment.
This shows it better - ie all the 'Opt Out' contacts are already Suppressed

select count(*), event_type FROM civicrm_mailing_provider_data WHERE contact_id BETWEEN 0 AND 99999999 AND is_civicrm_updated = 0 AND event_type IN('Opt Out', 'Reply Abuse', 'Suppressed') GROUP BY event_type;

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

count(*)event_type

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

32432Opt Out
3418Reply Abuse
4319890Suppressed

*TASK DETAIL*
https://phabricator.wikimedia.org/T161761
*EMAIL PREFERENCES*
https://phabricator.wikimedia.org/settings/panel/emailpreferences/
*To: *Eileenmcnaughton
*Cc: *gerritbot, JohnLewis, Ejegg, Eileenmcnaughton, CCogdill_WMF,
Aklapper, DStrine, CucyNoiD, NebulousIris, Gaboe420, Versusxo,
Majesticalreaper22, Giuliamocci, Adrian1985, Cpaulf30, Baloch007,
Darkminds3113, Bsandipan, Lordiis, Adik2382, Th3d3v1ls, Ramalepe, Liugev6,
EBjune, Lewizho99, Maathavan

Ok we are down to 0 here now

 select count(*) FROM civicrm_mailing_provider_data WHERE contact_id > 0 AND is_civicrm_updated = 0 AND event_type IN('Opt Out', 'Reply Abuse', 'Suppressed');
+----------+
| count(*) |
+----------+
|        0 |
+----------+