0

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

2
  • You need not use the sent data view, if your requirement is just to see who has clicked on the link. Also, I can see from your query you are trying to pull data more than 6 months. DV only fetches data for last 6 months. You also don't need to cast eventdate as it is already a date field. I would suggest just do a simple query on the click data view and you can utilize the eventdate field in that DV. Hope this makes sense. – Swati Mishra Aug 18 '20 at 19:52
  • Are you wanting one row per SubscriberKey or one row per SubscriberKey,JobID,BatchID,LinkContent,LinkName,ClickDate? – Macca Aug 19 '20 at 1:30
0

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] 

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.