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.
There is also an online version of the diagram, which you can access at diagram.io.
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.