-3

I am trying to fetch Open Date, Close Date , Email Address , Bounce Category , Email Name of all subcribers from query activity but the data that I am getting wrong data.Please see the query and let me know that my approch is right or not.

Query:

SELECT

ea.[first name] as 'First Name',

ea.[last name] as 'Last Name',

s.[EmailAddress] as 'Email',

s.SubscriberKey,

s.SubscriberID,

o.[EventDate] as 'Open Date',

c.[EventDate] as 'Click Date',

j.[EmailName] as 'Email Name',

b.[BounceCategory] as 'Bounce Category',

o.[IsUnique] as 'Unique Opens'

FROM

_subscribers as s with (nolock)

INNER JOIN _EnterpriseAttribute ea with (nolock) ON (s.subscriberID = ea._subscriberID)

INNER JOIN _Open as o with (nolock) ON (s.subscriberID = o.subscriberID)

INNER JOIN _Click as c with (nolock) ON (s.subscriberID = c.subscriberID)

INNER JOIN _Bounce as b with (nolock) ON (s.subscriberID = b.subscriberID)

INNER JOIN _Job as j with (nolock) ON (j.JobID = o.JobID)

WHERE

((o.EventDate >= dateadd(month, datediff(month, 0, getdate())-1, 0)

 AND o.EventDate <  dateadd(month, datediff(month, 0, getdate()), 0))

OR

(c.EventDate >= dateadd(month, datediff(month, 0, getdate())-1, 0)

  AND c.EventDate <  dateadd(month, datediff(month, 0, getdate()), 0)))
  • 1
    Would generally love to see the purpose of what you are trying to achieve. Fetching Email name + all subscribers doesn't make a lot of sense, as they are data at two different granularity levels - so if you include your goal for the query it will make it a lot easier to provide specific answers :) – Kenneth Wagner May 9 '17 at 11:01
1

The query in question would work - you are joining too many rows of data with potential matches. Below is a revised query that will return one row of data, based on subscribers - not based on a particular job, although you could amend the code to look for that:

SELECT
ea.[first name] as 'First Name',
ea.[last name] as 'Last Name',
s.[EmailAddress] as 'Email',
s.SubscriberKey,
s.SubscriberID,
(SELECT MAX(EventDate) FROM _Open o WHERE s.SubscriberID = o.SubscriberID) AS 'Open Date',
(SELECT MAX(EventDate) FROM _Click c WHERE s.SubscriberID = c.SubscriberID) AS 'Click Date'

FROM
_subscribers s 
LEFT JOIN _EnterpriseAttribute ea ON s.subscriberID = ea._subscriberID

If your goal is to pull data out based on email, the starting point must be different - but please elaborate on your end goal if the above doesn't meet your need.

Also - there is a risk of the above failing, as there may not be any value in _Open or _Clicks, which is why i would typically split it up in multiple sql's.

  • How we can compare the subscriber Id with open and click (SELECT MAX(EventDate) FROM _Open WHERE s.SubscriberID = o.SubscriberID) AS 'Open Date', (SELECT MAX(EventDate) FROM _Click WHERE s.SubscriberID = o.SubscriberID) AS 'Click Date', – Vasudha Tandon May 9 '17 at 8:50
  • sorry forgot to add the correct letters in front - amended the query to have the right ones now. - and it's not a comparison but a link / join. You are limiting your results by saying you only want the "open records" that matches the subscriber id - and then we select the max value from the returned results. – Kenneth Wagner May 9 '17 at 8:52
  • I am getting error: An error occurred while checking the query syntax. Errors: Old style JOIN (ANSI JOINs) syntax is not allowed. Please use standard syntax. – Vasudha Tandon May 9 '17 at 9:34
  • there was a comma at the last select line that shouldn't have been there. I just validated the query (without enterprise attribute) in my own environment. Does it work for you? – Kenneth Wagner May 9 '17 at 9:42
  • I removed it that comma while implementing the query but still I am getting the error. I want all the fields data of last month that are present in my query. I am getting bounce categories with open and close date which is impossible , if the email is bounce then how we can open or click it. – Vasudha Tandon May 9 '17 at 11:49
1

