Numeric and “numeroalphabetic” strings in Marketo, SQL, PHP and elsewhere

Sanford Whiteman -

Marketo user SS asked a good question about datatypes in webhook responses. Referring to the documentation provided by a 3rd-party lead enrichment service, she notes:

Because all of the custom fields created for the webhook must be string-type fields, I cannot use logic for their 'number of employees' data such as "if value is between x and y"…

Luckily for SS, the vendor docs are off-base. In fact, if a Marketo webhook returns a numeric string (that is, a JSON-encoded string value that starts with one or more numbers) you can safely map that property to an Integer or Score field.

At the end of a FlowBoost recipe, for example, this will work fine:

return {
  newScore : "999"
}

Of course, returning a number-like string instead of an actual number is best avoided for code clarity, but it will still work.

This isn't that strange

While Marketo's precise technology stack is still a black box, it stands to reason that string → int type conversion works because either:

  • a back-end SQL engine receives the value as a string, where it is converted automatically if the column type is INT; or
  • a dynamic language like PHP sits in-between, either ignoring datatypes or doing a deliberately generous conversion to a number; or
  • both of the above are in play

(Though I believe a Java tier initially fetches the JSON response data, if it doesn't parse it, it won't interfere with conversion. The JSON payload itself is just a string after all... sorry if that just messed with your mind!)

The numeroalphabetic quirk

What might be more surprising is this response will also set the score to 999:

return {
  newScore : "999 whimmydiddles"
}

SQL and PHP automatically recognize what I call “numeroalphabetic strings” — strings starting with a number — as representing a numeric value when needed. The value, as you can see, is the longest set of initial numbers.

This works with decimal numbers as well, so "9.99 dollars" will have the value 9.99 whether you wanted it to or not. Note that "$9.99", which might represent the same amount/units to a human reader, is not numeroalphabetic, since it doesn't start with a number. If read as a number, it will be 0.

So this feature can be either convenient or confusing. Convenient when you know a data source always pads with a known unit (like "attendees" or "inches") that you can safely discard. Confusing when a service screws up but data still ends up in your database. Imagine if there's a server error and the result is { "newScore" : "404 Error" } — you'd never notice you were sending the numeric value 404 to your database!

What about JavaScript?

JS, notoriously loose with datatypes, comes off as relatively strict here (compared to the other languages I mentioned):

> "99 dollars" + 1
< "99 dollars1"
> "9.99 dollars" + 1
< "9.99 dollars1"

Though if you do a deliberate conversion, you can see the numeroalphabetic idea is still in there:

> parseInt("9.99 dollars")
< 9
> parseFloat("9.99 dollars")
< 9.99

In all, a super-minor detail to dedicate a blog post to, but betcha someone out there didn't know about it yet!