This resource was originally posted on DEV by LearningCurv Marketing Automation Consultant Ed Gray.
Salesforce Marketing Cloud’s Journey Builder is a robust and powerful tool to target subscribers with tailored emails, texts and digital ads. But placing all your subscribers into perpetual journeys for segmenting is not always the best option (neither is Einstein’s fluctuating engagement scoring).
Below is a quick way to set up a data extension that updates everyday with a subscriber’s email behavior in the last 180 days. The SQL queries rely entirely on Data Views, so they should work the same on every account or business unit.
The Performance_Subscriber data extension we’re creating here can be filtered for further segmentation or customized with additional fields, and it can be used for reporting purposes.
Let’s jump into how to set up this data extension and start segmenting Marketing Cloud journeys more effectively.
This should prevent any typos or mistakes when setting up the data extension.
Any of the SQL queries below would require some modifications if changes to the field names or data types were made.
To learn more about creating data extensions using SSJS or WSProxy, we suggest reading Ivan Razen’s thorough guide here.
Performance_Subscriber now has the relevant subscriber information from the Subscribers and Sent Data Views.
Depending on the number of subscribers in the business unit, separating or combining some of the SQL queries may be possible. From what we’ve seen, this sequence of queries takes no more than a few minutes to run with 1.2 million records.
Performance_Subscriber records both unique and all clicks and opens. This can be helpful for finding your most engaged subscribers.
Performance_Subscriber is now ready to be filtered into sendable data extensions
For instance, you can create a segment of subscribers with a high open rate, but low click rate. With some additional SQL and columns, you could even track trends in a subscriber’s click or open rate over the last 30 days, 60 days, 90 days, etc.
Not only is Performance_Subscriber helpful for segmenting, but it can also be the basis of creating new and useful reports.
For example, a signup source could be added to a subscriber’s record. An additional data extension could then be created to group together the performance of that source, instead of the individual subscriber.
Marketing Cloud’s out-of-the-box segmentation capabilities for scheduled sends and precise journey entrance sources leaves much to be desired. But with some data extensions and SQL queries, the possibilities for segmentation are limitless.
With the above SSJS, SQL queries, and automation, any business unit can quickly implement a data extension that provides a powerful start to segmenting email subscribers.
You can find all the code necessary to build the Performance_Subscriber data extension in this GitHub repository.
If you need help creating automations or segmenting your subscribers in Marketing Cloud, reach out to LearningCurv’s certified consultants. Explore our Marketing Cloud services to learn how we can improve your platform.