Closed Bug 1048940 Opened 10 years ago Closed 9 years ago

Investigate mass unsubscription of vouched Mozillians emails

Categories

(Participation Infrastructure :: Phonebook, defect)

defect
Not set
normal

Tracking

(Not tracked)

RESOLVED FIXED

People

(Reporter: williamr, Unassigned)

References

Details

Attachments

(1 file)

Sometime during June 11-13, we had 974 vouched Mozillians unsubscribed from Exact Target. We have a feeling this happen unintentionally.

We sent two messages to all vouched Mozillians during this time period. Note the difference in number of emails sent.

Date/Time              Emails sent
6/11/2014 9:39 AM      3673
6/13/2014 9:50 PM      2699

We know there was an increase in traffic surrounding the vouched Mozillian flag on Exact Target, though we have no records of the API calls.

There was a spike in Exact Target API calls related to the Mozillians flag (red line is the custom view for mozillians FLG, blue is subscribe):

https://www.dropbox.com/s/glj99xd1a42rru7/Screenshot%202014-08-01%2013.26.27.png
Giorgos or Sancus, any ideas of what could have caused the mass unsubscription during June 11-13? Could it be related to the vouching changes?
Flags: needinfo?(sancus)
Flags: needinfo?(giorgos)
See Also: → 1046686
Blocks: 1046686
Activity during 11-13 Jun 2014:
 - No code pushes for mozillians to production. Last push May 22nd and next push Jun 25th. 
 - No code pushes for basket to production. Last push May 21nd
 - Checked mozillians production admin logs:
   - On Jun 11th we deleted 20 spam profiles. This would cause 20 unsubcriptions from the newsletter. It doesn't look related. bug 1007416
 - Checked New Relic didn't spot any issues, related activity
 - Checked google analytics, 294 pages views of /user/edit -which is the only page that can trigger basket updates- between Jun 10 - Jun 13. The number is too small compared to the 900 unsubscriptions, doesn't look related
 - Checked bugmail during these days:
   - I cannot see the graph (broken link) but I would expect a spike in basket requests on Jun 12th due to bug 1024094 which was responsible for running the geocode migration on stage (stage shares basket tokens with prod). The activity should be related to updates and not unsubscriptions. Similar spike should exist in Jun 26th (bug 1030613)
Flags: needinfo?(giorgos)
Following up on the last point here is what could have happened:

 1. Bug 1024094 run the geocode update for stage

 2. Geocode update updated saved each profile on stage.

 3. Profiles on stage have the same data with prod except for email addresses. Email addresses where sanitized in the following format XXX@mozilla.com where XXX is a number (user.id)

 4. On profile save (caused by 2) the basket update task gets triggered

 5. Basket update task thinks that user has changed their email address because of 3 and therefore unsubscribes the "old" email address from the newsletter and re-subscribes the user with the XXX@mozilla.com email address


Open questions:
 a. Verify that mozillians stage is connected to basket
 b. Verify that mozillians newsletter got new subscriptions on Jun 12th from XXX@mozilla.com where XXX is number e.g. 100@mozilla.com
 c. This doesn't explain why only some email addresses where removed from the newsletter. Any ideas Sancus?


Next steps:
 - Disconnect mozillians-stage from basket if connected. Sancus, if I remember correctly we discussed about doing that in the past but to keep stage as close to prod as possible we agreed not to. I don't see any benefit from having this connection though. What do you think?
