Pardot Support: Fixing Data Import Problems in Pardot

As Pardot Consultants, we see a lot of questions about how to fix a data mis-match with Pardot and Salesforce and other Pardot data syncing issues.

This is different than the question of making data appear in a new field in Pardot, or questions about why the Pardot and Salesforce sync aren’t quite working as expected.

This question deals with a fairly common data syncing problem where you have data in the wrong field and need to move it over to the correct field. Plus you want to delete the ‘bad’ data from Pardot.

Example of a common data swap in Pardot
Example of a common data swap = City is in the state field, and State is in the City field.

How does something like this happen?

There are many possibilities, including getting bad data in a form submission (Prospect using the wrong field on a form), or getting bad data from Salesforce in the first place.

Most commonly it happens because during an import to Pardot someone accidentally picked the wrong field to insert the data.

Pardot import gone wrong
A Pardot import with the city and state data mapped to the wrong fields.

So it’s fairly easy to see how this can happen. But once the ‘bad’ data is in Pardot, how do you fix it?

How do you clear out the bad data from a Pardot field?

Your first instinct might be to just import a new CSV, and be careful to match up the columns correctly, and also include a new column full of blank cells to overwrite the field you previously mixed up.

And yes, if you just have a couple of swapped fields:

  1. If there is data in every field that you uploaded
  2. And you map the fields correctly
  3. And you select “overwrite existing data”

Then yes, the import is probably the easiest way to get the correct data into the correct fields.

Check the overwrite box during your Pardot import
Just be sure to check the boxes for ‘overwrite existing data’ and it should be fine.

But what happens if you wrote data to a field that should have NO data in it?

For example, what if instead of putting “city” in the city field, you accidentally put it in the “Address two” field?

Data mapped to field that should be blank
Pardot import with City field mapping to wrong field (Address Two).

Performing a CSV upload won’t help you get rid of the bad data because it is not possible to overwrite the bad data by replacing it with a blank during import.

(You can certainly try, but it will not be fruitful.)

Cannot overwrite with Blank during Pardot import
Attempting to overwrite the bad data with a blank CSV file will not work.

Your import will probably proceed fine, and throw no errors. But when you go to check that the data field is now empty … you’ll find nothing happened.

The bad data is still there, which means you need to pursue other options to clear this field of the bad data.

How you tackle this depends on how your fields are set up in Pardot

Scenario 1: When the field you need to clear exists only in Pardot

This is the best possible scenario. If the field exists only in Pardot, then you can simply run an automation rule to clear the field.

Pardot automation rule action to clear field value
Choose the option “Clear prospect field value” from the first box.

Pardot Pro tip: You want to make sure you affect ONLY the prospects who were incorrectly updated. The best way to do that is to go to your import page, and apply a custom tag to your affected prospects. Make it something unique – a tag not used anywhere else in Pardot.

In Pardot apply tag to everyone in the import
Apply a Tag to all your Pardot prospects at once using a Table Action.

Now that every prospect has a Tag, you can create an Automation Rule where you match only prospects with this unique tag, and apply the action to clear their field.

Pardot automation rule clear field value
Pardot Automation Rule to clear a field of bad data.

Scenario 2: When the field you need to clear is synced to a Salesforce field

If the Pardot field is synced to a Salesforce field, then fixing this bad data gets trickier.

The way data syncing (field syncing) in Pardot and Salesforce operates is under the belief that ‘any data is better than no data.’

This effectively means that any time one system has data (the field contains any data) and the other system has no data (the field is blank), then the system with data pushes to the system with no data.

Data pushes to system with no data
Data pushes to system with no data

Which works perfectly fine when you have something like Pardot forms collecting the prospect’s Job Title, and Pardot pushes the new data up into Salesforce to complete the prospect profile.

But it’s a big pain when you already have data in both systems, and then want to clear the field. The problem is that the two systems keep pushing data back and forth, putting data into your recently cleared field.

What does this look like in practice?

  1. Let’s say your Salesforce field is blank, and the Pardot version has data. Pardot will push data up into Salesforce.
  2. Now you go wipe that field in Salesforce
  3. Next time the systems sync, Pardot will push the (bad) data back into Salesforce.
  4. You keep wiping out the field in Salesforce, Pardot keeps pushing it back.
  5. Over and over.

Next you think that you should be able to fix this behavior by changing the Pardot field settings – there is an option on each Pardot field that says ‘In the event of a Data Conflict’ which system do you want to win?

  • Salesforce wins
  • Pardot wins
  • The most recently updated record wins
Pardot field settings for data conflicts.

It seems like if you set this to ‘Salesforce wins’ then clearing the field in Salesforce should finally work, right?

Nope.

Remember how I said Pardot and Salesforce operate under the philosphy “any data is better than no data” ? Well, same thing here. Pardot has some data. Salesforce has none. Therefore the system with data (Pardot) pushes it to the system without data (Salesforce).

Pardot and Salesforce data push
The system with data (Pardot) pushes it to the system without data (Salesforce).

You try to clear one system. The other system pushes the data back. Over and over again.

And all your efforts to clear it were in vain.

So the underlying question: How to actually fix this data syncing issue permanently?

Option 1: Disconnect Your Salesforce Connector (Stop the Sync) and Reconnect

By disconnecting your Pardot Salesforce connector, you can now go clear the field in both systems.

Pardot and Salesforce Connector Syncing
Disconnect the Pardot Salesforce Connector to stop the back and forth syncing.

That handy Automation Rule described above will work great for wiping out the Pardot field, and for Salesforce your Salesforce Admin can clear it out for you (probably with Data Loader).

Once the field is empty in both systems, go ahead and re-connect your Pardot Salesforce Connector.

Salesforce Pardot Connector Syncing again

Now that there is no data in either system (the field is blank in both systems) they stop pushing data back and forth. (And peace returns!)

Option 2: Ask Pardot Support to Enable a Special Data Syncing Setting

This is not a setting that I recommend often, but some companies perform large-scale data cleansing efforts from the Salesforce side of the operation, and need to stop Pardot from ‘helpfully’ adding data back into Salesforce.

If this is you – if you frequently need to update data in Salesforce and need Pardot to stop pushing data into a newly-emptied field – then you do have one more option.

Open up a support ticket with Pardot Support, and ask them to turn on a special setting for you.

This setting doesn’t have an official name (as far as I know) but it allows you to do something special.

You can read more about this setting to have Pardot respect Null values from Salesforce at the documentation here, but the key line from the article is this:

There is a Salesforce Connector setting to allow blank or null values in Salesforce ‘win’ over existing Pardot values.

With this special data syncing setting, Salesforce null values overwrite Pardot
With this special data syncing setting, null values go one way only: from Salesforce to Pardot.

In plain English, this means that with this setting turned on:

  1. When the field is set so that ‘Salesforce wins’ (see earlier picture)
  2. When you clear that field in Salesforce
  3. This now-empty field (null field) in Salesforce will sync down to Pardot
  4. And Pardot will respect the empty field and ALSO clear the field.

Be Cautious of this Setting

Like I said, this is not a setting that I recommend often.

It seems really great, but the caveat here is that you cannot pick and choose which fields this affects. This is a GLOBAL setting for all Salesforce fields.

Need some help with data problems in Pardot?

There are so many things that can go wrong with your data syncing in Pardot Salesforce, and this barely scratches the surface. If you need help figuring out the problem with your data in Pardot we can provide Pardot support to help you solve it.

Type:

Share this:

Contact Us