0

I have a welcome journey in which the subscriber at a point chooses which room needs a redesign.

The links for these 3 rooms have tracking aliases like this:
Image_RoPLivingRoom
CTA_RoPLivingRoom
Image_RoPBedroom
CTA_RoPBedroom
Image_RoPDiningRoom
CTA_RoPDiningRoom

I want to pull everyone that clicks those links into a data extension with these fields:
SubscriberKey
EmailAddress
LinkName

I want the field LinkName to be populated with one of the 6 tracking aliases above. This is the query I have created for now:

SELECT MasterSubscribers.SubscriberKey, EmailAddress, _Click.LinkName
FROM MasterSubscribers
JOIN _Click
ON MasterSubscribers.SubscriberKey = _Click.SubscriberKey
WHERE MasterSubscribers.SubscriberKey IN (SELECT SubscriberKey FROM _Click WHERE LinkName IN ('Image_RoPLivingRoom','CTA_RoPLivingRoom','Image_RoPBedroom','CTA_RoPBedroom','Image_RoPDiningRoom','CTA_RoPDiningRoom'))

The DataExtension is populated, but not only with the tracking aliases as the ones above. ie. tracking aliases like CTA_Instagram, CTA_VelvetFelt and CustomerService are only in the Data Extension.

Can you please guide me to how alter the query to have only subscribers with clicks on those tracking aliases populated to the data extension?

Thank you

1

If your target DE have SubscriberKey as primary key, you should certainly apply a partition to the result to avoid a Primary Key violation error.

SELECT LinkName,
       SubscriberKey
FROM
  (SELECT LinkName,
          c.SubscriberKey,
          eventdate,
          ROW_NUMBER() OVER (PARTITION BY c.SubscriberKey
                             ORDER BY eventdate DESC) AS ROW
   FROM _Click c
   INNER JOIN YOUR_ENTRY_DE COLL ON COLL.SubscriberKey= c.SubscriberKey
   WHERE 
     LinkName ='Image_RoPLivingRoom'
     OR LinkName ='CTA_RoPLivingRoom'
     OR LinkName ='Image_RoPBedroom'
     OR LinkName ='CTA_RoPBedroom'
     OR LinkName ='Image_RoPDiningRoom'
     OR LinkName ='CTA_RoPDiningRoom'
AND TriggeredSendCustomerKey = '1058' ) AS s
WHERE ROW=1

You need also to change the TriggeredSendCustomerKey.

=====

Update

I understand that you have multiple versions of your journey. Not using the TriggeredSendCustomerKey on your query will get you all clicks on your alias links, even if it's outside of your welcome journey sends.

There is a way to get tracking of an email on all your journey's versions leveraging TriggererSendDefinitionObjectID. It is a unique identifier of your email on all versions of your journey.

More info here: Data View: Journey Activity

| improve this answer | |
  • Hi Rachid. Thank you, I have 2 additional questions to your solution. 1) My Data Extension indeed has SubscriberKey as primary key, what errors am I avoiding using the approach you have? 2) What is the TriggeredSendCustomerKey and why is it necessary in this scenario? Every click with that tracking alias should be available in the Click data view. Looking forward to your answers, I'm still learning – FelixSanchez Dec 6 '19 at 12:38
  • 1) There might be several clicks on the same link by the same person, doing a Partition by subscriber key will get you the last click on that link and avoid getting a violation of your primary key. 2) The TriggeredSendCustomerKey is the ID of your email send, you can fin it on Email Studio > Tracking > Sends > Journey Builder Sends. It is not necessary in this scenario, but you can use it to get only clicks tied to the specific email you are sending through your welcome journey. – Rachid Mamai Dec 6 '19 at 12:43
  • Great, the Partition part makes perfect sense. As my welcome journey is in version 15 now I'll skip the TriggeredSendCustomerKey part to get as much data as possible. When I replace YOUR_ENTRY_DE with my entry DE I get this error, that I'm unsure about: An error occurred while checking the query syntax. Errors: An unmatched parenthesis occurs in the query. ...Would you be so kind to update the AMPScript provided to not have this validation error? – FelixSanchez Dec 6 '19 at 12:53
  • Done, it should work correctly now :) – Rachid Mamai Dec 6 '19 at 12:54

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.