Consider the simple scenario below:

We have Yoga, Zumba, Bootcamp classes every week on different days/times, more classes can be added on future dates.

Someone pre-books 2 Yoga Classes that will start at 5pm Eastern time next Tuesday and next Thursday.

We would like to send a push notification (and/or email) 1 hour before each class begins.

We thought to achieve this by a Journey Builder with a 'Date Based Event', but the granularity for such are days, not hours. (Jan 2021).

How would you go about it?

Someone suggested Interaction Studio, but I don't think it'll solve this scenario.

What are our options? Remember, a contact can book multiple events (classed) for the same day.



  • How does your data structure look like. How do you know which person has which course? It would be helpful if you can provide this information. Commented Jan 13, 2021 at 15:15
  • Are your courses all starting at :00, or at :30 and :00 respectively, or is it more varied? Commented Jan 13, 2021 at 15:45
  • @JonasLamberty, as with any real-time class you'd attend - we can assume classes can begin at 15-min or 10-min intervals :00, :10 :15, :20, :30, :40, :45, :50.
    – LimeWeb
    Commented Jan 13, 2021 at 16:26
  • @JohannesSchapdick Great question! Data comes from the Mobile App, where We have a "Booking table". My idea was to require a sync of a Joined table of at least Bookings with ContactKey. So let's assume we have one big table on which each line is a booking for a class, and already has the ContactKey on that line.
    – LimeWeb
    Commented Jan 13, 2021 at 16:29

4 Answers 4


You have essentially a "countdown" or "countup" style usecase. By that I mean a process that sends a series of emails in relation to a point in time.

Since there will be either a lot, or even worse, more and more courses over time: I suggest an approach that automatically scales and is easy to maintain.

You can reuse this pattern for any usecase of this sort. It's quite a bit to digest for a stackexchange post, but since this solution applies to literally tons of usecases, I thought I'd go through it completely. Hope you can take something from it.

You need:

  • a highly dynamic message that can be reused for all your courses.

  • Let's assume your start times are at full hours only, then you need ONE SQL automation that runs hourly.

Disclaimer: I understand that one of your challenges is the fast rhythm, and sadly automation studio just has an hourly limit. The whole approach outlined in this post does not do anything to speed things up, for that you'd need another system that sends API calls for this.

If you are stuck with automation studio, the only thing you can do is copy more more, staggered automations and journeys to go in faster rhythms (one set at 00, one at :15, one at :30, one at :45). This is absolutely terrible and I would not advise to do it. After all, we want a scalable. low maintenance process, 4 copies of the same thing are the opposite.

Be advised that automation runs are technically billable (just not enforced at this time). A corporate edition SFMC license means you have around 8 hourly automations per year. This usecase would consume 50% of this. So, be very considerate if it REALLY needs to be this fast. No need to read on if what's in this Disclaimer kills it for you.

End Disclaimer.

  • Based on the resulting data from the hourly query, you want to inject into ONE journey. This journey sends the dynamic message.

So to reiterate: it's the same automation, the same journey and the same message for everything. All the "dynamic" stuff will be in data extensions and the message.

From here, this is first of all a data modelling exercise.

Data Extensions

  1. Think of your courses just as "numbers", or IDs.

Make a list of your courses:

courseDefinition data extension

id, name, starttime
1, course1, whenever it starts (date)
2, course2, whenever it starts (date)
  1. Make a list of course members

courseMembers data extension


these are two members of course 1 with subscriberkeys 123 and 456.


one course, one reminder message. So let's just number those, too ("messageNo"). Let's also classify them ("classification: courseReminder") because why not. Shows that this can be used for different things. And each messageNo leads to certain displaying certain dynamic elements, like a different headline, a different course time. So let's just write them next to their messageNo. We use a key / value format, because it makes sense, for the dynamic approach further below:


messageNo     classification            elementKey  elementValue
1                 courseReminder         headline    course1 is in one hour!
1                 courseReminder         time        21:00    
2                 courseReminder         headline    course2 starts soon!
2                 courseReminder         time        15:00      
/* ... more stuff as numbered, key / value pairs  */

