Is there a better way to handle emails 30 minutes before a specific datetime?

[jlinczak]

OK, since this group has been so helpful, allow me to indulge (context: completely new to Marketo and trying to learn rapidly).  Here's the context of what I'm trying to achieve:

1. We have custom objects of appointments with an appointment datetime field (amongst other attributes) - tied to lead records.  Datetime is always posted in Pacific Time offset.
2. The source system where these appointments are generated don't have webhooks, so we're having to do a daily query of the previous day's appointments scheduled and do a bulk import to custom objects via Marketo API
3. We'd ideally like to do appointment reminder emails 30 minutes prior to the scheduled appointment date time in the custom object (we have other frequencies in days ahead of the appointment, but this is not my concern - batch campaigns should do nicely), realizing that there could still be delays in delivery, but we're not expecting to send out anything more than 30 at one shot, so we're not too concerned.
4. Appointments for now are at the top and bottom of each hour, but would be great to handle any time in the future
5. Leads are limited to the USA, but we will have to contend with timezones.  Assume there's sufficient data on the lead to calculate time zone correctly.
6. There are no same-day appointments (for now...), which makes this a little easier.

The way I was planning to achieve this was one of 2 ways, each with their upsides and downsides:

Option 1

I set up essentially 48 campaigns (!) within a program that is set to use the recipient's timezone, each set to batch daily with one for each 30 minute timeframe.  They all point to the same email template, the campaign is setup with Has <CustomObject> with constraint of the datetime field being In Future 30 minutes.  It sends out the email to the leads, the email template pulls in the objects and formats correctly.

Upsides: I can (I hope?) take advantage of Marketo dealing with timezones correctly.

Downsides: I mean, 48 campaigns?!  What happens when they decide appointments can be made at any time (within reason - say every 5 minute increments)?

Option 2
I basically do all the logic outside of Marketo.  Perhaps do a query on the custom objects in Marketo on a regular basis (AWS service likely), and when it meets the criteria (I will have to get the TZ from Marketo or our source system through API), I will populate a temporary list.  Create a campaign that triggers on record adds in this list, sends the email template, then removes the records.  Rinse and repeat.

Upsides: One campaign, super-simple setup in Marketo
Downsides: I now have to manage everything outside of Marketo in another application doing more API calls to one or multiple systems.  It's possible then I can let this process run on a 5-minute cycle then to cover the situation where they may do appointments at nearly any time.

Yes, I'm glossing over other details around keeping data in sync, etc.

Am I missing something?  I've looked into webhook setup and possibly FlowBoost integration, but that doesn't seem to help this particular situation, unless I'm misunderstanding what these services can provide.  We also need to be HIPAA compliant, so we're weary of adding more services that we don't already have checked off and buttoned up.

Anyone with tons of insight or has built something like this?  I would welcome some insight.

[SanfordWhiteman]

Your overall goal should be to copy their Next Appointment Reminder DateTime into a person field and ideally also the Next Appointment Reminder Appointment ID for easy back-referencing.

Then you can use Advanced Wait steps to wait until that exact time and send an email. You can’t do this as long as the DateTime field is only on the Custom Object record.

---

Am I missing something?  I've looked into webhook setup and possibly FlowBoost integration, but that doesn't seem to help this particular situation, unless I'm misunderstanding what these services can provide.  We also need to be HIPAA compliant, so we're weary of adding more services that we don't already have checked off and buttoned up.

---

FlowBoost could do this. When a new appointment is added (thus a trigger campaign, no need to poll) you call FB, it gets list of that person’s COs, filters, and responds with the appropriate next reminder datetime info.

Of course you could also use a custom webhook-compatible service that does this one task.

[jlinczak]

Ah, I see now.  Given that appointment reminders can overlap, however, wouldn't that cause havoc on the wait times?  I assume wait times are set once at the time of the flow step and stored in some way that it can't be manipulated?  For example, say my schedule looks like this:

Reminders at:
 - Time of appointment creation
 - 3 days ahead of appointment date (if that's even possible, depending on date of appointment compared to now)

- 1 day ahead of appointment
- 30 minutes ahead of appointment

If a lead creates an appointment today (2022-11-16) for 2022-11-20 12:00 pm PT (ID 1), then they would get reminders at:

3 days - 2022-11-17  [let's call this point A]

1 day - 2022-11-19

30 minutes - 2022-11-20 11:30 am PT

Now let's say they create a second appointment tomorrow (2022-11-17) with another person for 2022-11-21 12:00 pm PT (ID 2).  Then they should get appointments at:

3 days - 2022-11-18

1 day - 2022-11-20   [let's call this point B]

30 minutes - 2022-11-21 11:30 am PT

Presumably on trigger for creation of the first appointment object (ID 1), we call the webhook to calculate the next reminder (Point A) and populate that and ID 1 on the person record.  But the next day a new one (ID 2) came in before the first reminder was to be sent, so I go through and send the initial email on trigger, call the webhook to recalculate the next reminder datetime and ID, realize that the next reminder time is earlier than the new one from this appointment and leave it alone on the person record.

When we get to point B in the schedules above, it's going to want to send both the second appointment and the first appointment at the same time.  So, is the only way to cover that is by looping through both in an email script in one email template?  I'm not using the ID on the person record to pull information because the TriggerObject is maintained the whole wait time, or do I need to use it because the TriggerObject is lost during wait?  Or does Marketo, even though it's the same person/lead record, treats them on different "tracks", per se, and therefore the worry of having to update a wait time, etc. is not a problem?

I hope that makes sense.  Thanks again for the response!

[SanfordWhiteman · Accepted solution]

Hmm... bunch of ideas mixed together in this last message but here are 2 realities to take away:

• No, the TriggerObject is not lost if the trigger type is Added to Custom Object, regardless of Wait. But your trigger wouldn’t be using that trigger type, rather it would be using Campaign is Requested or perhaps Has Custom Activity. You can provide additional context with each of those API calls so they know which Custom Object record is to be referenced.
• Yes, the Wait Until is transformed to a static future datetime when the Wait Step is entered. So you would not necessarily have a problem with multiple appointment times stored in the same field. But you could still use 4 different fields for clarity’s sake.
  

[jlinczak]

Thanks again @SanfordWhiteman!  To recap a few things just to add the documentation here for the discussion since there were a few pieces I wasn't aware of that helped me to figure out how all of this can come together:

1. Calling webhooks in a flow, for all intents and purposes, is nearly asynchronous.  While the flow *does* wait 30 seconds for a response from the service, the reality is it's likely better to just make sure the service called by the webhook does the logic of posting the, say, next reminder date/time and appointment ID to the lead record and then calling another campaign to run for this lead to store the wait time, rather than thinking it's going to return a value and keep running the same flow.
2. Because wait steps can't be determined based on a custom object, this is the reason for wanting to post it in, say, a lead record there.  Once the wait step is run, behind-the-scenes, it's storing a static value for the wait and is not affected by having to change, say, the next reminder date/time, on the same lead.
3. Flows themselves can be run for a lead multiple times without issue.  So if I setup a campaign to hold the reminder waits and then that lead creates *another* appointment, they can absolutely roll through the campaign again, and Marketo is not going to try to somehow overwrite, negate or combine information from the lead sitting in the existing wait step.  This makes the situation of setting up a lot of appointments not that big of a deal.
4. That being said, there could be a race condition to look out for - what happens if the service posting the custom objects for appointments happens irregularly (like once a day?) and the person created two appointments during that day?  It's possible there could be a race condition there that may happen given the timing of processing.  It's possible, thinking about it, that this could be negated by adding a few bits of smarts on the process that sends over the custom objects to break up the data import into pieces to avoid this.
5. While TriggerObject holds, the reality is that if you are setting up your webhook service to push data back in and call another campaign, it doesn't matter (what Sanford was referring to in his earlier response).  It would be best, too, to try to program the service for the webhook to do something like (pseudo-code) "FocusedObject = IF NO TriggerObject THEN <use result of query of data using APIs as your FocusedObject ELSE TriggerObject".  This preps you for the future as well to consider if you might have to somehow fix something and run things in batch mode rather than in trigger mode, making life a little easier.

For me, anyway, all of this knowledge helped me to get started on a path to building this.  Perhaps it will help someone else reading this.  I'll do my best to give back and maybe post more questions or some ideas back here as I attempt to build this.