0

I want to be able to trigger activities and journeys based on Click behaviours, but from what I understand, to do this I would need to create a SQL query, a Data Extension, automation, and a DE relationship to a journey if I wanted to use it for decision splits and exit criteria.

I want to find a quicker and more scalable solution so that I could reference one Data Extension for all clicks, but I don't know if this is possible (or wise).

The structure: - DE with columns: Contact ID, Email Name, Link Clicked, Date/Time of Click - SQL that populates all Lead/Contact click activity from all emails into this DE - 4x Automations that runs every 15mins of the hour

I'm still very much a newbie with SQL.

Can anyone help me with what the SQL would look like for this scenario? If the scenario isn't possible, could anyone share a simple SQL query that I could use to identify clicks from all emails in a journey?

Thanks in advance!

1

You should be able to pull in all clicks for a Journey (going back 6 months) using something like this:

select
c.subscriberKey as 'ContactID',
ja.ActivityName as 'EmailName',
c.EventDate as 'ClickDate',
c.LinkName as 'LinkName'
from [_click] as c
join [_JourneyActivity] as ja 
on c.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID
join [_Journey] as j
on ja.VersionID = j.VersionID
where ja.ActivityType in ('EMAIL','EMAILV2')
and j.JourneyName = 'Whatever you journey is named'

If you don't need to capture the entire click history of the Journey going back 6M, then I'd recommend you add a date qualifier as an additional "AND" statement in your query to shrink the date window you are pulling clicks from. This will ensure your query runs more quickly and that the resultant data set is easier to traverse.

|improve this answer|||||
  • Thanku Jason. I tried on an existing journey. The SQL validated, but in my case, the DE didn't populate when I know there have been clicks. Any idea where I'm going wrong? This is the SQL: select c.subscriberKey as 'ContactID', ja.ActivityName as 'EmailName', c.EventDate as 'ClickDate', c.LinkName as 'LinkName' from [_click] as c join [_JourneyActivity] as ja on c.TriggererSendDefinitionObjectID = ja.JourneyActivityObjectID join [_Journey] as j on ja.VersionID = j.VersionID where ja.ActivityType in ('19_0_draft_reminder_1_de') and j.JourneyName = '19_0_draft_reminder_journey' – Greg Beazley May 23 '19 at 15:38
  • 1
    You shouldn't be setting the ActivityType within the IN statement to '19_0_draft_reminder_1_de'. That is not an activity type within a journey so you need to specify the activity type (which I've listed in the query above). Should be 'EMAIL','EMAILV2'. – Jason Hanshaw May 23 '19 at 15:44
  • Aha! My bad, I thought it was referencing the Email Name in the journey. The SQL ran successfully - thank you so much for your help! Knowing how to do this now has opened a lot of doors for me in MC :) – Greg Beazley May 23 '19 at 16:02
  • Glad that was helpful! Best of luck in your MC journey. – Jason Hanshaw May 23 '19 at 16:12

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.