Page MenuHomePhabricator

Major Donors on email suppression list who should be opted in
Closed, ResolvedPublic1 Estimated Story Points

Description

We sent an email on Friday, 6/24 to Major Gifts' ~1300 person Quarterly Report list announcing Katherine as the new ED. After the email went out, I noticed there was a huge bump in suppressed email addresses compared the previous email we sent in April. Here's the numbers breakdown:

  • Total QR list: 1388
  • Total suppressed on last email in April: 157
  • Total suppressed on ED email in June: 354

This is a huge increase, especially for a list as well-engaged and well-curated as MG's. What caused these records to be added to the suppression list en masse?

My guess is this is related to the changes made in T131979, though I can't tell how. @LeanneS did a spot check on 15 of new email addresses added to the suppression list, and said the only trends she found were:

  • They all had last given in 2010 or earlier
  • Most, but not all, were marked Do Not Trade (which does not count as an unsubscribe)

I've added a file to the server with 4 tabs breaking down the data from the email we sent last week. Here's the file:
smb://filesrv1.corp.wikimedia.org/Fundraising/Development/Email/20160629_MGFemail_newsuppressions

The tabs are as follows:

  1. The "new" suppressions from the 6/26 email that weren't on the April suppression list
  2. The total suppressions from the 6/26 email
  3. The total suppressions from the April email
  4. The total who received the 6/26 email

This is really important to Major Gifts, as it cuts out 15% of a list of their most informed and engaged donors for no apparent reason. It also makes me worried we've opted out legitimate contacts on the small dollar donor side as well.

Can we investigate what happened?

Event Timeline

Restricted Application added subscribers: Zppix, Aklapper. · View Herald TranscriptJun 30 2016, 8:07 PM
CCogdill_WMF triaged this task as High priority.Jun 30 2016, 8:07 PM
DStrine set the point value for this task to 1.Jul 5 2016, 8:17 PM
Ejegg added a subscriber: Ejegg.Jul 28 2016, 11:51 PM

I think I found it - we're missing rows in the wmf_contribution_extra for some of those donations, which leads the export script to think a lot of those people haven't donated and should be on the suppression list. I think we're consistently generating rows in that table now, so we should probably backfill all the missing rows.

Ejegg claimed this task.Jul 28 2016, 11:56 PM

Okay, thanks for finding the issue, @Ejegg! Is there a reason the script
adds people with no donation to the suppression list? That's not ideal
behavior; Major Gifts especially has prospect contacts they may want to
email, even if they have never donated. Anyone who gets added to the
suppression list has to be manually removed or we can't ever email them, so
we want to be choosy with who ends up on the list.

With that in mind, will it be possible to generate a list of the people who
ended up on the suppression list and shouldn't be there? I'll need to opt
those people back in manually.

Ejegg added a subscriber: awight.Jul 29 2016, 12:44 AM

It's a consequence of the change we made to get suppress old addresses when email is edited. We take a list of every address in the db or logs, then figure out which actually correspond to donors who haven't opted out in some way. Everything else is suppressed.

I think we can update it so that if a person has neither donated nor opted out, they don't get exported on either list. Is that the right thing to do?

I'm running a modified version of the export (P3598) in my own database on the staging server to see who was put on the list by mistake.

This query will backfill wmf_contribution_extra for all donations > $200 ( or all donations if you leave off the last clause)

insert into wmf_contribution_extra ( entity_id, total_usd, original_amount, original_currency, source_name)
select
  c.id as entity_id, 
  total_amount as total_usd,
  substr(source, 4) as original_amount,
  left(source, 3) as original_currency,
  'BACKFILL' as source_name 
from civicrm_contribution c 
left join wmf_contribution_extra x on c.id = x.entity_id
where x.id is null
and c.total_amount > 200

@awight, want to review?

Ejegg added a comment.Jul 29 2016, 6:10 AM

Oh boy, it's a bunch - almost 300k donors, mostly those who last gave more than 3 years ago. There's a list on the file server in Development/Email/suppressedForMissingWmfContExtra.txt . There are another 18k with no donation record in suppressedForNoDonation.txt . I guess it doesn't make sense to un-suppress them just yet, as they'll be on the opt-out lists till we fix the export.

