Segmenting and Reporting On Subscriber Behavior in Salesforce Marketing Cloud
Segmenting and Reporting On Subscriber Behavior in Salesforce Marketing Cloud
Ed Gray
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.
1. Create Performance_Subscriber Data Extension
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.
Create the Performance_Subscriber Data Extension using the following SSJS activity:
2. Overwrite Data Extension with an SQL Query
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.
Create a SQL query that overwrites Performance_Subscriber with the following code:
3. Update Data Extension with Open & Click Counts
Performance_Subscriber records both unique and all clicks and opens. This can be helpful for finding your most engaged subscribers.
Next, use this SQL query to update Performance_Subscriber with information from the Open and Click Data Views:
4. Update Data Extension to Calculate Open & Click Rates
Calculate subscribers' open and click rates with an SQL activity that updates Performance_Subscriber:
5. Schedule Automation to Run SQL Activities
Finally, schedule an Automation to run the SQL activities. Run these SQL activities in the same order you created them.
Filtering Data from Performance_Subscriber
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.
The Key Takeaway
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.
