Looking for suggestions for lead list loading and data transformation tool

[derelict_wombat]

Question for the hivemind

What tools to do use to prep files (data transformation, standardization, etc) before loading a lead list into Marketo? 

I know a lot of people rely on Marketo smart campaigns to do data normalization but my concerns with that approach is that if there are spelling errors in the file then the data won't be normalized correctly which in some cases can and will cause sync issues between Marketo and SFDC.  Goal is to try to clean up as much of the data before loading into Marketo to prevent this. 

Example scenario: Lead file from a conference or tradeshow may have fields below: 

• First Name
• Last Name
• Email
• Country
• Company
• State
• Role
• Title
• Product of Interest

Some fields like Country and State in your system need to align with dependent picklist values from CRM so for example United States needs to transform to US and have 50 valid state values that are also 2 letter codes(New Hampshire = NH).   

Other fields such as Role and Product of interest are picklist values and need to match to API values in CRM [Role field value of Manager gets transformed to 0204 to map to a CRM field. 

Due to the large number of marketing activities its not scalable to review each file for spelling errors and formatting before loading. Would love something that can do some of these data transformations or at very least scan the file to make sure values are formatted correctly prior to importing. 

What tools do you use for data prep? Did you develop your own(ie python dataprep script) or use a vendor? 

For context we have been using a vendor for a few years but considering moving on due to cost and complexity of the application. 

[SanfordWhiteman]

We use a single-page app (literally hosted on a hidden Marketo LP as it doesn’t need a back end) that does validation/transformation using the PapaParse CSV library and a bunch of cool tricks.

You upload the original file to the app (it’s all done in the browser, so it’s more just getting a handle to the file) and then download a response file when it’s done.

[derelict_wombat]

Thanks @SanfordWhiteman great suggestion.  Assuming this SPA doesn't have storage so no worries about PII right?  Also in your app did you build in the ability to "fix" bad data (ie highlighted fields with drop/downs to fix?) or do you identify errors on the exported file? 

[SanfordWhiteman]

---

Assuming this SPA doesn't have storage so no worries about PII right?

---

Exactly, that was one of the drivers.

---

Also in your app did you build in the ability to "fix" bad data (ie highlighted fields with drop/downs to fix?) or do you identify errors on the exported file? 

---

We didn’t, in most cases it writes the errors + details to additional columns in the exported file. There are some “known aliases” that get transformed on the fly, though, and some columns have regex search/replace built in.

The next step up would be an inline editor. But that’s where you start incurring a lot of complexity, because the most usable in-app spreadsheet editors (I’ve used DHTMLX Grid for other projects) have a steepish learning curve to insert custom context menus, write back to the underlying sheet, etc. It made more sense to have editing be done in Excel per the Errors column.

[Michael_Florin]

My former employer built this as an Eloqua app: https://www.likereply.com/accelerators/contact-upload-tool/

So not only does it do data validation, it also uploads to Eloqua via API. The same tool exists as prototype for Marketo, but it doesn't have a fully fledged UI yet. You can book a demo with the guys if you want to see it.

[derelict_wombat]

Thanks @Michael_Florin Definitely will check this out!