Marketing-Cloud-Developer Salesforce Certified Marketing Cloud Developer (SU24) Question and Answers

Data extension functions in AMPscript, such asLookupRows,LookupOrderedRows, andUpdateDE, can most negatively impact send processing because they involve querying and manipulating data extensions. These operations can be resource-intensive and time-consuming, especially when dealing with large data sets.

To populate a data extension with the date of the most recent click for each subscriber, the developer should use the following SQL query:

SELECTc.SubscriberKey,MAX(c.eventDate)ASeventDateFROM_Click cGROUPBYc.SubscriberKey

This query selects the SubscriberKey and the maximum eventDate for each subscriber from the _Click Data View, ensuring that the most recent click date is retrieved.

References:

• Salesforce Marketing Cloud Data Views
• SQL MAX Function

To append the subscriber's zip code to a link and ensure that the clicks are tracked, you should use theLookupfunction to retrieve the zip code from the data extension and theRedirectTofunction to create the link with tracking enabled.

• Lookup: Retrieves the zip code from the data extension.
• RedirectTo: Ensures that the link with the appended zip code is tracked.

• Example:

ampscript

Copy code

%%[ SET @zipcode = Lookup("DataExtensionName", "zipcode", "SubscriberKey", _subscriberkey) SET @link = Concat("http://example.com?zip=", @zipcode) ]%% Click Here

A Contact can be reintroduced into the system onlyafter 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.

References:

• Salesforce Marketing Cloud Documentation on Contact Deletion
• Salesforce Marketing Cloud Contact Builder

TheHTTPGet2andHTTPPost2functions in AMPscript allow you to include additional headers, such as an OAuth token, in your HTTP requests. These functions provide more flexibility for specifying custom headers compared to the standardHTTPGetandHTTPPostfunctions.

• Example:

SET @header = 'Authorization: Bearer your_oauth_token' SET @response = HTTPPost2("https://example.com/api", @header, "data=example")

In an Enterprise 2.0 account, the Contact Delete feature can only be used from the Parent account. This centralization ensures that contact deletion is managed consistently and in compliance with organizational policies.

References:

• Salesforce Marketing Cloud Contact Builder
• Contact Management in Enterprise 2.0

To determine the best identifier for subscribers across all channels,Contact Key (A)should be recommended. The Contact Key is a unique identifier that can be used consistently across all channels, ensuring a unified view of the subscriber's interactions and data.

References:

• Salesforce Marketing Cloud Contact Builder
• Contact Key Overview

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

References:

• Salesforce Marketing Cloud AMPscript Guide
• Salesforce Marketing Cloud AMPscript Functions

A File Drop Automation should be configured to handle files that arrive at varying times and have unique filenames. This type of automation triggers when a file is dropped into a specific folder on the Enhanced FTP site, allowing the system to handle imports as soon as the file arrives.

In AMPscript, the valid syntax for comments is/* comment */. This allows you to add comments within your AMPscript code for documentation or clarification purposes.

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*FROMENT.MemberDataWHEREState='NY'ANDUS=1

To ensure that clicks are tracked for a dynamic link created using AMPscript, the developer should wrap the link variable in aRedirectTofunction and include an alias attribute for tracking purposes.

• RedirectTo Function: This function helps ensure that the link click is properly tracked by Salesforce Marketing Cloud (SFMC). The function takes a URL and ensures that tracking parameters are appended correctly.

%%[ SET @dynamicLink = "http://example.com/?id=" + AttributeValue("SubscriberID") ]%% Click Here

• uk.co.certification.simulator.questionpool.PList@17d31af0

Switching to Tenant Specific Endpoints (TSEs) provides the benefit ofimproved 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.

References:

• Salesforce Marketing Cloud Tenant Specific Endpoints
• API Best Practices

QUESTIONNO: 115

A data extension contains two fields which are being populated by a query activity. A third field has recently been added to the data extension.

Which SELECT statement is optimal for returning all of the columns in the data extension?

A. SELECT field*

B. SELECT *, field1, field2, field3

C. SELECT*

D. SELECT 22SE, field2, field3

Answer: C

The optimal SELECT statement for returning all columns in the data extension, including the newly added third field, is:

SELECT*

This statement ensures that all fields, including any new fields added to the data extension, are returned without needing to list them explicitly.

References:

• Salesforce Marketing Cloud SQL Reference
• Query Activity Best Practices

