Journey Builder - journey settings

Question

Based on who clicks a certain element in a mail, I'd like to add these subscribers to a journey. So, I have the following in place:

This query populates a data extension hourly (on the full hour)

SELECT c.SubscriberKey, m.EmailAddress, m.Country, m.Language, m.Gender, m.FirstName, m.LastName, m.Store, m.StoreId, c.LinkName
FROM _Click c
JOIN MasterSubscribers m on c.SubscriberKey = m.SubscriberKey
WHERE c.LinkName = 'WeLove_BornholmTable' AND m.Consent_status = 'Confirmed' AND c.EventDate > dateadd(hh,-24,getdate())

The journey is using that data extension for running hourly (on the 30th minute of the hour) The journey consists of both sends and waiting steps meaning that the journey will last 19 days (the last 12 days are pure waiting) which is the amount of time I find apropriate for having the same content sent again.

So, 1) The query is overwriting the data extension 2) The journey has Contact entry - re-entry only after exiting 3) The journey has Contact evaluation - evaluate new records only

Would you suggest seetings differently for 1), 2) or 3) and why? Setting up a journey like this is quite new to me and any advice would really, really come in handy. Thank you!

Answer 1

I would approach this more or less as you describe, with one little adjustment. My recommendation would be to put data retention policy on your entry data extension, with length equaling your "quarantine period" - e.g. 19 days:

It will require a simple addition to your query, ensuring the contact you are about to inject into journey don't exist in the data extension. It is covered by the two last lines below:

SELECT 
c.SubscriberKey, 
m.EmailAddress, 
m.Country, 
m.Language, 
m.Gender, 
m.FirstName, 
m.LastName, 
m.Store, 
m.StoreId, 
c.LinkName
FROM _Click c
JOIN 
MasterSubscribers m 
on c.SubscriberKey = m.SubscriberKey
WHERE 
c.LinkName = 'WeLove_BornholmTable' 
AND m.Consent_status = 'Confirmed' 
AND c.EventDate > dateadd(hh,-24,getdate())
AND c.SubscriberKey not in 
(select j.subscriberkey from journeyentry j)

As records remain in data extension for 19 days after initially being added to the journey, this query will ensure that a contact will first be inserted after 19 days. Managing your re-entry settings in the journey itself does not make much difference - as this setup ensures that a contact can enter more than once, and cannot re-enter before exiting (+ whatever is set in your data retention settings).

When it comes to evaluating all records vs. evaluating new records only: Each record in this data extension has an internal numerical ID, which is automatically incremented for each record added (and is not visible in the UI). You should be aware of what is called a high watermark. It is a value which is updated each time a journey, being based on the data extension above, is running. It designates the ID of the last row processed. Hence when new records are added to the data extension, they get a higher ID, and are evaluated for the journey.

If you update an existing record (e.g. when using Contact Key as primary key) this will not make the record be re-evaluated, as the internal ID is not updated. Hence only completely new records are evaluated. This is why using data retention settings is a clever solution here.

A complete overwrite of the data extension resets the high watermark to 0, which means all the records are evaluated - regardless the setting in journey builder.