I am trying to pull data about a sent email into a data extension using a SQL query in the Query Activity section.
The below is my query which pulls back all information about an email using a combination of the Sent and Job objects.
However I want to pull back further information in the form of Total Clicks for this email and Total Opens for this email. The information is all available in the "My Tracking" section of the email studio I just cant seem to get the correct query.
select j.JobID, a.Id as AccountId, s.subscriberKey, s.EventDate as SentDateTime, j.EmailID, j.emailName, j.EmailSubject, s.EventDate as ModifyDateTime, 'ID_' + a.Id + '_' + convert(nvarchar(50), j.JobID) as RecordId, s.TriggererSendDefinitionObjectID as SendID from _sent s join _job j on j.JobID = s.JobId join Account_Salesforce a on a.PersonContactId = s.subscriberKey where s.EventDate > dateadd(d, -50, getdate())