To select a unique list of subscribers who have made multiple purchases, the developer should use the following SQL statement:

SELECTSubscriberKeyFROMOrdersGROUPBYSubscriberKeyHAVINGCOUNT(*)>1

This query groups the records by SubscriberKey and selects only those groups where the count of records is greater than one, indicating multiple purchases.

References:

• SQL GROUP BY Statement
• SQL HAVING Clause

To update Contact data stored in a Data Extension using the REST API, the Data Extension must be in anAttribute Group (A). This is necessary because the REST API interacts with Contact data through Attribute Groups, which organize Data Extensions and their relationships within Contact Builder.

References:

• Salesforce Marketing Cloud REST API
• Contact Builder and Attribute Groups

To retrieve the value of the AMPscript variable namedsubKeyin Server-Side JavaScript (SSJS), the following statement should be used:

Var.Get("subKey");

This statement retrieves the value of the specified AMPscript variable within SSJS.

References:

• Salesforce Marketing Cloud SSJS Guide
• AMPscript and SSJS Integration

To personalize the welcome email with the recipient's first name from the Customers data extension, you use the Lookup function to retrieve the FirstName field based on the CustomerKey field.

• Lookup Function: The Lookup function retrieves a value from a specified data extension.
• Syntax:

%%=Lookup('Customers', 'FirstName', 'CustomerKey', CustomerKey)=%%

• uk.co.certification.simulator.questionpool.PList@19773ee0

In the provided AMPscript, theIFstatement checks the length of the@pointvariable and sets@valueaccordingly. Sincex = 'Tacos'has a length of 5, it meets the first conditionLength(@point) > 1, which sets@valueto 1. Subsequent conditions are not evaluated because the first condition is already true.

• AMPscript IF-ELSEIF Example:

%%[ SET @point = RequestParameter('x') SET @value = 5 IF Length(@point) > 1 THEN SET @value = 1 ELSEIF Length(@point) > 2 THEN SET @value = 2 ELSEIF Length(@point) > 3 THEN SET @value = 3 ELSEIF Length(@point) > 4 THEN SET @value = 4 ENDIF ]%%

To ensure that an installed package can access data from a child business unit, verify that the package has been granted access to the specific child business unit. This is configured during the package setup and permissions assignment.

The best Data Type to use for thePurchase_Pricecolumn, which includes values ranging from '$.05' to '$100', isDecimal(9,2) (C). This data type ensures that values are stored accurately without data loss, allowing for precision up to two decimal places.

References:

• Salesforce Marketing Cloud Data Extensions
• Decimal Data Type

======================

To collect a representative sample from a larger data set, the developer should use theNTILE (C)SQL function. The NTILE function is used to distribute rows into a specified number of approximately equal-sized buckets or tiles, which can be useful for sampling purposes.

Example usage:

SELECT*FROM(SELECT*,NTILE(10)OVER(ORDERBYSomeField)ASTileNumberFROMLargeDataSet )ASSampleWHERETileNumber=1

This query divides the dataset into 10 tiles and selects rows from the first tile, effectively providing a sample.

References:

• Salesforce Marketing Cloud SQL Reference
• SQL NTILE Function

To successfully create aTriggerSendobject using AMPscript's API functions, the following objects are required:

• TriggerSendDefinition (B)- Defines the parameters of the triggered send, including the email to be sent and the associated attributes.
• Subscribers (D)- Represents the individual recipients who will receive the email.

These objects ensure that the correct email is sent to the specified subscribers when the TriggerSend is executed.

References:

• Salesforce Marketing Cloud API
• AMPscript API Functions

TheSentdata 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, theSentdata view should be used.

• Query Example:

SELECTJobID, EventDate,COUNT(*)asSentCountFROM_SentWHEREEventDate>=DATEADD(day,-7, GETDATE())GROUPBYJobID, EventDate

To optimize a SQL Query Activity in Automation Studio that is experiencing timeouts, the developer shoulduse intermediate tables to break queries into smaller parts (B). This approach helps in managing complex queries by breaking them down into smaller, more manageable steps, thus reducing the likelihood of timeouts.

References:

• Salesforce Marketing Cloud Documentation on Query Activity
• SQL Query Optimization Tips

