How to convert multiple email addresses?

I believe that if your email address is attached to the login, the email address will follow to whichever account the login belongs to in V11.

We wanted our logins to live on the individual account, and had a problem as many of the flags on our logins were set to "both".  As the email addresses were linked, they followed the login to the household (even though the email addresses were mapped to A1/A2).

To get around this our DBA Ben Gu was able to match the email addresses (which in their email types have name 1 or name 2 descriptions eg. "N1 Primary Email") and update the login flag in V10 as applicable to N1 or N2, so they can now be mapped to the individuals as desired.

Hi Jane,

We had a situation similar to this, actually about 3000 records that fit the qualification of both N1 and N2 present and at least one email address/login on the account.  As it was our goal to map as many email addresses that were clearly one name or the other we did the following:

1)  Established two Primary Email Address types in EADDRESS_TYPE, Primary Email Address and Primary Email Address N2.  In TRU_EADDRESS_MAPPING we mapped to the respective A1/A2 accounts.

2)  If the email address clearly belonged to N1 or we were not sure, then email address type was Primary Email Address, the flag on both the Email address and the Login were set to Name 1.  If the eaddress clearly belonged to N2 we changed the type to Primary Email Address N2 and then made sure that both address and corresponding login were assigned to N2. 

The trickiest part is getting the logins to follow their corresponding email addresses by making sure they map together to N1 or N2.  In staging and testing to v11 these email addresses are moving to the individual accounts as expected, and not residing in the Household account.

Duane

Hi there, any chance you can post or send a copy of the script? We are attempting to do the same thing, but instead of re-inventing the wheel I thought I would see if anyone out there has a script already written to compare the email addresses to the names and then if there are two emails and two names the script can compare which is more likely to match. 

Your help is appreciated. 

Thank you Yvonne

Hi Yvonne,

The script Ben used does not compare the constituent names to the emails as such, rather matches the email address connected to the login with the email address stored on the addresses tab (which for us has a 'type' of N1 or N2), and updates N1/N2 flag on the login to match.

If this is of use to you contact Ben Gu and I'm sure he'll be happy to pass this on to you.   

Hi Yvonne,

Good day.

SELECT     b.eaddress_no, b.customer_no, b.eaddress_type, a.login_no, a.n1n2_ind
FROM         T_CUST_LOGIN AS a INNER JOIN  T_EADDRESS AS b ON a.eaddress_no = b.eaddress_no
WHERE     (a.eaddress_no <> 0)
ORDER BY b.customer_no

SELECT     id, description, email_ind, inactive
FROM         TR_EADDRESS_TYPE 
 
/*

--- RUN This after assign the right values.

UPDATE    T_CUST_LOGIN
SET              n1n2_ind = CASE WHEN b.eaddress_type = 1 ---- Come from TR_EADDRESS_TYPE table for N1
                            THEN 1 ---- N1 login matches eaddress_type
                            WHEN b.eaddress_type = 2  ----- Come from TR_EADDRESS_TYPE table for N2
                            THEN 2  ---- N2 login matches eaddress_type                         
                            END
                           
FROM         T_CUST_LOGIN INNER JOIN
                      T_EADDRESS AS b ON T_CUST_LOGIN.eaddress_no = b.eaddress_no
WHERE     (T_CUST_LOGIN.eaddress_no <> 0)  --and (T_CUST_LOGIN.customer_no=5)

*/

Please make sure email_type matches N1/N2 indicator.

You can update one record to check out, e.g. customer_no=5

have fun.

Thank you for your time.

Ben

Thank you for responding and posting the code! 

Just jumping on this discussion - how are organizations handling mapping email addresses that are not clearly linked to N1 or N2 by an eAddress Type or by the Login indicator?  I understand that anything with a status of "Both" will end up in the Household constituent record.  The majority of our emails are not clearly defined as N1 or N2 by an eAddress Type description and for the most part their corresponding Login indicators are set to "Both".  Has anyone else run into this?

Thank you,

Chuck Buchanan

Manager, Direct Marketing and List Management

92nd Street Y 

We had a majority of our emails in logins set to both with no clear indication of which name they belonged to.  We decided to keep them at the household level instead of assuming ownership and getting it wrong.  As we learn who owns what information we are moving it to the correct account, but we are ok with emails and logins living at the household level when we don't know more.

Ditto to what Boann said. We left them on the household if we weren't sure about them. (With the caveat that we don't have a bunch of account management online - only basic account info and ticket/donation which we are leaving on the household so a person's experience online is no different if they log in with an individual login or a household login)

- Heather

We do not have any kind of formal plan in place at this point.  Our website does not tailor the login to the individual and we have not had a lot of different contact preferences from individuals within a household, so this has not been a huge priority for us.  The box office, development and marketing departments know to move emails and logins around as they learn who owns what, but we are not actively seeking that information out at this time.