You could make huge lists of content per course, surely. It's just more lines.

reading help, first line:

We define that message "1", which is a "courseReminder", displays a "headline" which reads "course1 is in one hour!"

Then: We define that message "1", which is a "courseReminder", displays a "time" which reads "21:00"

  1. Who gets what message? Your sendout selection will be a list of persons, with their relevant message. this will be your target group (sendout DE) and it will be overwritten hourly by the SQL automation, see below.
SubscriberKey    email                 messageNo
123            [email protected]        1
456            [email protected]        2

This list basically says:

in the next sendout, person "123" will get message "1", person "456" will get message "2". All courses and all people are mixed here, which is exactly the point. You will be able to inject them all into the same journey, and they will the "same" email, just that this email will look at each person's messageNo to decide how it displays.

SQL / Automation

Now you need an SQL query that identifies your target group every hour, and writes the messageNo into the DE next to their subscriberkey.

Something like

id AS messageNo 
FROM courseMembers cm
INNER JOIN courseDefinition cd
on cm.id = cd.id
WHERE datediff(hh,cd.starttime,Now() <= 1)
/* OVERWRITE into hourlySelection */

= find all course members whose course is due in one hour or less. This sql is not tested, but it's pretty close.

in the next step, the journey injects the result of the query into the Journey.


Build the most boring journey ever. The journey has a schedule based on the automation above, and just one event: "send email." Journey settings: Reenter anytime.

It just sends the email that we will build next.

Prelim result you have a DE with recipients for the next run. We have a query that selects those people, and then injects into a journey, which does a sendout. everything up until here was extremely basic.

now the email is where the key piece for this to work lies. It is a bit more obscure but also super generic and extremely useful.

email message

Your message should be built in a way that every time it runs, it checks for each user: Which course is he in? Then gets all course specific information for that course from the "elements" data extension.

So, how does the email do this?

The cool thing about this is, it's completely generic after the first three lines. You create AMPscript variables on the fly (dynamic variable creation).

more on this, here https://gortonington.com/dynamically-creating-variables-in-ampscript/

Put the following into the email, probably at the top.