(In reply to Giorgos Logiotatidis [:giorgos] from comment #3)
>  b. Verify that mozillians newsletter got new subscriptions on Jun 12th from
> XXX@mozilla.com where XXX is number e.g. 100@mozilla.com

Jess can you verify this?
Flags: needinfo?(jdavis)
(In reply to Giorgos Logiotatidis [:giorgos] from comment #3)
> Open questions:
>  a. Verify that mozillians stage is connected to basket

Opened bug https://bugzilla.mozilla.org/show_bug.cgi?id=1049459
(In reply to Giorgos Logiotatidis [:giorgos] (afk Aug 11-20) from comment #4)
> (In reply to Giorgos Logiotatidis [:giorgos] from comment #3)
> >  b. Verify that mozillians newsletter got new subscriptions on Jun 12th from
> > XXX@mozilla.com where XXX is number e.g. 100@mozilla.com
> 
> Jess can you verify this?

Just ran a query for anyone who had a MOZILLA_PHONE_DATE between June 10 & 14 and an email address that ended in "@mozilla.com"


2,781 records were updated during this time
2 on June 11
1 on June 13
The rest on June 12

However! None of these email addresses were numbers (ie XXX@mozilla.com where XXX is number e.g. 100@mozilla.com).

And all of them currently have a MOZILLA_PHONE_FLG = N, except for the one updated on June 13th. The June 13th record does not matter - I just wanted to show that we got everyone with an @mozilla.com email address that could have been affected with this mass unsubscribe.

It looks like there was a mass unsubscribe of current Mozillian's email addresses that ended in @mozilla.com, but there was not an addition of new numbered email addresses that ended in @mozilla.com.

FWIW, Mozillians emails sent via ExactTarget *exclude* anyone with an email address that ends in @mozilla.com.  This means that outside of these staff emails there were ~1000 non-mozilla.com domain email addresses that were unsubscribed as well.

Please let me know if I can be of further help. I'm holding on reactivating anyone to Mozillians list until we have all the data we need to find the root of the cause. (I don't want to switch people's FLGS back to Y yet and mess up our data.)
Flags: needinfo?(jdavis)
Attaching screenshot to bug -- a more permanent solution than an external account like dropbox.
Sancus, could you answer the questions in comment 3 and give your thoughts on comment 6? I wonder if comment 6 provides enough information find out the root cause.

Giorgos will be offline for a while, though he could possibly look into this later next week.

Once we know the root cause, Jess will reactivate the people on the vouched Mozillians list who were accidentally unsubscribed.
Thank you Jess, valuable feedback. A couple of more questions to make sure that I understand correctly:

(In reply to Jessilyn Davis from comment #6)
> It looks like there was a mass unsubscribe of current Mozillian's email
> addresses that ended in @mozilla.com, but there was not an addition of new
> numbered email addresses that ended in @mozilla.com.

Questions: 
 1. There was no addition of _new_ numered email addresses but you do have XXX@mozilla.com emails in your database, correct? 
   
    Maybe we updated these entries.

 2. Do you only see unsubscribes during this period, or you also see equal number of subscribes as well?
  
    My theory is that we unsubscribed people (with mozilla.com or other email address) and re-subscribed them as XXX@mozilla.com (all with mozilla.com addresses)
(In reply to Giorgos Logiotatidis [:giorgos] from comment #3)
> 
> Next steps:
>  - Disconnect mozillians-stage from basket if connected. Sancus, if I
> remember correctly we discussed about doing that in the past but to keep
> stage as close to prod as possible we agreed not to. I don't see any benefit
> from having this connection though. What do you think?

stage server is indeed connected to Basket (comment #5 and bug 1049459). After re-thiking, I propose that we keep the connection but we delete all basket tokens imported from prod, so basket accounts in stage are unrelated to basket accounts in prod.
Add needinfo for comment #9
Flags: needinfo?(jdavis)
(In reply to Giorgos Logiotatidis [:giorgos] from comment #9)
> Thank you Jess, valuable feedback. A couple of more questions to make sure
> that I understand correctly:
> 
> (In reply to Jessilyn Davis from comment #6)
> > It looks like there was a mass unsubscribe of current Mozillian's email
> > addresses that ended in @mozilla.com, but there was not an addition of new
> > numbered email addresses that ended in @mozilla.com.
> 
> Questions: 
>  1. There was no addition of _new_ numered email addresses but you do have
> XXX@mozilla.com emails in your database, correct? 

Thanks for clarifying - I just took a fresh look at the data for any/all email addresses with an @mozilla.com email address in our system - and found ~2600 XXX@mozilla.com email addresses. And, they all have MOZILLA_PHONE_FLG = Y & MOZILLA_PHONE_DATE = 6/12/14. I'm unsure why they didn't appear in the last query.

>    
>     Maybe we updated these entries.

It looks like you did!

> 
>  2. Do you only see unsubscribes during this period, or you also see equal
> number of subscribes as well?

We actually don't see unsubscribes or subscribes at first glance; we just see x number of people currently have a subscription FLG of Y. 

Then it's up to some detective work to look into the date fields to determine when someone may have subscribed or unsubscribed:

If a person currently has a FLG = Y, assume the date field was the time of subscribe. 
If a person currently has a FLG = N, assume the date field was the time of unsubscribe.


>   
>     My theory is that we unsubscribed people (with mozilla.com or other
> email address) and re-subscribed them as XXX@mozilla.com (all with
> mozilla.com addresses)


I think you solved it with the "unsubscribe people with other email address" piece. We never send @mozilla.com email addresses Mozillians email comms - we have a filter that excludes sending to any/all @mozilla.com email addresses. We do this to avoid @mozilla.com email addresses from getting duplicate messages (one from the original email sent to all@, and the other from sending via ExactTarget).

So! The missing 974 non-@mozilla.com email addresses, must have been overridden with an XXX@mozilla.com email address when the 2600 email addresses were updated on June 12th. The other 1626 updated email addresses (to solve for the 2600 XXX@mozilla.com email addresses), were @mozilla.com email addresses.

I think Giorgos has cracked the case!

Now that we know what caused it, what's the plan to make sure that this kind of overwrite is prevented? I see something in comment #10, but that's a one time fix?

We also now have keys to a sandbox/testing environment of ExactTarget that we could set up for dev and stage testing. This way prod data only goes to the prod version of ExactTarget, and testing data only goes to the testing environment of ExactTarget. Would that be helpful?
Flags: needinfo?(jdavis)
Great! Good to know what caused the problem. 

To prevent this from happening again:
 1. I will file a bug to remove the stored basket tokens in mozillians stage server. (This will complete today-ish and does not conflict with (2)) 
 2. I will file a bug to setup a sandbox basket/ET on stage. That's very very helpful!
Flags: needinfo?(sancus)
(In reply to Giorgos Logiotatidis [:giorgos] from comment #13)
>  1. I will file a bug to remove the stored basket tokens in mozillians stage
> server. (This will complete today-ish and does not conflict with (2)) 

And that's bug 1048940
(In reply to Giorgos Logiotatidis [:giorgos] from comment #14)
> (In reply to Giorgos Logiotatidis [:giorgos] from comment #13)
> >  1. I will file a bug to remove the stored basket tokens in mozillians stage
> > server. (This will complete today-ish and does not conflict with (2)) 
> 
> And that's bug 1048940

Erm I mean bug 1059270
Commit pushed to master at https://github.com/mozilla/mozillians

https://github.com/mozilla/mozillians/commit/3f10e7dda95279aead0278fffa6b3623f1249803
[bug 1048940] Remove basket_token from stage dump.

Stage and Prod share the same Basket and ET setup which can result in
updating the email subscription preferences of users while testing on
stage.

We remove the basket tokens from stage dumps which, in combination with
the email address change of all profiles, will result in users getting
subscribed as new users in Basket/ET and therefore overcome this issue.

In the future we will have a staging Basket/ET setup connected with
mozillians stage and this change will be still needed, since the Prod
tokens will be no longer valid.
(In reply to Giorgos Logiotatidis [:giorgos] from comment #13)
>  2. I will file a bug to setup a sandbox basket/ET on stage. That's very
> very helpful!

And that's bug 1064163
We figured out the reason of the un-subscriptions, we re-subscribed the unsubscribed and we filed bugs to prevent this from happening again. I believe we can call this fixed. :williamr?
Flags: needinfo?(williamr)
We didn't have any more related issues. Per comment #18 I mark this one Fixed. Thanks everyone!
Status: NEW → RESOLVED
Closed: 9 years ago
Flags: needinfo?(williamr)
Resolution: --- → FIXED
You need to log in before you can comment on or make changes to this bug.

Attachment

General

Created:
Updated:
Size: