SFDC_Last_Contact_Time

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

Justin_Norris1
Level 10 - Champion Alumni
Level 10 - 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.

10908
14
14 Comments