SQL Code for Marketing Cloud Data Views

May 30, 2023 | Marketing Cloud, SQL

marketing cloud data views

To support my work, this post may contain affiliate links (these are referrer links sharing products and services. I get paid a small commission if you make a purchase through the link)

Marketing Cloud Data Views

In today’s data-driven marketing landscape, developers constantly seek a quick and efficient way to be productive.

One such way is copying ready-made SQL code to run within their application.

As a developer who googled the SQL code for different sfmc data views, I wanted to single location with all the frequently used code.

This post does exactly that and outlines 12 of the most frequent SQL code for marketing cloud data views.

You can use this within query studio or automation studio within Salesforce Marketing Cloud.

What are Marketing Cloud Data Views?

These are a collection of system-generated, read-only tables that store valuable information about your Marketing Cloud account’s data and activities.

These tables serve as a comprehensive data repository, capturing a wide range of interactions and behaviours of your subscribers, emails, journeys, automations, and more.

By accessing and querying these data views, marketers can unlock a wealth of information to understand their audience better, measure campaign performance, and optimize their marketing strategies.

Marketing Cloud stores data view information for six months.

If you need the data longer than that, the recommendation is to create an automation that regularly captures the data into a data extension.

SQL Code for Salesforce Marketing Cloud Data Views

1. Subscribers View

The subscribers view holds data on all subscribers on your account.


select 
    SubscriberID
    ,DateUndeliverable
    ,DateJoined
    ,DateUnsubscribed
    ,Domain
    ,EmailAddress
    ,BounceCount
    ,SubscriberKey
    ,SubscriberType
    ,Status
    ,Locale

from _Subscribers

2. Open View

The open view stores email opens data on marketing cloud emails.


select 
     AccountID
    ,OYBAccountID
    ,JobID
    ,ListID
    ,BatchID
    ,SubscriberID
    ,SubscriberKey
    ,EventDate
    ,Domain
    ,IsUnique
    ,TriggererSendDefinitionObjectID
    ,TriggeredSendCustomerKey

from _Open

3. Click View

The click view stores email click data from customers.


select 
     AccountID
    ,OYBAccountID
    ,JobID
    ,ListID
    ,BatchID
    ,SubscriberID
    ,SubscriberKey
    ,EventDate
    ,Domain
    ,URL
    ,LinkName
    ,LinkContent
    ,IsUnique
    ,TriggererSendDefinitionObjectID
    ,TriggeredSendCustomerKey

from _Click

4. Sent View

The sent view holds data on emails sent from your platform.


select 
     AccountID
    ,OYBAccountID
    ,JobID
    ,ListID
    ,BatchID
    ,SubscriberID
    ,SubscriberKey
    ,EventDate
    ,Domain
    ,TriggererSendDefinitionObjectID
    ,TriggeredSendCustomerKey

from _Sent

5. Bounces View

The bounce view holds hard and soft email bounces from your platform.


select
     AccountID
    ,OYBAccountID
    ,JobID
    ,ListID
    ,BatchID
    ,SubscriberID
    ,SubscriberKey
    ,EventDate
    ,IsUnique
    ,Domain
    ,BounceCategoryID
    ,BounceCategory
    ,BounceSubcategoryID
    ,BounceSubcategory
    ,BounceTypeID
    ,BounceType
    ,SMTPBounceReason
    ,SMTPMessage
    ,SMTPCode
    ,TriggererSendDefinitionObjectID
    ,TriggeredSendCustomerKey

from _bounce

6. Unsubscribe View

The unsubscribe view holds unsubscribe email events from customers.


select
     AccountID
    ,OYBAccountID
    ,JobID
    ,ListID
    ,BatchID
    ,SubscriberID
    ,SubscriberKey
    ,EventDate
    ,IsUnique
    ,Domain

from _Unsubscribe

7. Job View

The job view holds email send information from your platform.


