Questions tagged [data-views]
The data-views tag has no usage guidance.
78
questions
0
votes
0answers
41 views
GROUP BY in Marketing Cloud to get aggregate opens
We have queried _Sent and _Open data views to get the openers / non-openers for the last 30 days. In the second step, we would like to inner join it with _Job data view to get emailname and emailid. ...
2
votes
2answers
69 views
Einstein Engagement Frequency - How important are unsubscribes to this feature?
We are about to get Einstein Engagement Frequency and I can see one potential hiccup with us using it.
We have Salesforce CRM and the Marketing Cloud Connector configured. Our source of truth for ...
2
votes
2answers
37 views
dynamic subject line extraction
We sent an email which has a dynamic subject line using Ampscript logic. is there a way to find out how may people opened subject line A vS subject line b?
when checking the job table it just shows ...
2
votes
1answer
64 views
What exactly the “BatchID” is in tracking Data Views?
Lately, I have been trying to figure out the most generic query for getting tracking data into one table. As suggested in the SQL Reference, I have been using this join:
...
FROM _Sent s LEFT JOIN ...
0
votes
0answers
13 views
Excluding test sends from Email Open Data View query [duplicate]
we set up sth. like a custom lead scoring in Marketing Cloud by using web webtracking and data views. We are gathering everyone who e.g. opens/clicks an email, visits a specific page on the website ...
1
vote
0answers
28 views
Client MID is now a PARENT MID, but Data Views do not recognize this
We recently added a BU under an existing MID, so now the MID is the "parent" to the newly created BU's MID. However, when we run a simple query and add ent. as a prefix the system does not recognize ...
0
votes
0answers
24 views
Customer Key Of The _PushTag Data View [duplicate]
Is there a customer key to the "_PushTag" data view? I need it for API uses and SSJS development.
0
votes
1answer
43 views
Getting subscriber opens and clicks from journey emails
We would like to get subscriber opens and clicks for all emails from all versions of our journey in the last 6 months (I know that's the limit for data views). I've found the official SQL query but I ...
0
votes
2answers
48 views
Data View: Only count the JobID without TestSend
Is there a way to count with SQL the sents excluding the jobid from test sent?
My sintax below:
SELECT
DATEPART(yyyy,GETDATE()) AS [Year],
'BU1' AS [Business Unit],
COUNT(*) AS JobID,
DATEPART(m,...
1
vote
0answers
23 views
How to query Failed Email Sends over each business unit?
I have a request asking to send out an email with the failed send JobId's (lookback last 2 days) - I would like to automate this process by logging the failed send data to triggered send data ...
1
vote
0answers
24 views
Why is my _subscribers data view returning different results for email address?
I have both MC and SC connected together using the MC Connector. I have noticed a data inconsistency issue when creating an automation to update Email Addresses in my All Subscribers list.
When I the ...
0
votes
1answer
35 views
How to query data views in Child Business Unit?
Can someone please help me on this?
I have a scenario where I need to query the child business unit data views from child business unit.
When I'm trying to query on _open or _click data views it ...
0
votes
1answer
37 views
What does OptOut really mean in the SMSMessageTracking Data View?
Per the docs:
https://help.salesforce.com/articleView?id=mc_as_data_view_sms_message_tracking.htm&type=5
Whether the subscriber has opened out of SMS messages (1 = true, 0 = false)
Is ...
1
vote
1answer
104 views
SQL query to group EventDates by day of week
I'm trying to write a query in Marketing Cloud that will filter for an event (specifically a unique open) that took place on a specific day of the week. So for example, my query would answer the ...
-4
votes
1answer
64 views
Data view schema in marketing cloud [closed]
How all data views are connected to each other in salesforce marketing cloud?
0
votes
0answers
44 views
Marketing Cloud Tracking Exports to Data Extensions
I have a multi-business unit marketing cloud setup and I am trying to extract individual email send data to an external website.
I tried to look up querying this data from within automation builder ...
0
votes
1answer
29 views
Journey Goals based on email metrics
Is it possible to establish a Goal in a Journey based on the % of Open / Clicks / etc of an email within the journey?
I see a Goal can be only set based on Contact Data, so I was thinking in create a ...
0
votes
1answer
35 views
_Open Data View General Inquiry
I am planning on using the _Open data view in my queries in automation studio, but I am not entirely sure how data gets stored there. I will phrase my question in the following scenario: I sent 2 ...
0
votes
1answer
48 views
SQL Querying subscribers older than 6 months
I am trying to query data from an existing data extension into a new data extension and filter out the subscribers that were added within the last six months. The existing data extension does not have ...
0
votes
0answers
51 views
Marketing Cloud: Write “View As Web Page” link and subject of email in Data Extension
For our call center agents it is important to see the emails and its contents a subscriber has received. My idea is to provide our crm system with a file containing the subscriber id, the email ...
0
votes
1answer
81 views
How to eliminate bot clicks with query in Marketing Cloud?
we are querying several data views such as the click of specified links (via web collect) and email opens and email clicks.
In the target Data Extensions that stores all (unique) clicks we experienced ...
0
votes
0answers
24 views
SQL - I want only one row in the data extension per jobId counting all Sents,opens,clicks and unsubscribes for a specifc jobid/Emailsubject
SELECT
F.Emailsubject AS [Email Subject],
a.JobID AS [JobID],
(
SELECT COUNT(DISTINCT d.EventDate)
FROM _Click d
WHERE a.JobId = d.JobId
GROUP BY jobId
) AS Clicks,
(
SELECT COUNT(DISTINCT ...
0
votes
0answers
29 views
Can I query the Email customer key or retrieve via AmpScript?
I need to get the Email (Content Builder) Customer Key, this isn't available in the email send context as a personalisation string and the the _job table only gives back the Asset ID.
Any other way ...
0
votes
2answers
53 views
How to get email address against email id in Job data view?
Job data view provides the status of an email if it was delivered or not but it provides only Job Id and Email Id. This email id is a numeric number and not the email address.
How to get Email ...
0
votes
0answers
113 views
Marketing Cloud API for data views
I have a nice integration setup for salesforce using the simple-salesforce Python package. I can easily insert, extract and describe objects across Salesforce.
I would like something similar in order ...
1
vote
1answer
57 views
Querying for Content Views
in Marketing Cloud, I try to query for contacts who clicked on a specific link on a website and is hence stored in Content Views Data Extension.
We want to use that for a kind of scoring, however ...
0
votes
0answers
124 views
How to query “Last Application Open” & “Last Send” from _PushAddress or other relevant data view?
I can query the system attributes from the _PushAddress data view as listed here:
Attributes in _PushAddress and _PushTag Data Views
But I am unable to fetch the "Last Application Open" & "Last ...
1
vote
1answer
142 views
Is there a Data View of Email Content Tied to the Job Data View?
The Data View for Job has an EmailID as one of the columns. Is there a Data View for Email that can be associated with the job using that EmailID column? Documentation of Data Views doesn't have ...
0
votes
1answer
55 views
SQL Query on _Sent Data View for Suppression List (5 sends in 7 days)
I'm working in a new org where we don't have any send data. I need to set up a suppression list for any subscriber that received 5 emails in the last 7 days. I am not 100% sure if my code below will ...
5
votes
1answer
109 views
Get bounce reason into Sales Cloud
I'm trying to get information about bounce reason from Marketing Cloud to integrated Sales Cloud (I'd like to have this information on Individual Email Result record). Is there any way to do this ...
0
votes
2answers
312 views
Mapping between Contact Key and Contact ID
I have found this article which outlines the data available within mobilpush data views.
Attributes in _PushAddress and _PushTag Data Views
However i have not been able to find a way to retrieve the ...
1
vote
1answer
132 views
Is there a Mobile Push Job and App data view?
With Push Send Logging we are able to store PushJobID in a Data Extension. But is there also a Push Job data view to join and retrieve data? Same question for AppId and App data view.
Push Send ...
1
vote
1answer
55 views
Marketing Data Extension `Last Click Date` from data views
I have a query that's intended to update a field named Last_Click_Date based on the most recent EventDate from the _Click data view.
It doesn't appear to be grabbing the most recent date param and ...
0
votes
1answer
34 views
Behaviour of emails sent with “Surpress from reports” in Marketing Cloud
When this option is selected, how can we track scheduled sends? and is the send tracking still available in Data Views (i.e. Sent table)
0
votes
2answers
85 views
Date-time stamp in sends & opens data view in marketing cloud is not in sync with local time
I'm using salesforce marketing cloud for sending emailers. Date time settings in my account are set as per IST (Indian standard time), GMT + 5:30.
Though when I fetch data from "Sends" & "Opens" ...
1
vote
0answers
347 views
SFMC: SQL Query on Dataview objects to calculate Journey's sends, opens, and clicks
I want to calculate the sends, opens, and clicks of all journeys. Expecting the records in Date Extension consists of records in the following sequence:
JourneyActivityObjectID
VersionID
ActivityName
...
1
vote
2answers
434 views
What is the primary key in data view - sent and data view - open?
I want to create a data extension consisting of total sends, their send datetime value & total opens.
There isn't any standard report in marketing cloud which provides time of send. To fetch ...
0
votes
2answers
313 views
Does IsUnique in the _Click data view dedupe subscribers over multiple jobs?
I want to write a SQL query that will find the total clicks and unique clicks for 1 URL across multiple email sends. Total clicks is easy, but I'm not sure about the unique clicks. I want to basically ...
1
vote
1answer
69 views
Definition of Event date in _bounce data view in salesforce marketing cloud?
This is with reference to standard system data extension in marketing cloud named _bounce.
As per standard documentation definition of EventDate is "The date the bounce took place."
Let's say, email ...
0
votes
1answer
141 views
Querying ActivityName in _JourneyActivity data view returns no results
My brain is breaking.
I have journeys deploying emails in my Salesforce Marketing Cloud instance. According to all of Salesforce's documentation (specifically this page), I should be able to query ...
0
votes
1answer
92 views
Can't Query All Fields in AllSubscribers List
I need to grab fields within the AllSubscribers list. I attempted a simple query, but I'm getting the error "Invalid column name..." for the columns that I've commented out in the screenshot below. Is ...
1
vote
1answer
2k views
SFMC - Querying All Contacts with SQL Query Activity (System Data Views)
I am trying to create a data extension that contains all contacts from my enterprise account. I need a complete list of contacts as we are billed by SFMC per contact. Querying the data view ...
1
vote
1answer
221 views
SQL help on Data Views for _SENT table
In creating an audience for a Journey, I'm pulling everyone who already received one of our Welcome emails so they skip that step in the Journey. To do this, we've written the query below and it ...
0
votes
2answers
438 views
Error when trying to query All Subscribers
I'm trying to query all subscribers through an SQL query.
I've copied a query which I am using in another instance of marketing cloud which works fine. However in the new instance the query activity ...
1
vote
1answer
283 views
Accessing the Tracking Data
Does anyone know how can I access / export the data that is in the Tracking section in Email Studio?
I'm not interested in sending myself an Email Report. I'd like to get that data into a Data ...
2
votes
2answers
2k views
Query on system data views in Automation Studio
I was wondering if someone perhaps know how we can identify the system data views we use/have in our account?
According to this article we should be able to query for instance the data view: ...
1
vote
1answer
35 views
Duplicate Email Address, One Clicks but all records appear when querying _Click Data View
I have a requirement whereby a store owner may receive an email offer for every store they own, even though they have just one email address. To get around the duplication issue we marked both the ...
2
votes
0answers
541 views
Joining Enterprise Attribute Data View and Business Unit List Subscribers Data View
I need to query subscriber profile attributes, which are only available at the parent Enterprise level EnterpriseAttribute Data View, together with the subscriber’s Status in the child Business Unit ...
1
vote
1answer
667 views
How can I access _subscribers data view from child business unit?
As the title suggests I would like to query out _Subscribers from within a child Business Unit.
When running my query
select SubscriberKey, EmailAddress
from _subscribers
No results are returned, ...
1
vote
1answer
108 views
SQL set default when no row returned | Data Views
I am running SQL activity which grabs rows from last 2 hours.
However, there will be instances when there is no rows found in last 2 hours and I'd like to set Default values in that case.
Attempt 1:
...
