In this article lets understand some of the nuances involved while loading data from External systems into the Marketing Cloud. Some of the questions that are always asked include how long does it take to load data into Marketing Cloud. As part of the Import process, any data file that exceeds 20 Mb has to be loaded through the File Transfer Protocol(Ftp) process.

Extraction:

The first step includes Extraction of data from sources such as Teradata/Oracle/Vertica into your internal Ftp site. This could take anywhere from few mins to a few hours depending on the file size. In general, always split the file into multiple files if it has got more number of records. It is advisable to have 6 million to 8 million records in each file and the size would be anywhere from 2 gb to 2.5 gb per file. This extraction process/time depends on the environment you are generating. If you are generating in Unix box it could take more time compared to generating in Amazon S3 server. On an S3 server it could take 1 hour to generate 10 files with 80 million rows(8 million per file).

File movement:

After the extraction, We should move the file from our ftp server to SFMC site using Filezilla/Mft process depending on the organization’s internal protocols/process. Depending on the session configuration of Filezilla/Mft process it could take an hour to transfer 20gb of data from Internal ftp server to SFMC. There might be limitations on a number of files that could be transferred with each run(3 to 5 files) depending on the organization’s settings/internal process.

Processing in SFMC:

Once the files are moved to SFMC from our internal ftp server, Run the import process to load data into desired Data extensions. Alternatively, we can schedule the import activity to occur once a file is dropped into SFMC. We can map the import activity to load data into Data extensions based on the Column header of the incoming files. This import activity could load 30 million records into data extension in 60 mins. You could monitor this file runs in Automation Studio.

Parallel Runs: SFMC does not support the parallel running of multiple files into the Same Data Extension. I assume it might be having a table lock while one of the file is processing. One Great news is SFMC supports the parallel running of files into different data extensions at once.

Frequently Asked Questions:

1. Can we have one large file instead of multiple files during extraction from Source?

Ans. You could definitely have one large file instead of multiple files. We have extracted 50 gb file and transferred it to SFMC

2. Can we reduce FileZilla/Mft processing times by using smaller files?

Ans. Its advisable to move smaller files quickly through FileZilla/Mft process rather than huge files. It took 3 hours to move 50 gb file(single file) from our internal ftp server to SFMC ftp server

3. Is there any better way to optimize  Extraction and filezilla/mft process?

Ans. In general filezilla/mft process could be designed to look at specific file formats instead of picking all the files(complete/incomplete) from the specified folder. One approach could be generating the file with a different file format so that filezilla/mft process will not pick the file until the file is complete. You could rename it once the file extraction is complete. While mft process is initiated to move to sfmc then start the extraction of other files with a different name and rename this file once the mft process has completed file transfer to sfmc.

4. How long it takes for sfmc to load data into data extension?

Ans. It could load 30 million records in an hour. This could vary based on number of columns

5. Can we load multiple files into Same Data extension in Parallel?

Ans. No, sfmc does not load multiple files into same data extension in parallel. It will queue up and load one after the other

6. Can we load multiple files into different Data extensions in Parallel?

Ans. Yes, sfmc loads multiple files into different Data extensions in parallel.

7. Can we work with Account Executive or Success Manager to Expedite the loads into Marketing Cloud?

Ans. No, Success manager could not expedite your loads into Marketing cloud.

8. Can we monitor files loads at SFMC?

Ans. You can check the file progress in the Automation studio. It will display file status/success/ Errors.

9. How can we view error records in SFMC?

Ans. You will receive an email notification once the import activity is complete with Record counts and attachment in case of errors. Configure the notification to be sent to the Distribution list instead of a single person.

10. Do you suggest partial loads before running full loads?

Ans. It is always advisable to test your file run times and data validations with partial loads(5 percent data) instead of Full loads. Your learnings from partial loads will help you save much more production time(Saving time during Extraction/Load)Note: There are many factors involved while processing files and data and run times provided here are for guidance purposes only as it could change from organization to organization based on network/server capability/Number of columns in a file/etc.

In this article lets go through some of the common data import issues that we encounter while loading data. These learnings will help you avoid Import pitfalls and save your time during development or importing of data into Data extensions.

Issue 1: We try to set the field as non-nullable in our data extension and try to import data with null values.

Solution: Before quickly changing the field value to nullable on data extension to fix the issue, analyze your data or business use case if you are ok loading the field as null or populating the data.

Issue 2: No such column name exists in Data extension

Solution:  This is one of the most common errors that is encountered by customers. While importing data you might have selected the data extension field to be mapped based on the header of the file. If there is a column mismatch on your header then you will see this kind of error. Fix your header and re-import the file.

Issue 3: Date Format Issue

Solution: Marketing Cloud supports a wide variety of Date ranges. Go through below article to see supported date formats

https://help.salesforce.com/articleView?id=mc_overview_supported_date.htm&type=5

Note: Times shown in the journey and triggered send tracking information remain in CST regardless of setting.

Issue 4: Date Time field in Marketing Cloud

