Remove characters after @ in email address and replace with new domain

Question

I have four brands that all use different email domains however our SF only has one email allocated per user with a random branded domain. eg [email protected] also uses @brand-b.com, @brand-c.com and @brand-d.com emails but only has @brand-c.com domain allocated in SF.

In order to send comms addressed from these users I need to create a table that has these fields.

Question, how can i using SQL and Automation studio populate a new field with the domain stripped out of the email address (col C in the example below), then populate brand domain fields (col D, E, F, G)?

https://docs.google.com/spreadsheets/d/1BgHe76gn8oPsMjSLQGG1iRKZKwBjIcvf02lxypNmfhg/edit?usp=sharing

Thanks!

Answer 1

This is the perfect use for CROSS APPLY from SQL to take your data and push it into a set column structure.

This will allow you to create columns as normal and then change columns into rows, so you do not need multiple columns to hold these email addresses, and instead can have them each inside the sendable field.

See below:

SELECT u.emailaddress, u.brand
FROM  Branding_Addresses b
CROSS APPLY (
    SELECT (SUBSTRING(emailaddress, 1, charindex('@', emailaddress, 0) - 1) + '@brand-a.com'), 'Brand-A'
    UNION ALL
    SELECT (SUBSTRING(emailaddress, 1, charindex('@', emailaddress, 0) - 1) + '@brand-b.com'), 'Brand-B'
    UNION ALL
    SELECT (SUBSTRING(emailaddress, 1, charindex('@', emailaddress, 0) - 1) + '@brand-c.com'), 'Brand-C'
    UNION ALL
    SELECT (SUBSTRING(emailaddress, 1, charindex('@', emailaddress, 0) - 1) + '@brand-d.com'), 'Brand-D'
) u (emailaddress, brand)

This will allow the new email addresses to be pushed into the emailaddress column and then, you can replace 'brand' with whatever appropriate attributes you desire to store in the DE for each entry.

Answer 2

Assuming you have DE that has a field named EMAIL that has your email addresses (e.g TABLE-A),

1. Create a new DE (TABLE-B) with 6 fields as per your google sheet (Email,[User Name],[Brand-A Email],[Brand-B Email],[Brand-C Email],[Brand-D Email]).
2. Create a SQL Query activity to populate TABLE-B
3. Create a scheduled automation in automation studio and use the SQL activity to add the SQL you created in STEP 2 and activate the schedule.

You SQL code can be as below.

SELECT 
tbla.EMAIL As [EMAIl]
SUBSTRING(tbla.EMAIL,0,CHARINDEX('@',tbla.EMAIL)) As [user name],
SUBSTRING(tbla.EMAIL,0,CHARINDEX('@',tbla.EMAIL)) + '@brand-a.com.au' AS [Brand-A Email],
SUBSTRING(tbla.EMAIL,0,CHARINDEX('@',tbla.EMAIL)) + '@brand-b.com.au' AS [Brand-B Email],
SUBSTRING(tbla.EMAIL,0,CHARINDEX('@',tbla.EMAIL)) + '@brand-c.com.au' AS [Brand-C Email],
SUBSTRING(tbla.EMAIL,0,CHARINDEX('@',tbla.EMAIL)) + '@brand-d.com.au' AS [Brand-D Email]
From [TABLE-A] as tbla