Consolidate tracking data using SQL

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.

NameData TypeLengthNullable
SubscriberKeyText100no
EmailAddressEmail255no
EmailNameText100yes
EmailSubjectText200yes
FromNameText130yes
FromEmailText100yes
SentDateDateyes
OpenDateDateyes
NumberOfOpensNumberyes
ClickDateDateyes
NumberOfClicksNumberyes
NumberOfLinksClickedNumberyes
BouncedDateDateyes
BounceCategoryText50yes
UnsubscribedDateDateyes

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

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

Total number of clicks:

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

Total number of URLs clicked:

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

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

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.

One thought on “Consolidate tracking data using SQL

  1. Harish Janghel

    Hi Zuzanna

    Thanks for the ‘SQL to consolidate tracking data’.

    With this query I get only unique records means first open and first click. How can I get latest opendate and latest clickdate?

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s