top of page

SFMC SQL Reference (Practical, Not Theoretical)

This page is a working reference for SQL used in Salesforce Marketing Cloud.It focuses on real query patterns you reuse constantly when building audiences, journeys, and automations.

Use this page when:

​

  • building target audiences

  • excluding records

  • checking engagement

  • debugging joins

  • querying Data Views

​

Bookmark it.

Supported:

  • SELECT, FROM, WHERE

  • JOIN (INNER, LEFT)

  • GROUP BY, HAVING

  • DATEADD, DATEDIFF, GETDATE

  • ROW_NUMBER() (in limited cases)

Core Views

_Subscribers

SELECT
SubscriberID,
DateUndeliverable,
DateJoined,
DateUnsubscribed,
Domain,
EmailAddress,
BounceCount,
SubscriberKey,
SubscriberType,
Status,
Locale
FROM _Subscribers

Subscriber Status Email Address

_Bounce

SELECT
AccountID,
OYBAccountID,
JobID,
ListID,
BatchID,
SubscriberID,
SubscriberKey,
EventDate,
SMTPCode,
BounceCategory,
SMTPReason,
TriggererSendDefinitionObjectID,
TriggeredSendCustomerKey
FROM _Bounce

Bounce Email Activity

_Journey

SELECT
JourneyID,
JourneyName,
JourneyVersionID,
JourneyStatus,
JourneyType,
EntrySource,
EntryEventDefinitionKey,
CreatedDate,
ModifiedDate,
PublishedDate,
LastPublishedDate,
ActivationDate,
DeactivationDate,
Description
FROM _Journey

Journey Level metadata

_AutomationInstance

SELECT
AutomationInstanceID,
AutomationCustomerKey,
AutomationName,
AutomationDescription,
AutomationType,
Status,
StatusMessage,
StartTime,
EndTime,
LastRunTime,
NextRunTime,
CreatedDate,
ModifiedDate
FROM _AutomationInstance

Automation Runs Information

_PublicationSubscriber

SELECT
PublicationID,
SubscriberID,
SubscriberKey,
Status,
CreatedDate,
ModifiedDate
FROM _PublicationSubscriber

Subscribers' status in Publication Lists

_Sent

SELECT
AccountID,
OYBAccountID,
JobID,
ListID,
BatchID,
SubscriberID,
SubscriberKey,
EventDate,
Domain,
TriggererSendDefinitionObjectID,
TriggeredSendCustomerKey
FROM _Sent

Emails that have been sent

_Unsubscribe

SELECT
AccountID,
OYBAccountID,
JobID,
ListID,
BatchID,
SubscriberID,
SubscriberKey,
EventDate,
Domain,
Reason,
TriggererSendDefinitionObjectID,
TriggeredSendCustomerKey
FROM _Unsubscribe

Unsubscribe Events

_FTAT

SELECT
SubscriberID,
SubscriberKey,
JobID,
ListID,
EventDate,
SourceEmail,
TargetEmail,
IsUnique
FROM _FTAF

Forward-to-a-friend events

_SentDomain

SELECT
Domain,
JobID,
SentCount,
EventDate
FROM _SentDomain

Send data by domain

_JourneyActivity

SELECT
JourneyID,
JourneyName,
JourneyVersionID,
JourneyStatus,
JourneyCreatedDate,
JourneyLastPublishedDate,
ActivityID,
ActivityName,
ActivityType,
ActivityExternalKey,
ActivityObjectID
FROM _JourneyActivity

Journey Activity Step Information

_Open

SELECT
AccountID,
OYBAccountID,
JobID,
ListID,
BatchID,
SubscriberID,
SubscriberKey,
EventDate,
Domain,
IsUnique,
TriggererSendDefinitionObjectID,
TriggeredSendCustomerKey
FROM _Open

Email open events

_Complaint

SELECT
AccountID,
OYBAccountID,
JobID,
ListID,
BatchID,
SubscriberID,
SubscriberKey,
EventDate,
IsUnique,
Domain
FROM _Complaint

Spam Complaint Events

_ListSubscribers

SELECT
ListID,
SubscriberID,
SubscriberKey,
Status,
CreatedDate,
ModifiedDate
FROM _ListSubscribers

Subscriber status on specific lists

_ClickDomain

SELECT
Domain,
JobID,
ClickCount,
EventDate
FROM _ClickDomain

Click data by domain

_Click

SELECT
AccountID,
OYBAccountID,
JobID,
ListID,
BatchID,
SubscriberID,
SubscriberKey,
EventDate,
Domain,
URL,
LinkName,
IsUnique,
TriggererSendDefinitionObjectID,
TriggeredSendCustomerKey
FROM _Clic

Clicks on links in emails

_Job

SELECT
JobID,
EmailID,
AccountID,
AccountUserID,
FromName,
FromEmail,
SchedTime,
PickupTime,
DeliveredTime,
EmailName,
EmailSubject,
TriggererSendDefinitionObjectID,
IsMultipart,
JobType,
JobStatus,
ModifiedBy,
ModifiedDate,
IsWrapped
FROM _Job

Metadata on Email Send Jobs​

_AutomationInstanceActivity

SELECT
AutomationInstanceActivityID,
AutomationInstanceID,
AutomationActivityObjectID,
AutomationActivityName,
AutomationActivityType,
AutomationActivityDescription,
Status,
StatusMessage,
StartTime,
EndTime,
CreatedDate,
ModifiedDate
FROM _AutomationInstanceActivity

Automation Activity Infomation

_BounceDomain

SELECT
Domain,
JobID,
BounceCount,
EventDate
FROM _BounceDomain

Bounce data by domain

PlaceHolder

PlaceHolder

I'm a paragraph. Click here to add your own text and edit me. It's easy.

Behavioral Joins

Golden Engagement Path

SELECT
j.EmailName,
j.EmailSubject,
s.SubscriberKey,
s.EventDate AS SentDate,
o.EventDate AS OpenDate,
c.EventDate AS ClickDate,
c.URL AS ClickedURL
FROM _Sent s
INNER JOIN _Job j
ON s.JobID = j.JobID
LEFT JOIN _Open o
ON s.JobID = o.JobID AND s.ListID = o.ListID
AND s.BatchID = o.BatchID AND s.SubscriberID = o.SubscriberID
AND o.IsUnique = 1
LEFT JOIN _Click c
ON s.JobID = c.JobID AND s.ListID = c.ListID
AND s.BatchID = c.BatchID AND s.SubscriberID = c.SubscriberID
AND c.IsUnique = 1
WHERE s.EventDate > dateadd(day, -30, getdate())

Email Subject with engagement path

Unsubscribe Activity by Email Name

SELECT
j.EmailName,
j.JobID,
u.SubscriberKey,
u.EmailAddress,
u.EventDate AS UnsubDate,
u.Reason
FROM _Unsubscribe u
JOIN _Job j ON u.JobID = j.JobID
ORDER BY u.EventDate DESC

Who opted out, when

Subscribers Who Clicked But Never Opened

SELECT
c.SubscriberKey,
c.EmailAddress,
c.JobID,
c.EventDate AS ClickDate
FROM _Click c
LEFT JOIN _Open o
ON c.SubscriberKey = o.SubscriberKey
AND c.JobID = o.JobID
WHERE o.SubscriberKey IS NULL
AND c.IsUnique = 1

Bot Detection Helper

Open Activity by Email Name

SELECT
j.EmailName,
j.JobID,
o.SubscriberKey,
o.EmailAddress,
o.EventDate AS OpenDate,
o.IsUnique
FROM _Open o
JOIN _Job j ON o.JobID = j.JobID
WHERE o.IsUnique = 1
ORDER BY o.EventDate DESC

Who opened, when

Complaint / Spam Report Activity

SELECT
j.EmailName,
j.JobID,
c.SubscriberKey,
c.EmailAddress,
c.EventDate AS ComplaintDate
FROM _Complaint c
JOIN _Job j ON c.JobID = j.JobID
ORDER BY c.EventDate DESC

Spam complaints by send

Journey / Triggered Send Behavioral View

