MCE-Dev-201 Salesforce Certified Marketing Cloud Engagement Developer Question and Answers

To alternate the background color of rows in an HTML table using AMPscript, the MOD function can be used to determine if the row number is odd or even.

MOD Function: The MOD function returns the remainder of a division operation. By using MOD(@numerator, 2), the function returns 1 for odd rows and 0 for even rows, allowing the background color to be set accordingly.

AMPscript Example:

%%[ SET @numerator = 1 FOR @i = 1 TO RowCount(@rows) DO SET @color = IF MOD(@numerator, 2) == 1 THEN 'Red' ELSE 'White' ENDIF SET @numerator = ADD(@numerator, 1) ]%% %%[ NEXT @i ]%%

Salesforce AMPscript Functions

The FormatCurrency AMPscript function is used to format a numeric value as a currency. To return a value of $6.96, the function would correctly format inputs like:

6.96 (B) - This is a direct numeric value that will be formatted to $6.96.

6.96 (D) - Same as above, the numeric value is directly formatted to $6.96.

Using FormatCurrency(6.96) in AMPscript will result in $6.96.

To retrieve daily JSON data from a customer's API and write it to a data extension, the developer should use the following set of Server-Side JavaScript activities:

Platform.Function.HTTPGet(); - To make the HTTP GET request to the customer's API and retrieve the JSON data.

Platform.Function.ParseJSON(); - To parse the retrieved JSON data.

Platform.Function.UpsertData(); - To upsert the parsed data into a data extension.

Example code snippet:

var response = Platform.Function.HTTPGet("https://api.customer.com/data "); var jsonData = Platform.Function.ParseJSON(response); Platform.Function.UpsertData("DataExtensionName", ["PrimaryKeyField"], jsonData);

To create a query that aggregates clicks grouped by the language of the recipient, the developer needs to use Data Views that store subscriber and click information. The required Data Views are:

_Subscribers (A) - This Data View contains information about subscribers, including their profile attributes such as language.

_Click (D) - This Data View contains information about click events for email messages, which can be used to aggregate clicks.

The query would join these Data Views on a common identifier (e.g., SubscriberKey) and group the results by the language attribute.

The AMPscript function that returns the number of rows in a specified data extension is DataExtensionRowCount (B). This function provides the count of rows within a data extension, which can be used to dictate what is displayed on a landing page.

Example usage:

SET @rowCount = DataExtensionRowCount("MyDataExtension")

In AMPscript, comments can be added within a code block using the /* comment */ syntax. This allows developers to include notes or explanations without affecting the execution of the script.

Example:

ampscript

Copy code

%%[ /* This is a comment */ SET @var = "value" ]%%

Salesforce AMPscript Syntax Guide

The RedirectTo AMPscript function prevents link href values from getting recorded in tracking (C). When RedirectTo is used, it bypasses the standard link wrapping and tracking process, causing the link click not to be captured in the tracking data.

The JobID parameter is required for the LogUnsubEvent request to be tied to the appropriate send. The JobID uniquely identifies the email job that was sent, allowing the system to accurately log the unsubscribe event for that specific send.

JobID: This parameter ensures that the unsubscribe event is correctly associated with the email send job.

Salesforce LogUnsubEvent Documentation

To define a filename pattern that matches files starting with the current month and day, you can use the %%MMDD%% pattern and the "Begins With" operator.

%%MMDD%% Pattern: This pattern represents the month and day in a two-digit format.

Begins With Operator: This operator ensures that the automation matches filenames that start with the specified pattern.

Salesforce File Drop Automations

A Contact can be reintroduced into the system only after the deletion is fully complete. The Contact Delete process in Salesforce Marketing Cloud is designed to ensure that all references and data associated with the contact are thoroughly removed. Reintroducing the contact before the process is fully complete would disrupt this cleanup process and potentially lead to data integrity issues.