@Ejegg Maybe toss gateway in there while you're at it? Not a blocker, though... The backfill query looks correct otherwise--what happens if the source field is damaged, though? Might check for that and use an if, if so.

Phew, well thank you for finding it now! I'll figure out the best way to
purge these 300k out of Silverpop and wait for your OK to proceed :)

Oh @Ejegg, in response to your question:

I think we can update it so that if a person has neither donated nor opted out, they don't get exported on either list. Is that the right thing to do?

Sure, that should be fine. It's also fine to leave those in the main list; we use latest_donation_date to build our lists so we won't email people who don't have a value in that field.

Is the 300k people only those with donations > $200, or all donors?

Ejegg added a comment.Jul 29 2016, 5:48 PM

300k is the total number of donors who were excluded because of missing wmf_contribution_extra rows.

Ejegg added a comment.Jul 29 2016, 6:20 PM

@awight, much more detailed backfill here: P3602 . It should deal with malformed 'source' and should get gateway info in most cases, and should cause the minimum disruption in terms of table locking.

Change 301857 had a related patch set uploaded (by Ejegg):
Quit suppressing contacts with no donation record

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

Change 301857 merged by jenkins-bot:
Quit suppressing contacts with no donation record

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

@Ejegg woo, thanks for the quick fix here! So should I run that query you
sent earlier and purge the list out of silverpop? Or do we still want to
wait?

Le mardi 2 août 2016, Ejegg <no-reply@phabricator.wikimedia.org> a écrit :

Ejegg moved this task from Review to Done on the Fundraising Sprint
Nitpicking board.
*TASK DETAIL*
https://phabricator.wikimedia.org/T139102

*WORKBOARD*
https://phabricator.wikimedia.org/project/board/2133/

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

*To: *Ejegg
*Cc: *gerritbot, awight, Ejegg, JCuriel, CaitVirtue, CCogdill_WMF,
Aklapper, LeanneS, Zppix, DStrine, cwdent

Ejegg added a comment.Aug 2 2016, 5:22 PM

Hi @CCogdill_WMF, I updated the export script this morning and it's running right now.

Backfilling the wmf_contribution_extra data yesterday cut the suppression list down from 987,440 to 690,175. Today's update should cut it down by another 18k.

If you want to start fixing the blacklist right now, the list I exported as Development/Email/suppressedForMissingWmfContExtra.txt won't change.

Once the current export is uploaded and processed on the Silverpop side we can do another query to un-suppress emails that legitimately don't have donations. I think that'll just be

SELECT email
FROM silverpop_export
WHERE latest_donation IS NULL
AND opted_out=0

Thanks again, @Ejegg! Pretty exciting to get 315k donors back on our list :)

@MBeat33 can you keep an eye out for donors saying they were unsubscribed
who are still getting emailed? I just want to tread carefully by removing
these people from the suppression list, so let's be vigilant about any
complaints we receive.

This is all great news, thanks @Ejegg and @CCogdill_WMF!!

u, Aug 4, 2016 at 3:28 PM, CCogdill_WMF <no-reply@phabricator.wikimedia.org>
wrote:

CCogdill_WMF added a subscriber: MBeat33.
CCogdill_WMF added a comment.

Thanks again, @Ejegg https://phabricator.wikimedia.org/p/Ejegg/! Pretty
exciting to get 315k donors back on our list :)

@MBeat33 https://phabricator.wikimedia.org/p/MBeat33/ can you keep an
eye out for donors saying they were unsubscribed
who are still getting emailed? I just want to tread carefully by removing
these people from the suppression list, so let's be vigilant about any
complaints we receive.

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

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

*To: *Ejegg, CCogdill_WMF
*Cc: *MBeat33, gerritbot, awight, Ejegg, JCuriel, CaitVirtue,
CCogdill_WMF, Aklapper, LeanneS, Zppix, DStrine, cwdent

+1 @CaitVirtue

@CCogdill_WMF I will make sure the DS team is keeping a collective eye out for unsubscribed donors who are still getting emails.

Ejegg closed this task as Resolved.Aug 17 2016, 6:03 PM
mmodell removed a subscriber: awight.Jun 22 2017, 9:49 PM