Before we start with the technical stuff, let me share a piece of wisdom that someone shared with me a while ago.
How do you become great at something?
You become great by consistently being good
Cool. Now that the wisdom is out there in the world, lets get started on todays topic “Querying Data View in Salesforce Marketing Cloud”
Every time I have to configure a SQL activity in automation studio, I have to refer to the Salesforce documentation to check the column names. I do not like to waste time on repetitive tasks, so I wrote all the SQL queries for every Salesforce data view, and put them in one place.
I use this list as a starting point for my Salesforce SQL development. This saves a lot of my implementation time.
The below Salesforce documentation will explain you what the individual fields represent.
Thought it would be good to share this with everyone. Also, since I have tested all the SQL queries on the query studio, they can be used directly on your Salesforce instance. Bookmark this page for your future use. Enjoy!
..and make sure you use this extra saved time wisely 🙂
/**
Purpose: Query data views in Salesforce Marketing Cloud
Author: Prajwal Shetty
Tested: Tested on the query studio
**/
-- _Bounce
select top 10 a.[AccountID],a.[OYBAccountID], a.[JobID], a.[ListID], a.[BatchID], a.[SubscriberID], a.[SubscriberKey], a.[EventDate], a.[IsUnique], a.[Domain], a.[BounceCategoryID], a.[BounceCategory], a.[BounceSubcategoryID],a.[BounceSubcategory], a.[BounceTypeID],a.[BounceType], a.[SMTPBounceReason], a.[SMTPMessage], a.[SMTPCode], a.[TriggererSendDefinitionObjectID], a.[TriggeredSendCustomerKey]
from [_bounce] a
-- _BusinessUnitUnsubscribes
select top 10 a.[BusinessUnitID], a.[SubscriberID], a.[SubscriberKey], a.[UnsubDateUTC], a.[UnsubReason]
from [_BusinessUnitUnsubscribes] a
-- _Click
select top 10 a.[AccountID],a.[OYBAccountID], a.[JobID], a.[ListID], a.[BatchID], a.[SubscriberID], a.[SubscriberKey], a.[EventDate], a.[Domain], a.[URL], a.[LinkName], a.[LinkContent],a.[IsUnique],a.[TriggererSendDefinitionObjectID], a.[TriggeredSendCustomerKey]
from [_click] a
-- _Complaint
select top 10 a.[AccountID],a.[OYBAccountID], a.[JobID], a.[ListID], a.[BatchID], a.[SubscriberID], a.[SubscriberKey], a.[EventDate], a.[Domain]
from [_Complaint] a
-- _Coupon
select top 10 a.[ExternalKey],a.[Description], a.[BeginDate], a.[ExpirationDate]
from [_Coupon] a
-- _SubscriberID
select top 10 a.[_subscriberID ]
from [_EnterpriseAttribute] a
-- _FTAF
select top 10 a.[AccountID],a.[OYBAccountID], a.[JobID], a.[ListID], a.[BatchID], a.[SubscriberID], a.[SubscriberKey], a.[TransactionTime], a.[Domain], a.[IsUnique],a.[TriggererSendDefinitionObjectID], a.[TriggeredSendCustomerKey]
from [_FTAF] a
-- _MobileLineAddressContactSubscriptionView
Select convert(varchar, getDate(), 20) as NowDate,* from _MobileLineAddressContactSubscriptionView
select top 10 a.[ChannelID],a.[ContactID], a.[ContactKey], a.[AddressID], a.[IsActive], a.[CreatedDate], a.[ModifiedDate]
from [_MobileLineAddressContactSubscriptionView] a
-- _MobileLineOrphanContactView
Select convert(varchar, getDate(), 20) as NowDate,* from _MobileLineAddressContactSubscriptionView
select top 10 a.[ContactID], a.[ContactKey], a.[AddressID], a.[CreatedDate]
from [_MobileLineOrphanContactView] a
-- _Jobselect
select top 10 a.[JobID],a.[EmailID], a.[AccountID],a.[AccountUserID],a.[FromName],a.[FromEmail],a.[SchedTime],a.[PickupTime],a.[DeliveredTime],a.[EventID],a.[IsMultipart],a.[JobType],a.[JobStatus],a.[ModifiedBy],a.[ModifiedDate],a.[EmailName],a.[EmailSubject],a.[IsWrapped],a.[TestEmailAddr],a.[Category],a.[BccEmail],a.[OriginalSchedTime],a.[CreatedDate],a.[CharacterSet],a.[IPAddress],a.[SalesForceTotalSubscriberCount],a.[SalesForceErrorSubscriberCount],a.[SendType],a.[DynamicEmailSubject],a.[SuppressTracking],a.[SendClassificationType],a.[SendClassification],a.[ResolveLinksWithCurrentData],a.[EmailSendDefinition],a.[DeduplicateByEmail],a.[TriggererSendDefinitionObjectID],a.[TriggeredSendCustomerKey]
from [_Job] a
-- _Journry
select top 10 a.[VersionID], a.[JourneyID], a.[JourneyName], a.[VersionNumber], a.[CreatedDate], a.[LastPublishedDate], a.[ModifiedDate], a.[JourneyStatus]
from ENT._journey a
-- _ListSubscribers
select top 10 a.[AddedBy], a.[AddMethod], a.[CreatedDate], a.[DateUnsubscribed], a.[EmailAddress], a.[ListID], a.[ListName], a.[ListType], a.[Status], a.[SubscriberID], a.[SubscriberKey], a.[SubscriberType]
from ENT._ListSubscribers a
-- _open
select top 10 a.[AccountID],a.[OYBAccountID], a.[JobID], a.[ListID], a.[BatchID], a.[SubscriberID], a.[SubscriberKey], a.[EventDate], a.[Domain], a.[IsUnique], a.[TriggererSendDefinitionObjectID], a.[TriggeredSendCustomerKey]
from [_open] a
-- _sent
select top 10 a.[AccountID],a.[OYBAccountID], a.[JobID], a.[ListID], a.[BatchID], a.[SubscriberID], a.[SubscriberKey], a.[EventDate], a.[Domain], a.[TriggererSendDefinitionObjectID], a.[TriggeredSendCustomerKey]
from [_sent] a
-- _smsmessagetracking
select top 10 a.[MobileMessageTrackingID],a.[EID],a.[MID],a.[Mobile],a.[MessageID],a.[KeywordID],a.[CodeID],a.[ConversationID],a.[CampaignID],a.[Sent],a.[Delivered],a.[Undelivered],a.[Unsub],a.[OptIn],a.[OptOut],a.[Outbound],a.[Inbound],a.[CreateDateTime],a.[ModifiedDateTime],a.[ActionDateTime],a.[MessageText],a.[IsTest],a.[MobileMessageRecurrenceID],a.[ResponseToMobileMessageTrackingID],a.[IsValid],a.[InvalidationCode],a.[SendID],a.[SendSplitID],a.[SendSegmentID],a.[SendJobID],a.[SendGroupID],a.[SendPersonID],a.[SubscriberID],a.[SubscriberKey],a.[SMSStandardStatusCodeId],a.[Description],a.[Name],a.[ShortCode],a.[SharedKeyword],a.[Ordinal],a.[FromName],a.[JBActivityID], a.[JBDefinitionID]
from [_smsmessagetracking] a
--_UndeliverableSms
select a.[MobileNumber],a.[Undeliverable],a.[BounceCount],a.[FirstBounceDate],a.[HoldDate]
from _UndeliverableSms
-- _SocialNetworkImpressions
select top 10 a.[JobID],a.[ListID], a.[RegionTitle], a.[RegionDescription], a.[RegionHTML], a.[ContentRegionID], a.[SocialSharingSiteID], a.[SiteName], a.[CountryCode], a.[ReferringURL], a.[IPAddress], a.[TransactionTime], a.[PublishedSocialContentStatusID], a.[ShortCode], a.[PublishTime]
from ENT._SocialNetworkImpressions a
-- _SocialNetworkTracking
select top 10 a.[SubscriberID],a.[SubscriberKey], a.[ListID], a.[BatchID], a.[SocialSharingSiteID], a.[SiteName], a.[CountryCode], a.[PublishedSocialContentID], a.[RegionTitle], a.[RegionDescription], a.[RegionHTML], a.[ContentRegionID], a.[OYBMemberID], a.[TransactionTime], a.[IsUnique], a.[Domain], a.[PublishedSocialContentStatusID], a.[ShortCode], a.[PublishTime]
from ENT._SocialNetworkTracking a
-- _Subscribers
select top 10 a.[SubscriberID],a.[DateUndeliverable], a.[DateJoined], a.[DateUnsubscribed], a.[Domain], a.[EmailAddress], a.[BounceCount], a.[SubscriberKey], a.[SubscriberType], a.[Status], a.[Locale]
from ENT._Subscribers a
-- _SMSSubscriptionLog
select top 10 a.[LogDate],a.[SubscriberKey], a.[MobileSubscriptionID], a.[SubscriptionDefinitionID], a.[MobileNumber], a.[OptOutStatusID], a.[OptOutMethodID], a.[OptOutDate], a.[OptInStatusID], a.[OptInMethodID], a.[OptInDate], a.[Source], a.[CreatedDate], a.[ModifiedDate]
from ENT._SMSSubscriptionLog a
--_SurveyResponse
select a.[AccountID],a.[OYBAccountID],a.[JobID],a.[ListID],a.[BatchID],a.[SubscriberID],a.[SubscriberKey],a.[EventDate],a.[Domain],a.[SurveyID],a.[SurveyName],a.[IsUnique],a.[QuestionID],a.[QuestionName],a.[Question],a.[AnswerID],a.[AnswerName],a.[Answer],a.[AnswerData]
from [_SurveyResponse] a
-- _Unsubscribe
select top 10 a.[AccountID],a.[OYBAccountID], a.[JobID], a.[ListID], a.[BatchID], a.[SubscriberID], a.[SubscriberKey], a.[EventDate], a.[IsUnique], a.[Domain]
from [_Unsubscribe] aYou can also read this article by Mateusz. I found it very insightful.
