Tokens and importing complex data

[Jennifer_Bisho1]

We have an email send coming up where we need to use tokens to display the contact's renewal contract number and amount.  The contact can have 10+ different contracts/amounts that need to be displayed in one email.  Rather than creating 10-20 separate fields in Marketo to house this information and individually pull into the email layout, is it possible to somehow format a field in Marketo to display information in a column (see the attached screenshot to see something similar to what we want to do).  If we could somehow get 2 Marketo fields to display the data in 2 columns like in the screenshot (one for contract number, the other for contract amount), that would be perfect.

[SanfordWhiteman]

This is absolutely possible, and I don't think you need even 2 fields, just one.

Use a Textarea and store a string of JSON-formatted data, i.e. a field called Contracts:

[
 {
    "Contract Number" : "912454",
    "Sum of Line Item" : 129.99
 },
 {
    "Contract Number" : "218271",
    "Sum of Line Item" : 999.12
 },
 {
   "Contract Number" : "002328",
   "Sum of Line Item" : 1305.21
 }
] 

Then lay out it out using a Velocity (Email Script) token:

#set( $contracts = '#set( $contracts = ' + $lead.contracts + ' )' )
#evaluate( $contracts )
#if( !$contracts.isEmpty() )
<table>
<thead>
<tr>
<td>Contract Number</td>
<td>Sum of Line Item</td>
</tr>
</thead>
<tbody>
#foreach( $contract in $contracts )
<tr>
<td>$contract["Contract Number"]</td>
<td>$number.currency($contract["Sum of Line Item"])</td>
</tr>
#end
</tbody>
</table>
#else
You have no contracts up for renewal.
#end

This token will output the HTML:

<table>
<thead>
<tr>
<td>Contract Number</td>
<td>Sum of Line Item</td>
</tr>
</thead>
<tbody>
<tr>
<td>912454</td>
<td>$129.99</td>
</tr>
<tr>
<td>218271</td>
<td>$999.12</td>
</tr>
<tr>
<td>002328</td>
<td>$1,305.21</td>
</tr>
</tbody>
</table>

[Jennifer_Bisho1]

JSON and Velocity Email Scripts are definitely something I have zero experience with lol.  Where would I store these both and how would I use them?

This seems like the values would need to be manually added to the above code.  Is there a way to do this if someone provided a spreadsheet to me with a lot of contact information, and separate columns for the contract number/amounts next to their email addresses?

[SanfordWhiteman]

Where would I store these both and how would I use them?

The JSON is a Textarea field right on the lead. You might be confused by the line breaks, but that's just a string (like you would get from a Comments field).

The Velocity token is at any level of the Marketing Activities hierarchy (depending on whether you want to override it), like other {{my.tokens}}.

Then you include the {{my.token}} in your email as usual with other tokens.

This seems like the values would need to be manually added to the above code.

I wouldn't say they need to be manually added (that would be next-to-impossible to do without messing up).

You can import into that Textarea field as you would with any field. In Excel, if you have the data in different columns to start, create a formula field to add them together into one string (with the appropriate []{}"" characters surrounding them), then that string column gets mapped to Contracts.

[Dan_Stevens_]

This is awesome - and a great use-case to not only illustrate the power of velocity, but the use of JSON values in specific lead fields to support this approach.  Always learning from you, Sandy!

[SanfordWhiteman]

This is awesome - and a great use-case to not only illustrate the power of velocity, but the use of JSON values in specific form fields to support this approach. Always learning from you,

Glad you liked it!