Filtering consumers based on click activity in email

Question

I am trying to segment all subscribers who have clicked Link A(LinkName) which is present in emails that have been sent in multiple sends.

For example in the last week I may have done multiple email sends(which are triggered through multiple jobids). I want all those subscribers to be added to a DE who have clicked Link A(LinkName) across these jobids.

I know that we can use the _click data view to query this data. I set up a query activity for this purpose and selected a target data extension. I tried running the query but the target DE is showing as 0 records. The query is this:

select distinct 
s.SubscriberKey, 
s.JobID, 
s. BatchID, 
c.LinkContent, 
c.LinkName,
convert(char(19),c.EventDate,20) as ClickDate
from [_sent] s
inner join [_click] c
on s.JobID = c.JobID and s.SubscriberID = c.SubscriberID and
c.IsUnique = 1

 

Where cast(s.EventDate As Date) > cast('2020-01-01' as Date) and c.LinkName =[the name of the link in the email]

Could someone assist me. Is this query correct?

Regards, Navin

Answer 1

I'll try to sum up. It is correct that because you are looking for a timespan longer than 6 months, it is not relevant to have a JOIN at all, you can simply select from _Sent, which only holds data for this timespan.

In query one, I'll leave in the JOIN on _sent data view a) to illustrate how to join Data views correctly, and b) because it would be useful in case the timespan were under 6 months.

/* all unique clicks (= max one per subscriber) 
from sendouts of the last 6 months
on a link with Alias [aliasOfLink]  */

SELECT
s.SubscriberKey, 
s.JobID, 
s.BatchID, 
c.LinkContent, 
c.LinkName,
convert(char(19),c.EventDate,20) as ClickDate 
FROM [_sent] s

INNER JOIN [_click] c

ON
s.JobID = c.JobID 
and s.SubscriberID = c.SubscriberID 
and s.BatchID = c.BatchID
and s.ListID = c.ListID
/* the join on these 4 elements makes sure you get consistent results when joining Data views in all scenarios. */

WHERE
/* cast(s.EventDate As Date) > cast('2020-01-01' as Date)  
 this line can be omitted in your case. only sendouts of the last 6 months are in _sent. data views hold 6 months data */

c.IsUnique = 1 
/* this will deduplicate for unique click events -> no need for DISTINCT */

AND
c.LinkName =[aliasOfLink] 
/* this contains the link ALIAS, make sure that this matches what you refer to
as the "name of the link". Check URL or linkContent respectively. */

https://help.salesforce.com/articleView?id=mc_as_data_view_click.htm&type=5

With all that said, in your case, without need for a JOIN, it could simply be query two:

/* all unique clicks (= max one per subscriber) 
from sendouts of the last 6 months on a link 
with Alias [aliasOfLink]  */

SELECT
c.SubscriberKey, 
c.JobID, 
c.BatchID, 
c.LinkContent, 
c.LinkName,
convert(char(19),c.EventDate,20) as ClickDate 
FROM [_click] c

WHERE
c.IsUnique = 1 
AND
c.LinkName =[aliasOfLink]