Until now we have dealt with aspects such as the segmentation of a Data Extension or the interrelationship with several Data Extensions. Now more data comes into play, it will come from the DataViews, those tables that are often unknown to many users and that have important information to be able to enrich segmentations and therefore, efficiency in our communication strategy.
These dataviews store information from our subscribers and the last 6 months of tracking information. It is important to know that in your account you do not have them available by default (and they are not visible whether they are activated or not). If you have doubts, make a simple query to a dataview and if it responds with an error, you must request its activation. If you also want to have visible data extensions of the dataViews, you will have to do some automation with a sql query that extracts the data and stores it in those Data extensions, this is highly recommended if you want to store that information that Marketing Cloud progressively eliminates, it is That is, if you want to keep all your history.
With this information, you will be able to make rich segmentations and you can even use them in AMPSCRIPT through a simple lookup function.
Once these preliminary issues of the DataViews have been seen, the most important thing is to know what is in them and what relationship they have in order to extract data with business logic and really relevant.
_Subscribers
In this table you have all your subscribers, at the email studio level here a repository of all the users to whom you send an email is made.
In principle, at the DataView level, it is usually used to know the status of that subscriber.
If, for example, you have a DE and you want to know what status each record has, and your field that matches the Subscriber Key is the email (although it is not usual or recommended, a unique ID-type field is more often used), you can consult its status with a query like this:
I have seen it done many times with an INNER JOIN, but I prefer to use a LEFT JOIN, since there may be new users in that Data Extension who still do not have any status in All Subscribers and it is also important to know.
List of tables
Before continuing, it is important to see the relationship between the tables of the DataViews. The three main fields by which all the tables that are most used in the email part are related are SubscriberKey or SubscriberID (this is the ID generated intermittently by Marketing Cloud) and JobID. In this chart, you can see these relationships and the fields you can query. There are some more DataViews objects for the Mobile part mainly journey builder.
Structure of Salesforce Marketing Cloud Dataviews Tables:
1. Click
In this table, you can check everything related to clicks from your campaigns. You can check the number of clicks of a submission (if you want them to be unique, remember to use the IsUnique='true field). It can return the link of the click, the name if you have defined it, etc. You can also get this at the subscriber level.
2. Bounce
As its name indicates, from here you can get everything related to bounce from your emails. Bounce type by category and subcategory, smtp response, etc. Within categories, you can filter by Block bounce, Hard Bounce, Soft Bounce, and Technical/Other bounce.
Block bounce is a type of soft bounce caused by filters. Then there are the typical hard and soft ones, which you can find in more detail in the subcategory and the technical aspects can be a simple “saturated server”.
3. Journey
Saves general information of the journey such as its status, name, etc.
- JOURNEY_activity
This table is important to connect JOURNEY as an intermediate table with the email tracking system.
NOTE: JourneyActivityObjectID corresponds to TriggererSendDefinitionObjectID in the _Sent, _Open, _Click, and _Bounce tables.
4. Job
Each shipment task is collected here, so it has essential information such as the name of the shipment, the "From", the programming date, the category or the "Subject" of the shipment itself.
5. Unsubscribe
All the information regarding the cancellations, in which JobID it was generated, date, etc.
6. Open
All openings, with JobID, subscriberKey, etc. And IsUnique can be used if you only want unique openings.
7. ListSubscribers
Here you can find subscribers by lists.
8. Sent
In this dataView, you have all the subscribers to whom you have sent communications, once or n times. So it includes the JobID to relate that submission to the specific task (_Job)
Conclusion
With this description of the table relationship and a brief explanation of each object, you can get an idea of what we can include in various queries to obtain more precise analytics or segmentations for our communications.
In future chapters, we will put this information into practice to generate more interesting queries for our objectives.
If you liked this article, you might also be interested in:
