Salesforce Marketing Cloud Data Import Basics

The Importance of Salesforce Marketing Cloud Data Import

High quality and accuracy are the cornerstones of Salesforce Marketing Cloud data import.

Storing the right data is the fundamental ingredient for your business to be able to leverage SFMC.

Today more than ever.

Without the right data in SFMC, nothing else makes sense. Creating complex journeys, omni-channel customer experiences or even a data model depend on having the right data stored in Salesforce Marketing Cloud.

In this guide, I’ll help you understand how data import works in SFMC, to ensure you master and consolidate this foundational step for your business.

Understanding Salesforce Marketing Cloud Data Import

In a nutshell, SFMC works as a relational database.

You create tables (called “Data Extensions” in SFMC), and then you relate those tables so that you can leverage them later in all the different studios and modules of the tool.

A very basic example of a Data Model could be made with the following Data Extensions:

• Customers
• Orders
• OrderItems

Three tables. Three objects for your data model: your customers, their purchase data and the different order items for each of those purchases.

Now, you need to import data into those tables.

There are multiple ways you can populate a Data Extension with data in SFMC:

• SFTP File Imports
• Manual file imports
• API calls
• SQL Queries

In this guide, whenever I speak of Salesforce Marketing Cloud Data Import I’ll describe the data principles for the first two: 1) SFTP File Imports and 2) Manual File Imports.

This is so because API calls and SQL Queries are relatively different methods of populating a Data Extension.

SFTP File Imports

Process Overview

The way in which this data import method works is the following:

1. Every SFMC Business Unit comes with a dedicated SFTP.
2. Business generates the marketing data. Depending on the source, this can be a system like PowerBI, for example.
3. The generated data is transferred as files sent to the SFMC SFTP. Frequency can be daily, hourly, etc.
4. An Import Activity in a SFMC Automation Studio automation is triggered, picking up the file from the SFTP.
5. The data gets imported into the corresponding Data Extension.

Creation and configuration of the Import Activity

To use this method, you will need to create both an Import Activity and the corresponding Automation for that import in SFMC Automation Studio.

To create your Import Activity, go to:
SFMC > Journey Builder > Automation Studio > Activities > Import File > Create Activity

Now let’s look at the configuration options in the Import File activity.

Activity Info Tab – Name and Notification Email

In the Activity Info tab, you first need to set a name for your import activity.

You can also set up one email address for the notifications of this activity.

Import File Tab – File Location

Under Import File, first you’ll need to select the File Location.

A File Location is the SFTP relative path where your file is, and it has to be created first so it can be selected here, in the Import Configuration.

An example of a File Location can be the following SFTP path:
Import/SFMC_IMPORTS/ExampleSubFolder

You’re telling the import to look for the file in that path and not others.

Import File Tab – File Naming Pattern

Then, decide a File Naming Pattern so that your Automation knows what file name to look for in the SFTP File Location.

Here, it is a common best practice to use wildcards for timestamps in your File Naming Pattern. Wildcards are written with the date part name between two % signs.

For example:
SFMC_ExampleFile_%%Year%%%%Month%%%%Day%%.csv

This way, the import will replace those wildcards with the timestamp of the file name.

Import File Tab – Delimiter and Date Format

You may also set up the Date Format and Delimiter of your file in this tab.

For the date, just select the date in which the date columns of your file are (e.g: English United States).

For the Delimiter, there are three options available:

– Comma (this means your CSV file is a comma separated value file)
– Tab (this means the delimitir is a tab)
– Other (here you can set up others, such as a Pipe: |)

Destination Tab – Data Extension or Subscriber List

In this tab, you need to select the target destination of your data import.

You can select either a Data Extension or a Subscriber List.

Mapping Tab – Data Action

In this tab, you have to configure the Data Action of your import.

That is, what data action should the import perform when inserting the data?

For the Data Action, there are 4 options available:

1. Add Only
   The import will only add new data to your destination.
2. Update Only
   Only update current data that already exists in the target.
3. Add and Update
   If matching data is found, then update it. If not found, then insert the new data as well.
4. Overwrite
   Replace or overwrite the existing data in the target with the new data coming in the file.

Mapping Tab – Map

Mapping refers to how SFMC knows what columns in the source file correspond to what columns in the target DE or List.

There are 3 mapping methods that you can select:

1. Map By Header Row
   This option assumes that the headers in your source file perfectly match those in the target destination. The file headers should be the same as in the target.
2. Map By Ordinal
   This method allows you to select the order of the columns to be matched. E.g: the first column corresponds to CustomerID in the target Destination. The second column corresponds to CustomerKey, and so on.
3. Map Manually
   With this method, you need to provide and upload a sample file and then map the columns to your target.

Manual File Imports

This type of Salesforce Marketing Cloud Data Import is the simplest one.

Unlike the SFTP method, you don’t need to create an Automation for this import to be used.

Process Overview

1. In SFMC > Email Studio > Interactions > Import, you create an Import.
2. Define the properties of your import (data action, destination, file location, file naming of the source file, etc).
3. Prepare your file and once ready, drop it in the corresponding SFMC SFTP File Location.
4. Go to SFMC > Email Studio > Interactions > Import > YOUR IMPORT and start it to execute the data import process.

Create an Import – Properties

In the first section of the import creation, the only required field is the Name of your import.

The External Key, as any other activity created in SFMC, will auto-generate, and the description is optional.

Create an Import – Source

For the Source section, there are several things you can define:

• File Location
  Like in the SFTP Import option, you need to define the SFTP File Location in which the import will try to find the import file.
• File Naming Pattern
  Define the name of the source file so the import can find it. Wildcards are allowed here as well. Wildcards are written with the date part name between two % signs:SFMC_SampleImport_%%Year%%%%Month%%%%Day%%.csv
• File Type
  Just like in the Imports created in Automation Studio, the three options are:
  – Comma
  – Tab
  – Other
• Destination Type
  You may select either a Data Extension or a Subscriber List
• Subscriber Import Type
  This is a required field if you’re importing into a Subscriber List. The only option available is Email.

Create an Import – Import Action settings

In the same way you configure the Import Actions in Automation Studio, here you can define:

• Update Type
  Add Only
  Add and Update
  Update Only
  Overwrite
• Import Qualifications
  Whether to skip rows with bad data or respect double quotes (“) as a text delimiter.
• Validation
  Choose the Date Format of your source file.
• File Mapping
  Map using columns headings
  Map by ordinal
  Map manually

Create an Import – Import Completion settings

Here you can define an email address to which the notifications will be sent.

Create an Import – Starting the import

Once you have created and defined your import activity, you just need to go to:

SFMC > Email Studio > Interactions > Import > [YOUR IMPORT]

and click on START when you select yours.

Troubleshooting and Data Validation Errors

It is very common to encouter data validation errors when first importing your data into Salesforce Marketing Cloud.

Even if you have correctly configured your import activities.

Here are the most common reasons:

Field Mapping Errors

The mapping between the data schema in your source file and the destination is incorrect.

Some common causes:

• Your import file does not have some columns that are required in the destination Data Extension.
• The name of the headers in the source file has a typo or is different from the column name in the target.

Invalid Data Errors

The data in your source file is invalid due to an error.

• For example, a date field contains text values or viceversa.
• An email address is incorrectly formatted.
• Decimal values come with more digits that you defined in the target Data Extension data type.
• The field length is larger than the one set up in the target Data Extension for that column.

File Format Errors

If the file format is not supported by SFMC, the import will fail.

For example, make sure that your source file is a valid CSV file.

Duplicate Record Errors

If the source file contains duplicates and the data action is not the correct one, the import can fail.

Ensure you have selected the correct data action and always account for deduplication to prevent data loss, errors, etc.

Encoding Errors

Having a different character encoding can led to issues with imports. Always check that you’re using UTF-8 encoding to prevent these.

Further Reading

Check out the following resources for further documentation on Data Import:

Salesforce Marketing Cloud – Import Activity
Salesforce Marketing Cloud – Import File Activity
Salesforce Marketing Cloud – Import Data into a Data Extension
Salesforce Marketing Cloud – Data Types in Data Extensions
Salesforce Marketing Cloud – Error Import Failed during Import Activity