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 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 can then be filtered for further segmentation or customized with additional fields. It can also be used for reporting purposes.
Step 1
Create the Performance_Subscriber Data Extension using the following SSJS activity:
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, read Ivan Razen's thorough guide here.
Step 2
Create a SQL query that overwrites Performance_Subscriber with the following code:
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. This sequence of queries doesn't take more than a few minutes to run with 1.2 million records.
Step 3
Next, use this SQL query to update Performance_Subscriber with information from the Open and Click Data Views:
Performance_Subscriber records both unique and all clicks and opens. It might be helpful for finding the most engaged subscribers.
Step 4
Calculate their open and click rates with an SQL activity that updates Performance_Subscriber:
Step 5
Finally, schedule an Automation to run the SQL activities, ordered in the same as they were created.
Now What?
Performance_Subscriber is now ready to be filtered into sendable data extensions, like a segment of those 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, 60, 90 days etc.
Not only is Performance_Subscriber helpful for segmenting, it can also be the basis of creating helpful reports. For example, a signup source could be added to subscriber's record. An additional data extension could be created that grouped together the performance of the source, instead of the individual subscriber.
Summary
Marketing Cloud's out-of-the-box segmenting for scheduled sends and more precise journey entry sources is practically non-existent. But with some data extensions and SQL queries, the possibilities for segmentation is limitless.
With the above SSJS, SQL queries and automation, any business unit can quickly have a data extension that's a useful start to segmenting its email subscribers.
You can also find the code necessary to build Performance_Subscriber in this github repository.
How do you use SQL queries to segment subscribers?
Top comments (0)