Questions tagged [data-views]
The data-views tag has no usage guidance.
166
questions
0
votes
1
answer
38
views
How to get information about unsent emails in Journey Builder?
I have a Journey Builder, in which there are exceptional situations where an email, in general, is not sent and these people are not counted in the Contact Count. They are also not in the Data View ...
- 1
1
vote
1
answer
70
views
Update status in all sub with the master DE
I have a question about the unsusbcribe in the all subscriber.
When my customer decided to optout in the website of my client, the information of this unsubscribe goes up in service cloud, then in the ...
- 11
-3
votes
0
answers
20
views
How to retrieve Email address for hard bounced contacts from that account using dataviews [duplicate]
Select
E.[EmailAddress], E.Status
from
_subscribers E
right join
_bounce B
on
B.SubscriberKey = E.[SubscriberKey]
where
B.BounceCategory = 'Hard bounce'
Count was different from tracking ...
- 11
-1
votes
1
answer
50
views
Will _Subscribers Dataview retrieve only recent 6 months data?
I need to retrieve 1 year data from parent Bu
i used the following query.
Select EmailAddress from _Subscribers
But i got only 6 months data
- 11
0
votes
0
answers
11
views
Fetch campaign name and code of an email via SQL in SFMC [duplicate]
I want to fetch campaign name and campaign code of an email via SQL in SFMC. The campaign name and code will be in an analytics software.
I cannot seem to find the campaign name and code in any of ...
- 111
0
votes
1
answer
76
views
How to pull Hard bounce Emails from Data extension to exclude it from sending Emails
This is my Query. But its not working as expected.Count of Hard bounce list retrieved using Query is different from tracking report hard bounce list :
select
E.[Email]
from
Test_DE E
inner join
...
- 11
0
votes
0
answers
15
views
Can I track the total number of clicks on a SMS URL by data view?
I know in MobileConnect there is URL Tracking, which displays the total number of clicks the shortened link received, but it seems like skewed data. Can I use the _Click and _smsmessagetracking data ...
0
votes
1
answer
64
views
How SendLog can capture sent event date?
I am looking into the current SentLog and not seeing any date like Send date. How can I get / capture sent event date in send log?
- 131
0
votes
1
answer
32
views
Unique Opens no working
select
d.envios,
x.aberturas,
d.data_envio,
d.nome_email,
d.jobid,
pct_abertura = cast((x.aberturas*100/d.envios) as decimal(5,2))
from DE_BK_CV_DASHBOARD_JORNADA_FOPA d
left join (
select
...
0
votes
0
answers
63
views
SQL Data Views for Email
Can anyone help me with the SQL query for this? As I have written the query but still not getting a result.
I want Number of emails received(to whom we are sending email), opened and links clicked by ...
0
votes
0
answers
128
views
CloudPage Data Views
i'm trying to see the opens and clicks of my landing pages in marketing cloud.
I've tried creating an impression reports, but it doesn't have attributes like the ones i'm looking for. Then i've tried ...
- 1
0
votes
1
answer
24
views
Need help with a SQL query to marry Click and Subscriber data
I'm trying to run a query that will return all of the clicks from a specific email send including alias and email. I need to then marry that data with a custom attribute in our subscriber data (...
1
vote
1
answer
598
views
Different subscriber status between All Subscriber list and _Subscriber data view
A bit of context: I am building an automation that retrieves all subscribers that have a mismatch between their subscriber status in the All Subscriber list and the hasOptedOutOfEmail field on the CRM ...
- 307
0
votes
1
answer
43
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. ...
- 37
0
votes
0
answers
140
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
133
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
109
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 ...
- 849
0
votes
2
answers
82
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
292
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
79
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
113
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 ...
- 849
0
votes
1
answer
102
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
240
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 ...
- 173
0
votes
1
answer
118
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
132
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 ...
- 131
0
votes
0
answers
119
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
195
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
49
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
300
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
108
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
65
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
54
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
189
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,019
0
votes
1
answer
40
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 ...
1
vote
1
answer
407
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
203
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
122
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
1
answer
147
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
198
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
214
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
513
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 ...
- 57
0
votes
1
answer
293
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
324
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
375
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 ...
- 487
0
votes
0
answers
321
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
506
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 ...
- 487
1
vote
1
answer
277
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
60
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 ...
- 67
-1
votes
1
answer
868
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, ...
- 57
0
votes
0
answers
70
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?
