certified-marketing-cloud-developer sample questions

Question 1

A marketing team at a large university uses a SQL Query Activity to segment students for a new scholarship announcement. The query joins the `Student_Master` DE (containing `StudentID`, `GPA`, `Major`) with the `Course_Enrollment` DE (containing `StudentID`, `CourseCode`). The goal is to find all students with a GPA over 3.5 who are enrolled in any 'CS' prefixed course. The query is timing out.

**Query Snippet**:
```sql
SELECT
s.StudentID,
s.GPA,
s.Major
FROM
Student_Master s
INNER JOIN
Course_Enrollment c ON s.StudentID = c.StudentID
WHERE
s.GPA > 3.5 AND c.CourseCode LIKE 'CS%'
```

Assuming `StudentID` is the primary key on `Student_Master` and a sendable relationship exists, what is the most effective optimization to prevent the query from timing out?

Accepted Answer

Add an index to the `GPA` column in the `Student_Master` DE and the `CourseCode` column in the `Course_Enrollment` DE.

Answer

Add `WITH (NOLOCK)` to both table references to reduce read locks.

Answer

Create a Sendable Data Extension as the target and ensure `StudentID` is the Subscriber Key.

Answer

Break the query into two separate activities: first, select students with GPA > 3.5 into a staging DE, then join that staging DE with `Course_Enrollment`.

Question 2

True or False: When a contact is deleted from Contact Builder through the official Contact Delete process, their corresponding records in all Sendable Data Extensions are automatically removed, but records in Non-sendable Data Extensions remain untouched.

Accepted Answer

True

Answer

False

Question 3

A developer is building a CloudPage that functions as a custom preference center. The page needs to retrieve a subscriber's current preferences from a Data Extension named `Preferences_DE` upon loading, and then update them when the subscriber submits a form. The form submission is handled by the same CloudPage.

Accepted Answer

RequestParameter()

Accepted Answer

UpdateData()

Answer

LookupRows()

Answer

RaiseError()

Answer

Redirect()

Question 4

Northern Trail Outfitters (NTO) is launching a 'Track Your Order' CloudPage for customers. The page will accept an Order ID from a URL parameter (`.../track?order=12345`) and display sensitive order details, including the customer's shipping address and the items purchased. The development team is concerned about security, specifically preventing unauthorized users from guessing Order IDs and viewing other customers' data.

The initial plan is to use `RequestParameter("order")` to get the Order ID, then use `LookupRows()` on the `Orders_DE` to retrieve the details. The `Orders_DE` contains `OrderID`, `CustomerID`, `ShippingAddress`, and `OrderDetails`. To ensure the page is only viewed by the intended recipient, the links are sent in a transactional email and contain the subscriber's `SubscriberKey` as another parameter (`...&subKey=XYZ`).

The security team has mandated that a user accessing the page must be the actual customer associated with the order. They have also flagged potential Cross-Site Scripting (XSS) vulnerabilities if order details are displayed directly.

Accepted Answer

On page load, retrieve `OrderID` and `SubscriberKey` from the URL. Perform a `LookupRows()` on `Orders_DE` using both `OrderID` and `CustomerID` (where `CustomerID` matches the passed `SubscriberKey`). If a match is found, use the `AttributeValue()` function on each data field before displaying it to prevent XSS.

Answer

On page load, retrieve `OrderID` and `SubscriberKey` from the URL. Perform a `LookupRows()` on `Orders_DE` using both `OrderID` and `CustomerID` (where `CustomerID` matches the passed `SubscriberKey`). If a match is found, display the `OrderDetails`.

Answer

Encrypt the `OrderID` and `SubscriberKey` together into a single token before generating the email link. On the CloudPage, decrypt the token, validate the parameters, and then look up the order. Use the `TreatAsContent()` function to display all retrieved data.

Answer

On page load, retrieve `OrderID` and `SubscriberKey` from the URL. Perform a `LookupRows()` using only the `OrderID`. Then, in a separate step, compare the `CustomerID` from the result with the `SubscriberKey` from the URL. If they match, display the data wrapped in the `AttributeValue()` function for XSS protection.

Question 5

A developer needs to prevent an email from being sent to a subscriber if their `Status` field in the sendable data extension is 'Inactive'. The check must be performed at send time.

To accomplish this, the developer should use the `_____(1, "Subscriber status is inactive")` function in an AMPscript block within the email body.

Accepted Answer

RaiseError()

Answer

Skip()

Answer

Exit()

Answer

Abort()

Question 6

A company has a multi-brand architecture using separate Marketing Cloud Business Units (BUs) for each brand. They need to maintain a single, centralized data extension of all customers who have opted out of communications across all brands. This 'Global Unsubscribe' list must be accessible for suppression in sends from every BU.

Accepted Answer

Create the data extension in the parent BU, mark it as 'Shared,' and place it in a shared folder with permissions granted to all child BUs.

