Questions tagged [data-views]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
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 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 ...
-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 ...
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. ...
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 ...
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 ...
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 ...
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 ...
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: ...
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 ...
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 ...
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 ...
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 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 ...
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 ...
-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, ...
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?