Questions tagged [data-views]
The data-views tag has no usage guidance.
155
questions
0
votes
0
answers
7
views
Query all subscribers to find the subscriber key related to the contacts from mobile Data View
I would like to retrieve users who received sms and retrieve their salesforce ContactID. I had created a Data Extension from the template of the SMSMessageTracking DataView in a child Business Unit. ...
- 33
0
votes
0
answers
39
views
SFMC Data views for all subscriber clicks all time (inclusive of journeys)
Can someone please assist in writing a SQL query in automation studio to pull SFMC data views. I would like to query all users who have clicked a particular URL type across all emails sent in the SFMC ...
0
votes
1
answer
64
views
Query to Combine Data from 3 different Data View Tables
I've pulled the last 30 days of data from 3 data view tables (Job, Open, and Click) into 3 different data extensions. I'm attempting to run a query to dump data from my 3 different data extensions ...
- 17
0
votes
1
answer
46
views
_Open.IsUnique is TRUE for two records with the same JobId and SubscriberKey - Why?
From my understanding, the first "open" that's recorded for a particular JobId will have their IsUnique attribute as TRUE. Any subsequent opens AFTER that will still be recorded, but with ...
- 839
0
votes
2
answers
49
views
Why does selecting from _Sent and LEFT JOIN _Job return null values from _Job
When I select from _Sent and LEFT JOIN _Job it returns null values for fields selected from _Job.
I'm querying from the Parent BU so this shouldn't be the BU specific issue.
SELECT
s.SubscriberKey,
j....
- 21
0
votes
0
answers
94
views
How to get unique clicks, clicks, unique opens, opens with one SQL query?
I'm using the following (and very handy) consolidation query to pull out sends, opens, clicks, bounces and unsubscribes grouped by journey name etc.:
SELECT
jou.JourneyName,
jou.VersionNumber,
j....
0
votes
1
answer
34
views
Replace double quotes in dataview Bounce
I'm trying to replace double quotes in "SMTPMessage" field of Bounce dataview in Marketing Cloud. But I've entered in several ways, but the quotes do not disappear.
What I did so far is this ...
0
votes
1
answer
32
views
_Job data view and viewing dynamic subject lines, impossible?
Logically speaking, I believe it's pretty much impossible for the _Job data view to be able to record VARYING subject lines (based on dynamic rules and whatnot) with the same JobId - correct ...
- 839
0
votes
1
answer
50
views
Record not in unsubscribe data view when the LogUnsubEvent API request is made
I have added a subscriber via the update API with a status of "Active" to the all subscribers list, and then the logunsubevent request is made for that subscriber.
After that, I looked for ...
- 1
5
votes
1
answer
149
views
SMSMessageTracking Data View - Differences Between Undelivered and Delivered Fields
We are trying to reterive data from the _SMSMessageTracking data view in order to populate another DE with undelivered and delivered statuses.
We've found there is a difference between Undelivered and ...
- 213
0
votes
1
answer
70
views
SSJS WSProxy Create BounceEvent
I am Adding new subscriber to marketing cloud from another system. Some of the subscriber are hard bounced and have a number of bouncecount.
I can create subscriber with Active , Held and Unsubscribe. ...
4
votes
0
answers
62
views
how data in _open data view is recorded in SFMC
As of now, our understanding of data views is that data is mostly real-time. But is this true for all data views or only specific data views? if specific, then which ones?
Recently, we noticed that we ...
- 99
0
votes
0
answers
72
views
Adding additional field to '_PushAddress' in Marketing Cloud and passing the value through API call
Currently I cannot extract SubscriberKey/Contact Key when querying _PushAddress.
Hence, I added a an additional field called SubscriberKey to the _PushAddress in Data Designer.
I can query this field ...
- 29
0
votes
1
answer
78
views
SELECT from _sent dataview - duplicate value error occuring
I am trying to create an Automation to update sent dataview records daily.
The code I've tried is the one below, but it kept showing error messages with
"Violation of PRIMARY KEY constraint. ...
- 5
0
votes
0
answers
34
views
Measuring Email Send Speed
The Trailblazer "Email Send Speed Optimization" section does a fine job explaining WHAT send speed is and what to consider, but it doesn't explain HOW to measure send speed.
Is the speed ...
- 1,207
0
votes
1
answer
157
views
Count of no clicks on last 3 emails using data views in marketing cloud
I am trying to write a query in SFMC where i want create a data extension with people who have not clicked any emails and we have sent them at least 3 emails.
I have the below query but i am getting 0 ...
- 29
0
votes
0
answers
50
views
How to get Date only from data view _Sent?
I am using this query
SELECT
Convert(date,s.EventDate) as s.EventDate,
s.JobId,
s.SubscriberKey,
s.TriggererSendDefinitionObjectID,
s.SubscriberID,
su.EmailAddress
FROM _Sent s
...
0
votes
0
answers
43
views
Record count for sent data view object is different in query editor & tracking extract
We are using tracking extract API to extract data views from the Marketing cloud. We get ~382 records when we extract sent object using this. However, when same object is queried we get ~25k records ...
- 77
2
votes
1
answer
28
views
SMS MobileConnect Bounce Reason Code
Is there a way to get the reason for an SMS bounce? I see that the "Undeliverable SMS" data view shows if a subscriber has bounced, but it doesn't show the reason for the bounce.
I'd ...
0
votes
1
answer
106
views
Retrieve Bounces from the emails are part from the journeys
I would like to retrieve the list of bounces of all the emails are included in the different journeys on a daily basis.
I know we would have to use the Data Views but I need help to build the query as ...
- 1,017
0
votes
1
answer
36
views
Logging Journey Step information in Marketing Cloud
I wonder if there is any way in Marketing Cloud to log the information about a specific step a subscriber is at in a given journey with the use of Data Views? Not only emails but also any other ...
0
votes
0
answers
37
views
all subscribers per business unit - SQL code data views
I'm trying to get the number of subscribers per business unit using this code so I was wondering is this the right way to do it (using data views in Query studio):
SELECT count(a.SubscriberKey) as ...
1
vote
1
answer
224
views
How to extract Device opened data in SFMC using SQL Query?
Currently on a daily basis we store email open/clicks etc. data in a data extension in SFMC and then export it to our data warehouse.
Example :
SELECT O.JobID, O.SubscriberID, O.EventDate, O.IsUnique, ...
- 11
0
votes
1
answer
78
views
File convert to UTF8
I'm trying to extract the journeys data with data views and put the generated file in FTP with UTF format.
After configuring the query and the Data extract activity (Data extract from Data extension), ...
0
votes
1
answer
93
views
Unsubscribe data view Timed Out
I'm trying to get all the unsubs in MC with Unsub Data view, the query that I'm using :
select
j.JourneyName,
ja.ActivityName as 'EmailName',
su.EmailAddress,
u.SubscriberKey as 'ContactKey',
u....
0
votes
0
answers
17
views
View reference for MessageId (SMS)?
In the data view _SMSMessageTracking, there is a field - MessageId.
Is there a reference view? Maybe contains desciption, name, createddate...
Thanks
- 1
0
votes
1
answer
102
views
Create a Master DE of Subscribers in a Child BU
Hangup: We have four separate "master DE's" being imported from IT into our marketing child business unit, each segmenting customers a different way. There is overlap between the four.
Goal: ...
- 3
1
vote
2
answers
144
views
Joining SendLog and Click Data View does not return any results
I want pull out the list of customers that did not click on the mail we sent.
We maintain a SendLog in which we have additional attributes logged (Example: emailname, subject etc.)
I understand that ...
- 11
0
votes
1
answer
146
views
Query Preference Attributes
Does anyone has any idea on how to query the Preference Attributes? I can see them in All Subscribers, but I can't find a way to query them.
I tried doing a query activity on EnterpriseAttribute Data ...
- 119
0
votes
1
answer
329
views
Querying data view (Open) and grouping by date in SFMC
I am trying to get the number of opens for each day (Example: April 15, April 16, April 17 and so on....)
Date
Opens
Apr 15
17500
Apr 16
5000
Apr 17
200
I am using the below query, but not ...
- 55
0
votes
1
answer
177
views
Is there a data view for MobileConnect in Marketing Cloud?
Similar to what we have for emails using _subscribers would there be one for MobileConnect?
0
votes
1
answer
193
views
Sent Data View vs Bounce Data View
I know this is silly question and I believe that the answer is no, but I want to confirm. Can a subscriber be in both the _Sent Data View and the _Bounce Data View?
I figure if an attempt is made, the ...
- 49
0
votes
1
answer
266
views
Job Data View from several Business Units
I'd like to create a script in my parent Business Unit that tells me everything about which mails has been sent to a subscriber from our parent Business Unit (automated sends) and from our child ...
- 473
0
votes
0
answers
200
views
Create a Data Extension report with Data View and Journey information
My goal is to create a Data Extension (DE_Journeys_Report) that will contain a report of different Journeys, extracted from different Data Extensions.
I need the following fields from the **Sent Data ...
- 1
1
vote
1
answer
335
views
How to populate a Data Extension with click data for the last 2 years
I'd like to query the open, click and sent data views to ultimately find who
has never opened or clicked a mail (since May 2019)
and how many mails they've been sent (since May 2019)
I understand ...
- 473
1
vote
1
answer
192
views
Marketing Cloud: Accessing _smsmessagetracking data view records using SOAP API [closed]
I wonder if it is possible to retrieve records from the _smsmessagetracking data view using SOAP API. I have found Sent, Open, Click, Bounce and few other objects in the documentation but I need an ...
1
vote
0
answers
46
views
Path Optimizer system data (SFMC)
Is it possible to retrieve Path Optimizer data from the data views or from tracking extract?
We export both sets daily to BiqQuery.
Example:
A/B test, 2 versions (10k vs 10k)
Winning (50k) version ...
- 57
-1
votes
1
answer
544
views
SQL query to find Job ID and email name for triggered & scheduled email sends
I was trying to implement this answer given in StackExchange - Query for Emails sent in the past month, unsubscribes opens, clicks for each
If you look at, “III-C. SQL Query”, Paragraph in the answer, ...
- 55
0
votes
0
answers
68
views
Get Campaign name from _Sent dataview [duplicate]
I downloaded the _Sent dataview to my server. Now I need to join the _Sent data with the campaign names to filter. How can I get from the _Sent dataview to the campaign name?
0
votes
2
answers
669
views
how to automatically send email to contact who did not open or clicked email in marketing cloud?
For our monthly newsletters, we get a new list every month.
Original send - We have 20 different newsletter every month.
Resend - Each newsletter also need to go out to contacts who did not open or ...
- 99
0
votes
0
answers
53
views
Trigger/send email when any contact open Email sent via Journey
I have a scenario where in I wish to send an alert email to a specific Email-ID whenever any contact opens the email sent from the journey.
As of now I think the best approach as below,
Create an ...
- 1,378
1
vote
1
answer
329
views
Fetch the email name used in Journey via ssjs
I am trying to create a cloud page in which when I enter any journeys name with the click of a button I should get the name of all the emails used with that Journey.
Some of these journeys in my ...
- 11
0
votes
1
answer
38
views
Is it possible to map Data Views to Data Extensions in attribute group?
I have created a Attribute Group in Contacts Builder. I have a Data extension with customers(contactID, name, email).
Can I link Data Views in Attribute groups? Is it possible to do so? I want to link ...
- 3
0
votes
1
answer
285
views
Querying _job data view is resulting DeliveredTime = Null
I have a journey builder send in a child BU without using any publication list.
In the journey builder UI and email tracking, I can see that sends were being delivered and opened.
However, querying ...
- 43
-1
votes
2
answers
82
views
Joining Multiple Data view tables [closed]
I am joining multiple data view tables to get email metrics, but I keep getting a system error!
I read it online that joining multiple data view tables is a pain and not recommended (this post was ...
- 13
0
votes
1
answer
229
views
Query Unsubscribes for Child BU
I would like to get the # of unsubscribes per month for a specific child BU
(1) I queried _Unsubscribe where EventDate is within the desired period
(2) I queried _ListSubscribers where ...
- 17
0
votes
0
answers
53
views
How to query only subscribers that are in an active journey? [duplicate]
We want to get only subscribers that are on an active journey using SQL.
Is that possible using data views, and without updating another status DE in every journey?
- 71
1
vote
1
answer
1k
views
Querying Mobile Push Demographics Table
I'm attempting to query our mobile push demographics table that's tied to the contact record to pull some Application Version data and I'm struggling in Query Studio.
The name of the table is ...
0
votes
1
answer
149
views
marketing cloud - how to join my audience with subscribers having a distinct value [closed]
I have an audience in a table called wtab which contains a column mail,
and I want to join it with the subscribers data view without duplicates.
when I have one 1 column I can use the DISTINCT keyword ...
- 271
1
vote
1
answer
407
views
_Journey Data View query only listing Journeys with a VersionNumber of 1
I've been attempting to join up a query between the _JourneyActivity and _Journey data views on VersionID but am noticing that I am unable to successfully Join up on anything beyond a VersionNumber of ...
