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?
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.
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.
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.
SELECT DISTINCT subscriberkey FROM _Click Where URL = '...'
will return only unique entries of Subscriberkey, but will not allow any other fields.
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
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.
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.
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
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'