Hi Team,We have a requirement where a field is currently not available in our CRM as a temporary solution which will be fixed in long term. To support this, we will be receiving an Excel file containing Marketo ID, dollar amount, and revenue for each record. The goal is to use this data within our emails.I’d like to better understand the feasible approach here, as we want to avoid creating new records and instead leverage existing ones and update them. Specifically, I have two questions: Displaying Excel-based values in emails Is it possible to take values such as dollar amount directly from the Excel file and display them in an email? If so, what would be the recommended approach? Applying calculations (e.g., 10% of revenue) We also need to display a calculated value—specifically, 10% of the revenue provided in the file (e.g., if revenue is 5,000, the email should display 500). Is there a way to handle this dynamically within Marketo, or would this need to be pre-calculated before import? However, I’d appreciate confirmation on the recommended approach for displaying values from an Excel file in Marketo, including how this should be implemented. Additionally, I’d like to understand how we can calculate a derived value (e.g., a percentage of revenue) using a script once the data is available in Marketo—potentially via a custom field.Thanks in advance for your guidance.

Level 4

Solved

Marketo: Displaying Excel-Based Field Values in Emails & Calculating Revenue via Script

Forum|Forum|21 days ago
March 18, 2026
2 replies
64 views

Hi Team,

We have a requirement where a field is currently not available in our CRM as a temporary solution which will be fixed in long term. To support this, we will be receiving an Excel file containing Marketo ID, dollar amount, and revenue for each record. The goal is to use this data within our emails.

I’d like to better understand the feasible approach here, as we want to avoid creating new records and instead leverage existing ones and update them.

Specifically, I have two questions:

Displaying Excel-based values in emails
Is it possible to take values such as dollar amount directly from the Excel file and display them in an email? If so, what would be the recommended approach?
Applying calculations (e.g., 10% of revenue)
We also need to display a calculated value—specifically, 10% of the revenue provided in the file (e.g., if revenue is 5,000, the email should display 500). Is there a way to handle this dynamically within Marketo, or would this need to be pre-calculated before import?

However, I’d appreciate confirmation on the recommended approach for displaying values from an Excel file in Marketo, including how this should be implemented. Additionally, I’d like to understand how we can calculate a derived value (e.g., a percentage of revenue) using a script once the data is available in Marketo—potentially via a custom field.

Thanks in advance for your guidance.

Marketo

Best answer by SanfordWhiteman

You can create a new custom field, sure. But I thought you were trying for a disposable approach, and a custom field can’t be deleted like a CO.

Calculating 10% in VTL:

#set( $tenPercent = $number.integer( $math.mul( $lead.field, 0.1 ) ) )
10% is ${tenPercent}

Note this uses banker’s rounding (which only matters if you have revenue values like 5005 as opposed to 5000). If you need arithmetic rounding, use my boilerplate code for that.

SanfordWhiteman

Level 10

You cannot read data that isn’t stored in Marketo in some way, be that a Person field, Program Member field, or Custom Object. There’s no such thing as a fully external data source that just gets brought into Marketo using (for example) a remote HTTP call but is never stored anywhere.

If you want to treat both data and its underlying definition/schema as non-permanent, the easiest way is a Custom Object. This is because COs definitions + data can be completely destroyed when you don’t need to use them anymore. So bulk CO upload would be the best “temporary” choice.

You can do the calculation you describe using a Velocity {{my.token}}. That part is easy and doesn’t depend on how you store the data. It could be in a Person, Member or CO field.

A

ashah123Author

Level 4

@SanfordWhiteman

Thanks for sharing the feedback. I haven’t created a CO before, so I’m not fully familiar with that approach, but I’m open to learning and giving it a try if needed.

Another thing, I wanted to run by you. Would it make sense to create a custom field in Marketo and map a unique identifier from the Excel file to existing records? That way, we could update the custom field with the corresponding value from Excel instead of creating new leads. This would allow us to store the data directly in Marketo and reference it in the email for dynamic display. Let me know if you see any concerns with this approach.

