How do I update one field with a month/year value based on the date of a status change?

[Alex_Bridges3]

Hello there!

For analytics purposes, when a lead reaches a particular status, we're looking to update a new field with the month and year of when that status is reached. Currently, we're using the system.dateTime token for another field (which this new field will be replacing), but it gives too much information for what we're trying to do.

So, my question(s) for you all-

1. How can I update a field value to grab the current month and year, based on when a particular status updates? (Initial thought was to build a token, script it, and then update the field using a smart campaign with the token inserted into 'update field value', but it only displayed the token, not a value.)

2. How can I update the same field for everyone who reached the current status in the past, who had the dateTime token in the other, now-obsolete field? (basically taking the value of the old field, stripping it to just month/year, and then putting the new value in the new field)?

Thanks!

[Grégoire_Miche2]

Hi Alex,

You could imagine to do it with a smart campaign with the logic

if  field date starts with 02/ then MY field is set to 02-

then is field date contains /2018 the MY field is set to "MYField"+2018

But honestly, this will be awful to setup an maintain.

So the best answer is a webhook platform.

-Greg

[Alex_Bridges3]

Hi Greg!

Thank you for your insights. How would I leverage a webhook platform for this, though, since I'm not calling out to a 3rd party service?

-Alex

[SanfordWhiteman]

In this case, you would be calling an external calculation/parsing engine that plugs in as a webhook.

You're not storing data in any other db, merely using the 3rd party service's advanced abilities.

[Grégoire_Miche2]

+1 on Sanford on this. the webhook receives the date and immediately returns the formatted month-year string that you can use to update the target field. No outside storage, not even any personal information form the lead the info relates to.

-Greg

[Jay_Jiang]

You can host your own endpoint if you don't want to use a third party. Create a php file on your own server and use the path as your Marketo webhook url. Your payload template would be datetime={{lead.field with systemdateTime}} and response mapping JSON

If you don't need timezone translation

<?php
$dt = new DateTime($_POST["datetime"]);
$response = '{"response":"'.date_format($dt,"m/Y").'"}';
echo $response;
?>

If you need timezone translation (I think Marketo stores system date time as CST):

<?php
$dt = new DateTime($_POST["datetime"], new DateTimeZone('America/Chicago'));
$dt->setTimezone(new DateTimeZone('Australia/Sydney'));
$response = '{"response":"'.date_format($dt,"m/Y").'"}';
echo $response;
?>

Map 'response' to your new field in the webhook response mappings. Please google for documentation on php timezones and date formatting