0

I have a data extension called "MasterSubscribers" It contains a bunch of fields which holds the subscribers's information. I also have a field named "Subscriber Key" which is the primary key.

Now I would like to create a query to pull in all the tracking information from an email deployment (Clicks, Views, Unsubscribe). My email name is called "20161014_Brand_NL4"

I'm a beginner to queries so I need help doing something like this:

Select * from MasterSubscribers and pull in people who received the email "20161014_Brand_NL4". Aside from including every field from MasterSubscribers, also pull in the clicks, views, and unsubscribe as 3 new columns.

Sample Results would look something like this

0

Is there something specifically in reports that wouldn't get you what you needed? Do you really need per-user engagement data and have a plan for it? This is not easy to do and definitely not recommended for your first foray into queries. Nevertheless, if it must be done, these are the extensions you'd need in addition to your MasterSubscribers:

_Sent

_Click

_Open

_Unsubscribe

_Job

You'd need to JOIN these data extensions together on common values.

How many emails did you send with this content? If it's just one you'll need to find the JOB ID - it's the key that will relate to your other tables that you're looking to join. If it's not, you'll need to add another step: you'll have to pull all jobs that correlate to a specific email id. PS: your file name is not the email id. With that you'll have to build a new data extension that contains a list of all the JOB IDs that correlate to your email ID.

Then, you'll need to start writing queries that join this data together where each row matches to a correlating Job ID in your lookup data extension.

If you've never written a JOIN before, think of it like a venn diagram: https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

I didn't write out the SQL for you because I don't currently have any reports that do exactly what you're looking to do (and it takes time which is what you'll have to invest).

  • Hi Matt, thank you for your response. The reason why I wanted to do this is on a normal email report, i can get the following: subscribers email, clicks, views, unsubscribe, bounce) Those are great but I really want to dive in and filter using additional fields in the data extension such as gender or city for example. A filter for example can be - Selecting all Male from New York who has clicked on the email. – Allen Oct 14 '16 at 19:08
  • I sent the same email a couple of times so there are multiple jobIDs so thats why I wanted to see if i can select by email name. – Allen Oct 14 '16 at 19:09

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.