I would like to create a query that would join on the data extension that was used in the job.

Business Requirements:

  • Create a new data extension with the following fields: Email, Marketing_ID
  • Send Email using above data extension.
  • Create a report that will run daily. Which would find all open emails in the last 24 hours. It will also join on the data extension used for sending the Job to get get the Marketing_ID. (note: because the report will run daily, it has to be completely automated).

Is this possible?


Sure, but you'll need to hard-code your sending Data Extension in your report query.

You'll need the System Data Views enabled by SFMC Support, then you can write a Query Activity to pull it all together into a report Data Extension.

An Automation could execute the Query Activity, do a Data Extract and a File Transfer -- if you want the file dropped out on your FTP share.

A Send Log might be helpful -- as a source for your report.

If you'll take a stab at the query (and update your question), I'd be glad to review it.

  • Thanks Adam. I need a solution without hard-coding the sending data extension since the report will be ran daily. – bradley4 Aug 6 '15 at 13:55
  • As far as I know, it's not possible to dynamically retrieve the sending data extension used for a send. I've tried. – Adam Spriggs Aug 6 '15 at 15:12
  • Your report could be based off of the _Sent data view, tied back to the _Job if you know the name of the email. – Adam Spriggs Aug 6 '15 at 15:14
  • Thanks for the conformation Adam. My work around is to use a List instead of a Data Extension and embed the Marketing_ID inside the name of the list. I can then join on _ListSubscribers using the SubscriberID and ListID to get the ListName. I guess this would have to do. – bradley4 Aug 6 '15 at 15:50

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.