select 
    JobID
   ,EmailID
   ,AccountID
   ,AccountUserID
   ,FromName
   ,FromEmail
   ,SchedTime
   ,PickupTime
   ,DeliveredTime
   ,EventID
   ,IsMultipart
   ,JobType
   ,JobStatus
   ,ModifiedBy
   ,ModifiedDate
   ,EmailName
   ,EmailSubject
   ,IsWrapped
   ,TestEmailAddr
   ,Category
   ,BccEmail
   ,OriginalSchedTime
   ,CreatedDate
   ,CharacterSet
   ,IPAddress
   ,SalesForceTotalSubscriberCount
   ,SalesForceErrorSubscriberCount
   ,SendType
   ,DynamicEmailSubject
   ,SuppressTracking
   ,SendClassificationType
   ,SendClassification
   ,ResolveLinksWithCurrentData
   ,EmailSendDefinition
   ,DeduplicateByEmail
   ,TriggererSendDefinitionObjectID
   ,TriggeredSendCustomerKey

from _Job

8. Journey View

The journey view holds data on Journey Builder campaigns.


select 
    VersionID
    ,JourneyID
    ,JourneyName
    ,VersionNumber
    ,CreatedDate
    ,LastPublishedDate
    ,ModifiedDate
    ,JourneyStatus

from _Journey 

9. Journey Activity View

The journey activity view holds data on Journey Builder journey activities.


select 
    VersionID
    ,ActivityID
    ,ActivityName
    ,ActivityExternalKey
    ,JourneyActivityObjectID
    ,ActivityType

from _JourneyActivity

10. SMS Message Tracking View

The SMS message tracking view holds data on outbound and inbound SMS with customers.


select
     MobileMessageTrackingID
    ,EID
    ,MID
    ,Mobile
    ,MessageID
    ,KeywordID
    ,CodeID
    ,ConversationID
    ,CampaignID
    ,Sent
    ,Delivered
    ,Undelivered
    ,Outbound
    ,Inbound
    ,CreateDateTime
    ,ModifiedDateTime
    ,ActionDateTime
    ,MessageText
    ,IsTest
    ,MobileMessageRecurrenceID
    ,ResponseToMobileMessageTrackingID
    ,IsValid
    ,InvalidationCode
    ,SendID
    ,SendSplitID
    ,SendSegmentID
    ,SendJobID
    ,SendGroupID
    ,SendPersonID
    ,SubscriberID
    ,SubscriberKey
    ,SMSStandardStatusCodeId
    ,Description
    ,Name
    ,ShortCode
    ,SharedKeyword
    ,Ordinal
    ,FromName
    ,JBActivityID
    ,JBDefinitionID

from _smsmessagetracking

11. SMS Undelivered View

The SMS undelivered view holds data on failed SMS sent to subscribers.


select
    MobileNumber
    ,Undeliverable
    ,BounceCount
    ,FirstBounceDate
    ,HoldDate

from _UndeliverableSms

12. SMS Subscription Log View

The SMS subscription log view holds data on subscribers’ current SMS permission information.


select 
    LogDate
    ,SubscriberKey
    ,MobileSubscriptionID
    ,SubscriptionDefinitionID
    ,MobileNumber
    ,OptOutStatusID
    ,OptOutMethodID
    ,OptOutDate
    ,OptInStatusID
    ,OptInMethodID
    ,OptInDate
    ,Source
    ,CreatedDate
    ,ModifiedDate

from _SMSSubscriptionLog

SFMC Data View Diagram

As helpful as the data views are, it’s good to understand the relations between these backend tables.
Zuzanna Jarczynska from SF Marketing Cloud has an excellent diagram that shows this relationship.

sfmc data views diagrams

There is also an online version of the diagram, which you can access at diagram.io.

Conclusion

Marketing Cloud Data Views provide marketers with a powerful toolset to gain valuable insights, enhance campaign performance, and deliver personalized experiences to customers.

By tapping into the wealth of information stored in these tables, marketers can unlock the true potential of their marketing efforts.

Through effective segmentation, performance analysis, customer journey optimization, and data-driven decision-making.

0 Comments

Submit a Comment

Pin It on Pinterest