What new field type and formula for acquisition query...

[Sarah_Hare]

Hoping someone can assist with a new field query...

I would like to create a field that populates (could be with a number/ date/ true)  if the contact first appears in in Marketo and is then matched to our client records.

The objective of this is to show the contacts who have been acquired first through marketing and then purchased our product.

I was thinking this could be a Integer field and then assume a formula will have to be written - to find the difference between the dates and if the Marketo created date is BEFORE the Purchase date we get singing, dancing, bells and whistles!

Thanks in advance for your thoughts!

Sarah

[SanfordWhiteman · Accepted solution]

Hi Sarah,

It should probably be a simple Boolean field because:

• Marketo can't calculate and store intervals between Dates internally (you'd have to call a webhook, and then you might as well just pass the Purchase Date and Created At to the 'hook and get back a true/false result)
• While Marketo can subtract score tokens from score fields, which is a form of Integer arithmetic, it can't do this with 2 lead tokens (so even if you had the dates converted to Integer epoch times, you couldn't get the difference between them without, again, calling a webhook, and if you're doing that there's no need to convert in the first place)

On the other hand, if you mean an SFDC formula field, then using a Date + Formula does make sense.

But Marketo doesn't have built-in functionality like SFDC's, so you'll end up calling an external service, and that service can operate directly on (static) Date fields and return a Boolean.

P.S. In case it isn't clear to anyone who's lurking, Marketo absolutely can calculate the diff between dates in a Velocity script (e.g. to output different email content based on date math).  But the result of that calculation is ephemeral. It can't be written back to a lead record,* therefore can't be used to qualify leads for Smart Campaigns or viewed in reports.

* OK, there is one way, but the method is so exceptional and undocumented that it's best to pretend it doesn't exist.

[Sarah_Hare]

Hmmm - Thanks so much Sandy. It actually looks a little bit more complicated that we need at the moment so I think we will do an extraction and then cross check with our client purchase date....