File Import Data Source

[#1]

This article describes how to import CSV files as a data source, allowing offline data to become enriched and actionable within the Customer Data Hub.

Before you begin:

• Preparing a CSV File for Import 

This guide has the following sections:

• How It Works
  Learn about the process of setting up a file import data source, uploading files, and the final results within the Customer Data Hub.
  
• Using the File Import Data Source
  Step by step instructions for setting up a file import data source.
• Import Status
  See how to check on the status of your file imports, including details metrics for number of rows processed and errors.

In this article:

Table of Contents Placeholder

Prerequisites

• Your account must be enabled for AudienceStream

How It Works

The File Import feature provides the ability to import a CSV file to supplement online visitor profiles with valuable offline data. Using this feature, you can import a CSV file using a file transfer service, such as an Amazon S3 Bucket. The Tealium server connects to the file service, reads the file and ingests the data. Each imported row is processed as an event. Once ingested, the data can then be enriched, stitched to existing visitor profiles, and sent to other vendors.

There are two components used in setting up a file import: a column mapping configuration and a file transfer service.

CSV Column Mapping

The column mapping configuration determines the event attributes that correspond to each column in the CSV file. The column names are often different from the attribute names in the Customer Data Hub, so this mapping ensures that the data is imported properly. For example, a CSV file might have a column named postalCode, but the matching event attribute is named customer_zip, so a column mapping is needed to associate the two.

Mappings can be configured based on an existing event specification or as a custom event.

Event Spec Mapping

When a file import uses an event specification mapping, the event attributes are preselected and you specify the CSV column name that corresponds to each attribute. Each row is processed as an event of the selected specification, for example tealium_event = "purchase"

Custom Mapping

When a file import uses a custom event mapping, you specify the event attribute that corresponds to each CSV column. Each row is processed as an event with the following event identifier: tealium_event = "imported"

File Transfer Service

The file transfer service is a secure location where you upload the files to be imported. The following file transfer services are supported:

• Amazon S3 (Tealium bucket or your own bucket)
• Microsoft Azure File/Blob Storage
• FTP/SFTP

If using your own file transfer service, be sure to have the connection details ready before proceeding.

Workflow

The order of operations for importing a file as a data source is as follows:

Step 1 - Create a File Import data source

1. Create a CSV file to import. (Learn more).
2. Choose a file name related to the type of data to import, such as store_purchases.
3. Upload a sample file to automatically detect the column names.
4. Map the columns to event attributes.
5. Configure a new file transfer service or assign an existing file transfer service to your file import data source.

Step 2 - Upload a CSV file

• Manual Upload
  Drop a file into the upload area of the file import data source.
• File Transfer Service
  Upload files to the configured file transfer service.  The system checks for new files every 10 minutes.

File Import Process

Once a file transfer service is assigned to a file import data source you will upload files to the service. The system then uses the following order of operations:

1. Check for New Files
   The system checks the file transfer service for new files every 10 minutes.
2. Copy New Files
   When a new file is detected, it is copied from the file transfer location and processed in the Customer Data Hub.
3. Match Filename Prefix to File Import Data Source
   The prefix of the filename is used to identify which file import data source to use when importing the data in the file. 
4. Process Files
   The header line is read to identify the attributes being ingested. From there, the following processing is performed:
   
   • Visitor Lookup
     The visitor ID is used for a lookup of the visitor record in AudienceStream. If an existing visitor record is not found a new one is created.
     

     Grouping rows with the same visitor ID will increase the speed of the import.

   • Attribute Enrichment
     The visitor record will be enriched according to the attributes imported and the existing enrichments in your account.

Using the File Import Data Source

Setting up a file import data source requires that you prepare a CSV file, configure column mappings, and assign a file transfer service.

Before you begin, create a sample CSV file (less than 1,000 rows) to use during the setup process. It will be used to automatically detect the column mappings.

Use the following steps to begin setting up a file import:

1. In the sidebar, select Sources > Data Sources.
2. Click + Add Data Source.
3. Add a File Import Data Source
4. Upload a Sample File
5. Configure Column Mappings
6. Configure a File Service

Adding a File Import Data Source

Use the following steps to add a file import data source:

1. In the sidebar, select Sources > Data Sources.
2. Click + Add Data Source.
3. Under Categories, click File Import and select the File Import platform.
   
4. In the Name field, enter a unique name related to the file type and click Continue.

Upload a Sample File

The first step in setting up a file import is to upload sample CSV file. This will automatically detect the columns names in the file and pre-configure them on the column mappings screen. This step is optional and can be skipped by clicking Continue to go directly to the column mappings screen.

Use the following steps to upload a sample file:

1. Select a file.
   • Click [Choose a file...] and select the file and click Open, or
   • Drag and Drop a file directly onto the upload area.
     The Sample Preview displays the file contents in table format.
2. Scroll through the sample file to verify that the columns and data look correct.
   The detected column names are listed in CSV format below the table and are pre-populated on the column mapping screen.
3. If there are problems with the sample file, click Remove next to the uploaded file name and try another file.
4. Click Continue to go to the Mapping screen.
   

Configure Column Mappings

The Mapping screen is used to indicate the type of data being imported and how to map the CSV columns to event attributes.

Event Specification

Each row of the CSV file is processed as an event. Use the drop-down menu to select which specification matches the data in the file, otherwise select Custom. This selection determines the value of the tealium_event attribute during the import process. If Custom is selected, tealium_event is set to "imported", otherwise it is set to the corresponding specification name e.g. "purchase".

Changing the selected specification will reset the column mapping table.

Column Mapping Table

The column mapping table contains the following columns:

• Column Label from CSV
  The columns names from the CSV (pre-populated from the sample file)
• Date Formatter
  A date formatter for columns that contain date/time values.
• Event Attribute
  The event attribute to receive the data from that column.
• Sample 1 – Sample 3
  Values from the sample file.

Use the following steps to map each column to an event attribute:

1. Enter or select a column name from the file (if not already pre-selected).
2. Click the drop-down list in each Event Attribute column and select the event attribute you want that column to map to. Note each CSV column heading must be unique .
   
3. Columns with date/time values must have a matching date format setting. Click the checkbox in the date column next to any column for which you need to customize the timestamp format. The default value is yyyy-MM-dd.
   An interactive menu displays that allows you customize the timestamp format for that column.
   
4. Repeat the column mapping and date formatting steps for each column.
   Click the red "X" to left of any column you do not want to map to remove that column from the list.
5. (Optional) Click the Enable Visitor ID Mapping in AudienceStream checkbox.
6. Click Continue to advance to the Service Configuration tab.

Configure a File Service

The file transfer service is a secure location used you upload your files for Tealium to retrieve them. If you are using your own file transfer service, ensure that you have the connection details ready before proceeding.

Tealium supports the following file transfer services:

• Amazon S3 (Tealium bucket or your own bucket)
• Microsoft Azure File/Blob Storage
• FTP/SFTP

Use the following steps to add and configure your file transfer service:

1. Select a service from the Choose File Service drop-down list.
2. (Optional) If you do not have a file service set up or want to add a new service, click + Add New File Service.
   The Configure File Service dialog displays.
   • Enter the File Service Name.
   • From the Service drop-down list, select a supported file service from the list.
   • Enter the credentials necessary to access the service you selected.
     If you selected Tealium S3 Bucket, the necessary credentials are generated for you automatically.
   • Click Save to save this service for future use.
3. Enter the File Name Prefix, such as ordercompleted.
   The prefix of the filename is used to identify which file import data source to use when importing the data in the file.
   
   In this example, the prefix is used to create a CSV file titled ordercompleted_VERSION.csv.
4. Use the slider to Enable or Disable Service Processing for File Import.
   • When set to ON, the file service is checked and files are processed every 10 minutes.
   • Set to OFF if you are not ready to start processing files. You can enable the service later.
5. Click Continue to advance to the Summary tab.

Summary

In this final step, you will view the summary, make any needed corrections, and then save and publish.

1. View the Event Attribute Mappings.
   
2. To make changes, click Previous twice to return to the Mapping tab to update.
3. Click Finish to exit the configuration dialog.
   Your new data source now displays in the list of data sources.
   
4. Click Save/Publish to save and publish your changes.

Upload a File to a File Service

The final step is to upload your CSV files, which is done outside of the product interface. To upload a file to a file service, a third-party application is required to initiate the upload. Though you may use any client for this purpose, Tealium recommends Cyberduck because it’s free and supports FTP and Amazon S3.

When multiple files are uploaded at the same time using SFTP or S3, the files are processed in the order of the upload timestamp.

Use the following steps to upload a file via FTP or Amazon S3 using Cyberduck:

1. Install (or launch) Cyberduck.
2. Create a new connection and give it a title.
3. From the first drop-down list, select the file transfer service used in your File Transfer Service configuration.
4. Enter the credentials (username and password) for your service.
   
   • My FTP Connection and My SFTP Connection
   • Tealium S3 Bucket – the credentials are pre-populated as an Access Key (username) and Secret Key (password).
5. Provide other details, such as the server, path, port, etc, required by your service.
   Learn more about accessing third-party S3 buckets using Cyberduck.
6. Save the connection.
   You are now ready to upload files by dragging and dropping your CSV files into Cyberduck.
7. If you try to access an S3 bucket that is empty, the message "Listing directory / failed." may be displayed.  The workaround is to add ".s3.amazonaws.com" to the end of the information in the "Path" field and then cut and paste everything from the "Path" field into the "Server" field instead (leaving the "Path" field empty).  This is an issue only for when the bucket is empty; after that the normal set-up will work.

When using SFTP, the file must be located in the root folder. Files cannot be located in the sub-folders of an SFTP connection.

Check Import Status

Once the file import data source is set up and has begun importing files, you can view the import activity from the Status tab. A rolling month report will display, with details about how many rows were processed, with and without errors, in graphical format. A tabular view displays directly underneath the graph.

You can also see events imported from files on the Live Events screen by navigating to Live Events and selecting the File Import data source you want to view from the drop-down list.

If an error occurs, hovering over the red area of a bar in the graphical display displays a breakdown of the errors. Tooltips will show common errors with a link to detailed report of all errors hosted in S3. Hover over any bar in the display to view details about the number of rows processed.

FAQ

This section addresses frequently asked questions (FAQs) about the feature described in this article.

What if the file transfer service fails?

If AudienceStream fails to copy a file using the file transfer service, it will retry in 10 minutes.

What if the file fails to be processed?

When a file process failure occurs, the file is ignored. The system will not attempt to process the file again. The most common reasons for failure are:

• The CSV file is improperly formatted and is therefore not a valid CSV file.
• The CSV file must be in UTF-8 format with no BOM encoding.
• Column names used in the file definition do not exist in the file
• A column name is used more than once in the File Service configuration

Can files be encrypted with PGP/GPG?

File import does not support PGP or GPG key encryption. Unencrypted or decrypted files prior to transfer in order for them to be uploaded successfully. Files are secured with native SFTP in transit, or native S3 in transit and at rest.

Can files be compressed?

File import does not support compressed (zipped) files. Extract (unzip) files prior to transfer in order for them to be uploaded successfully.