When receiving an "insufficient Privileges" error while attempting to upload a base64-encoded file to Content Builder using an API Installed Package, the developer should validate the Client Id and Client Secret are correct. These credentials are necessary for authentication and authorization when interacting with Salesforce Marketing Cloud APIs.

The outdated subject line variable could be located in the HTML body which is processed after the subject line (D). This is because AMPscript variables declared in the HTML body can affect the overall email content, including the subject line, and if not updated, they can cause discrepancies in the displayed subject line.

To identify all subscribers who are unsubscribed from a specific Business Unit, the correct Data View to query is ListSubscribers (A). This Data View contains information about the subscription status of subscribers at the list level, including unsubscribe status for specific Business Units.

To aggregate monthly energy usage data stored in variables for each month, you should use the ADD function in AMPscript to sum up the values. The ADD function can be nested to handle multiple values.

AMPscript Example:

%%[ SET @total = ADD(@jan, ADD(@feb, ADD(@mar, @apr))) ]%%

Salesforce AMPscript Functions - ADD

The "View Email As Web Page" (VAWP) link relies on the original email content being available. If the email used at the time of send was deleted, updated, or moved, the link will not be able to retrieve and display the content, resulting in the error message.

Salesforce VAWP Documentation

When an email is sent to a list where subscribers have no associated Subscriber Key, Marketing Cloud will use the Email Address as the Contact Key by default. This ensures that each subscriber can still be uniquely identified even if they do not have a predefined Subscriber Key.

Salesforce Subscriber Key Concepts

When using the REST API to send emails to customers after a purchase, the developer should make a token API call and re-use the token until the token expires (D). This approach optimizes API usage by reducing the number of token requests, ensuring that the token is reused until it naturally expires, after which a new token can be requested.

A developer would use the LookupRows function instead of the Lookup AMPscript function to return a complete rowset from the data extension (A). The LookupRows function retrieves all rows that match a specified criterion, returning a rowset that can be further processed or iterated over in AMPscript. In contrast, the Lookup function retrieves only a single field value from the first row that matches the specified criteria.

To get a list of subscribers who were sent an email within the past 12 months, you should query the Job and Sent data views. These data views contain detailed information about email sends and can be used to filter subscribers based on the send date.

SQL Query Example:

SELECT s.SubscriberKey FROM _Sent s JOIN _Job j ON s.JobID = j.JobID WHERE j.SendDate >= DATEADD(month, -12, GETDATE())

Salesforce Data Views

Using the RaiseError AMPscript function allows a developer to handle errors when necessary data for building an email is missing. The possible outcomes of using this function include:

The send fails (A) - The entire email send can be stopped if a critical error is encountered.

The email is not sent to the particular subscriber (B) - The send can be aborted for specific subscribers who do not have the necessary data.

To reference subscriber, system, and sendable Data Extension attributes for content personalization using SSJS, the correct syntax is . This tag allows you to dynamically insert the value of an attribute into the email content.

Tag: This tag is used to insert the value of an attribute from a Data Extension or subscriber attribute into the content. The name attribute of the tag specifies which attribute's value should be used.

%%[ var @firstName set @firstName = AttributeValue("FirstName") ]%% Hello, !

Salesforce SSJS Guide

This syntax ensures that the correct personalization attributes are pulled into the email, enabling dynamic and personalized content based on subscriber data.

To create a JavaScript Web Token (JWT) using a key from Key Management in Salesforce Marketing Cloud, the GetJWTByKeyName function should be used. This function generates a JWT using the specified key from Key Management.

Salesforce JWT Documentation

To find all subscribers who made a purchase in the last 30 days, an INNER JOIN should be used to join the Customers data extension with the Orders data extension on the ContactKey. This ensures that only subscribers with matching records in both data extensions are retrieved.

SQL Query Example:

SELECT c.ContactKey FROM Customers c INNER JOIN Orders o ON c.ContactKey = o.ContactKey WHERE o.PurchaseDate >= DATEADD(day, -30, GETDATE())

Salesforce SQL JOINs

