Introduction
As a Flow Step, the Compute Formula service takes an Excel-style formula, computes it, and returns the resulting value to the selected field. This facilitates many different types of data transformations and use cases, including date math, text replacement, and composite scoring, among many others.
You can find the repository on Adobe Github.
Prerequisites
To deploy and use the Compute Formula Flow Step Service you will need:
- Experience using a command line.
- Have git installed and have basic experience in its usage.
- Install NodeJS, Node Package Manager (NPM), and the Adobe IO command line tool.
- Have an Adobe IO Runtime Account, and permissions to retrieve credentials for Runtime workspaces.
- A Marketo Account with Admin Credentials.
- An Adobe IO Runtime account. IO Runtime is currently available as an add-on to subscribers of AEP, AEM, Commerce, or Analytics
Support
While the Self-Service Flow Steps framework is fully supported, Compute Flow is an open-source application built on the framework using Adobe IO Runtime. Issues specific to the Compute Flow application should be logged on the github repository or on nation.marketo.com.
Deployment
Clone the Repository
In a command line, navigate to the folder where you want to clone the code from the Git Repository for AIO Compute Formula. From there use the following command to clone to repo:
git clone git@github.com:adobe/aio-compute-formula.git
Obtain IO Runtime Credentials
Make a new project in the Adobe Developer Console, selecting the “Project from Template” option:
Select the App Builder Template and save your project:
Select a workspace, either Production or Stage, that you want to deploy the application to and use the Download All option to download the credentials to the folder where you cloned the repository in the previous step:
Once you’ve downloaded the file locally, from your command line use the following command to use the downloaded credentials for your application deployment:
aio app use <file location>
Render Manifest from Template
Now you’ll need to use the render-manifest script to create a manifest yaml file including your desired API key (this is the API key which you will use later in the installation phase) and log level (info, warn, error, or debug). Use the following command to render your manifest:
npm run render-manifest <path:'./manifest.yml'> <apiKey> <logLevel>
Install NPM Packages
Now you will need to locally install the dependencies for this project using the following command in your command line:
npm install
Deploy Application and Obtain Service URL
To deploy your application, use the following command:
aio app deploy
Once finished the command line will list out the endpoint URLs of the application. Copy the URL ending in ‘serviceSwagger’ for use in the next step.
Install and Configure Service
In Marketo, navigate to the Admin->Service Providers menu and use the Add New Service button. In the dialog box, enter the URL you copied from the previous section:
Then follow the instructions for entering your API key:
In the Incoming Fields section, select the fields which you want the service to be able to write data back to:
You can always come back to change the field mappings later if you are not sure about what fields you want to be eligible for writes at this time. Follow the rest of the installation guide and then your service will be ready to use in Smart Campaigns
Usage
Flow Step Fields
Formula
The Formula field accepts Excel-style formulas, and supports all of the functions that are supported by FormulaJS. Lookup and other column-related functions are not available. Your formula should not include a leading = sign as you would when entering the formula in a cell of a spreadsheet application.
While standalone mathematical statements are supported to some extent, complicated cases should be implemented as Excel functions, as some standard mathematical operators, like ^, have behaviors distinct from the mathematical operation that they represent outside of a JavaScript context.
Strings embedded into the formula field, including tokens for string-like fields, e.g. dates, should be surrounded by quotation marks for functions to behave as they are expected to.
Return String
When returning a value to a string or string-like field, such as a Date or a Datetime, you should select the field you wish to return in the Return String field. Each field configured as an inbound field for your service will be listed in this field*.
Return Number
When returning a value to a number-type field, like Integer, or Float, you should select the field you wish to return in the Return Number field. Each field configured as an inbound field for your service will be listed in this field*.
*If the picklist of either Return String or Return Number is empty, or does not include choices which were recently added, you should refresh your picklist choices, either via the refresh button on the flow step card or from the Admin->Service Providers menu.
Example Use Cases
Composite Scoring
A common scoring use case is to create a weighted composite score from two or more other scores. The Compute Formula Flow Step makes this simple to do. For this example, you will use two source scores, Behavior Score and Demographic Score, and return the value to a field called Composite Score. To build our formula, you need to decide how to weight the source scores. Our hypothetical marketing organization values Behavior scores at 30% and Demographic scores at 100%. Our formula would then look something like this*:
SUM(PRODUCT({{lead.Behavior Score}}, 0.3), {{lead.Demographic Score}})
*When using numbers between -1 and 1 which have a decimal place, you will need to include a leading Zero (0) when embedding the number into your formula, e.g. 0.8.
If a person record has a Behavior Score of 3 and a Demographic Score of 20, then the resulting composite score would be 20.9. Scores in Marketo Engage are integers, however, so you should be sure to return a round number. Use the ROUND() function to make the output an integer. Now our Formula will look like this:
ROUND(SUM(PRODUCT({{lead.Behavior Score}}, 0.3), {{lead.Demographic Score}}))
Now that you’ve defined your formula, you’ll want to make sure that you’ve configured the fields which you want to write back to, in this case “Composite Score.” Go to Admin->Service Providers, drill down into the service, and edit the Incoming Fields section to enable the Composite Score field. Composite Score is a custom field for this example, so if you do not have this field in your instance, you’ll need to create a new one, or use a different score field.
Now let’s put this into practice. Since this is a composite score, in order for the score to be kept up to date, you need to listen for changes to the source scores to update the composite score when they change. That means you need a triggered Smart Campaign with Data Value Changes Triggers for both Behavior Score and Demographic Score.
Insert the flow step, populate your formula, and select your desired field to return to using the Return Number field.
Now when you activate this campaign and a person’s Behavior Score changes, then your campaign will activate and return the result to your selected field.
Date Differential/Time in Stage
To measure the efficiency of marketing and sales funnels it is common to measure the amount of time that a person spends in a Marketing Stage. In particular, the number of days a lead spends in the Marketing Qualified or Sales Qualified stages before the lead is acted upon is a significant indicator of how well aligned the marketing and sales processes are functioning. While this information is easy to synthesize with a business intelligence application, bringing that same data into Marketo and acting upon it has required complicated API integrations to populate back to person records in Marketo. With Compute Formula, the process is as simple as defining a formula and your return field. The DAYS() function counts the number of days between and end date and a beginning date.
DAYS(“{{lead.SQL Date}}”, “{{lead.MQL Date}}”)
Round Robin Lead Assignment
While Marketo provides Random Sample functionality to obtain a percentage-based sample of an audience, some Sales Owner assignment use cases benefit from more fine-grained control over lead assignment. One example of this is to take the modulus (remainder) of a lead id, using the number of potential owners that you can assign leads to. Suppose you have 55 potential lead owners, and you want to get an approximately even distribution of leads assigned to a given owner. One of doing this is to assign each of your owners a number from 1 to 55, and then to take the modulus of the integer lead id. Our formula would then look like this, where the Number is the lead ID, and the Divisor is the number of potential owners:
MOD({{lead.id}}, 55)
Given an id of 117, the resulting value would be 7.
Our campaign flow would have a Compute Formula step with our formula, and Return Number set to a Sales Owner Seed field, followed by a Change Owner step configured with choices corresponding to each sales owner.
.png)