1

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.

  • I am not 100% sure, but i believe SOQL cannot be used inside Marketing Cloud. According to developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/… marketing cloud cannot be accessed through SOQL. You do mean SQL. Can you tell us EXACTLY what data you are looking for? Then we can help with SQL Queries that will do the trick. – Johannes Schapdick Aug 9 '18 at 13:26
  • 1
    I'd like to have the sent numbers, click through rate, open rate, date/time sent and deliverability rates of all email campaign (the data visible in the tracking section) for the past 6 months, stored in a data extension. Would you be able to help with that? I'm also editing my question with exactly what I need. – firko Aug 9 '18 at 13:52
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.

  • 1
    I have looked into the Data Views, and the only way to get the total sent numbers of a campaign looked like the SQL I wrote in my question. If the data exists in any other way, and I failed to find the data, could you please let me know? As for the Tracking Extract solution you mentioned; that seems like above my knowledge since I don't know that first thing about connecting to a SQL server grab (yet), but I'll take a look into that. In the meantime, I've editted the question and added a comment to reply Johannes on exactly what I want in the end. – firko Aug 9 '18 at 13:58
  • 1
    Thanks for the reply @Gortonington, but the Sent number is only partial to what I need, and I don't know how can I apply the same logic within one query. I can create different SQLs targeting different Data Extensions, one for Open Rates, one for Clicks and so on and so forth. However when I try to join _Click and/or _Open to that, it doesn't work (we receive syntax errors, I've asked the help of a colleague who is proficient with SQL, but not in SFMC). Would you be able to write the whole query that gives me the result I'm asking in my first edit, if that is an ok thing to ask here? Thanks! – firko Aug 9 '18 at 14:16
  • 1
    I get the following error when I checked the syntax: Incorrect syntax near ')' I've also tried changing sJobId on line 3 to s.JobID as I think that was a typo :) but I still received the same error. – firko Aug 9 '18 at 14:26
  • I've ran the query, but after 19 minutes, it failed. Please note that I'm working at a big company that probably have sent 110 million or so emails so far. Therefore I'm thinking that the query failed because it was timed timed out, but I could be wrong. What I'm thinking is, when one clicks on Tracking, SFMC shows the numbers, sents, opens etc very quickly, so it isn't running a query on the background to get those numbers. So I'm quite sure that SFMC stores that data somewhere other than the Data Views. What should be my next move, what do you suggest? – firko Aug 10 '18 at 10:29
  • I think you should pull the 'Account Send Summary' report and have it land on the import folder of your FTP and then import it into a Data Extension. This will give you everything you need all without queries and can all be automated. – Gortonington Aug 10 '18 at 12:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.