There are many use cases for consolidating and transforming email tracking data in Salesforce Marketing Cloud, and they are usually related to a requirement for sending the tracking data in a given format to external systems, such as databases and data warehouses. We will use Salesforce Marketing Cloud’s Data Views to get all the details of the email job, opens, clicks, bounces and unsubscribes, as well as subscriber details.

Create a target Data Extension

Before we start with the query, let’s first create a target Data Extension to hold the results. The Data Extension should have the following columns and it shouldn’t have any primary keys. It also doesn’t need to be sendable or testable.

Name	Data Type	Length	Nullable
SubscriberKey	Text	100	no
EmailAddress	Email	255	no
EmailName	Text	100	yes
EmailSubject	Text	200	yes
FromName	Text	130	yes
FromEmail	Text	100	yes
SentDate	Date		yes
OpenDate	Date		yes
NumberOfOpens	Number		yes
ClickDate	Date		yes
NumberOfClicks	Number		yes
NumberOfLinksClicked	Number		yes
BouncedDate	Date		yes
BounceCategory	Text	50	yes
UnsubscribedDate	Date		yes

Joining data from Data Views

When working with data from Data Views in SQL, it’s important to correctly join the data from the different tables. Even though the diagram below shows that common columns that can be used for joining data are SubscriberID and JobID, you should in fact join on more than those two columns to get correct results from your queries.

This is especially important for Journey Builder sends, where every email that is sent out individually will have a different BatchID value assigned, so in case the same person enters the same journey multiple times and gets the same email more than once, you could potentially end up with incorrect tracking data if you only join on SubscriberID and JobID.

The most reliable way to join tracking data from Data Views is to actually use all the common column names that are present in the _Sent, _Open, _Click, _Bounce and _Unsubscribe tables, just like in the example below, where we are joining data on JobID, ListID, BatchID and SubscriberID:

	select
	s.subscriberkey
	from _sent s
	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

view raw joining-data-views.sql hosted with ❤ by GitHub

Getting the total number of opens and clicks

In our query, we will add a couple of subqueries which will allow us to get the total number of opens, clicks and URLs clicked per subscriber per email send. Below are the details of the three subqueries.

Total number of opens:

	select
	jobid,
	ListID,
	BatchID,
	subscriberid,
	count(subscriberid) AS Opens
	from _open
	group by subscriberid, jobid, ListID, BatchID

view raw total-number-opens.sql hosted with ❤ by GitHub

Total number of clicks:

	select
	jobid,
	ListID,
	BatchID,
	subscriberid,
	count(subscriberid) AS Clicks
	from _click
	group by subscriberid, jobid, ListID, BatchID

view raw total-number-clicks.sql hosted with ❤ by GitHub

Total number of URLs clicked:

	select distinct
	jobid,
	ListID,
	BatchID,
	subscriberid,
	count(distinct url) AS URLs
	from _click
	group by subscriberid, jobid, ListID, BatchID

view raw total-number-urls.sql hosted with ❤ by GitHub

SQL to consolidate tracking data

For our final query, we will combine the data from all Data Views mentioned above with the three subqueries and we will add one more additional join with the _ListSubscribers Data View to be able to display the email addresses of subscribers. If you’re running this query in a Child BU, make sure to use ent._ListSubscribers instead of _ListSubscribers.

	SELECT
	s.SubscriberKey,
	ls.EmailAddress,
	j.EmailName,
	j.EmailSubject,
	j.FromName,
	j.FromEmail,
	s.EventDate AS SentDate,
	o.EventDate AS OpenDate,
	op.Opens AS NumberOfOpens,
	c.EventDate AS ClickDate,
	cl.Clicks AS NumberOfClicks,
	url.URLs AS NumberOfLinksClicked,
	b.EventDate AS BouncedDate,
	b.BounceCategory,
	u.EventDate AS UnsubscribedDate
	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
	LEFT JOIN _Bounce b ON s.JobID = b.JobID
	AND s.ListID = b.ListID
	AND s.BatchID = b.BatchID
	AND s.SubscriberID = b.SubscriberID
	AND b.IsUnique = 1
	LEFT JOIN _Unsubscribe u ON s.JobID = u.JobID
	AND s.ListID = u.ListID
	AND s.BatchID = u.BatchID
	AND s.SubscriberID = u.SubscriberID
	AND u.IsUnique = 1
	LEFT JOIN (select
	jobid,
	ListID,
	BatchID,
	subscriberid,
	count(subscriberid) AS Clicks
	from _click
	group by subscriberid, jobid, ListID, BatchID) cl ON s.JobID = cl.JobID
	AND s.ListID = cl.ListID
	AND s.BatchID = cl.BatchID
	AND s.SubscriberID = cl.SubscriberID
	LEFT JOIN (select distinct
	jobid,
	ListID,
	BatchID,
	subscriberid,
	count(distinct url) AS URLs
	from _click
	group by subscriberid, jobid, ListID, BatchID) url ON s.JobID = url.JobID
	AND s.ListID = url.ListID
	AND s.BatchID = url.BatchID
	AND s.SubscriberID = url.SubscriberID
	LEFT JOIN (select
	jobid,
	ListID,
	BatchID,
	subscriberid,
	count(subscriberid) AS Opens
	from _open
	group by subscriberid, jobid, ListID, BatchID) op ON s.JobID = op.JobID
	AND s.ListID = op.ListID
	AND s.BatchID = op.BatchID
	AND s.SubscriberID = op.SubscriberID
	LEFT JOIN _ListSubscribers ls ON s.SubscriberID = ls.SubscriberID

view raw sql-tracking-query.sql hosted with ❤ by GitHub

You can now automate this query in Automation Studio and create a recurring export of the results and send them to the FTP for consumption by external systems.

---

Questions? Comments?

Leave a comment below or email me at zuzanna@sfmarketing.cloud.

---