Last call to make your voice heard! Our 2022 Developer Survey closes in less than a week. Take survey.
18 votes
Accepted

Attributes in _PushAddress and _PushTag Data Views

I was able to retrieve the following from the DataExtensionField retrieve request: _PushAddress: | Field Name | Data Type (Length) | Required (Y/N) | Data Default | | :- | :- ...
user avatar
  • 1,207
9 votes

Einstein Engagement Frequency - How important are unsubscribes to this feature?

The default recommendation does not take unsub into consideration, unless you choose to adjust the recommendation by using the advanced option, where you can adjust how much unsub should be considered ...
user avatar
  • 7,367
7 votes
Accepted

Is there a functional difference between _ListSubscribers and _Subscribers with respect to STATUS?

Status of a subscriber on a list (_ListSubscribers) is not the same as the overall status in All Subscribers (_Subscribers). Lists are sub-sets of All Subscribers for which SFMC maintains status. ...
user avatar
  • 27.9k
7 votes
Accepted

How to populate a Data Extension with click data for the last 2 years

You'll need to use Tracking Extracts in order to obtain the data beyond the window provided in data views. For reference, your automation to pull this data should look something like this: Step 1: ...
user avatar
6 votes
Accepted

Data view updates timing out in marketing cloud

So with data views, they contain about 180 days worth of rolling data, this amount of data based on activities within a MC account can end up quite large over time. Overwriting queries is always ...
user avatar
6 votes

Query on system data views in Automation Studio

First of all ask support if they enable you to access the dataviews. I believe they have to enable it for your account. Create a data extension with these fields: https://help.salesforce.com/...
user avatar
6 votes
Accepted

Query: Deduplicate results (from _Click data view query)

If your target Data Extension's primary key is just Subscriber Key, I'd expect a primary key violation since you're joining with a one-to-many relationship (MasterSubscribers -> _Click). I think ...
user avatar
  • 27.9k
5 votes
Accepted

How can I access _subscribers data view from child business unit?

You need to prefix "ent" eg ent._subscribers
user avatar
  • 6,459
5 votes

Marketing Cloud Data Views - _Job and _Subscribers

Yes, according to the official documentation on the Query Activity page. Good luck getting any official clarification on details like that.. If you're wanting have more than 6 months of data in ...
user avatar
  • 27.9k
5 votes

SFMC - Querying All Contacts with SQL Query Activity (System Data Views)

There is no way for you to query certain system tables that would get you the full list of all billable contacts in your Marketing Cloud account. There used to be an option to request Marketing Cloud ...
user avatar
  • 5,302
5 votes
Accepted

SQL Querying subscribers older than 6 months

Instead of a sub query, you will want to use a join to the subscriber data view. For example: Select de.* from mydataextension de Join _subscribers s On s.subscriberkey = de.subscriberkey Where s....
user avatar
  • 364
5 votes
Accepted

What exactly the "BatchID" is in tracking Data Views?

BatchID is the 'grouping' of the email inside the batches sent to the Mail Transfer Agent for sending. In a Bulk send environment, this means the following: (numbers are all arbitrary) Assume that ...
user avatar
  • 27.9k
5 votes
Accepted

What is a 'false bounce'?

A false bounce is when a bounce occurs when an inbox is full or some sort of technical bounce issue, but when the delivery is retried it gets delivered and the subscriber opens the message. Marketing ...
user avatar
  • 7,367
4 votes

Is impression tracking data available in a data view?

There is limited/scattered information in the sfmc documentation regarding the impression region and tracking. However I will layout and consolidate all the relevant information from the sfmc help &...
user avatar
  • 3,052
4 votes

Query on system data views in Automation Studio

Also, if you are in a child business unit, you may need to add an "ent." prefix depending on the data view you are trying to query (e.g. _subscribers). Example: Select SubscriberId, DateJoined FROM ...
user avatar
  • 181
4 votes

Reason why we cannot access data more than 6 months in data views?

