1

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'
3
  • 2
    Without knowing exactly what you're wanting to see in your results, are you getting duplication because you're joining from Lead_Salesforce to _Subscribers on EmailAddress (rather than SubscriberKey) and you have leads that share the same email address? Do you have Multi-Entry Journeys you're reporting on where the same lead has indeed been sent the same email more than once and you really need to join from _Sent to _Click using JobID-ListID-BatchID-SubscriberID?
    – Macca
    Apr 24, 2019 at 3:55
  • You can join to _job on jobid and get the email ID. You can then group your report by email ID Apr 24, 2019 at 13:39
  • Thank you both. I changed the links from SubscriberKey to SubscriberId and my data is closer. I also joined _job and am seeing the individual jobids but each of those have two of the same records for some reason. I'm closer but trying to find the missing link in the SQL that is giving me duplicates of every click. Apr 24, 2019 at 23:46

0

You must log in to answer this question.

Browse other questions tagged .