As we have commented in previous articles, if we want to improve our segmentations and especially our results in our Marketing Cloud strategy, it is essential to develop increasingly precise queries, and this is where DataViews come into play.
If we want to identify a potential customer of a product, a signal may be that in a generic email, the user clicks on the link of that product. Also, a customer always clicks on a specific product category. For this, it is important to use the LinkName intelligently. Many times only a descriptive name is used, but if you put a category, a separator and then the descriptive name, it will activate other interesting options to identify users who are interested not only in a specific product but in a category as such.
A first query to know the status of the subscribers
A very simple first query can be the status of your subscribers in a DE, through this query you can see the true impact or final delivery that you will have based on that status:
SELECT firstname, de.lastname, de.cathegory, de.email, s.[Status]
FROM Test_Data_Extension as de LEFT JOIN _Subscribers s
ON email=s.SubscriberKey
Through this query, those with NULL status will probably be new users or simply that it is the first time that you send them something through Marketing Cloud.
Reinsistence Email to Non-Openers
One option with DataViews queries is to retry a second submission to users who didn't open it the first time. If they have not opened it, the best options that you can choose to improve the results focus on 2 variables; the “From” and the “Subject”, modify one or both and try to seduce those users that you didn't succeed in the first try.
To carry out this query you need the JobID of your shipment, if you activated tracking, it's simple, go to Tracking and locate your shipment, what you need is the aforementioned JobID
With that number you can already make the query…
Openers of a shipment
If we make a sequence of submissions, but we want to send that second submission only to users that we know have opened the communication, we can use this query using the JobID as well.
SELECT de.SubscriberKey
FROM YourDataExtension of
INNER JOIN _open o
ON o.SubscriberKey=de. SubscriberKey
WHERE
o.JobId=’1234567’
AND o.IsUnique='True'
And if, in addition, we want to know that they did not click
Many times we manage to capture attention with the subject, but once inside, the user shows no further interest and does not click on any of the links included. The reasons can be multiple:
-
upon entering they see that it was not what they expected (the subject confused them).
-
expectations were greater than what they found.
-
a friend called them at that moment and told them something so interesting that later they forgot to click.
-
or maybe they did not understand the offer well.
-
or other reasons.
The key is to analyze the message sent, to see if we can take advantage of the fact that they at least saw it to go with a sequential option or a different option.
SELECT SubscriberKey
FROM TuDataExtension
WHERE SubscriberKey IN
(SELECT SubscriberKey FROM _open WHERE JobId=’1234567’)
AND SubscriberKey NOT IN
(SELECT SubscriberKey FROM _click WHERE JobId=’1234567’)
It may also be that you are interested in the fact that they have also clicked (they still arrived, but they did not convert), in this case, by changing the last NOT IN to simply IN, you have already done it.
What if I want to know who clicked on a particular link?
Imagine that you send a very generic email with a bunch of hardware products, but you want to send a subsequent email only to those who showed interest in NAS products with an irresistible offer.
With this query, you can get those subscribers:
SELECT linkname,JOBid, s.EmailAddress, s.SubscriberKey
FROM _Click c
INNER JOIN _subscribers s
ON c.SubscriberID=s.SubscriberID
WHERE IsUnique='true' AND JobId=’1234567’
AND linkname='Productos NAS'
Recover the "asleep"
We receive more and more emails, and we sign up with great enthusiasm, but then we start to decline, it is good to detect these sleepy people before they fall into an infinite sleep.
A fresh communication, a promo just for them, we have to look for some resources to revive them and also offer them to modify their preferences or frequencies.
To identify these sleepers with 30 days without activity, here is a query:
SELECT DISTINCT s.SubscriberKey
FROM _Sent s
LEFT JOIN _Job j
ON s.JobID = j.JobID
LEFT JOIN _Open o
ON s.JobID = o.JobID AND s.ListID = o.ListID AND s.BatchID = o.BatchID AND s.SubscriberID = o.SubscriberID AND o.IsUnique = 1
LEFT JOIN _Click c
ON s.JobID = c.JobID AND s.ListID = c.ListID AND s.BatchID = c.BatchID AND s.SubscriberID = c.SubscriberID AND c.IsUnique = 1
WHERE
s.EventDate > dateadd(d,-30,getdate())
AND (o.SubscriberID is NULL AND c.SubscriberID is NULL)
Conclusion
This is just a generic sample of things you can do with DataViews, but this can be used much more customized to your needs depending on your business, target, and the types of communications you do. If you use journeys, there are also dataViews, also for the mobile part, etc. In the end, the goal is to make segmentations increasingly intelligent and effective.
If you liked this article, you might also be interested in:
- We are Salesforce Marketing Cloud Official Partner Agency
- Main advantages of segmentation in Salesforce Marketing Cloud
