Hi Everyone,

                                             Marketing Cloud - Email Studio - Query Activity

Please have a look and let me know whether my approch is correct or not.

Can we create query to fetchfirst name , last name, email address, open , click , bounce email data  by query activity in single data extension?

I have created a query to fetch all the last month data in single Data extension but I am getting bounce data with open and click date which is impossible. how we can open the bounce email.

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)))