How to Import a Marketing List without Screwing Up Your Data

Letting marketing managers casually upload lists baffles me. Importing ill-formatted data from a random spreadsheet is a recipe for incorrect lead scores and assignments. If you want qualified leads worked quickly, you have to match data to the existing fields, formats, and picklists in your MAP and CRM. Here’s how I templatize and QA a list import.

In this post:

Use a damn template!

If you only take one thing from the post, this is it: create and religiously use a list import template:

Liaise with Sales Ops to capture the exact fields you need

Do you need Job Title or Job Type? Company Name or Account Name? Lead Source or Lead Source Detail? Don’t wing it!

Instead, meet with Sales Ops and document the fields, picklist values, and formats that power your scoring, sales territores, attribution reports, nurture streams, email tokens, and database health.

In this sample, the org has to capture GDPR consent, UTMs, and Lead Source Detail.

Click to expand

Explainer tabs, tooltips, and data-validation rules are your friends

The list import template sample above is explicit about what the case-sensitive(!) options are, and forces importers to use true/false instead of yes/no or 1/0.

This same template has a tab that spells out how Countries, States, and Territories get populated in Salesforce. To a casual list-importer, the nuances of District of Columbia vs. Washington, DC vs DC vs D.C. will be lost. Make the template foolproof.

Replace workflow steps with columns

Chances are your marketing automation does a bunch of things during an import:

capturing UTMs
setting Lead Source and Lead Source Detail
setting campaign status (like “attended”)
capturing consent preferences
manually assigning or scoring leads

Instead of running a big list through all those processes, simply add them as columns. It speeds up the import and reduces the processing load.

Create video tutorials

Most non-admins simply won’t read or reference 30-pages of screenshots and size 10 font. Instead, create a series of 2-minute video tutorials (and embed them in the template for extra credit):

How do they use the list import template?
How do they de-dupe? Which is the “winning” status?
Why is data normalization important?
Which columns require specific values or formats?

We use Screencastify for these. Taking 30-minutes to film and organize video tutorials saves hours of training with campaign marketers.

Embrace QA-before-import

If you have lots of casual list-importers, consider forcing them to first review their cleaned list with your admin. I promise spending 5 minutes reviewing the columns and demanding missing requirements is faster than cleaning up errors after import.

At the very least, create a list import QA checklist that checks:

Required fields are populated
Columns are in the correct format (this is especially important for phone)
Data matches the picklist values
Emails don’t contain spaces
The list is deduped

Import in small batches

Okay, this one is annoying, but I import in batches of just 500 contacts at a time. This reduces sync errors with Salesforce, and ultimately saves you time. But, yes, it sucks.

Embrace QA-after-import

List imports are a stark reminder that systems (and the humans that use then) are imperfect. There will probably be an upload or sync error in every list import. You need to QA lists about 45 minutes after they’re imported:

After importing, do the campaign member quantities match across the original spreadsheet, Marketo/HubSpot, and Salesforce?
Create a HubSpot/Marketo smart list called “List QA”. After importing, filter for MAP campaign members who are not Salesforce campaign members.
Review in Salesforce:
1. Did everyone score as expected?
2. Are funnels stages correctly set?
3. Are there data holes (such as account name or UTMs)?

Since databases are big/old, I often catch errors on existing contacts from long before the import. Usually because their state or country doesn’t match the picklist values. Luckily Marketo tells you exactly what the sync error is, so you can go find that field, edit it, and sync it back over.

And if certain sync errors are happening a lot, I refine my list import template to better prevent them.

Helpful Excel formulas when importing a marketing list

List cleanup happens in Excel, and sometimes the scale of the list is daunting. Here are the shortcuts I use to do helpful things like dissociate data or filter:

Vlookup

(=VLOOKUP(A2,$A:$G,2,false))

Vlookup null N/A

(=ifna(VLOOKUP(A2,$A:$G,2,false),””)))

Filter

(=FILTER(A2:A201,NOT(COUNTIF(B2:B201,A2:A201))))

Separate first and last name via Text to Columns

Add 2 extra blank columns next to full name
Select full name column and go into the Data tab
Select “Split text-to-columns” from the drop down

Separate first and last name via formula

FIRST NAME: (=LEFT(A2,SEARCH(” “,A2)-1)))
LAST NAME: (=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1))

A sample of this is:

Full Name	First Name	Last Name
Emily Smith	Emily	Smith
John Doe	John	Doe
Matt Towner	Matt	Towner

Want tips, tutorials, best practices, and other cool stuff delivered to your inbox? Subscribe to the quarterly Sponge newsletter >

Header image from giphy