SFDC_Last_Contact_Time

Marketo Time-Based Logic for Emails, Lead Routing, and More!

Level 8 - Champion Alumni
Level 8 - Champion Alumni

Do you want to send a different message to someone if they email you during business hours or after hours? What about on the weekend? Should leads be routed to a different sales team based on the time of an inquiry? What about segmenting leads based on dayparting or day-of-week? You can do all this with Marketo and a connected CRM like Salesforce.

What You Need

To use time-based logic, you need the following:

  1. Fields to contain date/time stamps and (optional) day of week
  2. Marketo Smart Campaign to populate date/time fields
  3. Salesforce logic to parse date stamp into the day of the week (optional)
  4. Marketo Smart Campaign to route leads based on time and (optional) day of week

Step 1: Create Your Fields

To get the benefit of routing by both the time of day AND day of week, you’ll need to create your fields in your CRM. I’m using Salesforce for this example. You can call these fields whatever you like and can track whatever behavior you like. Let’s assume for this example you are interested in tracking the time of a “Contact Us” form submission.

Create a Field to Hold Your Time Stamp Only

In Salesforce, go to Setup > Customize > Leads > Fields > New and use the type “Text”. Make sure to repeat this on the Contact object and map these fields to each other so that this field can be used for both Salesforce Leads and Contacts in Marketo. Why use “text”? This will make sense later on!

SFDC_Last_Contact_Time

Create a Field to Hold a Date/Time Stamp

In Salesforce, go to Setup > Customize > Leads > Fields > New and use the type “Date/Time”. Make sure to repeat this on the Contact object and map these fields so that this field can be used for both Salesforce Leads and Contacts in Marketo.

SFDC_Last_Contact_Date

Create a Field to Calculate Day of Week

In Salesforce, go to Setup > Customize > Leads > Fields > New and use the type “Formula” with a return type of “Text”. Make sure to repeat this on the Contact object and map these fields so that this field can be used for both Salesforce Leads and Contacts in Marketo. Here’s a screenshot of the formula you need (substitute your actual field name instead of “Last_Contact_Us_Date__c”):

SFDC_Day_Week_Formula

And here’s text you can copy:

CASE( 
MOD(DATEVALUE(Last_Contact_Us_Date__c) - DATE(1900, 1, 7), 7),
0, "Sunday",
1, "Monday",
2, "Tuesday",
3, "Wednesday",
4, "Thursday",
5, "Friday",
6, "Saturday", "Error")

Here’s how this formula works: First you get the date-only value of your date/time field. Then you subtract another date from it with a known day of the week. Then you calculate the modulus (remainder) when dividing that number by seven. The remainder indicates the day of the week of the date value you are trying to parse. You can then use a CASE formula to populate a string with the name of the day of week based on that remainder. It’s a little complicated, but basically you just need to know it works. 🙂

Step 2: Stamp Your Date/Time Fields in Marketo

The next step is to use a triggered Smart Campaign to populate your time and date/time fields based on the lead behavior you want to track. Our goal is to stamp the time and date/time fields, pass the data into Salesforce so our formula field can calculate day of week, and give that data time to sync back to Marketo. To ensure the right order of operations, we’ll be using a simple two-campaign structure here where the first campaign will request the second.

Marketo Date_Routing_Program_Overview

Marketo Campaign #1 - Stamp Your Fields

Smart List

Use a “Fills Out Form” trigger with the name of your form.

Marketo Date_Stamp_Trigger

Flow Steps

  • Use a “Change Data Value” flow step referencing your time field and populating it using the Marketo system token, {{system.time}}.
  • Use a “Change Data Value” flow step referencing your date/time field and populating it using the Marketo system token, {{system.dateTime}}.Note, these tokens will use the timezone set in your account settings.
  • Sync the lead to SFDC
  • Wait a few minutes, to ensure that the formula field is populated
  • Sync the lead to SFDC again, to bring the formula field value back into Marketo (this is usually necessary because formula field changes will not trigger a sync by themselves)
  • Wait a few minutes again to ensure the formula field value is safely in Marketo
  • Finally, request the second campaign. You’ll need to build it first in the next step before you can add this Flow Step.

Marketo Date_Stamp_Flow

You’ll note this method is a bit “expensive” in terms of Salesforce API calls, so use it judiciously.

