Importing Visitor Data Mart Event Data Lookup Tables

To simplify uploading lookup and translation data to Visitor Data Mart, you can upload your lookup table files directly using secure FTP (SFTP).

When you upload lookup tables, you also upload a configuration file to a specified SFTP location. This configuration file specifies additional information about the lookup table you are importing, such as the type of import, the file upload properties, and the error notification settings.

To create a lookup table and import data:

1. Create a lookup table in Webtrends Administration. See “Configuring Lookup Tables,” below, for additional information.

2. Create a flat file containing the data you want to upload. For more information, see “Data File Requirements,” below.

3. Create an XML configuration file with processing instructions for how the data should be added to the table. For more information, see “Creating a Lookup Table Import Configuration File,” below.

4. Upload the data file and the configuration file to your Visitor Data Mart SFTP location. For more information on importing data, see “Importing Data Process," below.

Configuring Lookup Tables

When you create a lookup table, you must specify at least two columns—one containing the unique identifier for each row in the table and one or more columns containing translation values.

To create a lookup table:

1. In the left pane of Webtrends Administration, click Web Analysis > Marketing Warehouse > Lookup Tables.

2. Click New. The New Lookup Table wizard opens.

3. In the Lookup Table Name text box, specify a lookup table name.

4. Click New.

5. In the Column Name text box, specify a column name.

6. Select an attribute type from the Attribute Type list. If you select Date, select a format from the list.

7. Repeat Steps 2 through 6 as necessary.

8. If you create a lookup table with multiple columns, indicate which column contains the unique identifier for the lookup table.

9. Click Next.

10. If you are satisfied with the new lookup table, click Save.

About Importing Event Data to Lookup Tables

Webtrends recommends uploading the Visitor Data Mart data import lookup files to:

sftp://sftp.webtrends.com/WarehouseDataImport

You will be automatically logged into an SFTP directory for the account to which you logged in.

After logging in, you should see a folder structure like the one shown here:

Best Practices When Importing Data Files

Webtrends recommends the following best practices for data import files:

• Always upload the data file first, allow it to finish copying, then upload the configuration file. If your WarehouseDataImport directory contains a configuration file but no data file when Visitor Data Mart checks for files, an error email is sent to the addresses specified in the <EmailNotificationList> node of the configuration file.

• Upload the XML configuration file to your Warehouse data import directory. The XML configuration file tells the Warehouse which data file to process, which lookup table to import the data to, how to add the data to the lookup table, and where to send email notifications about the upload.

• Move processed files to the a folder named “Completed” in your Warehouse data import directory.

Creating a Lookup Table Import Configuration File

The data import configuration file is an XML file that contains processing instructions for the data import.

To create a configuration file:

1. Use the sample file in your Warehouse data import directory called ImportFileTemplate.xml. If this file is deleted or re-named, Webtrends automatically re-creates it so you always have a well-formed example to start from.
2. Fill in the values for each node as described in the section “Configuration File Nodes,” below.

Naming Your Configuration File

When the Visitor Data Mart looks for configuration files, it searches your WarehouseDataImport directory for XML files with the prefix “ImportFile”. The only naming convention required is that your configuration files must begin with this prefix.

Examples:
ImportFile_myNewData_01.xml
importfile-lookup-data.xml

Configuration File Nodes

The following section describes the nodes found in a lookup table configuration file.

<FileImport>
Specifies the root element of the configuration file.

<FileImport> required attributes:

Example:
<ImportFile Version="1.1">

<Account>
Identifies your Webtrends account and the profile to which the lookup table belongs.

<Account> required attributes:

Example:
<Account ID="1" ProfileFileName="K729vc8Qqj5">

<FileType>
Specifies the file type of the data file. The value is always “flatfile”.

Example:
<FileType>flatfile</FileType>

<ImportType>
Specifies how data is imported to your lookup table. Supported values are merge, delete, and insert.

Example:
<ImportType>merge</ImportType>
For more information on import types, see the section “Data File Requirements.”

<PurgeExistingData>
Specifies whether to delete all existing data in the lookup table before importing new data. Supported values are true and false. This function is only valid with the merge and insert <ImportType>.

Examples:
<PurgeExistingData>false</PurgeExistingData>
<PurgeExistingData>true</PurgeExistingData>

<ColumnDelimiter>
Specifies the character used to separate columns in your data file.

Examples:
<ColumnDelimiter>,</ColumnDelimiter>
<ColumnDelimiter>_</ColumnDelimiter>

<EmailNotificationList>
Specifies a semicolon-delimited list of email addresses for sending success or failure notifications. You can enter as many email addresses as you like.

Example:

<EmailNotificationList>
poweruser@webtrends.com;admin@webtrends.com
</EmailNotificationList>

<File>
Contains the <FileName>node, which specifies the file name of the data file.

<FileName>
Specifies file name of the data file. The ImportTable attribute specifies the name of the lookup table to which the data is imported.

<FileName> Required Attributes:

Example:
<FileName ImportTable="LookupTest01">LookupTest00.txt</FileName>

Configuration File Example

<?xml version="1.0" encoding="utf-8"?>
<FileImport Version=”1.1”>
  <Account ID="1"
ProfileFileName="K729vc8Qqj5,AVEqrAMcf45,iSTJxuiH8i7,sN7kOGMr9a4">
	<FileType>flatfile</FileType>
	<ImportType>merge</ImportType>
	<PurgeExistingData>false</PurgeExistingData>
	<ColumnDelimiter>,</ColumnDelimiter>
	<EmailNotificationList>poweruser@webtrends.com;admin@webtrends.com