The error message "Cannot Perform 'Post' on objects of type 'SentEvent'" indicates that the SentEvent object is not updatable via the SOAP API. The SentEvent object is typically read-only and is used for tracking purposes.

Read-Only Object: The SentEvent object is used to log events and is not designed to be modified via API calls. This is why attempts to perform POST operations on it will result in an error.

Salesforce SOAP API Error Codes

The SQL statement in question does not return any records because it is joining on the EmailAddress field, which is not present in the _Bounce Data View. The correct approach is to join using the SubscriberKey (or SubscriberID). Here is the corrected SQL statement:

SELECT s.EmailAddress, s.SubscriberKey, b.JobID, b.EventDate, b.SMTPBounceReason FROM _Subscribers s JOIN _Bounce b ON s.SubscriberKey = b.SubscriberKey WHERE b.EventDate > DateAdd(DAY, -30, GETDATE())

This ensures that the join is based on the SubscriberKey, which is a common field in both data views.

To automate the process of importing encrypted files from an external CRM, the following actions should be included:

File drop to the SFTP Import directory: Place the encrypted files in the SFTP Import directory to trigger the automation.

File transfer activity to the Safehouse for decryption: Move the encrypted files to the Safehouse for decryption, ensuring the data is securely handled.

Import definition to the necessary data extension: After decryption, import the data into the relevant Data Extension for use in marketing sends.

Salesforce File Drop Automations

Salesforce File Transfer Activity

Salesforce Import Activity

Before importing zipped files into a Data Extension, a File Transfer activity is needed to unzip the files on the Marketing Cloud Enhanced FTP server. This activity moves and decompresses the files to make them available for import.

File Transfer Activity: This activity is used to move files from one location to another and to unzip compressed files.

Salesforce File Transfer Activity

In Salesforce Marketing Cloud, you can use either AMPscript or Server-Side JavaScript (SSJS) in email messages. Both languages are supported and can be used to manipulate data, create dynamic content, and perform other server-side operations.

Salesforce Programming Languages in Emails

To successfully create a TriggeredSend object using AMPscript's API functions to send a confirmation email when an email is texted into their shortcode, the following objects are required:

Subscribers (A) - Represents the individual recipients who will receive the triggered email.

TriggeredSendDefinition (B) - Defines the parameters of the triggered send, including the email to be sent and the associated attributes.

When using the REST Authorization Service to obtain an OAuth access token, the standard method to include this token in subsequent API requests is by adding it to the Authorization header with the Bearer scheme.

Correct Method: Authorization: Bearer YOUR_ACCESS_TOKEN

This format is a standard practice for OAuth 2.0 token usage.

Salesforce Authentication and Access Tokens

The UpdateDE function in AMPscript updates the data extension rows as the emails are being sent. If the send is canceled and only 400 emails are sent, then only the 400 subscribers who received the email will have their data extension rows updated by the UpdateDE function.

Salesforce AMPscript UpdateDE Function

In an Enterprise 2.0 account, when querying shared data extensions from a child business unit, the data extension name should be prefixed with 'ENT.' to indicate it resides in the shared data extensions folder.

Correct Query:

SELECT * FROM ENT.MemberData WHERE State = 'NY' AND US = 1

Salesforce Shared Data Extensions

Synchronous REST API calls to Marketing Cloud have different timeout limits based on the type of operation:

300 seconds for tracking and data retrieve operations: These operations involve more extensive data processing, hence the higher timeout limit.

120 seconds for non-tracking operations: Standard non-tracking API calls have a shorter timeout limit.

Salesforce Marketing Cloud API Timeout Limits

To sum up multiple variables in AMPscript, the Add function should be used. The Add function can be nested to handle multiple values.

Example:

%%[ SET @total = Add(@var1, Add(@var2, @var3)) ]%%

Salesforce AMPscript Add Function

To inject contacts into a journey using the REST API, the correct endpoints involve the event definitions and events.

POST /eventDefinitions/key:{key} or /eventDefinitions/{id}: This endpoint allows you to interact with event definitions by key or ID, which is essential for managing journey events.

POST /interaction/v1/events: This endpoint is used to inject events into the journey, thus adding contacts to the journey based on the defined event.

Salesforce Journey Builder API

For a weekly promotional send, the marketer can use:

Sendable Data Extension: These are standard data extensions that are configured for sending and contain the necessary fields like Email Address and Subscriber Key.

Salesforce Data Extension: These are data extensions that are synchronized with Salesforce objects and can be used for sending emails.

Salesforce Data Extensions

Salesforce Marketing Cloud offers various encryption options when creating a new key in Key Management. The three encryption options that can be used are:

Asymmetric (B) - Asymmetric encryption uses a pair of keys (public and private) for encryption and decryption. This method is highly secure as the private key remains confidential.

RSA (C) - RSA is a specific type of asymmetric encryption. RSA stands for Rivest-Shamir-Adleman, and it is widely used for secure data transmission.

Symmetric (E) - Symmetric encryption uses the same key for both encryption and decryption. It is efficient for encrypting large amounts of data.

To analyze the Send, Click, and Open Data Views and transfer the data to the SFTP, the developer should build the following activities:

Query Activity - To query and gather the data from the Send, Click, and Open Data Views.

Data Extension Extract - To extract the queried data from the data extension and prepare it for transfer to the SFTP.

To track different versions of content within an email using dynamic content contained in a series of conditionals, the marketer should use the BeginImpressionRegion AMPscript function. This function marks the beginning of a region of content to track impressions, making it easier to analyze which versions of the content were viewed.

To dynamically populate the physical address of a company's locations using the variable @address and ensure the fastest possible performance for millions of subscribers, the recommended AMPscript solution is:

%%[ SET @address = Lookup("Building_Locations", "Address", "Id", @Id) ]%%

This solution uses the Lookup function, which retrieves a specific field value from a data extension based on the given criteria. It is efficient and performs well for high-volume sends.

The Sent data view contains records of all emails that have been sent, including details such as JobID, EventDate, and other relevant information. To gather the required information about emails deployed in the last seven days, the Sent data view should be used.

Query Example:

SELECT JobID, EventDate, COUNT(*) as SentCount FROM _Sent WHERE EventDate >= DATEADD(day, -7, GETDATE()) GROUP BY JobID, EventDate

Salesforce Data Views

To retrieve multiple records associated with a particular customer from the OrderDetails data extension, the LookupRows and LookupOrderedRows functions are suitable. These functions return a rowset containing the records that match the specified criteria.

LookupRows: Retrieves a rowset of records based on specified criteria.

Example:

ampscript

Copy code

SET @rows = LookupRows('OrderDetails', 'CustomerID', @customerID)

LookupOrderedRows: Retrieves a rowset of records based on specified criteria and allows for sorting.

Example:

SET @rows = LookupOrderedRows('OrderDetails', 0, 'OrderDate DESC', 'CustomerID', @customerID)

Salesforce AMPscript LookupRows

To configure a File Drop Automation with a filename pattern that allows for timestamps and starts with the month and day, the developer should use:

Begins With operator (D) - This ensures that the automation triggers when the file name begins with the specified pattern.

%%Month%%%%Day%% (C) - This is the correct syntax to match the month and day in the file name. For example, if the file name is "MAY15", the pattern will be %%MMMM%%dd.

AMPscript is evaluated in the following order before an email is sent:

Subject Line: Evaluates AMPscript in the subject line first.

HTML Body: Evaluates AMPscript within the HTML body of the email.

Text Body: Evaluates AMPscript within the text body of the email.

This order ensures that any dynamic content or personalization logic is correctly applied in all parts of the email.

Salesforce AMPscript Evaluation Order

Before importing a compressed file into Marketing Cloud, a File Transfer activity is required to move and decompress the file. The File Transfer activity allows you to specify the source and destination locations and to decompress the file, making it ready for the import process.

