Segmenting and Reporting On Subscriber Behavior in Salesforce Marketing Cloud

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:

	<script runat="server">
	Platform.Load("core", "1.1.1");
	var api = new Script.Util.WSProxy();
	try {
	var FolderName = "Reporting";
	var FolderAttributes = Folder.Retrieve({ Property: "Name", SimpleOperator: "equals", Value: FolderName });
	var FolderID = FolderAttributes[0].ID;
	var perf = api.retrieve("DataExtension", ["CustomerKey"], {
	Property: "Name",
	SimpleOperator: "equals",
	Value: "Performance_Subscriber"
	});
	var perfKey = perf.Results[0].CustomerKey;
	var perfFields = [
	{
	"Name": "EmailAddress",
	"FieldType": "EmailAddress"
	},
	{
	"Name": "Sent",
	"FieldType": "Number",
	"DefaultValue": 0
	},
	{
	"Name": "UniqueOpens",
	"FieldType": "Number",
	"DefaultValue": 0
	},
	{
	"Name": "UniqueOpenRate",
	"FieldType": "Decimal",
	"DefaultValue": 0,
	"MaxLength": 18,
	"Scale": 2
	},
	{
	"Name": "UniqueClicks",
	"FieldType": "Number",
	"DefaultValue": 0
	},
	{
	"Name": "UniqueCTR",
	"FieldType": "Decimal",
	"MaxLength": 18,
	"Scale": 2,
	"DefaultValue": 0
	},
	{
	"Name": "AllOpens",
	"FieldType": "Number",
	"DefaultValue": 0
	},
	{
	"Name": "AllOpenRate",
	"FieldType": "Decimal",
	"MaxLength": 18,
	"Scale": 2,
	"DefaultValue": 0
	},
	{
	"Name": "AllClicks",
	"FieldType": "Number",
	"DefaultValue": 0
	},
	{
	"Name": "AllCTR",
	"FieldType": "Decimal",
	"MaxLength": 18,
	"Scale": 2,
	"DefaultValue": 0
	},
	{
	"Name": "Bounces",
	"FieldType": "Number",
	"DefaultValue": 0
	},
	{
	"Name": "Status",
	"FieldType": "Text",
	"MaxLength": 12
	},
	{
	"Name": "DateJoined",
	"FieldType": "Date"
	},
	{
	"Name": "DateUnsubscribed",
	"FieldType": "Date"
	},
	{
	"Name": "SubscriberID",
	"FieldType": "Number",
	"IsPrimaryKey": true,
	"IsRequired" : true
	}
	];
	var perfConfig = {
	"CustomerKey": perfKey,
	"Name": "Performance_Subscriber",
	"CategoryID": FolderID,
	"Fields": perfFields
	};
	var createPerf = api.createItem("DataExtension", perfConfig);
	Write(Stringify(createPerf));
	} catch(error) {
	Write(Stringify(error));
	}
	</script>

view raw Create Performance_Subscriber Data Extension with SSJS hosted with ❤ by GitHub

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:

	SELECT
	e.SubscriberId,
	e.Status,
	e.DateJoined,
	e.DateUnsubscribed,
	e.EmailAddress,
	e.BounceCount AS Bounces,
	COUNT(d.SubscriberID) AS 'Sent'
	FROM _Subscribers e
	LEFT JOIN _Sent d
	ON e.SubscriberID = d.SubscriberID
	GROUP BY e.SubscriberID, e.Status, e.DateJoined, e.DateUnsubscribed, e.EmailAddress, e.BounceCount
	HAVING COUNT(d.SubscriberID) > 0

view raw Updates Performance_Subscriber w _Subscriber and Sent Count hosted with ❤ by GitHub

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:

	SELECT
	e.SubscriberID,
	g.AllClicks,
	r.AllOpens,
	a.UniqueOpens,
	y.UniqueClicks
	FROM _Subscribers e
	JOIN (
	SELECT SubscriberID, COUNT(SubscriberID) AS 'AllClicks' FROM _Click GROUP BY SubscriberID
	) g
	ON e.SubscriberID = g.SubscriberID
	JOIN (
	SELECT SubscriberID, COUNT(SubscriberID) AS 'AllOpens' FROM _Open GROUP BY SubscriberID
	) r
	ON e.SubscriberID = r.SubscriberID
	JOIN (
	SELECT SubscriberID, COUNT(SubscriberID) AS 'UniqueOpens' FROM _Open WHERE IsUnique = 1 GROUP BY SubscriberID
	) a
	ON e.SubscriberID = a.SubscriberID
	JOIN (
	SELECT SubscriberID, COUNT(SubscriberID) AS 'UniqueClicks' FROM _Click WHERE IsUnique = 1 GROUP BY SubscriberID
	) y
	ON e.SubscriberID = y.SubscriberID

view raw Updates Performance_Subscriber w Open & Click Counts hosted with ❤ by GitHub

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:

	SELECT
	SubscriberID,
	CAST(UniqueOpens AS DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'UniqueOpenRate',
	CAST(UniqueClicks AS DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'UniqueCTR',
	CAST(AllOpens AS DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'AllOpenRate',
	CAST(AllClicks AS DECIMAL(18,2)) / CAST(Sent AS DECIMAL(18,2)) * 100 AS 'AllCTR'
	FROM Performance_Subscriber
	WHERE Sent > 0

view raw Calculates CTR and Open Rate for Performance_Subscriber hosted with ❤ by GitHub

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?