%%[ /* dynamic content assembly */

/* define the person, the data extensions for people and content */
SET @subscriberkey = _subscriberkey 
SET @recipientsDE = "hourlySelection"
SET @elementsDE = "elementsByMessageNo"

/* find the Message number for the current subscriber */
SET @dynamicMessageNo = Lookup(@recipientsDE,"messageNo","subscriberKey",@subscriberkey)

/* get the elements that are associated to each ID */
    SET @elementsAsRowset = LookupRows(@elementsDE,"contentNo",@dynamicMessageNo)
    SET @numberOfElements = rowcount(@elementsAsRowset)
    IF (@numberOfElements == 0 ) THEN
        SET @debugmessageElements = concat("no elements to display for this message")
      /*SET @debugmessageElements = @numberOfElements*/
      FOR @j = 1 TO @numberOfElements DO
          SET @row = row(@elementsAsRowset,@j)
          SET @elementName = field(@row,"elementName")
          SET @elementValue = field(@row,"elementValue")
          IF not empty(@elementName) and not empty(@elementValue) THEN
            SET @elementValue = Replace(@elementValue,"'","&#39;")
            /* create variables for each elementname */
            ]%%</span>%%=TreatAsContent(Concat("%","%[SET @",@elementName," = '",@elementValue,"']%","%"))=%%<span style="display:none">%%[
      NEXT @j 


You can also test this in isolation on a cloudpage without worrying about sending. you just need to replace _subscriberKey with a valid susbcriber key from your "hourlySelection" DE (in my case, SET @subscriberkey = "123" could work)

After the "treatAsContent" line of code, it has created a few ampscript variables for the current user's messageNo.

In plain English, this code does this:

"find the current recipient's messageNo, then find all elements associated to that messageNo in the elements DE. For each line in elementsByMessageNo, use the value from "elementKey" as the name of an Ampscript variable. Assign that variable the value you find next to it, in "elementValue".

So essentially, for message1, the following AMPSCript code is created in the background - i.e. this is not physically present in your code, but you can use the result of it :

SET @headline = "course1 is in one hour!"
SET @time = "21:00"

This means: Even if you don't understand what the code did, below the snippet above, you can now just use the values of the column "elementkey" in the email as an AMPScript placeholder.

Just like so:

%%=v(@headline)=%% and %%=v(@time)=%%

And you just do that, and your email fills up with content that is depending on the messageNo, which is dependent on the recipient. Meaning that: your one email covers all courses that are entered in the elementsDE. Every additional course is just a few lines in the elementsDE, you never add any more automations or journeys.

wrap up

So now you have:

  • one automation
  • one journey
  • and one dynamic message
  • some Data extensions, incl. one where you can just add courses or "elements" that you need for each course, which can grow with more courses being added.

Once it runs, you never need to touch anything but the elementsDE.

== sidenote: You need to understand that you will also only have ONE jobId and statistic for this (all course reminders are treated as one email), which maybe is great, maybe not. That can be solved with a sendlog, but this goes too far here.

  • I believe you can easily run into edge cases with this one. Furthermore its hard to maintain when a particular course is cancelled due to illnesses or whatever. You would then need to change basically everything and you cannot pause the journey because other courses that are not effected by illnesses would still need the information. Therefore your data structure needs to solve that with the query and you must be aware of every case you want to briefly adress. In dynamic emails this is quite hard. When you have an automation for each course it is kind of simple, you could change mails fast. Commented Jan 14, 2021 at 12:29
  • 1
    I see where you are coming from. but it's actually pretty easy. There are two ways to quickly cancel particular sendouts. You remove either the rows for the cancelled course from the elementsDE, or the members from the selection, or use an autosuppression configuration in combination with a sender profile for REALLY fast cancellations of sendouts. I really prefer maintenance that is "do THIS ONE thing" vs. "cancel the journey, then the automation, then make a new version, then turn both back on". Commented Jan 14, 2021 at 13:31
  • Yeah, you are right but most of the content creators will not be able to understand these dataextensions and are afraid of touching something (or havent the rights to do) they dont understand. Therefore the solution that i outlined is more about... how can give some sort of automation but hand it towards the content creators or persons in charge. Keep in mind that this is something you must be aware of. When you make the greatest solution but nobody understands it, the people will most likely are afraid to touch or do not try to understand it ;) Thats when they do not come with more ideas ;) Commented Jan 14, 2021 at 13:45
  • 2
    We have the same mindset, we just interpret differently what's easy and hard to explain to "normal users". My opinion is, I'd rather explain a user how to enter a Data Extension record than to explain them automations, journeys and emails and how they are connected. I guess we have both made our experiences :) Commented Jan 14, 2021 at 14:10
  • @JonasLamberty, thank you so much. Amazing tuto on how to use Ampscript with dynamic variables! Though, unless I am missing something - I believe the scheduling is not right. The SQL example - scheduled once an hour isn't sufficient - "find all course members whose course is due in one hour or less" - means that if the automations runs at 5pm and the class starts at 5:30pm, the recipient will get the notification "your class starts in 1 hour" at around 5:04pm, which is wrong. Or what am I missing? The challenge is the timely messaging triggering, and to me it looks like MC is not equipped
    – LimeWeb
    Commented Jan 20, 2021 at 1:06

I would generally go for an automation inside the automation studio and schedule this automation, which pulls the subscribers inside a dataextension via sql activity or activities.

There must be multiple queries for these because you need one automation for each course (appointment) basicly. You would use conditions to only filter the contacts you want to be inside the journey for one particular course at a time, and also have one journey for each course.

Then you have X automations. And X Journeys.

The Journey would be marked with reentry any time and would have a schedule that is based on one of these X automations. The trigger of the journey is the automation. And the dataextension is the one that gets filled by this particular automation.

Assumption of your DataExtension "CourseSubscribers"