Salesforce File Transfer Activity

To normalize customer data in a staging data extension where a text field named 'birthday' contains date values in various formats, the developer can use the following SQL keywords and functions:

CASE, ISDATE, CONVERT (A) - These functions can be used to handle different date formats and convert them into a consistent format.

CASE, ISDATE, CAST (C) - Similar to the above, CAST can be used to convert text to date format if ISDATE returns true.

Example SQL query:

SELECT CASE WHEN ISDATE(birthday) = 1 THEN CONVERT(DATE, birthday) ELSE NULL END AS normalized_birthday FROM StagingDataExtension

To ensure performance, it is recommended to create one Population (D). Creating multiple populations can lead to complexity and performance issues. Having a single population helps in managing contacts efficiently and reduces the overhead on the system.

To retrieve the status of an asynchronous SOAP API call to the TriggerSend object, you should use the ResultItem object along with the RequestID attribute.

ResultItem Object: This object provides detailed information about the status of each individual request made within the API call.

RequestID Attribute: This unique identifier is used to track and retrieve the status of the specific API request.

Salesforce SOAP API Developer Guide

To handle unexpected errors gracefully in a custom subscription center, wrapping the code in a Server-Side JavaScript (SSJS) Try/Catch block is recommended.

Try/Catch Block: Using a Try/Catch block in SSJS allows the developer to catch and handle exceptions, providing a mechanism to manage errors without disrupting the user experience.

SSJS Example:

Salesforce Server-Side JavaScript (SSJS) Guide

If the email send's tracking information is not available in Sales Cloud, the next step is to confirm that the audience was a Salesforce Data Extension containing the appropriate Salesforce ID (SFID).

Salesforce Data Extension: Ensure that the data extension used for the send is a Salesforce Data Extension that includes the appropriate SFID for each subscriber. This is crucial for the integration to correctly match the tracking data with the Salesforce records.

Salesforce Marketing Cloud Connect Troubleshooting

The Describe method in the SOAP API provides metadata about the objects available in the system, including Profile and Preference Attributes. This method can be used to dynamically retrieve and display these attributes in a custom profile center.

Describe Method: This method returns the schema of the objects, including fields and their properties, which can be used to dynamically generate forms and interfaces.

Salesforce SOAP API Describe Method

To configure an Email Send Logging Data Extension for a new business unit, the recommended approach is to use the SendLog Data Extension Template. This ensures that all necessary fields and configurations are correctly set up to log email send data.

SendLog Data Extension Template: This template includes predefined fields and settings that are required for logging email sends. Using this template ensures that the data extension is set up correctly for tracking send logs.

Go to Email Studio -> Subscribers -> Data Extensions.

Click on "Create".

Choose "Create from Template" and select the "SendLog" template.

Follow the prompts to configure and save the new Send Log data extension.

Salesforce Email Send Logging

After using LookupRows to retrieve data when building a dynamic email, the next step before using this rowset within a FOR loop is to use RowCount to ensure the rowset contains data (D). This validation ensures that there are rows to iterate over, preventing potential errors or empty iterations in the FOR loop.

Example:

SET @rows = LookupRows("DataExtensionName", "FieldName", "Value") SET @rowCount = RowCount(@rows) IF @rowCount > 0 THEN FOR @i = 1 TO @rowCount DO SET @row = Row(@rows, @i) /* process each row */ NEXT @i ENDIF

Before using the 'contacts/' route of the REST API to update records in a data extension, the developer should verify that the data extension should be linked in an AttributeGroup in Contact Builder (D). This ensures that the data extension is part of the contact model and can be accessed and updated using the REST API.

Switching to Tenant Specific Endpoints (TSEs) provides the benefit of improved API performance (D). Tenant Specific Endpoints are designed to provide optimized performance by ensuring that API calls are routed through endpoints that are dedicated to a specific tenant, reducing latency and potential congestion issues associated with shared endpoints.