2

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.

Thoughts/Solutions?

Thanks!

4
  • 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. Jan 13 '21 at 15:15
  • Are your courses all starting at :00, or at :30 and :00 respectively, or is it more varied? Jan 13 '21 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
    Jan 13 '21 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
    Jan 13 '21 at 16:29
2

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

memberId,subscriberkey,courseId
x,123,1
y,456,1

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

3)

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:

elementsByMessageNo

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            course1member@test.com        1
456            course2member@test.com        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

SELECT 
subscriberkey,
email,
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.

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")
    ELSE
      /*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">%%[
          ENDIF
      NEXT @j 
    ENDIF

]%%</span>    

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.

7
  • 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. Jan 14 '21 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". Jan 14 '21 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 ;) Jan 14 '21 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 :) Jan 14 '21 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
    Jan 20 '21 at 1:06
1

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 ;)

4
  • And then, Automations can run only once an hour best.. what would the query say?
    – LimeWeb
    Jan 13 '21 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
    Jan 13 '21 at 16:35
  • Check out Jonas and my updated answers ;) Jan 14 '21 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
    Jan 20 '21 at 1:24
1

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

Would something like this work for you?

  • Hourly file drop for all appts at hour
  • Data Extension with Minute column for time intervals
  • Minute intervals determine waits

example: at 2pm a file drop contains all appts from 2PM to 2:45pm

ApptTime Minute 2:00 0 minute wait - sends immediately 2:10 10 minute wait 2:15 15 minute wait

allow re-entry and evaluate new records only

1
  • 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
    Jan 20 '21 at 0:44

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.