0

I want to write a SQL query that will find the total clicks and unique clicks for 1 URL across multiple email sends. Total clicks is easy, but I'm not sure about the unique clicks. I want to basically dedupe all subscribers who have clicked on that URL, even if they received two emails and clicked the link in both of them. Can I use the IsUnique field for that? Does that mean that the click is unique for each email or across all emails?

2

As Johannes stated, IsUnique is job based, so you will not get your desired output as there is still the possibility of duplicates over the different jobs.

To gather unique subscribers that have clicked a certain URL over multiple jobs, there are 3 different approaches.

Approach 1:

Set the primary key of your target Data Extension to SubscriberKey and set the action of your Query to 'Update' (Add/Update). This will add any non-existant records to the DE and update to those that already exist. Basically meaning that only 1 instance of the subscriberkey can exist in this DE.

Approach 2:

If you are only after the subscriberkey, you are able to utilize DISTINCT in SQL. This will only provide results where the fields you show in your SELECT statement are unique. The issue with this is that if you are looking to pull anything beyond Subscriberkey, the DISTINCT call will include them as a 'primary key' and pull unique combinations of those fields.

E.g.

SELECT DISTINCT subscriberkey
FROM _Click
Where URL = '...'

will return only unique entries of Subscriberkey, but will not allow any other fields.

but

SELECT DISTINCT subscriberkey, jobid
FROM _Click
Where URL = '...'

will return any unique combination of subscriberkey and jobid.

For example, table below:

Subscriberkey  | JobID
Bob@bob.bob      124345
Bob@bob.bob      124345
Bob@bob.bob      112134
George@bob.bob   124345

would return:

Subscriberkey  | JobID
Bob@bob.bob      124345
Bob@bob.bob      112134
George@bob.bob   124345

This restriction is why I usually steer away from utilizing DISTINCT.

Approach 3:

This is my recommended approach as it is the most versatile and secure way to accomplish retrieving unique results.

You would utilize the ROW_NUMBER() function in SQL and then use a conditional to only select the first row. This will grab your query results then set a row number to each entry partitioned by subscriberkey inside a subquery. You then have a query on that, which has the conditional to only grab the first row.

For example:

SELECT SubscriberKey, URL, LinkName
FROM (
    SELECT SubscriberKey
           , URL
           , LinkName
           , ROW_NUMBER() OVER (PARTITION BY [SubscriberKey] ORDER BY [SubscriberKey]) AS rn
    FROM _Click
    WHERE URL='...'
) a
WHERE rn = 1
1

IsUnique is always based on JobIDs. If you refer clicks as IsUnique over multiple sends you will get more than one because each first click on a job is marked as IsUnique (even if the link is different only the first will get tracked as IsUnique).

When you write an SQL Query you would normally use functions like distinct (to have a subscriber only once) and a where clause to the link like "where linkcontent = 'YOUR-URL-HERE'

Select distinct SubscriberKey
FROM _Click
where LinkContent='https://www.test.com'

The above has to be used when you want ampscript variables to be executed when you want them inside the link you have to the following:

Select distinct SubscriberKey
FROM _Click
where URL='https://www.test.com??j=fec61576736c0479&m=%%ex2'

Or when you gave your link an unique name you would do this

Select distinct SubscriberKey
FROM _Click
where LinkName='Your-Linkname'

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.