Secondly, do you happen to have a sample Velocity script for calculating and displaying the percentage value? My thought is to store the value in a custom field and then use Velocity to compute and render the percentage amount dynamically in the email. If you could share an example script, that would be really helpful for testing.

SanfordWhitemanAccepted solution

Level 10

You can create a new custom field, sure. But I thought you were trying for a disposable approach, and a custom field can’t be deleted like a CO.

Calculating 10% in VTL:

#set( $tenPercent = $number.integer( $math.mul( $lead.field, 0.1 ) ) )
10% is ${tenPercent}

Note this uses banker’s rounding (which only matters if you have revenue values like 5005 as opposed to 5000). If you need arithmetic rounding, use my boilerplate code for that.

AmitVishwakarma

Community Advisor

Hi @ashah123

you can do this, but not directly from Excel at send time. You must first load the values into Marketo fields, then use tokens / script in the email.

1. Using Excel-based values in emails

You can't point an email directly at an Excel file. Instead:

Create custom fields on the Person (e.g.
- Excel Amount (Currency/Number)
- Excel Revenue (Currency/Number))
Import the Excel as a list (CSV) using Marketo ID or Email as the key and map columns to those fields.
- This updates existing people; it does not overwrite with blanks unless you explicitly import NULL values.
- See list import behavior in https://nation.marketo.com/t5/knowledgebase/list-import-blank-values-in-csv-or-txt-file/ta-p/250538 and spreadsheet import in https://nation.marketo.com/t5/knowledgebase/marketo-supported-crm-integrations/ta-p/249815
In your email, just use normal tokens, e.g.:
- {{lead.Excel Amount}}
- {{lead.Excel Revenue}}
This way, your Excel is only a staging file; email personalization is fully field-based.

2. Calculating 10% of revenue

You have two solid options once Excel Revenue is in Marketo:

Option A – Pre-calc in Excel (simplest)

Add a column Revenue_10pct = Revenue * 0.1 in Excel.
Import into a numeric field (e.g. Excel Revenue 10%).
Use {{lead.Excel Revenue 10%}} in the email. No extra Marketo logic needed.

Option B – Calculate inside Marketo

Using Compute Formula (flow step, if available in your subscription)
Create a numeric field Excel Revenue 10%.
Use a Smart Campaign with flow step Compute Formula and a formula like: ROUND(PRODUCT({{lead.Excel Revenue}}, 0.1))
Return to Excel Revenue 10%.
Then token in the email: {{lead.Excel Revenue 10%}}.

Using an Email Script Token (Velocity)

Keep only Excel Revenue as a numeric field.

Create an Email Script token (e.g. {{my.Revenue10Pct}}) with something like:

#set($rev = $convert.toBigDecimal($lead.ExcelRevenue))
#if($rev)
  #set($pct = $rev * 0.1)
  $number.format("###,###", $pct)
#end

Drop {{my.Revenue10Pct}} into your email.

Amit Vishwakarma - Adobe Commerce Champion 2025 | 16x Adobe certified | 4x Adobe SME

SanfordWhiteman

Level 10

This code will fail for 2 reasons:

$convert.toBigDecimal does not exist. There’s no such method in Velocity Generic Tools.
The # is a special character in VTL. To use literal #, as in Number formats, you must replace it with the escape sequence ${esc.h}.
Even if you were to obtain a BigDecimal using the correct method, (a) casting Double to BD is always lossy and (b) using the * operator ends up gaining you nothing.

Remember to test code in the real Marketo environment before posting.

Also notable that $number.format uses banker’s rounding (as does $number.integer, as noted in my response). This may be surprising in a marketing context. For arithmetic rounding, you must set HALF_UP on the NumberFormat.

A

ashah123Author

Level 4

Thankyou @AmitVishwakarma for sharing your guidance on this ..its really helpful . Thanks @SanfordWhiteman I will test the code and let you guys know the result on how it works.

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded