Is there any way to pull sent email data based on template keywords within Marketing Cloud?

Question

Has anyone found a way in which to extract email send data from Marketing Cloud and sort prior to extracting based on keywords in the title of the email template sent? My organization has built out a specific naming structure to make it easier to identify which department the email is coming from (Date - 2-3 letter department shortcode - email subject) and my team is trying to find a way in which we can pull bulk email data such as open rates, click rates, etc. broken down by department.

Right now the best option I can find, to my knowledge, is executing ExactTarget email extracts by available metrics (attributes, bounce, click impressions, etc.) and then compiling all of this information, then trying to sort through email titles (if these reports even provide that information). We'd prefer to be able to select what we deem to be important metrics over a specific time frame for all emails that include the specific shortcode.

Has anyone had a similar issue or know of any potential workarounds? Any advice would be greatly appreciated. Thanks!

Answer 1

There is Discover Reporting where you can filter on Email Name and use a contains operand. You could also leverage the Data Views, and join on _job dataview, then you could leverage an where clause on emailname field.

You can use the query in this post as an starting example: Query Activity to Return Number of Opens and/or Number of Clicks

Answer 2

If it’s the email name that you would like to use for sorting results, you could run the following SQL query, either manually or automate it, to get tracking data from Data Views:

SELECT 
s.SubscriberKey, 
j.EmailName, 
s.EventDate as SentDate, 
o.EventDate as OpenDate, 
c.EventDate as ClickDate, 
b.EventDate as BounceDate, 
b.BounceCategory, 
u.EventDate as UnsubscribeDate
FROM _Sent s LEFT JOIN _Job as 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
WHERE j.EmailName LIKE '%EXL%'

In above query, you can replace EXL in the last line with any of the three letter department short-codes you have in your emails.

The easiest way to get results, would be to install Query Studio, paste the above in the query editor, update the email name and run it. If you are happy with the results, you can save both the query and the Data Extension directly from Query Studio.

If you’d be interested in automating the task of pulling results into a Data Extension, read here how to automate it in Automation Studio.