SELECT
j.EmailName,
j.JobID,
j.SchedTime,
j.DeliveredTime,
j.TriggererSendDefinitionObjectID,
COUNT(DISTINCT s.SubscriberKey) AS Sent,
COUNT(DISTINCT o.SubscriberKey) AS Opens,
COUNT(DISTINCT c.SubscriberKey) AS Clicks
FROM _Job j
LEFT JOIN _Sent s ON j.JobID = s.JobID
LEFT JOIN _Open o ON j.JobID = o.JobID AND o.IsUnique = 1
LEFT JOIN _Click c ON j.JobID = c.JobID AND c.IsUnique = 1
WHERE j.TriggererSendDefinitionObjectID IS NOT NULL -- filters to triggered/journey sends only
GROUP BY j.EmailName, j.JobID, j.SchedTime, j.DeliveredTime, j.TriggererSendDefinitionObjectID
ORDER BY j.SchedTime DESC

Triggered send engagement stats

Bounce Report by Email Name

SELECT
j.EmailName,
j.EmailSubject,
b.SubscriberKey,
b.BounceCategory,
b.SMTPCode,
b.EventDate AS BounceDate
FROM _Bounce b
INNER JOIN _Job j
ON b.JobID = j.JobID
WHERE b.EventDate > dateadd(day, -7, getdate())

Bounce to the specific Job asset

Click Activity by Email & Link

SELECT
j.EmailName,
j.JobID,
c.SubscriberKey,
c.EmailAddress,
c.LinkName,
c.LinkContent,
c.URL,
c.EventDate AS ClickDate
FROM _Click c
JOIN _Job j ON c.JobID = j.JobID
WHERE c.IsUnique = 1 -- deduplicate to first click only
ORDER BY c.EventDate DESC

Clicks per link tracked

Subscribers Sent to But Never Opened

SELECT
s.SubscriberKey,
s.EmailAddress,
COUNT(DISTINCT s.JobID) AS TotalSends,
MAX(s.EventDate) AS LastSentDate
FROM _Sent s
LEFT JOIN _Open o
ON s.SubscriberKey = o.SubscriberKey
WHERE o.SubscriberKey IS NULL
GROUP BY s.SubscriberKey, s.EmailAddress
HAVING COUNT(DISTINCT s.JobID) >= 3 -- adjust threshold
ORDER BY TotalSends DESC

Any Send — Lapsed Contacts

Send Volume Summary by Email

SELECT
j.EmailName,
j.JobID,
COUNT(DISTINCT s.SubscriberKey) AS TotalSent,
COUNT(DISTINCT o.SubscriberKey) AS UniqueOpens,
COUNT(DISTINCT c.SubscriberKey) AS UniqueClicks,
COUNT(DISTINCT u.SubscriberKey) AS Unsubscribes,
COUNT(DISTINCT b.SubscriberKey) AS Bounces
FROM _Job j
LEFT JOIN _Sent s ON j.JobID = s.JobID
LEFT JOIN _Open o ON j.JobID = o.JobID AND o.IsUnique = 1
LEFT JOIN _Click c ON j.JobID = c.JobID AND c.IsUnique = 1
LEFT JOIN _Unsubscribe u ON j.JobID = u.JobID
LEFT JOIN _Bounce b ON j.JobID = b.JobID AND b.IsUnique = 1
GROUP BY j.EmailName, j.JobID
ORDER BY j.JobID DESC

Engagement Funnel

Subscriber Engagement History

SELECT
s.SubscriberKey,
s.EmailAddress,
COUNT(DISTINCT s.JobID) AS TotalSends,
COUNT(DISTINCT o.JobID) AS TotalOpens,
COUNT(DISTINCT c.JobID) AS TotalClicks,
COUNT(DISTINCT u.JobID) AS TotalUnsubs,
MAX(o.EventDate) AS LastOpenDate,
MAX(c.EventDate) AS LastClickDate
FROM _Sent s
LEFT JOIN _Open o ON s.SubscriberKey = o.SubscriberKey AND s.JobID = o.JobID AND o.IsUnique = 1
LEFT JOIN _Click c ON s.SubscriberKey = c.SubscriberKey AND s.JobID = c.JobID AND c.IsUnique = 1
LEFT JOIN _Unsubscribe u ON s.SubscriberKey = u.SubscriberKey
GROUP BY s.SubscriberKey, s.EmailAddress

Cross-Send Behavioral Profile

bottom of page