Glen Mazza's Weblog

Building on my previous article on extracting email clicks data from Marketing Cloud, I'd like next show how to obtain email sends and opens data with Automation Studio, covering both together as they are quite similar. List sends are covered here. For brevity I won't detail how to create Data Extensions (DE's) nor the Data Extract and File Transfer activities as they would be the same as covered in the previous article. The primary focus instead will be the SQL Query Activities for this data.

The implementation below requires a five step automation, with the first three steps consisting of SQL Query Activities and last two being the usual Data Extract and File Transfer activities (one each for opens and for sends). This data primarily relies on the Sends and Opens data views, with some joins to ListSubscribers in order to bring in email addresses and list names.

For the first step, we have five SQL Activities to make, each of which will need to be placed in their own data extensions. I list the names of the data extension used for each query as they will be used in subsequent steps, but you can name them as you wish. The first two queries have the main sends and opens data we care about, with a DATEDIFF to get just the previous day's records, while the others serve as lookup tables. Note the Account ID refers to your business unit's MID, if you have more than one BU you may wish to filter on the ones you wish to receive data from.

Goes into a DE called SENT_YESTERDAY:

SELECT SubscriberKey, EventDate, ListID, JobID, BatchID, AccountID
FROM _Sent
WHERE DATEDIFF(day, EventDate, GetDate()) = 1

Goes into a DE called OPENED_YESTERDAY:

SELECT SubscriberKey, EventDate, ListID, JobID, BatchID, AccountID
FROM _Open
WHERE DATEDIFF(day, EventDate, GetDate()) = 1

Goes into a DE called DISTINCT_SUBSCRIBERS:

SELECT DISTINCT SubscriberKey, EmailAddress
FROM _ListSubscribers

Goes into a DE called LIST_ID_REF:

SELECT DISTINCT ListID, ListName
FROM _ListSubscribers

Goes into a DE called YESTERDAY_JOBS:

SELECT JobID, EmailID, AccountID, FromName
FROM _Job
WHERE DATEDIFF(day, DeliveredTime, GetDate()) <= 15

For the Jobs view above we go back 15 days in case it takes two weeks for someone to open an email.

For the 2nd Step in the Automation we join the SENT_YESTERDAY and DISTINCT_SUBSCRIBERS DE's, and the SENT_YESTERDAY and YESTERDAY_JOBS DE's, in order to bring in the sender and sendee's email addresses. The same thing is done in this step with the OPENS_YESTERDAY DE, omitting below as it is otherwise identical with the Sends join:

Goes into a DE called SENT_WITH_SUBSCRIBER_EMAIL:

SELECT sub.EmailAddress 
  , 'Sent' AS EventType
  , sent.SubscriberKey
  , sent.EventDate
  , sent.ListID
  , sent.JobID
  , sent.BatchID
  , jobs.FromName
  , jobs.EmailID
  , sent.AccountID
FROM SENT_YESTERDAY sent WITH (NOLOCK)
LEFT OUTER JOIN DISTINCT_SUBSCRIBERS sub ON sent.SubscriberKey = sub.SubscriberKey
LEFT OUTER JOIN YESTERDAY_JOBS jobs ON sent.JobID = jobs.JobID

For the 3rd Step, we query the List Name lookup table we created in the first step to bring in the list names for each ID. Again just providing the Sends here, as the Opens one is analogous.

SELECT sent.AccountID 
  , sent.JobID 
  , sent.BatchID
  , sent.SubscriberKey
  , sent.EmailAddress 
  , sent.ListID
  , REPLACE(lists.ListName, ',', '') AS ListName
  , FORMAT(sent.EventDate, 'M/dd/yyyy hh:mm:ss tt') AS EventDate
  , 'Sent' AS EventType
  , REPLACE(sent.FromName, ',', '') AS FromName
  , sent.EmailID
FROM SENT_WITH_SUBSCRIBER_EMAIL sent WITH (NOLOCK)
LEFT JOIN LIST_ID_REF lists ON sent.ListID = lists.ListID

As these files are going into a CSV, the query above strips away commas that might be present in the input to simplify processing downstream. (That said, MC's data extract activity probably can properly escape commas anyway.) This automation can be finished by adding the fourth and fifth steps for the Data Extract and File Transfer activities.