Is it possible to have a DE that houses all click-related information to be used in a journey?

Question

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!

Answer 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.