Page MenuHomePhabricator

Changing email address in Civi should be exported to Silverpop
Closed, ResolvedPublic2 Story Points

Description

When a donor emails Donor Services asking us to change their email address, DS can't simply go into Civi, find the contact record, and edit the email address. If they do that, Silverpop never gets notification that the old email address needs to be deleted, so we have seen donors complain because they are getting emails at the new and old address after being helped by DS.

Our workaround has been for agents to email me (ccogdill) whenever performing an email address update and tell me what email address I need to opt out of Silverpop. I've done about 20 of these in the past week, but there were more before I realized DS wasn't successfully opting out old addresses. Having to involve two people in one simple task, especially when it pulls me out of my own work so completely, is a shame.

Can we find a way for Civi to add an email address to the unsubscribe list when DS updates a contact record with a new email?

Related Objects

Event Timeline

There are a very large number of changes, so older changes are hidden. Show Older Changes
CCogdill_WMF updated the task description. (Show Details)Dec 24 2015, 6:57 PM
CCogdill_WMF set Security to None.

Just an update - I've had to do upwards of 150 of these email address updates in the last 2 weeks, which means that's over 150 for both me and whatever DS agent sends these to me. I will certainly up the priority on this as we plan work for next Big English if it hasn't been looked at by then.

CCogdill_WMF raised the priority of this task from Low to Normal.Jan 21 2016, 11:26 PM

Changing priority because this is continuing to be very time consuming!

Ejegg added a subscriber: Ejegg.Jan 21 2016, 11:27 PM

Could do this with audit logging, but we're not sure if we'll be able to use that.
Otherwise we could add an edit hook that adds the deleted email address to a table.

Bringing this up again since we're in the Civi design meeting... this edit hook idea Elliott mentioned sounds promising :P This is still really annoying, and takes up my time daily.

CCogdill_WMF raised the priority of this task from Normal to High.Mar 15 2016, 9:49 PM
DStrine renamed this task from Civi should allow users to do email address changes that are communicated to Silverpop to Changing email address in Civi should be exported to Silverpop.Mar 15 2016, 9:50 PM

CCogdill says that all we really care about is that the old email is added to our unsubscribed list.

Eileen points out that we can do this from a hook on contact save.

DStrine set the point value for this task to 2.Mar 16 2016, 10:52 PM
awight claimed this task.Apr 5 2016, 5:12 PM
awight moved this task from Backlog to Doing on the Fundraising Sprint Ghostbusting board.
awight added a comment.Apr 5 2016, 5:19 PM

@CCogdill_WMF
I'm pretty surprised, but we currently aren't doing anything with the is_primary flag on civicrm_email. Respecting this flag might be the solution to our troubles. So we're currently adding all of a contact's email addresses to the mailing list, but what I would recommend instead is that we only add the primary email to the mailing list, and all other addresses be added to the unsubscribe list. Then, we create the save hook as suggested, but it works by adding the previous email address as an additional "outdated" email on the contact, if the agent hasn't done so already.

Your thoughts?

Yikes @awight, this is_primary thing has come up before. The more I think about it, the more it feels like this is really high priority. It's not good contact management for us to be emailing all of a donor's addresses if we can help it!

Sounds like a perfect solution to me to use the edit hook you mention. I think only using is_primary needs to happen either way. Just to be sure -- there's no way that a contact could *not* have a primary email, right?

Also, as context on continued volume — I've performed 95 manual unsubscribes since last Monday, 3/28 :D

Just to be sure -- there's no way that a contact could *not* have a primary email, right?

All contacts who we have email addresses for will have exactly one primary email. People without an email address (or anonymous@wmo) will have zero primary emails.

I'll make very certain that when saving a changed email, we do the correct thing by setting the current is_primary email to the new address after the operation.

I ran a check on our database,

create table awight.tmp1 select count(*) as num_addresses, contact_id from civicrm_email group by contact_id;
create table tmp2 select num_addresses, count(*) as count from tmp1 group by num_addresses;
select * from tmp2;
+---------------+----------+
| num_addresses | count    |
+---------------+----------+
|             1 | 14872051 |
|             2 |    98621 |
|             3 |     1339 |
|             4 |       84 |
|             5 |       69 |
|             6 |       74 |
|             7 |        1 |
|            15 |        1 |
+---------------+----------+

-- And then zero.
select count(*) from civicrm_contact c left join civicrm_email e on c.id=e.contact_id where e.id is null;
163184

Change 281884 had a related patch set uploaded (by Awight):
[WIP] double up on exclusion logic--treat !is_primary the same as is_deleted

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

Change 281889 had a related patch set uploaded (by Awight):
Split out contribution save hook

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

Change 281891 had a related patch set uploaded (by Awight):
[WIP] Stub contact save hook

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

@Eileenmcnaughton
I'll go ahead and implement this as a hook, but I'm starting to think it should be core functionality. When a primary email address is changed, it's probably going to be one of two cases,

  • Fixing a mistake in the address.
  • Updating the contact with a new address.

