Accessing the Tracking Data

Question

Does anyone know how can I access / export the data that is in the Tracking section in Email Studio?

I'm not interested in sending myself an Email Report. I'd like to get that data into a Data Extension, then would to query that data with given criteria (in a Cloud Page, with AMPscript or SSJS).

So far, I've checked all of the Data View tables, _Sent, _Job etc. I thought that the field SalesForceTotalSubscriberCount in _Job was the one I'm looking for, however, the query returned 0 for the email campaigns we've sent out.

I thought about writing a SQL query, something like

Count(SubscriberKey) as num, JobID From _Sent

But I'm not very proficient in SOQL. And I'm assuming that this data is stored somewhere since it's visible in the Tracking section.

edit: In the end, I'd like to have the Date/Time Sent, Emails Sent, Clickthrough Rate, Open Rate and Deliverability Rate of all the email campaign that have been sent out in the past 6 months stored in a Data Extension.

second edit: I've also tried creating a Data Extension as it was described here https://help.salesforce.com/articleView?id=mc_es_create_email_sendlogging_de.htm&type=5, however I'm not sure if that is what I need, and the Data Extension creation keeps failing with this error: Error: Error updating filter

Any help is appreciated.

Answer 1

EDIT BASED ON YOUR COMMENTS

You were very close with your example to the basics. To get the count of subscribers sent, open and clicks sent per job, I would do the following SQL:

SELECT s.JobId, ed.SentDate, s.Sends, o.Opens, c.Clicks 
FROM (
    SELECT JobId, Count(*) as Sends
    FROM _Sent
        GROUP BY JobId
) s
LEFT JOIN (
    SELECT JobId, Count(*) as Opens
    FROM _Open 
    GROUP BY JobId
) o
ON o.JobId = s.JobId
LEFT JOIN (
    SELECT JobId, Count(*) as Clicks
    FROM _Click
    GROUP BY JobId
) c
ON c.JobId = s.JobId
LEFT JOIN (
    SELECT JobId, MIN(EventDate) as SentDate
    FROM _Sent
    GROUP BY JobId
) ed
ON ed.JobId = s.JobId

This will give a list of each job (campaign) sent as well as the count of the subscribers inside each job (num).

You can also put in a 'WHERE' conditional to limit the results to specific job ids or dates, etc. if you want.

---

Depending on the complexity of what you want, you can use the data views (_Sent, _Bounce, _Open, etc) to find simple info on opens, clicks, etc. to a fairly granular level.

e.g. SELECT SubscriberKey, EventDate FROM _Open WHERE JobID = XXXXXXX
which will give you the subscriberKey and day it was opened for all those that opened on a specific email send.

If you need something a bit more 'raw' and verbose, I would take a look at tracking extracts in SFMC. These will export to your FTP, due to the large size of the files - usually as a zip file, so it may take a bit of work and time to import these into data extensions, but it is possible.

For the tracking extract, I would recommend, for efficiency sake, having a 3rd party analytics tool or sql server grab it off the FTP and then 'play' with it there.