I've created a data views report for capturing clicks across my subscribers and I'm having issues with returning subscribers multiple times for different emails but with the same links and click event date times. I was hoping to just capture the individual links one time but think my SQL might be getting my data mixed. How could I achieve unique subscribers per emails and links across all their activity in my journeys?
SELECT
s.SubscriberID,
s.SubscriberKey,
su.EmailAddress,
ja.ActivityName,
de.Market__c as Market,
de.Unqualified_Property_Lookup__c as UnqualifiedProperty,
de.Itinerary_Start_Date__c as ItineraryStartDate,
de.Partner_Name__c as PropertyManagerName,
U.Name as OwnerName,
j.JourneyName,
c.LinkContent,
c.LinkName,
c.URL,
c.EventDate as ClickEventDate,
c.IsUnique
FROM
[Lead_Salesforce] de
JOIN [_Subscribers] su
on de.Email = su.EmailAddress
JOIN [_Sent] s
on su.SubscriberKey = s.SubscriberKey
JOIN [_JourneyActivity] ja
on s.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
JOIN [_journey] j
on ja.VersionId = j.VersionID
JOIN [_click] c
on s.SubscriberKey = c.SubscriberKey
JOIN
User_Salesforce U
ON de.OwnerId=U.Id
WHERE
c.IsUnique = 'True'