In Salesforce Marketing Cloud, landing pages can use the Core Library and the Platform Library of Server-Side JavaScript (SSJS). The Core Library provides fundamental functions, while the Platform Library provides additional functionality to interact with Marketing Cloud-specific features.

• Core Library: Provides basic SSJS functionality.
• Platform Library: Extends SSJS functionality with additional methods specific to Marketing Cloud.

To optimize the performance when retrieving a large number of rows from a Data Extension via the API:

• Use a SimpleFilterPart to retrieve small sets of relevant data (A)- This approach helps in fetching only the necessary data by applying filters, thus reducing the amount of data transferred and processed.
• Use the ContinueRequest feature (C)- This feature in the SOAP API allows for pagination of results, making it possible to handle large sets of data in manageable chunks.

References:

• Salesforce Marketing Cloud API
• Salesforce Marketing Cloud ContinueRequest

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

References:

• Salesforce Marketing Cloud Data Views
• ListSubscribers Data View

To reference SSJS (Server-Side JavaScript) variables for content personalization in Salesforce Marketing Cloud, you use the<ctrl:eval>tag. This tag evaluates the SSJS variable and inserts its value into the content. The correct usage is:

variableName

This syntax allows you to dynamically insert the value ofvariableNameinto the email content.

References:

• Salesforce Marketing Cloud Documentation
• AMPscript and SSJS Guide

To export send data to their SFTP, the following automations can be used:

• Tracking Extract > File Transfer (B)- This automation extracts tracking data and then transfers the file to an SFTP location.
• Query (Data Views) > Data Extension Extract > File Transfer (C)- This automation queries the data views to gather the necessary data, extracts the data from the data extension, and then transfers the file to an SFTP location.

References:

• Salesforce Marketing Cloud Tracking Extract Activity
• Salesforce Marketing Cloud File Transfer Activity

To write an Exclusion Script to exclude sending an email at send time when comparing against a Boolean field in the Sendable Data Extension, the developer can use:

• %%SendBoo1 < 1 (B)- This script directly checks if the Boolean fieldSendBoo1is less than 1, effectively excluding records where the field is false.
• %%=Lookup('Excluded', 'SendBoo1', 'Subscribekey', _Subscribekey) == false (C)- This script performs a lookup to check the value ofSendBoo1in the 'Excluded' data extension, comparing it against theSubscribekey. If the value is false, the email is excluded.

References:

• AMPscript Guide
• Salesforce Marketing Cloud Documentation

To retrieve multiple records associated with a particular customer from theOrderDetailsdata extension, theLookupRowsandLookupOrderedRowsfunctions 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.

ampscript

Copy code

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

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

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

To build an audience by identifying subscribers who opened a specific email, the developer should use the following query:

SELECTSubscriberKeyFROM_OpenWHEREJobID='1234'

This query selects the SubscriberKey from the _Open data view where the JobID matches the specific email send.

References:

• Salesforce Marketing Cloud Data Views
• Salesforce SQL Reference Guide

The operational order of the Contact Delete process for Marketing Cloud is as follows:

• Delete initiated- The process begins when the contact delete request is initiated.
• Suppression phase- During this phase, the contacts are marked for deletion and are suppressed from further communications.
• Final Deletion- The contacts are permanently deleted from the system.

References:

• Salesforce Marketing Cloud Documentation on Contact Deletion
• Contact Builder in Marketing Cloud

To add records to a Data Extension using the SOAP API, theDataExtensionObjectobject is utilized. This object represents a row within a data extension.

• DataExtensionObject: This object is specifically designed to interact with data extensions, allowing you to insert, update, and retrieve rows. Here is a basic example of how to use this object to add a record:

DataExtensionExternalKeySubscriberKeyexample@example.comFirstNameJohnLastNameDoe

• uk.co.certification.simulator.questionpool.PList@1c42a370

To create contacts in Marketing Cloud via API calls, both the REST API and the specific POST route for contacts can be used:

• POST /contacts/v1/contacts route: This is a specific REST API endpoint designed to create new contacts in Marketing Cloud.
• REST API: The broader REST API provides various endpoints for managing contacts, including creation, updating, and deletion.

To programmatically inject Contacts into a journey via the REST API, the recommended route is/interaction/v1/events. This endpoint allows you to trigger an event which can be configured to start a journey for the specified contacts.

References:

• Salesforce Marketing Cloud REST API
• Journey Builder API

To optimize a query activity that is currently timing out, a developer can use the following strategies:

