[Custom Objects] How to target COs by a datetime field

[Anonymous]

Hi there,

I have a Custom Object that has a number of fields, one of them being eventDate which... of course is the date of a given event specific to each instance of this Custom Object.

In the Smart Campaign Smart List, I would like to be able to target by batch each day the leads who have a Custom Object whose eventDate is 7 days back to be able to send a campaign 7 days after the event took place.

So for instance today being October 30, I would like my Smart List to list leads who have a Custom Object with eventDate between October 23 0:00:00 and October 23 23:59:59

The problem is there is no operator "is 7 days ago" in the "Has Custom Object" block:

Until now I have been using the "Was added to Custom Object" block instead with 2 constraints to make this work but it's very clunky:

- eventDate in past before 7 days

- Date of Activity in past 7 days (this is actually the date when the Custom Object instance is created, so I should not depend on this because some times our API import script doesn't work and needs to be run the next day which ruins everything)

I would rather only use the "Has Custom Object" block and only my custom eventDate datetime value if possible.

Does anyone know how to manage this?

Many thanks,

Thomas

P.S. I tried using 2 blocks "Has Custom Object" with "eventDate in past 7 days" in the first and "eventDate in past before 7 days"in the second and this doesn't work because it pulls all leads that have either condition although I use AND between both blocks. I ended up with leads that had COs for October 15 (2nd block) and October 27 (1st block)

[Grégoire_Miche2]

Hi Thomas,

Technique 1:

Just use the "in past before" filter, run your smart campaign every day. Make simply sure that each lead can run through the smart campaign once or once every 7 days.

Technique 2:

To accomodate the fact that the lead can be created one or 2 days after the event datetime :

• Create a static list (STL)
• Run a daily smart campaign that take every lead that is not in the STL and has a CO which datetime is in the past 6 days. have this smart campaign add the leads to the STL.
• Create a smart list (SML) that gives all the leads that have an event in the past 7 days
• Create another smart campaign that selects all members of the STL that are not member of the SML. Schedule it to run every day an have it doing what you want.

These techniques work if each lead will get this CO only one, or only once within a 7 day period. They also work if, once the lead is in the 7 day period between the event and the send, the targeting smart campaign wait until the most recent event is 7 days old incase multiple CO's were added.

-Greg

[Anonymous]

Hi Greg,

Thanks for your reply, those techniques are great but as you state in your last paragraph, these events can be multiple for each lead, as they are produced by the leads themselves (specific actions they take on our website). I should have mentioned this in my first post! So I need to be careful about the logic implied.

I do have an Email Script that checks if the event occurred exactly 7 days ago (to avoid using data from an earlier CO, 3 days ago for example), but this script executes once the leads have been elected to receive the campaign so I need to make sure beforehand that each lead really satisfies the conditions in the script (line 07) or else the lead will receive an email filled with non-parsed merge tags:

#set ( $sortedUpdated = $sorter.sort($COname_cList, "updatedAt:desc"))
#set ( $todayCalObj = $date.toCalendar($date.toDate('yyyy-M-d',$date.get('yyyy-M-d'))))
#set ( $found = 0 )
#foreach ( $user in $sortedUpdated)
#set ($eventDate = $date.toCalendar($date.toDate('yyyy-M-d', ${user.eventDateTime} )))
    #set ( $eventDateDiff = ($todayCalObj.getTimeInMillis() - $eventDate.getTimeInMillis()) / 86400000 )
#if( (${user.merchantOptedOutReviews} == 0) && (${user.userLanguage} == "en") && (${user.userHasReviewed} == 0) && ($eventDateDiff == 7) )
#set( $merchantID = ${user.merchantID} )
#set( $merchantName = ${user.merchantName} )
#set( $uploadPhotoURLEN = "www.xyzen.com/merchant/imageUploadPrev/${user.merchantID}.html")
        #set( $uploadPhotoURLFR = "www.xyzfr.com/merchant/imageUploadPreview/${user.merchantID}.html")
        #set( $merchantPageURLEN = "www.xyzen.com/mer/${user.merchantID}.html")
        #set( $merchantPageURLFR = "www.xyzfr.com/mer/${user.merchantID}.html")
#set( $YPUserID = ${lead.mYPUserID} )
        #set ($found = $found + 1)
        #break
    #end
#end

I would like to use your 1st technique but I need to be able to filter out all users who were already sent the campaign in the past before 7 days except the ones who truly have had another event exactly 7 days ago. Any tips?

About technique 2, all our CO instances are created for leads that have been created prior to the event.

Thanks, (Merci beaucoup !)

Thomas

[SanfordWhiteman]

If I understand the problem domain correctly, at the time someone triggers Added to Custom Object, the eventDate is only interesting if it's sometime within the last 7 days. It could be just yesterday, so you'd want to send someone a follow-up 6 days from now, or it could be 6 days back and it's about time to check in tomorrow.

So in light of the limited possible conditions, why not have 7 triggers running?

Another approach would be to use a Velocity "poison pill": throw an error from Velocity if there are no qualifying objects, and the email won't go out.

Also, since I know you push the limits of Velocity, you'll like my upcoming blog post on leveraging lists + streams + filters in VTL... can make Velocity more fluent.

[Nicholas_Manojl]

So the idea is that the eventDate is not created immediately? Otherwise I'd say you'd run the campaign immediately the eventDate was updated (Added to Custom Object, Constraint: Event Date is in timeframe Today) and have a wait step of 7 days.

Alternatively, I wonder if you need to create a SFDC formula that will add 7 days to the event date in another field, which you then listen for daily.

[Nicholas_Manojl]

Also, if you run the campaign immediately the object is updated via a trigger, you can use the trigger.object to reference in your email script and process the CO in a linear fashion.

In fact, I think the trigger is essential to this approach and a batch won't work.

I don't know how you're managing to specify exactly which record in the CO to reference in your email script if you're just taking the latest one.. what if it's not the latest? Your approach is looking for in past before 7 days but referencing the CO that was updated yesterday.

[Anonymous]

Hi Nicholas,

On line 7 of my Email Script at the end (need to scroll horizontally) I check that there is a datediff of 7 days between eventDate and today's date. Shouldn't this work? It looks like it works when I execute the campaign manually.

[Nicholas_Manojl]

I see a problem because of the way you're sorting the records, and expecting there to be no records in the time period between when the initial record is created and when you want to expose this info in an email.

Day 1: Custom object record added

Day 7: Sort custom objects, check if -7 days, Send

In the above scenario, there isn't an issue.

Day 1: Custom object record added

Day 2: Custom object record added

Day 7: Sort custom objects, check if -7 days.. fail so the if statement doesn't process.. you've sorted the record only 6 days away. If you ditched this, you'd be grabbing information our of the Day 2 record.

I think then you need to process the records in a linear fashion using a trigger and the trigger.object, which will persist alongside the trigger, even after subsequent triggers have fired.

Day 1: Custom object record is added, Trigger object contains the information

Day 2: Custom object record is added, Trigger object contains the information

Just some thoughts - we might be talking right past each other but hope it helps anyhow.

[Anonymous]

Thank you all for your inputs. I wasn't clear enough in my initial post so here are a few important precisions:

- eventDate is the datetime when the event took place (user booked a restaurant, called a business, etc...). It is *not* the datetime at which the CO is created. eventDate is the datetime I would like to reference to send the campaign 7 days later

- Added to CO: I can't (shouldn't) use this date at which the CO is created in Marketo because the script usually runs the day after the eventDate, but not always. If it fails it's run manually the next day, etc... the delay wouldn't be ok.

I like the idea of sending the email after a 7 days wait step, at least I know I won't be sending the email out too soon as it will be sent out after a minimum of 7 days, event if the import script skipped a day. But can I use a triggered campaign when I import hundreds of COs in a matter or seconds each night?

Thomas

[SanfordWhiteman]

In my example you're not using the date that the trigger fired, you're using your internal eventDate. By constraining on that date you can determine what 7 days from then would be -- again, because you have a small number of choices it's not impractical to have 7 checks.

And I wouldn't worry about hundreds at once, maybe thousands though.