</EmailNotificationList>
	<File>
	  <FileName ImportTable="LookupTest01">LookupTest00.txt</FileName></File>
  </Account>
</FileImport>

About Import Types

There are three methods for importing data to your lookup tables: merge, delete, and insert. Each method combines new data with existing data in a different way.

Importing Lookup Table Data with Merge

When you import data using the merge method, new records are added to your lookup table. Existing records whose Unique Identifier (as defined in the configuration of the lookup table) matches the unique identifier of records in the data file will be overwritten. You can also delete all existing data in the lookup table before importing new data. For more information on how to delete existing data before importing a lookup table, see “<PurgeExistingData>," above.

Use merge when you want to update records.

Importing Lookup Table Data with Delete

When you import data using the delete method, records are removed from the lookup table. Existing records whose Unique Identifier (as defined in the configuration of the lookup table) matches the unique identifier of records in the data file will be deleted.

Use Delete when you want to target specific records for deletion from the lookup table.

Importing Lookup Table Data with Insert

When you import data using the insert method, new records are added to the lookup table. If the unique identifier of an existing record matches the identifier of a record in the data file, an error is logged in an error file. The import will continue and valid records will be added to the lookup table. You can also delete all existing data in the lookup table before importing new data. For more information on how to delete existing data before importing a lookup table, see “<PurgeExistingData>,” above.

Use Insert only when you want to add new records.

Data File Requirements

The data file must be a flat file that meets the following requirements:

• The file must contain only the data. Do not include column headings, notes, extra carriage returns, or any other characters that are not part of the data you want to import.

• The order of the columns in the data file must match the order of the columns in your lookup table. If the order of columns does not match, the import will fail and produce column errors. If the column types match but the columns are not in the correct order, the data will be imported successfully, but its meaning will be lost because the wrong data will be associated with the wrong column heading.

• The flat file must not be tab-delimited.

Importing Data Process

After you upload a data file and a configuration file to your WarehouseDataImport directory, Visitor Data Mart processes checks for files periodically and processes them automatically. The data is loaded into the Visitor Data Mart lookup tables. However, lookup table values may not show up in your Visitor Data Mart Visitor Intelligence until after an analysis cycle.

Email Notifications

If you specify one or more valid email addresses in your configuration file, a notification email is sent to the specified address(es) for each successful or failed processing of a configuration file. The email notification indicates the locations of processed files and error logs, if applicable.

Dealing with Errors

Errors that can occur during a lookup table data import include:

• Column errors - Column errors occur on individual rows of the data file. They do not prevent successful import of valid rows.

• Import errors- Import errors occur on individual rows of the data file. They do not prevent successful import of valid rows.

• Configuration errors - Configuration errors occur because there are problems with the configuration file. Configuration errors prevent data from being imported.

Column Errors

Column errors occur when your data file contains rows with a number of columns that doesn’t match the number of columns in the lookup table you are importing data to.

When a column error occurs during an import, Visitor Data Mart writes an error log to the Errors folder. Column error log files are named like this:

ColumnErrors(ProfileName-ProfileGUID)_YYYYMMDDMMSS_ImportFileName.txt

Where ImportFileName.txt is the file name the configuration file.

Each entry in a column error log contains the invalid row from the data file, the line number of the invalid row, a description of the error and an example of the column delimiter.

Example:
1111.1,shirt,2/1/09,----> ERROR (Line 1): There were 4 columns found, only 3 columns were expected [column delimiter = ',']

In this example, the error occurred because the comma at the end of the row created an empty fourth column at the end of the row. The lookup table contains only three columns, so this four-column row produces an error.

To correct column errors:

1. Open the error file and correct the errors. As you correct each row, delete the error message and the arrow to the right of the entry itself.
2. Delete the long dashed line and everything above it. When you are done, there should be nothing in the file but the corrected rows.
3. Rename the file and save it to your WarehouseDataImport directory.
4. Open the configuration file (in your “Completed” folder). Change the value of the <FileName>node to the file name of the error file you just corrected and saved.
5. Save the configuration file and move it to the WarehouseDataImport directory.
6. Corrected entries are added to the lookup table.

Import Errors

Import errors occur when your data file contains data that is not formatted correctly. The format of acceptable data is defined by the lookup table. When an error occurs during an import, Marketing Warehouse writes an error log to the Errors folder in your WarehouseDataImport directory.

Import error log files are named like this:

ImportErrors(ProfileName-ProfileGUID)_YYYYMMDDMMSS_ImportFileName.txt where “ImportFileName” is the file name of the configuration file.

Each entry in a column error log contains the invalid row from the data file, the line number of the invalid row, and a description of the error.

Example:
888888888888888,Testing,1/1/11----> ERROR (Line 3): Conversion failed because the data value overflowed the type used by the provider.

In this example, the error occurred because the data in the first column of the row contains too many characters.

To correct an import error:

1. Open the error file and correct the errors. As you correct each row, delete the error message and the arrow to the right of the entry itself.
2. Delete the long dashed line and everything above it. When you are done, there should be nothing in the file but the corrected rows.
3. Rename the file and save it to your WarehouseDataImport directory.
4. Open the configuration file (all configuration files are moved to the “Completed” folder after processing). Change the value of the <FileName> node to the file name of the error file you just corrected and saved.
5. Save the configuration file and move it to your WarehouseDataImport directory.
6. Corrected entries are added to the lookup table.

Configuration File Errors

Configuration File errors occur when there is a problem with the configuration file. If configuration errors occur, a notification email is sent to the addresses listed in the configuration file, but no error file is written to the Errors folder in your WarehouseDataImport directory. The notification email contains details that will help you correct configuration errors.