• Use Primary key(s) on fields used in joins (C)- Ensures that the join operations are efficient by utilizing indexed fields.
• Use intermediate tables to stage data (D)- Breaks down complex queries into simpler steps, improving manageability and performance.
• Only update records that have changed since last execution (E)- Reduces the amount of data processed by focusing only on changes since the last query run.

References:

• Salesforce Marketing Cloud SQL Optimization
• Query Activity Best Practices

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:

SELECTCASEWHENISDATE(birthday)=1THENCONVERT(DATE, birthday)ELSENULLENDASnormalized_birthdayFROMStagingDataExtension

References:

• Salesforce Marketing Cloud SQL Reference
• SQL CASE and ISDATE Functions

The SQL statement in question does not return any records because it is joining on theEmailAddressfield, which is not present in the_BounceData View. The correct approach is to join using theSubscriberKey(orSubscriberID). Here is the corrected SQL statement:

SELECTs.EmailAddress, s.SubscriberKey, b.JobID, b.EventDate, b.SMTPBounceReasonFROM_Subscribers sJOIN_Bounce bONs.SubscriberKey=b.SubscriberKeyWHEREb.EventDate>DateAdd(DAY,-30, GETDATE())

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

References:

• Salesforce Marketing Cloud Data Views
• Query Activity Best Practices

TheUpdateDEfunction 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 theUpdateDEfunction.

When creating a Server-to-Server Installed Package and associated API Integration in Marketing Cloud, it is essential to grant specific scopes (permissions) to the API Integration component. This ensures that the integration has the necessary access to perform its tasks without granting unnecessary permissions.

Transparent Data Encryption (TDE) is the appropriate method for ensuring that the underlying database files are encrypted at rest. TDE encrypts the database files themselves, protecting the data in case the physical media is stolen, while allowing the data to be viewed in clear text by authorized users within the system.

• Transparent Data Encryption: Encrypts data at rest, ensuring that the database files are secure.

Regarding Populations in Contact Builder:

• Populations need to be added to an Attribute Group (B)- This is necessary to define the relationship between the population and the associated data extensions and attributes.
• No more than three Populations should be created (C)- Creating more than three populations can lead to performance issues and complexity in managing contacts.

References:

• Salesforce Marketing Cloud Contact Builder
• Populations in Contact Builder

The Contact Delete process in Marketing Cloud removes contact data from various locations to ensure complete deletion. This includes both sendable data extensions and mobile lists.

• Sendable Data Extensions: The Contact Delete process will remove contact data from sendable data extensions, ensuring that the contact is no longer present in any data used for sending.
• Mobile Lists: Contact data will also be removed from mobile lists, ensuring that the contact is fully deleted across all communication channels.

A developer should request a new tokenonce an hourwhen making multiple API calls in v1. Salesforce Marketing Cloud access tokens are typically valid for one hour, and refreshing them every hour ensures that the API calls are authenticated.

References:

• Salesforce Marketing Cloud API Token Management
• Salesforce Marketing Cloud Authentication

TheLookupRowsfunction retrieves rows from a data extension where specified criteria are met. In an Enterprise 2.0 account, the data extension name in shared data extensions should be prefixed with 'Ent.' to indicate it is in the shared space.

• Example:

SET @rows = LookupRows('Ent. Inventory', 'InStock', 'true')

When thesubjectLinevariable is declared in multiple locations throughout the email, the value used at the time of deployment will be the last value assigned to the variable. In the provided declarations, the last assignment is:

%%[ SET @subjectLine = 'Enjoy 20% off today' ]%% was declared within the Subject Line

Therefore, the subject line used at the time of deployment will be"Enjoy 20% off today" (C).

References:

• Salesforce Marketing Cloud AMPscript Variable Scope
• AMPscript Guide: Variable Declaration

The outdated subject line variable could be locatedin 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.

References:

• Salesforce Marketing Cloud AMPscript Guide
• Email Subject Line Best Practices

The first step in using the Transactional Messaging API to send emails is to authenticate and obtain an access token. This is done by making a POST request to the/v2/authorizeendpoint with the client credentials.

The purpose of the IF statement in the AMPscript code is to check if any rows are returned by theLookupRowsfunction. If rows are returned, it retrieves the image field from the data extension and sets the image source accordingly. If no rows are returned, it defaults to using a standard image.