What I did was to copy exactly the system data views and make my own data extensions using the exact schema. I then run a query every month on the first that grabs the entire last month's worth of ...
user avatar
  • 814
4 votes
Accepted

SQL help on Data Views for _SENT table

I think you need to filter the query to only pull those email ids instead of creating a case around it. For example: SELECT SubscriberKey as EMAIL_ADDRESS_, 'TRUE' as WelcomeSent FROM _SENT s JOIN ...
user avatar
  • 27.9k
4 votes
Accepted

Is there a Data View of Email Content Tied to the Job Data View?

I assume that you are trying to store a copy of the sent email. This is a great question. There are several considerations with your proposed approach. 1. Send Performance While all AMPscript calls ...
user avatar
  • 15.6k
4 votes
Accepted

_Open Data View General Inquiry

Open DataView is filled in on the 'back end' of SFMC. The user has no control over this DV. Basically view it as a SQL table without any primary keys, meaning every record is a new record (append ...
user avatar
  • 27.9k
4 votes
Accepted

Data View: Only count the JobID without TestSend

You should be able to achieve this by adding the following condition to your query: WHERE TestEmailAddr = '' TestEmailAddr column will only be populated for emails which were sent as test sends, so ...
user avatar
  • 10.3k
4 votes

Marketing Cloud Data View Question - Query returns 0 records

You need to run this in the ENT BU to get records, as it sounds that you might have a subscriber filter configured. When you send an email to a data extension, the contacts will always be added to ...
user avatar
  • 16.9k
4 votes
Accepted

Querying Mobile Push Demographics Table

The proper name for the Mobile PushDemographics data view is _PushAddress. There's an answer made by @Daniel that lists all default fields and their datatype here, but be aware that you your data ...
user avatar
4 votes
Accepted

SMSMessageTracking Data View - Differences Between Undelivered and Delivered Fields

I just closed a support case regarding this view (#41900016). Here's what I learned: Delivery fields are updated as the send process is occurring. If the delivery status changes the Sent, Delivered,...
user avatar
  • 27.9k
4 votes
Accepted

_Open.IsUnique is TRUE for two records with the same JobId and SubscriberKey - Why?

Uniqueness in the events tables (click, open, sent etc data views) is established by the four IDs: subscriberId (or key) => person jobid => sendout listId => which list is targeted (e.g. all ...
user avatar
3 votes
Accepted

SQL set default when no row returned | Data Views

So, with some testing and investigation this is how I have resolved: SELECT SubscriberID, JobID, ListID, BatchID, EventDate FROM _Sent WHERE EventDate >= DateAdd(hh,-2,CURRENT_TIMESTAMP) UNION ...
user avatar
  • 3,052
3 votes
Accepted

SQL Query: Data view with data extension

The query specified will return multiple rows if more than one open event is found for the particular subscriber. You can add the keyword distinct to your query to insure only unique row of data are ...
user avatar
  • 2,585
3 votes

Data View : Unsubscribe and ListSubscribers

The _Unsubscribe data view contains unsubscribe actions taken by the subscriber after receiving an email. It's by JobID. _ListSubscriber list status is the current status of the subscriber specific ...
user avatar
  • 27.9k
3 votes

Date-time stamp in sends & opens data view in marketing cloud is not in sync with local time

The system date time for Marketing Cloud is almost always in CST. In your query you can just convert the CST time to your timezone. To adjust it by 11 hours. SELECT DATEADD(hour, 11, s.EventDate)...
user avatar
3 votes

How to get email address against email id in Job data view?

Please read the documentation of salesforce regarding the dataviews. The Job data_view does not give information about the status of an email. It just tells you that the a job has been scheduled for ...
user avatar
3 votes

Data View: Only count the JobID without TestSend

These test sends also have a Category of Test Send Emails so you could have your query have the following in the where clause: where category != 'Test Send Emails'
user avatar
  • 7,367

Only top scored, non community-wiki answers of a minimum length are eligible