Skip to main content
21 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 | | :- | :- ...
Daniel's user avatar
  • 1,287
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 ...
EazyE's user avatar
  • 8,547
8 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 ...
Gortonington's user avatar
  • 30.7k
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. ...
Adam Spriggs's user avatar
  • 30.8k
7 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 ...
zuzannamj's user avatar
  • 14.4k
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: ...
Jason Hanshaw's 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/...
Johannes Schapdick's user avatar
6 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 &...
Brad Sapkota - altus.digital's 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 ...
thechrishaddad's user avatar
6 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'
EazyE's user avatar
  • 8,547
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 ...
Adam Spriggs's user avatar
  • 30.8k
6 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 ...
EazyE's user avatar
  • 8,547
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 ...
Adam Spriggs's user avatar
  • 30.8k
5 votes
Accepted

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

You need to prefix "ent" eg ent._subscribers
0xsegfault's user avatar
  • 6,603
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 ...
Rain's user avatar
  • 5,785
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....
Tyler's user avatar
  • 374
5 votes
Accepted

Different subscriber status between All Subscriber list and _Subscriber data view

All Subscribers is not a direct representation or "frontend" to the ENT._Subscribers Data view. You can simply deduce this by looking at the fields it shows and the documentation on the data ...
Jonas Lamberty's user avatar
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 ...
Stan Alachniewicz's user avatar
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 ...
Carrie T's user avatar
  • 181
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 ...
Gortonington's user avatar
  • 30.7k
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 ...
Eliot Harper's user avatar
  • 16.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 ...
Gortonington's user avatar
  • 30.7k
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 ...
Lukas Lunow's user avatar
  • 21.7k
4 votes
Accepted

Can we extract data of contacts that entered a journey and are not a part of email activity in Journey builder

The available data views for Journeys are only centered around Email interactions (which does make sense). There is nothing relating to other interactions inside of the Journey Builder process there. ...
Gortonington's user avatar
  • 30.7k
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 ...
Rafał Wolsztyniak's 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,...
Adam Spriggs's user avatar
  • 30.8k
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 ...
Jonas Lamberty's user avatar
4 votes

Will _Subscribers Dataview retrieve only recent 6 months data?

All Data Views hold up to 6 months of data: Query data views using Automation Studio for up to six months of Marketing Cloud subscriber and journey information. Source: https://help.salesforce.com/s/...
zuzannamj's user avatar
  • 14.4k
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 ...
Brad Sapkota - altus.digital's user avatar
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 ...
David Devoy's user avatar
  • 2,615

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