ExactTarget and Webtrends Behavioral Remarketing Integration

How To Create the Staging and Permanent Data Extensions

The ExactTarget for Webtrends integration uses two data extensions to store information from Webtrends for use in the ExactTarget application. The integration initially imports information into the Staging data extension and then transfers the information to the Permanent data extension. You must create a Staging and Permanent data extension for each category of information:

eCommerce Integration

• Staging_CompletedPurchasesbyVID
• Staging_ProductsAbandonedbyVID
• Staging_ProductsViewedbyVID
• Staging_ExternalSearchbyVID
• Staging_OnsiteSearchbyVID
• CompletedPurchasesbyVID
• ProductsAbandonedbyVID
• ProductsViewedbyVID
• ExternalSearchbyVID
• OnsiteSearchbyVID

Create all of the data extensions above with the fields contained in the Webtrends file and add a column for the date. For all Staging data extensions, check the Use Current Date As Default checkbox for the Date field.

Do not check this checkbox for Permanent data extensions.

How to Create a File Transfer Location

Create the file transfer location using the information listed below:

• Name - Enter a descriptive name that will help you identify the location in the Webtrends application.
• External Key - Enter the applicable information for your account.
• Description - Enter the phrase Webtrends SFTP Server.
• Location Type - Choose External SFTP Site from the dropdown menu.
• URL - Enter the URL sftp.webtrends.com/DataScheduler/%profile_name%/, where the profile name is the dedicated profile that analyzes data captures in Webtrends.
• Port - Enter 22.
• Username - Enter the username for the WTOD account with appropriate permissions. That username should be in the format of account_name\user. For example, example\johndoe.
• Password - Enter the password for the above account.

How to Create a File Transfer Activity

Create a file transfer activity to bring the information from Webtrends to the ExactTarget application. Use the following information to create your activity:

• Transfer Type - Select the Download radio button.
• Name - Enter the name of the file transfer activity.
• External Key - Enter the external key you wish to use.
• File Location - Choose Webtrends_SFTP from the dropdown menu
• File Naming Pattern  - Enter the filename of the Webtrends file and add the personalization strings to bring in the date of the file transfer. For example, a sample file might be named ExampleFile_PagesbyVID_%%Year%%%%Month%%%%Day%%.csv.gz.
• Transfer Settings - Check the File is Compressed checkbox.

How to Create an Import Activity

Create an import activity for each Staging data extension in your integration. Include the following information in your import activity:

• Name - Enter a descriptive name for the import activity.
• External Key - Enter an external key for the import activity.
• File Location - Select ExactTarget Enhanced FTP from the dropdown menu.
• File Naming Pattern - Enter the filename of the Webtrends file and add the personalization strings to bring in the date of the file transfer. For example, a sample file might be named ExampleFile_PagesbyVID_%%Year%%%%Month%%%%Day%%.csv.gz.
• File Type - Select Comma Delimited (CSV) from the dropdown menu.
• Destination Type - Click the Data Extensions radio button and select the data extension to which you wish to import the data.
• Update Type - Select Overwrite from the dropdown menu.
• Data Quality Settings - Select the Skip Rows with Bad Data and Respect Double Quotes (") as a Text Delimiter button.

How to Create a Query Activity

Create a query activity for each Staging data extension. The query activity copies the data from the Staging data extensions to the Permanent data extensions. Enter the following information for each query activity:

• Name - Enter a descriptive name for the import activity.
• External Key - Enter an external key for the import activity.
• Query - Enter the SQL query that will move the data from the Staging data extension to the Permanent data extension.
• Select Data Extension to Populate - Select the complementary Permanent data extension to the Staging data extension from which you are transferring the data.
• Update Type - Select Append from the dropdown menu.

How to Create a Program

Create a program to run daily that includes all of the activities included in this document. Include the information below in your program:

• Name - Enter a descriptive name for your program.
• External Key - Enter the external key you wish to use.
• Notifications - Click the Send Status Notification checkbox and enter the email address(es) you wish to receive an email notification when the program runs in the Notification Email Address(es) field.
• Process - Create the following steps in your program:
  • First step - Include all of the file transfer activities
  • Second step - Include all of the import activities
  • Third step - Include all of the query activities

Once you have created the program, schedule and start your program to start the transfer of data.