Solution: In the Marketing cloud, the Date field supports both Date and Date time values. If you specify Date as January 1st, 2019 then MC defaults to 01/01/2019 00:00:00. It would be confusing initially as we have both Date and Date time fields in Sales cloud/salesforce

Issue 5: StartDate issue when bringing in Data to Marketing cloud from other systems

Solution: When you are bringing in data from other systems we commonly have the start date as 01/01/1111 if the component system does not support null in date fields. In Marketing Cloud the Start Date will start from 12/31/1772 00:00:00

Issue 6: EndDate issue when bringing in Data to Marketing cloud from other systems

Solution: When you are bringing in data from other systems we commonly have the End date as 01/01/00 if the component system does not support null in date fields. In Marketing Cloud the End Date could up till 12/31/9999 23:59:59

Issue 7: “Import Validation Error: CaseSensitiveSalesforce Error Id” while loading data into Data extension

Solution: If you have one of the columns starting with Salesforce object id (001/002/003/etc) and the length of the column is 15 digit then you get this kind of error even though it may not be a Salesforce id.  To turn off this validation work with support or add MC as a prefix to records while you work with a support team member.

https://help.salesforce.com/articleView?id=000312821&type=1&mode=1

In this article, we will learn various ways to load data into Data Extensions and some of the use cases surrounding this. We are using Customer_Form Data Extension that we have created in an earlier article to Load data in the Marketing cloud.

Let us understand various ways to load data into a data extension.

1. Manually Creating Data into Data Extension

2. Import Data into Data Extension

3. Schedule file imports into Data Extensions

4. FTP into Data Extensions

Manually Creating Data into Data Extension:

This process is mostly used when we want to have validate /test our email template using test data that are loaded into Data Extension. For this use case, we will try to manually create records in Data Extension.

Navigation: Login to MC –>Audience Builder –> Contact Builder –>Data Extensions –>Select Desired Data extension/Create Data Extension –> Click on Records.

As said earlier, we are using Customer_Form Data extension to load data. Click on “Add Record” as shown below in the screen.

Enter desired attributes and non-nullable fields into the record before clicking on save. See below screenshot with Sample data created manually in Customer_Form Data Extension.

Import Data into Data Extension:

We will always have business scenarios to load huge volumes of data into Data extensions. These use cases can be achieved by using the Import process in Marketing cloud. You can load multiple records from csv/spreadsheet into Data extension using Import functionality.

In the above(image) Customer_form data extension there is an Import button next to search and above the records. Click on the import button and select Import into Data extension and click ok.

You will be redirected to upload the file as shown below. Drag and drop the desired csv file to upload the file.

Once you upload the desired file then select the Delimiter type as comma and Import type as Add only/ Add and Update/Update Only/Overwrite. For this demo, we are selecting Add only to add the records to data extension. Click Next and it will take us to Configure Mapping screen. You can use Map by Header Row or Map Manually to map the fields on your data extension to csv file as shown below.

Click on Next once you map all the fields and it will redirect us to Review & Create screen. The marketing cloud gives us the ability to notify over email once the upload of data is complete. Click on Finish and you will be shown an Import progress status bar. As part of Customer_form.csv, We have uploaded 4 records into Data extension.

Note: Import process supports files with a maximum size of 20 MB. Anything beyond 20 MB file requires us to use FTP Process which we will cover it in a different article.

In the previous article, you learned how to access Data extension from Email studio and Audience builder/Contact builder. Let’s Navigate to Data extensions from Email Studio for Creation of Sample Data extension.

Navigation: Click on Email Studio and select Email from the Dropdown. After Selecting Emails then navigate to Subscribers dropdown and click on Data Extensions.

Based on your role in the Marketing cloud you will have the ability to view available Data extensions or create Data Extensions by using create a button on the top right corner. You can see the screenshot below for visualization purposes.

Click on Create and Select Standard Data extension for time being and let us fill other details such as Creation Method(Create from New), Name(Desired Value) and Description(Brief explanation/purpose of Data extension). Mark the Is Sendable field as true as this will help us in sending emails from this data extension. You could mark Is testable field as true if you want to use this data extension for testing purposes.

You can also create a campaign and associate this data extension as well but for this demo, we are skipping that step. Click on Next and you will be on the Data Retention policy page and by default, the value will be off. You can click the Retention setting to On and it will give you multiple options to decide on the number of days the data has to be retained. You can move on to the next step by clicking on the Next button and this will bring up Fields Page. Marketing Cloud provides you with various Field types such as Text, Number, Date, Boolean, EmailAddress, Phone, Decimal, and Locale.

Click on the below link to understand more about different data types in Marketing cloud.

Note: Date values generated by the Marketing Cloud use Central Standard Time

You have to select a Primary key on one of the fields and you can also select nullable against any field(other than Primary key field) if you expect the value to be Null. Optionally you can also set a default value against any desired field. Select the desired field from Send Relationship dropdown on Fields page before clicking on the Create button. Click Create button to complete the creation of Data extension in the Marketing Cloud. You will be redirected to the available Data extensions page and validate if the Data extension that you created exists in the List.

We have Successfully Created Data Extension with various field types. In the next article, we will understand various ways to populate Sample data in Data extensions.