1

Contact located in Salesforce Service is added with "[email protected]" mail. Salesforce Service is connected to Marketing Cloud.

Note: Subscriber Key is Salesforce Contact ID.

Subscriber related to this Contact in Marketing Cloud receives email. At a given time the mail in Salesforce Service is changed to "[email protected]". Contact mail is updated in Marketing Cloud Contact Object from "[email protected]" mail to "[email protected]". Subscriber mail in all Subscriber is not updated and remains as the initial one "[email protected]". Subscriber receives mail to old email "[email protected]" and not the new one "[email protected]".

How can the email in All subscribers be automatically updated?

SELECT 
  s.SubscriberKey AS Contact_ID, 
  s.Email as EmailAddress, 
  sf.email 
FROM 
  ent._Subscribers s 
  LEFT JOIN ent.Contact_Salesforce sf ON s.SubscriberKey = sf.id 
WHERE 
  s.EmailAddress != sf.Email 
  AND s.SubscriberKey LIKE '003%' 
  AND s.SubscriberKey LIKE '__________'

I am also checking the subscriber keys which are contacts. But this code is not working. Where do I make mistakes?

2 Answers 2

0

As this article said:

The Email Address in the All Subscribers list doesn't get updated automatically when changed in the Sales Cloud, even when using Synchronized Data Extensions or SF Imports to Data Extensions

It also suggested a workaround that you can automated perform an Import update against the All Subscribers List through automation. There are 3 fields which are required:

  • Email Address
  • Status
  • Subscriber Key

To pull all subscribers who have different email addresses in the All Subscribers List than in Synchronized Contact DE. I would write the SQL query to store the result like this:

SELECT 
  s.SubscriberKey as [Subscriber Key], 
  c.Email as [Email Address], 
  s.Status as Status 
FROM 
  ent._Subscribers s 
  INNER JOIN ent.Contact_Salesforce c ON s.SubscriberKey = c._ContactKey 
WHERE 
  s.EmailAddress != c.Email

In the automation, it would need to include these activities:

  1. SQL query activity (above)
  2. Data Extract activity: Data Extension extract for the result DE
  3. File Transfer activity: Move file from Safehouse to FTP
  4. Import activity: import the file in FTP to All Subscribers List with "Map by Header Row"
3

I have added a step by step implementation guide on this below as this. Hope this answers your question or can give you and idea of fixing your issue.


The automation solves the following

This is my personal opining of what it solves. Feedback on solution suggestion is appreciated

  • Custom connector sync when the Marketing Cloud Connector does not update your profile attributes.
  • Gives the framework to retrieve field information from other related object
  • Gives the framework to create custom "global" personalization strings. One use case could be to include a “Client Number” in the header of all emails. For example, if you have a Profile Attribute named Member ID, then you can reference it as an attribute personalisation string in a AMPscript block as %%[Member ID]%% or inline like %%Member ID%%.
  • Works with a custom SubscriberKey that is not Lead or Contact Id (<- though this is not recommended).

Setup

We start by creating an automation in Marketing cloud. The purpose of the automation is to create a log of Contact and Leads that have been changed in salesforce the last 24 hours. This will be added to a list for later to be extracted to a file and placed on the customers sFTP marketing cloud server. This file will then be imported to the “All Subscriber” list.

Profile Management

We start off by verifying that “First Name” and “Last Name” and any of our own custom attributes are created and available in Profile Management.

Location: Email Studio → Tab: Subscribers → Tab Meny Choice: Profile Management

Profile Management with custom attribute

Data Extensions

We then need to create to Data Extensions. The first one “UpdateProfileAttributes” will hold all the fields and the subscriberKey of the users that we will update.

Suggested DE location: Data Extensions → SYS_ADMIN → Profile Management Data Extension location for Custom Profile Management

DE Name: UpdateProfileAttributes

| Field Name     | Data Type    | Length | Primary Key | Nullable |
|----------------|--------------|--------|-------------|----------|
| Subscriber Key | Text         | 18     | true        | false    |
| Email Address  | EmailAddress | 254    | false       | false    |
| First Name     | Text         | 40     | false       | true     |
| Last Name      | Text         | 80     | false       | true     |

DE Name: UpdateProfileAttributes_Log

| Field Name       | Data Type    | Length | Primary Key | Nullable | Default Value |
|------------------|--------------|--------|-------------|----------|---------------|
| Subscriber Key   | Text         | 18     | true        | false    |               |
| EmailAddress_New | EmailAddress | 254    | false       | false    |               |
| EmailAddress_Old | EmailAddress | 254    | false       | true     |               |
| FirstName_New    | Text         | 40     | false       | true     |               |
| FirstName_Old    | Text         | 40     | false       | true     |               |
| LastName_New     | Text         | 80     | false       | true     |               |
| LastName_Old     | Text         | 80     | false       | true     |               |
| DateAdded        | Date         | N/A    | false       | true     | Current Date  |