Either way, we might benefit by keeping a copy of the old address as a non-primary, "Old", maybe even hidden, associated civicrm_email record. The date range during which it was the primary address could also be useful information, but not necessary to have.

Cool @awight, thanks!

select count(*) from civicrm_contact c left join civicrm_email e on c.id=e.contact_id where e.id is null;

163184

Does that mean there should be 163k new opt-outs once this is rolled out?

awight added a comment.Apr 6 2016, 9:20 PM

@CCogdill_WMF
No, that's the number of contacts with no associated email address. They won't show up in any of our export lists, cos no email ;)

Got it :) I guess this won't trigger any major influx of opt-outs at all,
because it'll only apply to future actions.

awight added a comment.Apr 6 2016, 9:39 PM

@CCogdill_WMF
Actually, you were right, there should be a flood of opt-outs. I'm thinking, roughly 50k new opt-outs: the secondary emails for everyone in the "2 addresses" group.

Okay okay really got it this time. Let me know what day I should expect to
see the flood and I'll confirm the total number.

Change 281884 merged by Ejegg:
Double up on exclusion logic--treat !is_primary the same as is_deleted

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

Eileen has a nice workaround for this, we just query log_civicrm_email! This will make the silverpop query a bit more complicated, but what else is new.

Change 281891 abandoned by Awight:
[WIP] Stub contact save hook

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

Change 282106 had a related patch set uploaded (by Awight):
[WIP] Unsubscribe old emails; fix is_primary bug

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

awight added a comment.Apr 7 2016, 3:02 PM

@Eileenmcnaughton This patch is waiting on logging in production, now... I see that you're using the database name "log_civicrm" on staging, will that be the same on production?

Change 282304 had a related patch set uploaded (by Awight):
[DO NOT MERGE] Calculate opt-outs using change log

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

Change 281889 merged by Ejegg:
Split out contribution save hook

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

Hi, any update on this? Currently spending half my afternoon unsubscribing emails in our Silverpop database...

@CCogdill_WMF
So sorry to hear it! My code uses the logging data, so we're blocked until we can deploy that. Logging deployment is tracked as T132056, today is a trial deployment and if that goes well, we might be able to turn logging on next week.

Okay thanks for the update @awight.

awight added a comment.May 4 2016, 9:48 PM

ping: This one is ready for review and deployment now that logging tables are deployed.

Hey @MBeat, have you handled any of these cases recently? Can you confirm
this is working?

@CCogdill_WMF , the email address in the header of Zendesk #223914 I recently merged into the donor's preferred email address (the one in the comment box). The address from the header shouldn't be in Silverpop - is there a way I can view that?

Thanks for the example! This email is still in our main database in
Silverpop, not on the unsusbcribe list. BUT, you processed this 40 minutes
before Adam deployed the change... Maybe that's why it didn't work? Can you
send me any that happened after 3pm PST yesterday?

I'll opt out this email address manually now.

The most recent one I could find was #225749, but it looks like it was before 3PST - I'm sure we'll get a few new ones tomorrow, and will add one here.

Sounds good, thank you!

SORRY! I meant ready for code review--the change is not deployed yet.

Oh... oops! My mistake, getting too excited trying to clean out the
vacation backlog. Thanks for clearing that up :P

Change 282304 abandoned by Awight:
[DO NOT MERGE] Calculate opt-outs using change log

Reason:
squashed into @I37b1f5dbd660fa6ecc0b9f1227b12fbf6f555c88

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

Change 282106 merged by jenkins-bot:
Unsubscribe old emails; fix is_primary bug

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

Deployed and ran the new script, and it died after the database work, during the export to file. Heads up, the "export only" Jenkins job might be useful for this.

I'll create a subtask to track the new failure.

Fun new log_civicrm_email logic has been rolled back.

Change 289716 had a related patch set uploaded (by Ejegg):
Remove old email edit hook

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

Change 291136 had a related patch set uploaded (by Ejegg):
Keep Civi users off suppression list

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

Change 291136 merged by jenkins-bot:
Keep Civi users off suppression list

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

Change 289716 merged by Ejegg:
Remove old email edit hook

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

New changes are deployed. @CCogdill_WMF Heads up, tonight's export will include code that "keeps civi users off suppression list", let us know if the results look good to you.

Great news, I'll keep an eye out. Thanks!

Le mardi 7 juin 2016, awight <no-reply@phabricator.wikimedia.org> a écrit :

awight moved this task from Pending Deployment to Done on the Fundraising
Sprint Killing Time board.
TASK DETAIL

https://phabricator.wikimedia.org/T122411

WORKBOARD

https://phabricator.wikimedia.org/project/board/2011/

EMAIL PREFERENCES

https://phabricator.wikimedia.org/settings/panel/emailpreferences/

To: awight
Cc: Eileenmcnaughton, gerritbot, DStrine, MBeat33, Ejegg, CCogdill_WMF,
Aklapper, Lewizho99, Maathavan, cwdent, RLewis

Just want to confirm this worked on the Silverpop side. Thanks!

Ejegg closed this task as Resolved.Jun 13 2016, 3:38 PM

Woohoo! Closing this ticket.