Newbie Q: “Where do I click to see X?”
— the least happy type of Community thread
New users may search in vain for a log of all Filled Out Form events plus point-in-time form data. (That is, even if field updates were blocked or values were later overwritten, you see the values that were on the form when each person clicked Submit).
To be clear, it's not correct to say that Marketo doesn't store the original data from form posts: in fact, the REST Activity API includes historical POST payloads, including since-overwritten values. But it's true that it's not easy for a non-developer to see form data history across leads.
When seeking such a report outside of Marketo, users tend to say “a Google Sheet” (as opposed to XLS/CSV or other onine workbook). I'm not a Sheets user myself unless forced, but I know how it's done, and it's really easy. So that's what we'll use today.
Prereqs: A Google Form and backing Google Sheet
I'm going to assume you know how to set up a Google Form that saves responses to a Sheet. (If not, learn how — it's easy — then come back!)
Set up a placeholder form that includes email address and the fields you want to record from Marketo (it can be a subset of the fields on the Marketo form). Don't worry, no one is going see this form: we're going to call its post endpoint in the background to store the Marketo data.
Get the auto-generated field names
Google generates random names for each of your fields. (Other form builders do this, too, and I absolutely hate it. Marketo forms do not mangle the <input> names, and that’s a godsend.)
There’s a trick to get the field names, each of which starts with the string entry. followed by a number.
Go to the 3-dots menu and select Get pre-filled link:
Fill in the form with placeholder data, then click Copy Link:
The resulting URL will have all the field names as query params, like:
.../viewform?usp=pp_url&entry.1413462437=Option+1&entry.129283687=Sandy&entry.878123412=Whiteman
Those entry.nnnnnnnnn params are your field names, in the order they’re displayed.
Get the form’s POST endpoint
Now go to the the Send button and copy the link to the standalone form.
That'll take you to a standard page like this:
Open up F12 Dev Tools Console and paste this one-liner (you could poke around in the HTML if you prefer):
console.log( 'Endpoint: ' + document.forms[0].action );
You'll see an Endpoint value:
Leave this tab open. You're going to paste the Endpoint into the Marketo UI when we set up a webhook. After that, you're going to take the field names and plug them into some Forms 2.0 API JavaScript code.
But let's first make sure your Marketo form is ready to go.
Create a Marketo “superfield” and get its form field name
You need to create a single Text type field (on the lead/person, no need for CRM sync). Here I'm using one of my multipurpose fields, testTextArea01 (c’mon, I can't be creating a new field for every blog post!):
Go into Form Editor, where you should already have your form set up. Temporarily add your new “superfield” as a hidden field. You can delete the field from the form afterward, but adding and inspecting it (in Dev Tools) is a good shortcut/sanity check to know what the exact field name is. Copy out the for value of the <label>. Here it's testTextArea01:
Like this:
The Webhook Side
Go to Admin » Webhooks. Time to create your (first?) webhook.
The webhook URL is the Endpoint you discovered above. In this example, the URL is
https://docs.google.com/forms/d/e/1FAIpQLSclNww40BK2oUeeOZplB0bqtQHbDFPH6OwUcfIohWSo78f-xw/formResponse
The webhook Template is the {{lead.token}} for the superfield. That's it, that's the entire Template. You can browse for it by clicking Insert Token.
Leave the Request Token Encoding and Response type at None. Don’t worry, the payload will be 100% correct for all values, since we did the pre-encoding on the client side.
And one more thing, annoying but simple enough. You have to add a custom header to the webhook definition.
That's Header Content-Type with Value application/x-www-form-urlencoded. (If you're curious, you have to add this because we're pre-URL-encoding the form data in the browser, so even though you selected None above for encoding, the data actually is encoded.)
The Forms 2.0 JS Side
Finally, the client side. We're going to map the Google Sheet field entries to our underlying Marketo Form field names. Aside from the hard-coded emailAddress field, Google generates random field names (entry.{random}) per form (as do a lot of form builders) but with that little script you ran above, you showed the real name that corresponds to each random field.
-
Enter the translation between Marketo fields and Google fields in the
mktoSheetsTranslateMapin the code that follows. The left-hand-side (keys) are the Marketo field names, the right-hand-side (values) are the Google field names. (It'll make sense when you look at it!) -
Set the
mktoPostDataTransportvariable to the name of your Marketo superfield.
That's it for the form code. You don't need to edit anything below the usual --- LINE! ---
(function() {
var mktoSheetsTranslateMap = {
'Email': 'emailAddress',
'FirstName': 'entry.684262398',
'LastName': 'entry.213821674'
},
mktoPostDataTransport = 'testTextArea01';
/* --- NO NEED TO EDIT BELOW THIS LINE! --- */
MktoForms2.whenReady(function(form) {
form.onSubmit(function(form) {
var currentValues = form.getValues(),
hiddenFields = {};
/* accumulate Mkto field values, translate field names w/Sheets map, URL-encode */
hiddenFields[mktoPostDataTransport] =
Object.keys(mktoSheetsTranslateMap)
.reduce(function(prev, next) {
return ( prev ? prev + '&' : prev ) +
[mktoSheetsTranslateMap[next],encodeURIComponent(currentValues[next])].join('=');
}, '');
form.addHiddenFields(hiddenFields);
});
});
})();
And the Smart Campaign
You might have figured this out already, but you also need a trigger SC:
And that’s — finally — it!
What about rate limits?
You tell me, I guess? 🙂 I haven't been able to find information on the posts-per-minute or posts-per-minute-per-source-IP allowed by Google Forms. I know Google Sheets has a limit of 2 million cells in a sheet, which should be… just fine!
Add’l note from a friend
In the comments, reader Kat mentioned an interesting case they’d solved for:
I ended up having a special use-case where the Google Form in place used section breaks, which left me only able to fill the first page’s fields. Because of the difficulty I had finding the solution, I wanted to share it here. This can be overcome by adding a hidden Marketo field with the number of sections in the Google form, starting from 0 - so if there are 4 pages, then the value would be “0,1,2,3” — and then mapping it in the
mktoSheetsTranslateMaptopageHistory.
.png)