Migrate Subscriber Email Lists and Sign-up forms to Exact Target

[adamlofting]

Depends on: Setting up email templates in ET

Key Steps:

• [x] Decide which subscriber lists we need going forward (expecting: Mozilla Foundation, Webmaker, Learning Networks)
• [x] Work with Jess and Pmac at MoCo to decide how we setup these lists in ET and Basket (for email preference center)
• [ ] Update email sign-up forms to talk to ET (via Basket API)
  • Not all sign-up forms will need migrating
  • Audit of existing forms here
• [ ] Import current mailing lists into ET (needs support of DEG due to basket token process)

[adamlofting]

I've started these conversations with MoFo teams who currently have email lists in BSD to agree the lists we need moving forward.

[adamlofting]

Booked a call to talk through the details of ET on Monday.

There will be a Mozilla list, a Webmaker list and an MLN list, but I'm also talking further on Monday about some smaller lists related to Maker Party, Mozfest and others to make sure our plan works for those requirements.

[adamlofting]

Met with Jess, Sean and Joel today: https://etherpad.mozilla.org/mofo-lists-in-basket

I have a clearer understanding of the setup now.

The basic plan remains the same, but we'll think about about of naming issues from a user facing point of view, which I think will be good for the programme overall.

Open question is about how we want to do the big one-time import of our existing lists. We'll setup a call to follow up with this.

[jessilyndavis]

1) Jess to set up Basket + ExactTarget integration: Adam, can you confirm this information: https://docs.google.com/spreadsheets/d/1xtOKfA10-a0yeoxc9uwkDZ9iXY2zRwR9_MLDav14yFs/edit#gid=0

Looking for:

• User-facing Name for the newsletters (col A)
• Input on an intuitive subscription FLG that we use on the backend (col F)
• Description (col H) - not a blocker, but would be nice to have sooner rather than later.

2) Foundation Email Opt-in signup forms connected to Basket + ET Once item 1 is complete, forms can be updated to go into ET. Adam, do you have devs that can update the signup forms?

3) Import current mailing lists into ET Work is started here: https://bugzilla.mozilla.org/show_bug.cgi?id=1194810

Steps (to my knowledge right now):

1. Once 1 & 2 are done, we'll need a secure place for DEG to get the database of email addresses.
2. DEG will then compare the list to our current list of subscribers in ExactTarget and weed these individuals out.
3. DEG will provide Pmac access to a list of email addresses of individuals who do not currently exist in ExactTarget.
4. Pmac will assign tokens to these individuals.
5. An import file containing the columns of: email address, token, and subscription flgs with corresponding Y values, will need to be created. (Or is there a better way to do this for 1.5 million records?)
6. DEG will assist with importing.

[jessilyndavis]

As part of item 2: Foundation Email Opt-in signup forms connected to Basket + ET

Double opt-in and welcome emails need to be set up inside of ET for each newsletter list.

Needs: Template Copy Sender information

• From name & email address ("____@e.mozilla.org" - most newsletters use "mozilla@e.mozilla.org")
• Reply-to email address

[adamlofting]

Thanks @jessilyndavis

1) Jess to set up Basket + ExactTarget integration: Adam, can you confirm this information:

Yes, I'll get this confirmed asap. As a first response, it looks good to me though.

Adam, do you have devs that can update the signup forms?

Yes, this work is on MoFo to complete. I need to schedule this with various teams.

Double opt-in and welcome emails need to be set up inside of ET for each newsletter list.

TBC if we do this - we're not double opt-in currently. So I need to check if teams want to change this.

Other notes

The other task is Setting up email templates in ET - we can't do the migration until ET is ready to use in this respect too.

[adamlofting]

Work we can do now (no blockers):

• Setup lists in ET with current names
• Setup email templates in ET
• Get devs for each MoFo team to review Basket integration and schedule work to update subscription forms
• Work out BSD data export options
• Map fields from BSD to ET

Then:

• Update current subscriptions forms to use Basket

After all subscription forms are updated:

• Export data from BSD, import to ET

[jessilyndavis]

Hey Adam!

Double opt-in and welcome emails need to be set up inside of ET for each newsletter list.

TBC if we do this - we're not double opt-in currently. So I need to check if teams want to change this.

Explicit opt-in in some countries means using a double opt-in process, where the person signing up must confirm their email address (via a transactional email) and intent to subscribe to the email communications before you can communicate with him/her. I don't believe Mozilla has a hard & fast rule on this - but it is a best practice especially as our email programs span global audiences.

[jessilyndavis]

For migrating subscribers, I had a chat with DEG. This is the best way to proceed to make sure we have all the information where we need it in ET from BSD:

They are going to:

