SQL: Email Opens Based on JobID

Using Query activities within Automation studio can open up lots of audience possibilities and can often save the sanity/sleep of your email admin.

One of my most used queries is pulling a list of everyone who opened an email based on JobID. You can use this audience to send to or use the audience as a group to exclude from an email. For instance, if you opened the original email, then I may not want you to get the same reminder email as those who never saw the first one.

There are manual ways to pull this audience, as you can see them on every jobid’s tracking page. But building it manually may mean that someone has to login at to compile this group BEFORE the next email can be sent. And as someone who lives in California, who often needs to send things in Eastern time… that means getting up at the crack of dawn. And that’s a little too early for my blood.

So instead, I use Query Activities within Automation studio. I can set these activities up ahead of time and then run the automation right before my email is set to deploy. Salesforce will do the work of compiling the list and I can continue getting my beauty rest.

Before you can use query activities, you will need the following:

1. Identify what table(s) you are pulling data from
2. Identify what field(s) you need from those tables
3. Build a Data Extension (DE) to house the data you pull
   • (Step by Step Instructions for building a data extension can be found here)

For this example:

1. Table – [_open] – This is a built-in data table within Salesforce Marketing Cloud. You can go here to see all the available fields in this table
2. Field – “SubscriberKey”
3. JobID – #12345 – You can find the JobID in Email Studio under Tracking > Sends.