Query to pull users who clicked on different jobids in one data extension

Question

I am no SQL expert (really, not at all), so I'm having trouble figuring out the SQL to pull users who have clicked on different campaigns into one data extension in Exact Target. I have about 6 job ids that I need to look at. I don't want users who have clicked on ALL but I want users who have clicked on any of those job ids and pull them into one data extension. Any ideas?

Answer 1

Welcome to SFSE.

Something like this should do the trick:

select
de.emailaddress
, j.jobname as emailName
, c.eventDate as clickDate
, s.status as SubscriberStatus
from DataExtensionName as de
left join _click as c on (c.subscriberkey = de.emailaddress /*and c.isunique = 1*/) 
left join _subscribers as s (s.subscriberid = c.subscriberid)
left join _job as j on (j.jobid = c.jobid)
where c.jobid in (123,345,456)

You can uncomment the isUnique part if you want to exclude multiple clicks on a single link

The activity System Data Views relate by JobID. SubscriberID is an internal number that uniquely identifies a subscriber. It's used to relate the activity data back to the _Subscribers data view. The SubscriberKey is the identifier that SFMC uses to keep track of a subscriber's status. It is also mapped as the sendable field in your Data Extension.

One word of caution: Joining multiple system data views together is not very scalable if you have a subscriber-base of more than a few hundred thousand. If you do, I'd suggest splitting the queries by activity to stay under the 30 minuted Query Activity timeout threshold.

If you query has any kind of runtime error, you'll have to contact SFMC Support to determine the source of the error. If you're planning writing more than a few queries, replicating your SFMC schema outside the platform is a good idea. I'd recommend using a local installation Microsoft SQL Server or SQLFiddle (using MS SQL Server 2008).

Reference:

Query Activity (System Data Views)