Can concatenated fields be read/used?

Anonymous
Not applicable

Can concatenated fields be read/used?

Our database has two payment history fields. The first has six fields and shows the counts of people paying after the due date. The second has 24 fields and shows the payment amounts over time. We would like to conserve the number of custom fields and our database can concatenate all the fields into one.

For example, 000125 000024 000009 000000 000000 000000 is one field and shows 125 on time payment, 24 within a 30 day window and 9 within 60 days.

Can Marketo logic be written to answer this query: look at the 3rd set of numbers and send to people with 5+ 60-day late payments?

Thanks,

Carol

5 REPLIES 5
Anonymous
Not applicable

Re: Can concatenated fields be read/used?

You can do this in an email with email scripting, but I don't believe this will be possible in a smartlist

Edward_Unthank_
Level 10

Re: Can concatenated fields be read/used?

Your operations you can use in that case are the same as you would see on a trigger or filter regarding a field:

Data value is changed, and new value:

pastedImage_0.png

Or, as a filter, that specific field:

pastedImage_1.png

So there are some options as long as you concatenate those fields with prefixed unique identifiers. The problem is you won't be able to do ranges effectively, but instead you'll have to watch for all options via the green plus sign there.

Your values now:

000125 000024 000009 000000 000000 000000

An example of what those values could be:

OTP000125 TDP000024 NDP000009 X000000 Y000000 Z000000

If the above were the values, with each one being prefixed with a unique identifier ("OTP" = "On Time Payment", "TDP" = "Thirty-Day Payment", "NDP" = "Ninety-Day Payment"), you could have a smart list targeting all values of five or more by explicitly calling out each value (can't do ranges when you don't have a number field).

Smart List "Late Payers (90+ day payments of $5 or more)"

"Payment Field" CONTAINS:

  • NDP000005
  • NDP000006
  • NDP000007
  • NDP000008
  • NDP000009
  • NDP000010
  • (etc.)

And you could reference membership of that smart list in your email campaigns (so you're not replicating that logic more than once).

What you're getting into sounds like a good use of custom objects, which might be worth getting into.

Best,

Edward Unthank | Founder, Etumos

Josh_Hill13
Level 10 - Champion Alumni

Re: Can concatenated fields be read/used?

What exactly do you want to do here? The solutions are good, but maybe there's another way if we understood the goal and process behind the question.

Anonymous
Not applicable

Re: Can concatenated fields be read/used?

Hi Carol,

Nice solution by Edward. The choices can be overwhelmingly large as technically you will have to check from NDP000001 to NDP999999. That may not be really required, though in reality

This functionality can be achieved best using Marketo web hooks.

A web hook can be created which will accept following input parameters,

1> This concatenated number

2> Window (You will pass 30days, 60days, 90days and so on for this parameter)

3> Minimum late payments (You can pass value 5 here for your query)

The web hook will then be able to process the information you submitted and return response as

Yes - if there are 5+ late payments in the appropriate set of digits for the window you specified.

No - if there are less than 5 late payments in the appropriate set of digits for the window you specified.

You can configure the web hook call in Marketo to accept this return value in a lead field. And then make the decision to send email or not in your flow action / smart campaign based on that value.

Note:

I understand that your goal is to 'conserve' fields you use. But as per as I know web hooks need a field to return data into. So this might not be a solution for you but still wanted to mention it.

Hope this helps


Rajesh

Anonymous
Not applicable

Re: Can concatenated fields be read/used?

To answer your other question,

In Marketo we can use basic operations such as 'contains' and 'not contains' for a field value.

But we do not have much control to evaluate precise portions of field values in flow actions or smart lists.

We can extract and display portions of field values using Velocity email scripts. But that is only for Emails and only for display of data. The underlying data value can not be changed using Velocity scripts.

Hope this helps.

Rajesh