Glen Mazza's Weblog

https://glenmazza.net/blog/date/20221120 Sunday November 20, 2022

Obtaining Marketing Cloud Sends and Opens Data (List Sends)

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.

Posted by Glen Mazza in Salesforce at 03:08PM Nov 20, 2022 | Tags:  marketingcloud | Comments[0]

Post a Comment:

Calendar
« April 2023
Sun Mon Tue Wed Thu Fri Sat
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
Today
About Me
Java Software Engineer
TightBlog project maintainer
Arlington, Virginia USA
glen.mazza at pm dot me
GitHub profile for Glen Mazza at Stack Overflow, Q&A for professional and enthusiast programmers
Blog Search


Blog article index
Navigation
About Blog
Blog software: TightBlog 3.7.2
Application Server: Tomcat
Database: MySQL
Hosted on: Linode
SSL Certificate: Let's Encrypt
Installation Instructions