File under *DO NOT USE*: Marketo Currency fields are badly broken, Part I
Update 2018-09-13: Per confirmations/remarks from friends of the blog, this bug is even worse than originally thought. (You know it's bad when “never use this thing” doesn't seem strong enough!) Turns out the problem begins before the known 32-bit float threshold of 16,777,216: values as tiny as 100,000 are unreliable. Haven't been able to reverse-engineer why that is yet, but get away even faster from this datatype.
Great Marketo interview question:
“When should you choose the Currency datatype in Marketo?”
The correct answer:
“Never.”
(If the interview is otherwise going well, you might allow “At gunpoint” as an alternate — at your discretion.)
Yes, it's as bad as it sounds: Marketo offers a datatype that sounds like money but is dead wrong for money values — and also wrong for any other application you'll see in martech.
In fact, there is no Marketo datatype that is suitable for decimal money values like 1999.99. Even if your numbers don't have a decimal point, you will lose data if you use the tantalizingly-named Currency. And you'll lose exactly the same data with the innocent-sounding Float (which is actually the same type as Currency!).
I'll explain the mathematical Why in Part II, but to cut to the chase:
- You must use Integer fields for round, decimal-point-less money values (like $200,000). If you have no need to store cents/pence/centavos/kopeks (which is usually the case in martech) you should already be going straight for Integer.
- You must use scaled Integer fields to store values that originally had numbers after the decimal point. “Scaled” means multiplying by 10(number of fractional digits). All worldwide currencies use 2 digits[1], and 102 = 100. So to represent $2199.00, you store the number 219900; to represent $1999.99, store 199999. Make sure your Smart List filters all reflect the multiplier as well. Use Velocity to divide it back into a decimal for display if needed.
If you do anything other than the above, you're doing it wrong.
A quick proof
Go to a lead. Find a Currency or Float field. Set that field to 19,999,999:
You'll see the Auto-save run accordingly:
Check the Activity Log Summary and Detail:
Now change the field to 20,000,000:
Auto-save will dutifully log the change:
And here's the Activity Log:
No, I didn't mess up and include the wrong screenshot! That's the log after the “change” — a change that will never actually happen with Currency or Float fields, because 19,999,999 and 20,000,000 are both stored as the float value 19999999. If you refresh and go back to the field, you'll see it has not changed.
Worried? You should be.
This behavior, which is defined by the way floats work, means a Data Value Changes/Changed trigger or filter is broken if the old and new values have identical float representations. It also means filters with Equals/Greater Than/Less Than constraints on float fields cannot be fully functional.[1]
This is how floats roll
Let me repeat my point: it's not a bug that float columns in the Marketo database work this way. Floats can't be “fixed” (pun intended, though you won't get it yet ☺) and still be floats!
Now, it is a mistake — IMO — that floats are even offered by Marketo as user-selectable types, but that ship has sailed. You can only hope to contain the damage by never choosing them from the Field Management dropdown.
In the midst of this bad design, there's actually a stroke of luck: the design is so prone to error that it's easy to demonstrate. It's like the difference between, say, a car's engine falling out during a test drive as opposed to an airbag problem that you may or may not run into.
The thing that makes it super-glaring is that Marketo uses an older-school 32-bit float as opposed to a 64-bit float that's available in modern databases. It would be a mistake to use either kind of float, but because of the 32-bit-ness, it's far easier to show the screwup. But we'll learn much more about that in Part II.
If floats are designed to do this terrible thing, why do they even exist?
Floats were one of the brilliant strokes that made computing possible (back when computers were still partly mechanical and jaw-droppingly resource-constrained from our current vantage point, I'm talking about the 1930s here). And they're still critical for advanced applications and/or low-footprint devices even in 2018.
Maybe 1% of full-time developers need to use floats if they have a choice[3], and almost no one in martech should be using them. (No, that was needlessly generous: unless I think of an exception in the wee wee hours, literally 0.00% of the martech world has any use for floats.)
That 1 out of 100 developers couldn't do their work without floats, but choosing floats without understanding them leads to disaster (even initially making an informed choice, but forgetting to manage the consequences, has led to human casualties).
Much more in Part II, the technical, even mathematical (yikes!) deep dive.
Notes
[1] Yes, in finance four digits to the right of the decimal point are commonly used, but I can't see a reason to include basis points in a marketing email. If you do, then multiply by 104 = 10000 instead.
[2] The overall filtering and reporting consequences are much more severe than this 19,999,999 + 1 example, because they also involve SQL-level rounding errors. (And those errors aren't part of regular/expected float behavior, but are specific to Marketo's use of default MySQL functions; might get into those in Part III, if I have the stamina to finish Part II!)
[3] Sadly, Marketo gives you more of a choice than JavaScript, because you have Integer available. In JS, Numbers are always floats; at least they're 64-bit, so they would not demonstrate the 19,999,999-vs.-20,000,000 weirdness above (that weirdness starts in the low quadrillions with 64-bit floats, so it's less likely to be encountered than in the low ten-millions) but they still produce all manner of undetected errors with fractions.