Automation

This automation will hold all the actions that are required to maintain the profile attributes.

  • Name: SYS - Update Profile Attributes
  • Repeat: Every 1 Day. Automation: SYS - Update Profile Attributes

Step 1: Get all changed profile attributes from Contact from last 24 hours

  • Name: SQL_UpdateProfileAttributesContact
  • Data Action: Overwrite
SELECT 
  c.Id                      AS [Subscriber key], 
  c.Email                   AS [Email Address], 
  c.FirstName               AS [First Name], 
  c.LastName                AS [Last Name]
  // Add new fields here
FROM 
  [Contact_Salesforce] c 
  LEFT JOIN _Subscribers s ON c.Id = s.SubscriberKey 
  LEFT JOIN _EnterpriseAttribute ea ON s.SubscriberId = ea._SubscriberID 
  // Join new data sources here 
WHERE 
  (
    COALESCE(c.FirstName, '') <> COALESCE(ea.[First Name], '') 
    OR COALESCE(c.LastName, '') <> COALESCE(ea.[Last Name], '') 
    OR c.Email <> s.EmailAddress
    // Add new OR filter here for the new field
  ) 
  AND c.Email IS NOT NULL 
  AND c.IsPersonAccount = 1 
  AND (
    CAST(c.LastModifiedDate AS Date) > CAST(
      getdate()-1 as Date
    ) 
  )

Step 2: Get all changed profile attributes from Lead from last 24 hours

  • Name: SQL_UpdateProfileAttributesLead
  • Data Action: Append
SELECT 
  l.Id AS [Subscriber key], 
  l.Email AS [Email Address], 
  FirstName AS [First Name], 
  LastName AS [Last Name] 
FROM 
  [Lead_Salesforce] l
  LEFT JOIN _Subscribers s ON l.Id = s.SubscriberKey 
  LEFT JOIN _EnterpriseAttribute ea ON s.SubscriberId = ea._SubscriberID 
WHERE 
  (
    COALESCE(l.FirstName, '') <> COALESCE(ea.[First Name], '') 
    OR COALESCE(l.LastName, '') <> COALESCE(ea.[Last Name], '') 
    OR l.Email <> s.EmailAddress
  ) 
  AND l.Email IS NOT NULL 
  AND s.Status not in ('bounced','held')
  AND (
    CAST(l.LastModifiedDate AS Date) > CAST(
      getdate()-1 as Date
    ) 
  )

Step 3: Verify if we have any contact or leads to update

Name: Verification UpdateProfileAttributes

Verification step in automation

Step 4: Create a log of old and new changes. For tracking purposes

  • Name: SQL_ProfileManagementLog
  • Target Data Extension: UpdateProfileAttributes_Log
  • Data Action: Append
SELECT [Subscriber Key]            AS SubscriberKey,
       [Email Address]             AS EmailAddress_New,
       s.EmailAddress              AS EmailAddress_Old,
       up.[First Name]             AS FirstName_New,
       ea.[First Name]             AS FirstName_Old,
       up.[Last Name]              AS LastName_New,
       ea.[Last Name]              AS LastName_Old
      // Add new fields here to log old and new changes
FROM   [UpdateProfileAttributes] up
       LEFT JOIN _subscribers s
              ON up.[Subscriber Key] = s.SubscriberKey
       LEFT JOIN _EnterpriseAttribute ea
              ON s.SubscriberId = ea._SubscriberID 

Step 5: Extract a file of the Data Extension with the Contacts or Lead to be updated

  • Name: DataExt_UpdateProfileAttributes
  • File Naming Pattern: profile_update_%%Year%%%%Month%%%%Day%%.csv
  • Extract Type: Data Extension Extract
  • Column Delimiter: ,
  • DECustomer Key: UpdateProfileAttributes
  • Has Column Headers: TRUE
  • Text Qualified: TRUE Extract configuration properties 1 Extract configuration properties 2

Step 6: Move the extracted file to MC sFTP server to later import

Name: FileTrf_UpdateProfileAttributes | File Naming Pattern: profile_update_%%Year%%%%Month%%%%Day%%.csv Move activity

Step 7: Import the file to "All Subscribers"

Name: DataImp_UpdateProfileAttributes | File Naming Pattern: profile_update_%%Year%%%%Month%%%%Day%%.csv

Depending on the customers licensing wishes you set the automation to “Update Only” to restrict updates to only the ones in “All Subscriber” list, or “Add and Updates” for best coverage of updates.

Import definition configuration Import definition destination Import definition mapping

Other useful links

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .