Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

SFMC TIP | Data views for transactional emails

SFMC tips and tricks

Data views in Salesforce Marketing Cloud are very handy, when it comes to getting data insights from various sources inside the platform.

I will give you a quick start on how to get data insights for you transactional emails using data views. We will use data from _Sent, _Job, and _Subscriber view

SELECT top 100
s.SubscriberKey,
s.TriggererSendDefinitionObjectID,
s.TriggeredSendCustomerKey,
s.EventDate,
j.DeliveredTime,
j.PickupTime,
j.SchedTime,
s.ListID,
s.JobID,
sk.EmailAddress,
j.EmailName,
from
_Sent s
left join _Subscribers sk ON s.SubscriberKey = sk.SubscriberKey
left join _Job j ON s.JobID = j.JobID

where ListID = 708
order by j.DeliveredTime DESC
s.TriggererSendDefinitionObjectID - object id (auto generated)
s.TriggeredSendCustomerKey - definition key (you choose)
s.EventDate - date when the first message has been triggered for a particular day
j.DeliveredTime - date when the message was actually delivered to recipient
j.PickupTime - when the message was picked up by currently running job
j.SchedTime - when the job scheduled the message
s.ListID - list where you add all who you sent transactional email 

Good to know

Use one publication list for all transactional messages. so when you will come to reduce your contacts you can remove the contacts only ever targeted in transactional messages

There is actual delay from when you send the transactional message and when it is actually written to the _Sent view

Oh hi there 👋
I have a FREE e-book for you.

Sign up now to get an in-depth analysis of Adobe and Salesforce Marketing Clouds!

We don’t spam! Read our privacy policy for more info.

#data views #SQL
Marcel Szimonisz
Marcel Szimonisz
MarTech consultant As a marketing automation consultant, I specialize in solving problems, automating processes, and driving innovation in my clients' marketing platforms.

I hold certifications in Adobe Campaign v6 (3x certified) and Salesforce Marketing Cloud (5x certified).

Additionally, I serve as a community advisor for Adobe Campaign, offering expert insights and guidance.

In addition to my professional pursuits, I also enjoy exploring various programming languages, CMSs, and frameworks, further enhancing my technical expertise and staying at the forefront of industry advancements.
Take a look at our subscription offering in case you are looking for a Marketing Automation Consultant.

Leave a comment

Your email address will not be published. Required fields are marked *

Similar posts that you may find useful

Adobe Campaign Classic tips
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

Sending SMS with Unicode Characters in adobe campaign

1 minute read

Are you planning to send SMS messages with Unicode characters? If so, you may be wondering why your messages are split into multiple messages even though they don’t exceed the character limit. Here’s what you need to know about sending SMS messages with Unicode characters. What are Unicode Characters? Unicode is a character encoding standard […]

Continue reading
Salesforce Marketing Cloud Tips
Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

Unlocking the Power of AMPScript Functions in SSJS

3 minutes read

Have you ever wondered, while working with SSJS, if there was a function in SSJS similar to the one you use in AMPscript? What if I told you that you can bring native AMPscript functions to SSJS? Today, I will show you how to incorporate the best features from both scripting worlds into SSJS automation, […]

Continue reading
Email Double opt in
Marketing Automation, Salesforce Marketing Cloud

SFMC | Double opt-in with service cloud

14 minutes read

Double opt in is basically the must have process on any newsletter sign up page in any marketing automation tool. This will help you to establish cleaner email list of contacts, that most likely have given in their email address that they use. This will reward you with better deliverability rate. Double opt in process, […]

Continue reading
Adobe campaign tips and tricks
ACC Tips & Tricks, Adobe Campaign

How To Convert Base64 to PDF attachment

1 minute read

In this article, I will provide a simple trick for converting Base64 encoded data to PDF using JSAPI in Adobe Campaign Classic. Due to AC’s inability to attach Base64-encoded files directly to emails, this method can prove to be highly useful. Base64 data format that would be easy to use when supperted by Adobe Campaign […]

Continue reading
Link campaign to the delivery send in salesforce marketing cloud
Salesforce Marketing Cloud

SFMC | Link campaign to delivery send

3 minutes read

Are you aware of Salesforce Marketing Cloud’s additional marketing feature known as Campaign? This tool allows you to group similar journeys together, providing greater organization for your marketing activities. However, one drawback is that the campaign information is not available in the data views. While waiting for a fix from Salesforce, you can establish a […]

Continue reading