Answer

Create the data extension in each child BU and use a daily SQL query automation to synchronize them with a master list in the parent BU.

Answer

Create a standard data extension in the parent BU and use the API to query it from each child BU during the send process.

Answer

Utilize the out-of-the-box All Subscribers list at the Enterprise (parent) level, as it automatically handles global unsubscribes.

Question 7

A developer is building an integration that programmatically creates and updates hundreds of assets (emails, templates, content blocks) in Content Builder. After implementing the logic using the REST API, they notice the process is slow and occasionally fails with `429 Too Many Requests` errors. The current implementation sends one API request per asset.

Accepted Answer

Use the REST API's batch endpoint (`/asset/v1/content/assets/batch`) to create or update up to 25 assets in a single request.

Answer

Implement an exponential backoff strategy for retrying failed requests.

Answer

Switch to the SOAP API, as its `UpdateBatch` method is better suited for bulk operations.

Answer

Utilize the REST API's asynchronous processing mode by including the `async=true` query parameter in the requests.

Question 8

A marketing email needs to display a personalized greeting, "Hi [FirstName]," but must fall back to "Hi Valued Customer," if the `FirstName` attribute is empty or does not exist for a subscriber.

Accepted Answer

Hi %%=IIF(Empty(AttributeValue("FirstName")), "Valued Customer", AttributeValue("FirstName"))=%%

Answer

Hi %%=v(IF(@FirstName != "" THEN @FirstName ELSE "Valued Customer" ENDIF))=%%

Answer

Hi %%=Lookup("Subscribers", "FirstName", "SubscriberKey", _subscriberkey)=%%

Answer

Hi %%=Iif(Not IsNull(FirstName), FirstName, "Valued Customer")=%%

Question 9

A data analyst needs to create a targetable data extension containing customers who have opened any email in the last 90 days but have NOT clicked any email in the last 180 days. The analyst must use the system Data Views.

Accepted Answer

```sql
SELECT o.SubscriberKey FROM _Open o
LEFT JOIN _Click c ON o.SubscriberKey = c.SubscriberKey AND c.EventDate > DATEADD(day, -180, GETDATE())
WHERE o.EventDate > DATEADD(day, -90, GETDATE())
AND c.SubscriberKey IS NULL
```

Answer

```sql
SELECT s.SubscriberKey FROM _Subscribers s
WHERE s.SubscriberID IN (SELECT o.SubscriberID FROM _Open o WHERE o.EventDate > DATEADD(day, -90, GETDATE()))
AND s.SubscriberID NOT IN (SELECT c.SubscriberID FROM _Click c WHERE c.EventDate > DATEADD(day, -180, GETDATE()))
```

Answer

```sql
SELECT o.SubscriberKey FROM _Open o
WHERE o.EventDate > DATEADD(day, -90, GETDATE())
AND NOT EXISTS (SELECT 1 FROM _Click c WHERE c.SubscriberKey = o.SubscriberKey AND c.EventDate > DATEADD(day, -180, GETDATE()))
```

Answer

```sql
SELECT DISTINCT o.SubscriberKey FROM _Open o
LEFT JOIN _Click c ON o.SubscriberKey = c.SubscriberKey
WHERE o.EventDate > DATEADD(day, -90, GETDATE())
AND (c.EventDate IS NULL OR c.EventDate <= DATEADD(day, -180, GETDATE()))
```

Question 10

During a code review, a senior developer points out that a block of AMPscript is making repeated `Lookup()` calls inside a `FOR` loop to retrieve the same piece of information for every row being processed. This is causing significant performance degradation in email rendering.

Accepted Answer

Before the loop begins, perform a single `Lookup()` or `LookupRows()` call to retrieve the required data and store it in a variable. Reference this variable inside the loop.

Answer

Replace the `Lookup()` calls with `LookupRows()` to retrieve the data in a batch, but keep the call inside the loop.

Answer

Move the entire `FOR` loop and lookup logic into an SSJS Script Activity and run it before the send.

Answer

Use the `TreatAsContent()` function to force the AMPscript to evaluate faster.

Free certified-marketing-cloud-developer sample questions

True or False: When a contact is deleted from Contact Builder through the official Contact Delete process, their corresponding records in all Sendable Data Extensions are automatically removed, but records in Non-sendable Data Extensions remain untouched.

A marketing email needs to display a personalized greeting, "Hi [FirstName]," but must fall back to "Hi Valued Customer," if the `FirstName` attribute is empty or does not exist for a subscriber.

A data analyst needs to create a targetable data extension containing customers who have opened any email in the last 90 days but have NOT clicked any email in the last 180 days. The analyst must use the system Data Views.

During a code review, a senior developer points out that a block of AMPscript is making repeated `Lookup()` calls inside a `FOR` loop to retrieve the same piece of information for every row being processed. This is causing significant performance degradation in email rendering.

10 more free samples are waiting