0

I'm trying to query the _Click data view and return a SubscriberKey for every user that clicked a link in a particular email. I've try several different ways to get at this and none are returning any results. My query validates and runs.

I've tried:

SELECT DISTINCT 
a.SubscriberKey
FROM _Click a
Where 
a.JobID = 12345
AND
a.URL = 'https://www.mylink.com?UTM-STUFF'

I tried to write this to a data extension with just one field called SubscriberKey (text, 250 chars) but it didn't return any results.

I've tried:

SELECT 
a.SubscriberKey,
a.JobID
FROM _Click a
INNER JOIN _Job b
ON a.JobID = b.JobID
WHERE 
a.JobID = 59881
AND a.URL = 'https://www.mylink.com?UTM-STUFF'

And many other iterations. But nothing returns any results.

Is there something obvious I might be missing?

  • Have you verified that the url actually exists in the click DV for that job? Perhaps there are no results because it does not exist – Gortonington Feb 15 '19 at 0:04
  • This question might help you to understand and cover this even though it is a more complicated approach because it wants to receives clicks on multiple jobs. I believe URL might be your wrong decision, maybe LinkContent is right here. salesforce.stackexchange.com/questions/246264/… – Johannes Schapdick Feb 15 '19 at 8:56
  • Thanks to both of you. It turns out I was using the wrong job ID (the job ID for the content, rather than the send). Fixing that corrected the problem. – Peachy Feb 15 '19 at 14:42
1

I tried this code and I am getting values in my DE. I am getting the Subscriber ID as well as the Job ID.

SELECT Distinct
a.SubscriberKey as SubscriberKey,
a.JobID as JobID
FROM _Click a 
Where 
a.JobID = 3681xxx
AND
a.URL = 'https://www.xxxxxxxxxxxx.com'

Although I am a bit confused with your question, you said "...and none are returning any results" and also "This wrote to a data extension with just one text called SubscriberKey (text, 250 chars)" in the next line. So are you getting any results in your DE or you are just getting Subscriber Key column populated in your DE and you want the other data too(such as Job ID, Batch ID, etc.)? Also, please check your URL once(as already suggested by Gortonington.)

| improve this answer | |
  • Thanks for this - it was an issue with the Job ID. I was using the job ID for the email itself, not the send. – Peachy Feb 15 '19 at 14:42
0

Answering my own question here because I made a mistake. There are two job ID's. One that's found in the content folder (the actual email) and one for the send itself in the tracking section. I was using the job ID for the content rather than the send.

So, the query above actually works as expected when the correct job ID is used.

| improve this answer | |

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.