List upload - fixing wrong values in bulk?

[Noam13]

Hi all,

Hope to find a solution with the help of the experts here 🙂

I'm looking for a tool that can help with the following issue:

I'm getting a list of new leads from my marketing managers to upload to Marketo,

the list wasn't created via a Marketo form so the values I have in the CSV file are not matching the values I have in Marketo for these fields, for example - the "Country" field might come with a value "Korea", while the value for "Korea" in Marketo is "South Korea" - this prevents the lead from being uploaded obviously 

This issue might occur on other fields like "state" that might be wrong, or an email address that has redundant characters - I'm looking for a tool or an excel formula that can help me either:
1. Indicate all errors in the file for a manual adjustment
2. or even better - can identify and automatically replace the wrong values with the correct ones so from there I can simply take the list to Marketo and upload it

How do you guys manage with list uploads where values are not matching to Marketo and prevent the sync?
Is there something or only manual work?

Thanks in advance! 

[Darshil_Shah1 · Accepted solution]

I don't know if you really need to purchase a third-party platform for this (as there would be an extra cost associated with it and on top you'd need to make sure that the platform is secure enough for handling the PII that you'd be supplying). You should be good with the excel formulas to cleanse/normalize your list import sheet before importing people into Marketo. Along with that, I'd also recommend creating field monitoring smart lists to catch people with non-standard values (i.e., non-empty values other than those in the pick list valid values) in your database. Additionally, you should also consider creating data normalization campaigns to update the records with non-standard field values to valid ones.

Also, having non-standard values in the fields (of course, with the correct format) should not prevent you from importing people in Marketo (IMO), rather you would be running into errors upon trying to sync those records with CRM. Hope this helps! Let us know if you have questions. 

[Noam13]

Thank you for your reply,

Yes - the issue is the sync to the CRM later on if that wasn't clear 🙂

Is there an excel formula you can recommend that will at least help identify the errors in the excel file before we upload the list? As of now the only way we have is to check each row manually to make sure the data values are correct, and of not so to change them manually.... I'm looking for a solution to automate things (at least some of the process)

[Darshil_Shah1]

Well, you can create a list import template excel file with standard/normalized values for all the fields in one of the tabs. You can then use these standard values to see if they're matching or not with the actual values of records in another tab of this spreadsheet (highlight if they don't match, so you know which values you'd need to update). If you have a list of non-standard values as well, you can automate the process of updating the respective non-standard value with the corresponding standard value too. Unfortunately, I don't have a sample list import template file handy that I could share right now.

Also, most users set up normalization campaign setup and ensure that these campaigns process new/updated people first before they are synced with the CRM (great use-case for executable campaigns).

[SanfordWhiteman]

Here’s one place where I think people would benefit from using SmartSheet instead of to Excel. SS has an easy “limit to dropdown values only” option for columns. You can create 2 columns, one unlimited with the original values and the other limited to specific values. Try to paste column A into column B and the invalid values will be skipped.