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?

1 Answer 1


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
    Commented Aug 6, 2015 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. Commented Aug 6, 2015 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. Commented Aug 6, 2015 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
    Commented Aug 6, 2015 at 15:50

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .