Getting JSON into marketo

[Anonymous]

Hi there, looking for some help. Hopefully the below makes sense from a non-technical marketer..

Goal: Have the ability to token data into a marketo email that has been passed from a JSON file on form submission.

Situation: On a non-marketo form submission we will have a web API that will be passing form data into marketo, however along with normal field data, the form submission includes a JSON file that has more complex data - two levels.

Question - it is possible for Marketo to unpack a JSON file with nested data into marketo fields? Or does it need to be passed to a CRM to do this/sync back a simple single level data back to Marketo. Or is there an alternative method altogether?

[SanfordWhiteman]

You should move the Q to Products​ as it's firmly product-specific ("Central" is supposed to be for general marketing topics.)

If you simply want to output JSON stringified data, then you can absolutely do this using a Velocity script. You don't have to parse the string into multiple fields. I've written extensively about Velocity techniques like this on my blog: see the tagged posts @ http://blog.teknkl.com/tag/velocity.

[Anonymous]

Thanks Sanford, have moved this to the Products section. So just to clarify would the JSON file (that has been created from a form submission) need to go into Marketo? Or would this data get extracted from the JSON via velocity scripting outside of Marketo.

I dont really understand that process - ie JSON file that exists outside of Marketo, and how that data would get into Marketo?

Cheers,

Josh

[SanfordWhiteman]

Thanks Sanford, have moved this to the Products section. So just to clarify would the JSON file (that has been created from a form submission) need to go into Marketo? Or would this data get extracted from the JSON via velocity scripting outside of Marketo.

Well, when you refer to it as a JSON file, that's very different from a JSON field.

See, a Marketo textarea field can happily hold JSON strings (they're just strings, so there's no reason for any problem). Then other technologies (Velocity, JavaScript) can read those strings. So if you can write JSON to a field, you're all set (that writing could happen via either form post or API call).

But if you mean your JSON is in a separate file, like a standalone filename.json file saved on a remote webserver, you have to do something to bring that onto a lead. A webhook can pull the remote file onto a lead, to be sure: that's about the easiest possible kind of webhook, a 'hook that fetches a JSON file on a remote box and then spreads the results back into Marketo fields.  But Velocity can't read the remote file directly: Velo can only read the contents once they've been put into lead fields. And you also can't run a webhook against a lead that doesn't exist yet (i.e. webhooks are fundamentally for enriching lead data, but the lead has to be in Marketo first, even if all its fields are empty).

Perhaps you should explain in more detail what happens when a form is submitted to your external system. Does the data just sit in the external database? It is truly stored as JSON in the db, or is it stored as rows + columns?

[Anonymous]

Hey Sanford,

Hopefully this might make things a bit clearer.

When a form is submitted all related JSON data is captured and stored in the CMS database. Data is stored to individual column holding string value for either individual value or Object. The CMS (EPI) actually has a Marketo API connector, so based off what you have said, it sounds like it is possible for Marketo to hold the JSON string fields if it can get extracted out of the CMS database. Potentially by a Web API?

Thoughts?

Thanks for your help, really appreciate it.

[SanfordWhiteman]

Oh geez, the epiServer connector. A DoSser's best friend... they have no concept of API limits at all over there.

Anyway, it's still not completely clear. If you say the JSON data "is stored to individual column" you mean a single text (i.e. CLOB/BLOB/VARCHAR(MAX) column?

Either way, the bottom line here is you need some C# here to safely manage the connection with Marketo.  Whether it's being read from a single SQL column and pushed into a single Marketo field, or read from a single column and shredded into multiple fields, or read from multiple columns and assembled into a single Marketo field, whatever.