• Take records from BSD, match those that exist in ET already.
• Outputs: 1 file with all matches (subscription flags as well), 1 file with no matches for pmac to assign a token.
• Data enhancement/cleanup. To be done by MoFo. \ This means to create a .csv file for all 1.5 million rows of data from BSD to match to the columns in ExactTarget. Is this doable? (Do y'all have the resources to run queries and assign values to columns for email subscribers easily?)
• Import of all 1.5 million records into SFMC.

[jessilyndavis]

Heyo! Happy Sept 1! In order to make our Q3 goal, we need the list of email addresses to be matched to tokens no later than Monday, Sept 21. @adamlofting, is that doable? Will the signup forms be able to be converted by then?

I'm setting up the basket IDs in our dev environment today so that development and testing can begin on changing over the signup forms.

[jessilyndavis]

Basket Dev environment (that connects to ExactTarget Sandbox account) has been setup!

Information can be found here: https://docs.google.com/spreadsheets/d/1xtOKfA10-a0yeoxc9uwkDZ9iXY2zRwR9_MLDav14yFs/edit#gid=0

Newsletter IDs (to be used in the Basket form code) are: mozilla-foundation webmaker mozilla-learning-network mozilla-festival

Once you sign up and get a token back from the API call, you can use it to see if the subscription process worked by going to:

https://www-dev.allizom.org/en-US/newsletter/existing/TOKEN

[adamlofting]

Heyo! Happy Sept 1! In order to make our Q3 goal, we need the list of email addresses to be matched to tokens no later than Monday, Sept 21. @adamlofting, is that doable? Will the signup forms be able to be converted by then?

Confirming this data extract is ready (it's in a database we control now, being kept in sync hourly with updates to BSD).

File will include 1.6 million subscribers:

• bsdId, bsdCreatedAt, emailAddress, firstName, lastName, countryCode, subscribedMofo, subscribedWebmaker, subscribedLearning, subscribedMozfest, interestMakerparty

We also have the following fields which I don't think need including in ET.

• addr1, addr2, city, state, zip

[jessilyndavis]

Nice! Quick questions inline:

On Fri, Sep 11, 2015 at 9:04 AM, Adam Lofting notifications@github.com wrote:

Heyo! Happy Sept 1! In order to make our Q3 goal, we need the list of email addresses to be matched to tokens no later than Monday, Sept 21. @adamlofting https://github.com/adamlofting, is that doable? Will the signup forms be able to be converted by then?

Confirming this data extract is ready (it's in a database we control now, being kept in sync hourly with updates to BSD).

What's the plan for the incoming signups that happen after we send a list of subscribers to DEG to match tokens, Pmac to assign tokens, and then import into ET?

Is it possible that the signup forms will be converted to Basket?

File will include 1.6 million subscribers:

• bsdId, need to keep up with in ET?
• bsdCreatedAt, CREATEDDATE ? Can we use this existing field in ET?
• emailAddress, EMAILADDRESS
• firstName, FIRST_NAME
• lastName, LAST_NAME
• countryCode, COUNTRY_ (2 digit country codes like: http://www.web-l.com/country-codes/) - will that work?
• subscribedMofo, FOUNDATION_FLG = Y
• subscribedWebmaker, WEBMAKER_FLG = Y
• subscribedLearning, MOZ_LEARN_FLG = Y
• subscribedMozfest, MOZFEST_FLG = Y
• interestMakerparty, NEED TO CREATE - can you fill in the info in the gdoc https://docs.google.com/spreadsheets/d/1xtOKfA10-a0yeoxc9uwkDZ9iXY2zRwR9_MLDav14yFs/edit#gid=0 ?

Can you also send me the grouping of the newsletters + interests for incoming signups? For example, if someone opts into Makterparty, what newsletter(s) will they need to also be signed up for?

-

We also have the following fields which I don't think need including in ET.

• addr1, addr2, city, state, zip

  Nice.

— Reply to this email directly or view it on GitHub https://github.com/MozillaFoundation/mofo-crm/issues/21#issuecomment-139540365 .

[adamlofting]

What's the plan for the incoming signups that happen after we send a list of subscribers to DEG to match tokens, Pmac to assign tokens, and then import into ET?

Two options:

1. Delay bulk import until all sign-up forms are converted
2. Use the BSD scraper app to keep any new or updated records in sync with ET after an initial bulk import is done (until all sign-up points are updated)

Is it possible that the signup forms will be converted to Basket?

I was originally aiming for option 1, but option 2 avoids the blocker of coordinating all the updates. This keeps us on track for end of Q3.

NEED TO CREATE - can you fill in the info in the gdoc

I've updated the Google Doc via comments (I didn't have editing permission).

Quick questions inline:

All the mapped fields look good to me.

incoming signups? For example, if someone opts into Makterparty, what newsletter(s) will they need to also be signed up for?

These rules will be true of the historic data when imported. But I believe need configuring in ET to manage future subscriptions.

• Subscribe to Mozilla Foundation
  • No further action needed
• Subscribe to Webmaker
  • Also subscribe to Mozilla Foundation
• Subscribe to Mozilla Learning Network
  • Also subscribe to Mozilla Foundation
• Subscribe to Mozilla Festival
  • Also subscribe to Mozilla Foundation
• Subscribe to Maker Party
  • Also subscribe to Mozilla Learning Networks
  • Also subscribe to Mozilla Foundation

[jessilyndavis]

On Mon, Sep 14, 2015 at 10:25 AM, Adam Lofting notifications@github.com wrote:

What's the plan for the incoming signups that happen after we send a list of subscribers to DEG to match tokens, Pmac to assign tokens, and then import into ET?

Two options:

1. Delay bulk import until all sign-up forms are converted
2. Use the BSD scraper app to keep any new or updated records in sync with ET after an initial bulk import is done (until all sign-up points are updated)

Is it possible that the signup forms will be converted to Basket?

I was originally aiming for option 1, but option 2 avoids the blocker of coordinating all the updates. This keeps us on track for end of Q3.

Cool - re: #2, there will need to be some consideration for timing of API calls to keep the databases in sync across the systems so that double opt-in confirm messages, and welcome emails, are fired in a timely manner from ET, when an incoming signup happens in BSD.

NEED TO CREATE - can you fill in the info in the gdoc

I've updated the Google Doc via comments (I didn't have editing permission).

I've updated Basket Dev and our Sandbox ET account.

Quick questions inline:

All the mapped fields look good to me.

incoming signups? For example, if someone opts into Makterparty, what newsletter(s) will they need to also be signed up for?

These rules will be true of the historic data when imported. But I believe need configuring in ET to manage future subscriptions.

• Subscribe to Mozilla Foundation
  • No further action needed
• Subscribe to Webmaker
  • Also subscribe to Mozilla Foundation
• Subscribe to Mozilla Learning Network
  • Also subscribe to Mozilla Foundation
• Subscribe to Mozilla Festival
  • Also subscribe to Mozilla Foundation
• Subscribe to Maker Party
  • Also subscribe to Mozilla Learning Networks
  • Also subscribe to Mozilla Foundation

I just updated this in Basket Dev (that's connected to ET Sandbox). The thing to look out for will be how to handle double opt-in for these newsletter groups and making sure that when someone is opt-ed into multiple subscriptions, they are (or are not) sent multiple welcomes. I'm unsure how Basket currently handles this. Need to test and document

Basket Dev and Sandbox has also been set up to do double opt-in for "Mozilla Foundation" in EN and DE languages with placeholder text for confirm and welcome emails.

It's ready for testing the signup form points.

Once it's working appropriately in Dev, we'll need to set up Stage (then Production) Basket, and our live ET account (that's connected to Basket Stage & Production) - and test each configuration.

Please let me know when testing starts on dev and the plan to move to stage and prod.

-

— Reply to this email directly or view it on GitHub https://github.com/MozillaFoundation/mofo-crm/issues/21#issuecomment-140097981 .

[jessilyndavis]

For testing, here is a doc I use to test different signup forms:

https://docs.google.com/spreadsheets/d/1xtOKfA10-a0yeoxc9uwkDZ9iXY2zRwR9_MLDav14yFs/edit#gid=516902671

If you'd like, edit this tab "9/22 - Foundation - Testing on Dev" to put in the different places to test the Foundation signup form scenarios and expected behavior.

Let me know if you'd like help filling out, or if you've got it.

[jessilyndavis]

Circling round to make sure we have a plan for the fields coming out of BSD, and the fields that will be updated in ET:

• bsdId, need/want to keep up with in ET?
• bsdCreatedAt, CREATEDDATE
• emailAddress, EMAILADDRESS
• firstName, FIRST_NAME
• lastName, LAST_NAME
• countryCode, COUNTRY_ (2 digit country codes like: http://www.web-l.com/country-codes/) - will that work?
• subscribedMofo, FOUNDATION_FLG = Y Do you want to update FOUNDATION_DATE?
• subscribedWebmaker, WEBMAKER_FLG = Y Do you want to update WEBMAKER_DATE?
• subscribedLearning, MOZ_LEARN_FLG = Y Do you want to update MOZ_LEARN_DATE?
• subscribedMozfest, MOZFEST_FLG = Y Do you want to update MOZFEST_DATE?
• interestMakerparty, MAKER_PARTY_FLG = Y Do you want to update MAKER_PARTY_DATE?
• new LANGUAGE_ISO2 = EN

We also have a MODIFIED_DATE field in ET to indicate the last time the record was updated - do you want to fill this in with something? Or leave it blank?

I'll send you, via email, a sample record so you can format the file for import.