Based on your latest comment, about creating one data extension with all the data i would split it up into four queries. In order to ensure that you get ALL of the data, you would need to create a data extension with the following fields marked as primary key:

  • SubscriberKey
  • JobId
  • EventDate

Create the other necessary fields you reference, "SentDate", "OpenDate", "ClickDate" and "BounceCategory" - the tricky one might be EmailName, but, I'll give my suggestion below in the queries:

Query 1: _Sent data

SELECT s.SubscriberKey, s.JobId, s.EventDate AS 'Sent Date', j.EmailName AS 'Email Name' , sub.EmailAddress AS 'Email', ea.[first name] as 'First Name', ea.[last name] as 'Last Name' 
FROM _Sent s 
LEFT JOIN _Job j ON s.JobId = j.JobId 
LEFT JOIN _Subscribers sub ON s.SubscriberKey = sub.SubscriberKey 
LEFT JOIN _EnterpriseAttribute ea ON (s.subscriberID = ea._subscriberID) 
WHERE s.EventDate > DateAdd(day,-30, GetDate())

If you wish for the operation to replace existing data, so the data extension only contains the last 30 days of data - then you must set this to overwrite

Query 2: _Open data

SELECT SubscriberKey, JobId, EventDate AS OpenDate
FROM _Open
WHERE EventDate > DateAdd(day,-30, GetDate())

This must be an update operation.

Query 3: _Click data

SELECT SubscriberKey, JobId, EventDate AS ClickDate
FROM _Click
WHERE EventDate > DateAdd(day,-30, GetDate())

This must also be an update operation.

Query 4: _Bounce data

SELECT b.SubscriberKey, b.JobId, b.EventDate AS BounceDate, b.BounceCategory, j.EmailName, sub.EmailAddress AS 'Email', ea.[first name] as 'First Name', ea.[last name] as 'Last Name' 
FROM _Bounce b
LEFT JOIN _Job j ON b.JobId = j.JobId
LEFT JOIN _Subscriber sub ON b.SubscriberKey = sub.SubscriberKey
LEFT JOIN _EnterpriseAttribute ea on b.SubscriberId = ea.SubscriberId
WHERE b.EventDate > DateAdd(day,-30, GetDate())

This must also be an update operation.

The data extension configuration based on my queries:

  • SubscriberKey (primary key, string, length 100)
  • JobId (primary key, numeric)
  • EventDate (date)
  • EmailName (string, length 100, nullable)
  • Email
  • First Name
  • Last Name
  • OpenDate (date, nullable)
  • ClickDate (date, nullable)
  • BounceDate (date, nullable)
  • BounceCategory (String, length 50, nullable)

Automation
The queries must be run using an automation in automation studio, in 4 steps, in the order indicated above.

  • Thanks for your described solution. I will try it and let you know but if I also want to fetch First Name , Last Name and email address then in a same way, should I do write another 2 queries on _EnterpriseAttribute and _subscribers? – Vasudha Tandon May 10 '17 at 11:49
  • One more thing you said Event date mark as primary key, so it is sent Event Date or all 3 Event Dates i.e. sent , open and click. – Vasudha Tandon May 10 '17 at 12:12
  • Hi kenneth, Your solution is working fine. Thanks a lot. If possible please let me know how to fetch first name , last name , email adddress .Should I write 2 more queries on _EnterpriseAttribute and _subscribers in a same way and what are the sequence ? – Vasudha Tandon May 10 '17 at 14:42
  • Hi Kenneth, Please see and let me know that to fetch first Name, Last Name and email address I updated Sent data query. SELECT s.SubscriberKey, s.JobId, s.EventDate AS 'Sent Date', j.EmailName AS 'Email Name' , sub.EmailAddress AS 'Email', ea.[first name] as 'First Name', ea.[last name] as 'Last Name' FROM _Sent s LEFT JOIN _Job j ON s.JobId = j.JobId LEFT JOIN _Subscribers sub ON s.SubscriberKey = sub.SubscriberKey LEFT JOIN _EnterpriseAttribute ea with (nolock) ON (s.subscriberID = ea._subscriberID) WHERE s.EventDate > DateAdd(day,-30, GetDate()) – Vasudha Tandon May 10 '17 at 20:08
  • Will look at this tomorrow – Kenneth Wagner May 10 '17 at 20:13

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.