Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

Data views for transactional emails

Data views for transactional emails

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 problem-solving, process automation, and driving innovation for clients' marketing platforms.

I hold certifications in Adobe Campaign v6 (3x certified) and Salesforce Marketing Cloud (5x certified), as well as 1x Salesforce Associate certified.

Moreover, I serve as a community advisor for Adobe Campaign, providing expert insights and guidance.

Beyond my professional pursuits, I explore various programming languages, CMSs, and frameworks, 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

Column must appear in GROUP BY clause

1 minute read

Creating aggregated data reports may not be a daily task, but there are times when you might be tasked with generating such reports. For instance, you could group and track log clicks to create the coveted ‘Hot Clicks Report’. You tell yourself, ‘It’s nothing major,’ and believe you have an idea of how to do […]

Continue reading
How to map enumeration with file extract
ACC Tips & Tricks, Adobe Campaign, Marketing Automation

How to map enumeration with file extract

1 minute read

I was recently asked about the purpose of a checkbox, and believe it or not, it turned out to be the function I thought was missing in Adobe Campaign Classic – but it’s actually there. It’s the File Extract activity feature hidden in one of the final steps of the extraction wizard. It is simple […]

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

Load the first workflow in campaign view

1 minute read

Every campaign manager, sooner or later, finds their way into marketing automation tools like Adobe Campaign Classic. However, one common challenge, for some order loving campaign managers, is encountered when navigating through campaign workflows. By default, the system loads the first-added workflow as a first on the list, which can lead to confusion to some. […]

Continue reading
A futuristic digital artwork depicting a technician troubleshooting automated machines. The technician, adorned with a high-tech tool belt and a headset featuring a data-displaying visor, stands at the center of a dimly-lit tech facility. Around them are holographic screens presenting system diagnostics, error messages, and programming code. The background is bustling with a variety of machines and robots, some exhibiting malfunctions with sparks and error signals. The scene is illuminated by cool blues and greys, punctuated with orange and red highlights to emphasize the technical issues. Neon lights and exposed wiring add to the tech-savvy ambiance.
Automation studio, Marketing Automation, Salesforce Marketing Cloud, SFMC Tips & Tricks

Troubleshooting issues in automation studio

2 minutes read

Another series of troubleshooting automations in Salesforce Marketing Cloud. I will regurarly add any new additions that I have already resolved and spend great deal of time and life energy. So hope it will help resolve someones issues. I will try to add, throughout my daily life as an SFMC consultant, all the errors that […]

Continue reading
Adobe Campaign Classic API
Adobe Campaign

Adobe Campaign Classic API

3 minutes read

My grandad used to say, ‘When there is a platform, there is an API.’ The same goes for Adobe Campaign Classic, which was equipped with its own programmable interface. With this, we can access various core functionalities of the platform, adding new integrations and functionalities In a world where mentioning API typically brings REST to […]

Continue reading