0

I'm trying to query the _click table to fill a new table with the email of the users who have clicked a link in the email and on the date clicked. What would my query look like? Also trying to figure out what columns are needed for the target Data Extension. Any ideas?

I was using something like to find all clicks to see if it works, but im getting 0 results.

SELECT Email 
from ( 
 SELECT 
   SubscriberKey AS Email,
   MAX(EventDate) AS MaxDate
 FROM _Click
 WHERE AccountID = xxxx
 GROUP BY SubscriberKey
) AS Foo
1

I figured it out by using this instead:

select 
s.EmailAddress 
from _Subscribers s 
inner join _click c on (
       s.subscriberid = c.subscriberid 
)
where JobID = xxxxxx

replace xxxx with the JOBID

1

The above query doesnt actually give you date, just the email address and it assumes you used email address and subscriber key.

The correct way to get the person, email and date of click is as below. Replace Linkname with the actual Alias of the link, and replace jobid with your job id. In your DE you will need SubscriberKey, EmailAddress, and EventDate as fields.

This is the correct query:

select 
emailaddress
, subscriberkey
, eventdate

from _click with (nolock)
where linkname = ""
and jobid = ""

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.