Step 3: Do Some Time-Based Routing!

Now you’ve got everything you need at your fingertips to route based on time of day and day of week. Our final steps are to build a Smart List to crunch the logic, and then a Smart Campaign to take the action we want.

Build an “After-Hours” Smart List in Marketo

To determine whether a lead has made an inquiry outside of business hours, we’re going to use a Smart List as a local program asset. The Smart List will check whether the time of last contact was before 8 AM or after 5 PM on Monday-Friday, or anytime on Saturday or Sunday. Here’s what that Smart List looks like:

Marketo After_Hours_Smart_List

How does this work? Well, consider that the actual time-stamp looks something like, "11:35 AM (-800 GMT)". So basically you are parsing the time-stamp string to see whether it contains a combination of certain hours (numbers that end with ":") and AM or PM. And that’s why this field must be a text field, because otherwise we couldn’t use the “contains” operator. Using advanced filter logic you can combine these filters together with the day of the week to make your targeting as granular as you like! Make sure to check your filter logic and test, test, test, to be sure you’re getting the intended result.

Build Your Marketo Outbound Smart Campaign

In this example, the campaign is sending an email.

Smart List

Use a “Campaign is Requested” trigger. Remember this request is coming from the Flow Steps of the previous campaign.

Marketo Auto_Responder_Trigger

Flow Steps

Use a Send Email flow step that checks for membership in our “After Hours” smart list using a Choice. If not a member, send the standard auto-response.

Marketo Auto_Responder_Flow

Think of the Possibilities…

Sending different emails is just one option. You can do lots of different things with this technique, including assigning a lead to different sales team or a third-party answering service based on time of day, using different marketing channels (an SMS, etc.) depending on time of day, using the data to see which days of the week bring in the best leads...use your imagination! See also a similar use of this technique from Josh Hill for routing leads to different locations based on time of inquiry.

Credits: Credit for the technique to parse the time-stamp using filters should go to Adam New-Waterson, since I’m pretty sure I first saw this in a Community post he wrote. Thanks Adam! Credit for the formula to parse the day of the week name from a date value belongs to someone on the Salesforce Success Community. Unfortunately I can't remember who, but thanks, whoever you are.

5860
14
14 Comments

Fantastic sir!

Does this work for calculating and sending SLA alerts only during business hours? And if so, how hard is it to take into account holidays?

Level 8 - Champion Alumni

Hey Pierce,

I think this technique could certainly be used to meet those requirements, although I haven't tried that specifically.

Few thoughts:

  • If you want to have no action taken during non-business hours, that would be pretty easy - just specify no action if the person is a member of the after hours smart list

  • If you want to have the logic check if it is non-business hours and then wait until business hours to send something, I would try using a wait step with day of week constraints - e.g., wait 1 minute and must end Mon-Fri at 9 AM. My expectation is that this would force it to wait until the next business day and then execute the next step.

  • Holidays is an interesting one. My first thought is that once a year you could build a smart list with the dates of all the holidays for that year and then include "Member of Smart List = Last Contact Us Date on Holiday" as a filter in your non-business hours smart list, so that a holiday would trump all other criteria.

    This requires manual updating once a year but would not be too onerous. There's possibly more crafty ways of pinging an external service and doing a check in real time, but the manual route shouldn't take much more than 20 minutes a year to update I'd suspect.
Anonymous
Not applicable

the power

Anonymous
Not applicable

Amazing Justin!  Great post - I'm going to put this into practice this quarter.

Cool - great post.

Champion Moderator

just stumbling across this now, Justin Norris‌, and unfortunately the screenshots are all missing -- the logic looks good, but the example formulas are hard to come by... any chance you happen to still have these 3+ years later??? lol

Level 8 - Champion Alumni

Hey JD Nelson‌ - unfortunately looks like the screenshots were hotlinked to our website and they got purged at some point. 

So I don't have them anymore, but I think they were mostly illustrative of the Marketo config which is hopefully still clear from the descriptions. 

The only formula is the CASE and that is still in the text above luckily.  

If there's somewhere not clear enough and you are getting stuck let me know and I am happy to supplement!

Champion Moderator

Most is, but I was stuck on the 'after hours smartlist' 

Level 10 - Community Moderator

Wouldn't it be easier to use a webhook to do the date parsing (relative to the multiple sync overhead)?