Velocity Scripts - Integrating Data From An External Database

[Anonymous]

Is it possible, using a Velocity script, to take a single data point in a custom data object and reach out to some external database to pull in relevant information when an e-mail is sent?

For instance there’s a custom object (below) on each lead which contains a list of products of interest. A given lead has the following in this object:

Product

A

C

E

And in an external database table (below), there’s additional data points that you’d like to include in an e-mail:

Product	Cost	Earliest Shipping Date
A	1.00	October 23, 2015
B	5.00	October 27, 2015
C	8.00	October 25, 2015
D	4.50	October 23, 2015
E	2.25	October 25, 2015

Is it possible for Velocity to loop through the entries in each lead’s data object and, for each product contained in the object, pull in external data, like the cost of the product or earliest shipping date, as in the example external database table above?

Or would we need to run some sort of job to update a more complete custom object (below) using the API with all the up-to-date data points immediately before releasing our e-mail:

Product	Cost	Earliest Shipping Date
A	1.00	October 23, 2015
C	8.00	October 25, 2015
E	2.25	October 25, 2015

At which point Velocity could, presumably, access all of the data in that custom object and output that information in whatever format we choose.

I read a comment by a Mr. Kenny Elkington in this thread:

"Our velocity implementation does not support outgoing requests at runtime."

Is this precisely what I'm asking for in the top portion of this post? An "outgoing request at runtime"?

Any other clever ways to go about this? Maybe compiling each user's e-mail outside of Marketo and send to each recipient using the API?

Also, how much flexibility to sort the data do you typically have? From Kristen Carmean's comment in this thread, it seemed like there's some restrictions:

"Just to add one more thing, the Sort Tool is listed on the Marketo developer site as not supported right now."

If that's correct, what options do we have on that front? Should we leverage the API to sort the data within our objects en masse before we release our e-mail? In this video​, Julia Linker talks about disabling logging during what appears to be a pretty massive update via the API. Might that be necessary, depending on the amount of data?

Any catches on the analytics side?

Whew! Sorry! Long post! It's just that I'm trying to conceptualize how we might go about building something like this.

Any input would be great.

[SanfordWhiteman]

Great, detailed question with an unfortunate answer: No.  You can't call out.  Velocity Email Scripting may not be as logic-less as, say, Mustache, but that's something you can't do. (Actually, I shudder to imagine the performance impact if literally at send time you were calling an external database.)

But!  This is (almost) exactly what webhooks are for: enriching lead data from external sources.  Set up a webhook endpoint that returns precisely formatted data into a textarea field (i.e. rather than a data-only JSON response with each field separated) and you should be good to go.

[Anonymous]

+1 for this method.  We store JSON data from an external source and use velocity to make it look pretty within emails.

It is a good strategy in that we only use 1 custom field rather than making separate fields for each data point.

[Anonymous]

Just to add to this, Sanford is correct, you cannot call out from velocity scripts.  In this case you would run one campaign to get the data from the exterrnal database and place it in a field within marketo.  This field could be in the lead record or a custom object.  Then you could use a token in the email to access the data.  If the data is stored in a custom object then you would need a script to access it.

[Anonymous]

Thanks, guys! Just to provide a bit more context, the user will pick and choose which products, from a fairly large selection of products, would be included in their e-mail, and the number of data points related to each product could be fairly extensive. I tried to keep it simple in my example, but it won't be as little as three data points.

Given that, is the sensible approach to use a custom object and the API? It appears, from this video​, that Julia Linker and her team used a field, but it seems like they expected to produce an e-mail with a fairly limited set of items and data points. (But still experienced a lot of overhead updating the fields before their send.)

Sanford Whiteman, in this thread, you mention the field limit for a text area field, presumably the largest field available, is either 32,000 or 65,535. Would I assume the latter, in this case, if we were updating the field using webhooks or the API? 32,000, might actually be too little in our case, once some sort of delimiter is included to break up all of the data points. It seemed safer to use a data object, so each entry would have a bit more structure and there would be, theoretically, no limits. (No limits? Please let me know if that last sentence sounds insane.)

Jamie Lewis​, when you say I "would need a script to access it", do you mean looping through the object with a Velocity script to access the data?

Thanks, both, for your feedback. Very helpful.

[Anonymous]

If you store a value to a custom object you cannot refer directly to it in a token.  You need to use a velocity script to create a token for it.

[SanfordWhiteman]

I'm not convinced you need a data object. If this value is meant to be totally opaque to Marketo (you will not be peering into its internals, just outputting it as text/html) and has a one-to-one relationship with a lead, then I see it as belonging on the lead. You could use a few different textareas, so the 64K isn't a worry (though I worry about an email that truly has 32K of content, even including markup). Also, you can still use Velocity to add markup, just using your 'hook to do the fetching and the sorting.

[Anonymous]

I agree, this may be data stored to the lead record. 

[Anonymous]

Thanks, everyone! I might come back with more questions, but this has been very, very helpful. Really appreciate the feedback.

[Bang-Tai_Chen]

We are working an Order Confirmation Transactional Email .

The Order Lines {{my.lines}} such as "15206||Part 1: FDA Initiatives to Meet the Medical Needs||url||1,274.15||||15 September 2015"
will be passed from requestCampaign API Call.

Question: How to access other program token value in Email script token?

Email Script #set($orders="${my.lines}")
causes email Rendered_Email_Velocity_Error_Area
where {{my.lines}} is a Text token with value passed by from requestCampaign API Call.