Calculate number of years based on a date field

[Anonymous]

We are looking to create an automation in which the client has a date field called Client Anniversary that basically shows the joining date.

The automation sends out an email on the anniversary which is fine (we have built) but inside the email there is a part where it says:

Can you believe it has been X year(s) since you joined our family?

That X field needs to basically come from calculating This year - Client Anniversary year and give us a number (ie. 1, 2, or 3 ...)

Has anyone ever worked with something like this and know how it can be achieved?

Thanks.

[samsellars]

Hello Marketo Experts:

In the original post the author says, "The automation sends out an email on the anniversary which is fine (we have built) . . ." 

We are struggling to find a way to populate or using a computed column that holds a date inside Marketo itself.  Not populating the date ourselves with the REST API.  It is nearly the same scenario as this - we need have an anniversary date (birthday in this case) in a column that we can use in a campaign run (trigger).  Would it be possible to run some kind of trigger that would populate the field of all records with the month, day, and current year (birthday)?  Ideally, this would happen on a custom object.

Best regards,
Sam

[SanfordWhiteman]

---

Would it be possible to run some kind of trigger that would populate the field of all records with the month, day, and current year (birthday)?  Ideally, this would happen on a custom object.

---

Not seeing why you‘d want a Custom Object to store the anniversary of a field (Date of Birth) that itself is a flat field on a person. In any case there’s no way to update COs from flows.

You’ll find a couple of options here: https://nation.marketo.com/t5/product-discussions/building-a-birthday-anniversary-email-without-usin...

[Victor_Herrero]

Hi Sanford Whiteman​,

Thanks for sharing this.

I managed to modify it to work (on a test email) with our system but I am having trouble passing the calculated value into an integer field.

Is there any way to get around this? I take it the problem is the "email script token" is understood as a "text" input type.

Thanks!

[SanfordWhiteman]

You can't use Velocity output anywhere but in the email assembly context. It's never calculated at the flow point you're showing, so even if the red squiggly didn't show there wouldn't be any data.

If you need complex calculations like you can do in Velocity, but need to persist the result permanently to a lead field, you need a webhook solution.

[Victor_Herrero]

Thank you fr confirming that. I was having a look at a formula field, but it seems they can't be used in smart campaigns and will not be exported. Those would be the main uses I would have for this field...

[SanfordWhiteman · Accepted solution]

Use a Velocity (Email Script) token:

#set( $defaultTimeZone = $date.getTimeZone().getTimeZone("America/New_York") )
#set( $defaultLocale = $date.getLocale() )
#set( $calNow = $date.getCalendar() )
#set( $ret = $calNow.setTimeZone($defaultTimeZone) )
#set( $calConst = $field.in($calNow) )
#set( $ISO8601 = "yyyy-MM-dd'T'HH:mm:ss" )
#set( $ISO8601DateOnly = "yyyy-MM-dd" )
#set( $calJoinDate = $convert.toCalendar(
  $convert.parseDate(
    $lead.JoinDate, 
    $ISO8601DateOnly, 
    $defaultLocale, 
    $defaultTimeZone 
  )
) )
#set( $differenceInYears = $date.difference($calJoinDate,$calNow ).getYears() )
#set( $friendlyLabel = $display.plural($convert.toInteger($differenceInYears),"year") )
You joined us ${differenceInYears} ${friendlyLabel} ago!

Where $lead.JoinDate is the joining date.

More at http://blog.teknkl.com/velocity-days-and-weeks/

[Anonymous]

Thank you. I am not exactly sure where I need to place/use this code actually.

[SanfordWhiteman]

And make sure to check off your JoinDate field in the tree on the right-hand side of the Script Editor. And change the name of that variable accordingly in the script.

[Anonymous]

Thanks. I have done as mentioned. I have a small concern. The Client_Anniversary__c is under Account not Lead, however when I drag and drop it puts the lead. in front, would that be an issue?

Thanks.

[SanfordWhiteman]

Is the data there? That's the question.

Also, you can take out the {} from the variable name in this context, it just makes it harder to read.

[Sarah_Greig2]

I was looking for something based on date e.g if x field = system date then trigger x. Would this need velocity scripting? I tried to use if field = system date token but it wouldn't accept it.

[SanfordWhiteman]

You can't use Velocity to trigger campaigns. Velocity executes when an email is being assembled, i.e. after leads have been qualified for a send.

If you want to compare an arbitrary field to today's date you'd need a webhook.

[Sarah_Greig2]

So I have a renewal date stored in a Marketo field and when it's that date I want to send an email. I can't find any examples of this in the community?

[SanfordWhiteman]

OK, that specific goal can be accomplished in Marketo without webhook assistance -- look for "birthday" or "anniversary campaign" in the Community. (Renewal is a form of anniversary.)

But don't get too optimistic as not all date-related goals are possible natively. For example, extended date comparison in a SC Flow (not in Smart List qualification), and arbitrary comparison between two dates or date-like strings, needs a webhook to do the (pre)calculation.

[Sarah_Greig2]

The renewal dates are different lengths e.g they could be 12 months, 18 months etc. I get that I could maybe put in a calculated field e.g renewal date = 30/04/2018 x 12 months = (Renewal Date) but then how do I get Marketo to know to send on that specific date if it is Today?>

[SanfordWhiteman]

Have you checked out the anniversary/birthday best practices? You can have different campaigns with different lengths (as long as there aren't like 100s of different options to keep track of).

[Sarah_Greig2]

had a quick look at the documentation. So you would get the webhook to check that the date was todays date and then put a true/false in a field which would then trigger the email?

[Grégoire_Miche2]

Hi Hossein,

Go the program containing the email, and click the "tokens" tab, then drag & drop a script token. Name it as you want and paste Sanford's code there.

-Greg