Certified Marketing Cloud Developer

By Salesforce · Question Mode

QuestionQ105

VerifiedData Modeling and Management

A developer wants to broaden their knowledge of Query Activities. They want to identify email addresses that have bounced in the last 30 days, along with the Bounce Reason and some additional subscriber-specific data; however, the SQL they wrote does not return any records. The SQL statement is:

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

What updates should be made to ensure that this SQL statement returns the desired results?

Explanation

The _Bounce data view identifies the subscriber with SubscriberID rather than EmailAddress. Join _Bounce to _Subscribers with s.SubscriberID = b.SubscriberID to retrieve the subscriber email address and bounce details. DATEADD and GETDATE() are supported in Marketing Cloud Engagement SQL Query activities.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!