| Subscriberkey | CourseID |
|             1 | MO1700   |
|             2 | SU0900   |
|             3 | MO1700   |
|             1 | MO1800   |
|             2 | WE1930   |

Assumption for Automation course / appointment MO1700, automation name MO1700, Schedule Weekly 16:00.

Select subscriber from CourseSubscribers where courseID = 'MO1700'

Target DataExtension DEX_MO1700, result of the query:

| Subscriberkey |
|             1 |
|             3 |

Journey trigger, automation based on automation MO1700. Inside the journey only one email activity.

Now you have created the task for one particular appointment, but to be honest that is bullshit when a trainer is a subscriber of that list and has 7 Monday courses, then he receives 7 mails/sms whatever. I would solve this with an sms that would be send on the morning and would include each course of that subscriber per day. But that is a different use case like you described, therefore i won't go in more details about that one.

I have not read Jonas answer but i believe it is more dynamic than this one ;)

Just one sidecomment: It is a really bad idea to name courseIDs the way i did it. You will mostly get duplicates if you have courses at the same time. This is just to make it easier to read ;)

  • And then, Automations can run only once an hour best.. what would the query say?
    – LimeWeb
    Commented Jan 13, 2021 at 15:31
  • if we go the Automation-Studio way, I think the challenge is not finding who needs to get injected to the journey, but actually holding them not to receive the message too early/too late. I have modified the question to clarify that more classes can be added. To my knowledge Marketing Cloud is not equipped to be the trigger for such dynamic messaging;
    – LimeWeb
    Commented Jan 13, 2021 at 16:35
  • Check out Jonas and my updated answers ;) Commented Jan 14, 2021 at 12:18
  • voted this +1 as this seems like the only answer that will trigger the notification for the class on time. On the other hand, as Jonas mentioned, it is not scalable to have an automation per class - i.e. X automations and X journeys. Which leads me to the validation of what I suspected - Marketing Cloud in 2021, can't trigger these kinds of reminders. Another system (like Salesforce CRM) or other, will have to trigger the sends.
    – LimeWeb
    Commented Jan 20, 2021 at 1:24

Reading through all the answers, it seems to me like the correct answer is: Marketing Cloud alone (in early 2021) can't do it in a scalable way for this granularity. (Daily would have been possible).

Instead, we'll have to use another system to trigger the messages in real-time (e.g. via API)

The reasons for this statement:

  • Using an automation in automation studio - is limited to a 1 hour frequency.
  • We are limited to ~8 one hour automations within an account, so this kind of use case will consume them all on the 8th class.

Assumption: trigger needed from within Marketing Cloud Data extensions

Automation including SQL activities querying eligible records and wait activities https://help.salesforce.com/s/articleView?id=sf.mc_as_add_a_wait_activity.htm&type=5

SQL activity to select subscriber from 'Appointments' Table include records where ApptDateTime is +/- x minutes from current Time

ApptDateTime > DateAdd(minute,-55, getdate()) and < DateAdd(minute,-65, getdate())

exclude records already sent

AND ReminderSent != True (see 'ReminderSent Tracking' below)

Insert records into data extension linked to journey

Appointments Table

Subscriber ClassName ApptDateTime
101 Yoga Friday July 4, 2020 10:00 AM
101 Zoomba Friday July 4, 2020 11:00 AM

ReminderSent Tracking to track sends and/or as additional means to ensure records already sent reminder can easily be excluded from the SQL you can use ReminderSent boolean field updated to 'True' using Journey Builder updateContactActivity following the messaging activity.

Subscriber ClassName ApptDateTime ReminderSent
101 Yoga Friday July 4, 2020 10:00 AM TRUE
101 Zoomba Friday July 4, 2020 11:00 AM FALSE

There may be more options if Marketing Cloud to Salesforce connector is integrated.

  • hmmm... @JenDietrich, not sure I am able to follow - how do we schedule the wait to be 0 min. or 10 min. based on a field with the above example?
    – LimeWeb
    Commented Jan 20, 2021 at 0:44

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .