I am trying to create a query that will pull the unique clicks on a specific link within in the email and also pull acct_number on a daily basis and then do an extract of the DE. I do not need to have Subscriber Key or Email appended to the file. Since this is a file that will be used for a different purpose the less bulky the better. So far I have been able to build a query that will get all of the link click but not a specific link or acct_number to be included. I have been able to get subscriber key but I still need acct_number. Current query:

    SELECT DISTINCT o.SubscriberKey as Subscriber_Key 
    ,o.JobID as job_id
    ,o.EventDate as click_date
    ,o.IsUnique as is_unique
    FROM _Click o
    JOIN _Job j
    ON o.JobID = j.JobID
    JOIN _Subscribers sub
    ON sub.SubscriberID = o.SubscriberID
    AND CAST(o.EventDate as date) >= CAST(getdate()-1 as date)
    WHERE o.JobID IN (
    AND o.IsUnique = 'false'
  • Can you specify what acct_number is? Is this the Business Unit, SubscriberID, Campaign, something Custom? Is this really a field of salesforce MARKETING CLOUD? Mar 21 '18 at 7:12
  • This field is held in the preferences of all subscribers and is imported along with the rest of the subscribers information. While its not a subscriber key, it is a unique identifier to the user.
    – cottdc
    Mar 21 '18 at 14:31

I don't have comment privileges yet, but I don't know from your question why the IsUnique would be false and where the subscribers sit. So I am making a couple of assumptions here in that there's a subscriber or audience DE, and that we can pull a larger amount of data as a baseline. If so, this query should pull out all of the data relating to people who have clicked on a specified URL in a specified send. If you need to make this distinct per subscriber, it should be possible to tweak the code to make this so.

SELECT a.SubscriberKey as Subscriber_Key 
,c.JobID as job_id
,c.EventDate as click_date
,c.IsUnique as is_unique
FROM _Click c
INNER JOIN Audience a
ON a.SubscriberKey = c.SubscriberKey
WHERE c.JobID = [JobID of Send]
AND c.URL = [Link that you are trying to track]
  • Where are you getting Audience from? I think the trouble that i am having is appending the acct_number that should be stored in both the All Subscribers and the Clicks. I have verified that this attribute lives in All Subscribers and when I look to export the clicks manually it is also provided. I have also tried to bring in the Data Extension where there will be acct_account number present but again the trouble is joining the information with the Clicks/All Subs.
    – cottdc
    Mar 22 '18 at 15:47
  • SELECT DISTINCT(c.SubscriberKey) as Subscriber_Key, c.JobID as job_id, c.EventDate as click_date, c.IsUnique as is_unique, d.acct_number as acct_number FROM _Click o JOIN _Job j on (c.JobID = j.JobID) JOIN ent._Subscribers sub on (sub.SubscriberID = c.SubscriberID AND CAST(o.EventDate as date) >= CAST(getdate()-1 as date)) JOIN UAT_Survey_Seed_List d on (c.acct_number = d.acct_number) WHERE o.JobID IN ('10091444') AND o.IsUnique = 'false' AND o.URL IN ('www.wawarewards.com')
    – cottdc
    Mar 22 '18 at 15:48
  • I got audience from making an assumption that you have an audience or subscriber DE. AFAIK it's not possible to add fields to data views.
    – r0bsta
    Mar 26 '18 at 2:43

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.