0

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())
Improve this question
2
  • Apologies. I didn't fully read your question. You'd need SQL for this. If you have a fairly busy account, your challenge will be tuning your query(s) to work reliably. The simplistic pattern looks like what you have there and you add a bunch of the Data Views with outer joins on [jobid,listid,batchid,subscriberid]. You'll find a query like that with many joins to big tables like opens and clicks will likely timeout. You'd actually stage subsets of the big tables in Data Extensions and run your complex thing on these smaller subsets of tracking data. Sorry. Simple question and complex answer.
    – Macca
    Aug 9, 2017 at 14:16
  • Understood thanks very much for your comments they are very helpful. For the moment we are doing a very small scale project but interesting to know that we may run into difficulty further down the line
    – user3456401
    Aug 9, 2017 at 14:21

1 Answer 1

Reset to default
0

You need to use Open and Click data views and count the clicks/opens grouped by JobID. Instead of joining for this you need to use sub-queries, that use the count-function. These sub-queries are queries inside your query.

The subquery could look like this and would be used as a "column" of your query:

(SELECT
    count(SubscriberKey)
FROM
    _open o
WHERE
    o.JobID = s.JobId
GROUP BY JobID) as OpenCount

This is basic SQL however and not specific to Salesforce Marketing Cloud. So SQL specific questions could be asked on StackOverflow. Marketing Cloud SQL is based on Microsoft SQL Server 2005, so anything possible there should also work.

Documentation articles:

Improve this answer
3
  • Hi Markus, I tried to do this and joined both the _Open and _Click Data Views but I cannot seem to find the right syntax to sum or count the opens/clicks as suggested
    – user3456401
    Aug 9, 2017 at 14:09
  • added more info to my answer
    – Markus Dang - formerly Slabina
    Aug 9, 2017 at 14:16
  • Hi Markus, Thanks very much this seems like exactly what we need. I will test it out now and close the question should I get the expected results. Thanks a million
    – user3456401
    Aug 9, 2017 at 14:22

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.