Design Considerations

A key feature of the Data Sources module is the ability to match incoming records to existing Aprimo records.

For an overview of the data importing process, see Importing Data into the Application.

Modules Into Which You Can Import Data

You can use data sources to import data into these modules:

• Contact History
• Inbound Forms
• Master Audience Members List
• Master Companies List
• Master Participants List
• Master Products List
• Master Suppliers List
• Product History

Key Fields

• Details
• Schedule Information
• SQL Statement
• Field Maps
• Matching Rules
• Candidate Set Rules
• Company Matching Rules
• Company Candidate Set Rules

Details

These fields appear for all data sources, unless stated otherwise.

Key Fields that Appear Based on Target Group

• Audience Members
• Suppliers, Companies, Contact History, Products, or Product History
• Participants

These fields appear for a data source if you select Audience Members in the Target Group field.

These fields appear for a data source if you select Suppliers, Companies, Contact History, Products, or Product History in the Target Group field.

These fields appear for a data source if you select Participants in the Target Group field.

Import Type Options

A key feature of the Data Sources module is the ability to match incoming records to existing Aprimo records. The import type determines the matching option to use when the data source is imported.

Schedule Information

Data sources import information on a scheduled basis. Schedule information determines when data sources run and how often they run. Before you can start a data source, you must configure the schedule information.

SQL Statement

For more information, see Defining SQL Statements.

SQL Builder

The SQL statement must include the name of every field in the data source from which you want to obtain data. If the application connects to the data source through an OLE DB connection, you can select all fields in a table by using an asterisk (*) in a SELECT statement. However, if the application connects to the external database using a native database connection, you cannot use an asterisk (*) in a SELECT statement. In this case, the SELECT statement must list the name of every field you want to import.

Instead of manually adding field names to the SQL statement, you can use the SQL Builder to select the fields you want to use. The SQL builder can access one table at a time.

SQL Statement Examples

• Handling Special Characters
• Handling Dual Keys
• Using Aliases to Generate New Data
• Concatenating Separate Fields into a Single Field
• Dividing a Field into Multiple Fields
• Using the <<LastImportJob>> Meta Tag
• Importing Data from Excel

Handling Special Characters

If the data source contains field names that have special characters, you might need to adjust these field names so that the application can validate the data source. The special character in the following example is the space in the First Name field and the Last Name field. The SQL statement uses an alias to make the query valid.

Handling Dual Keys

If you need to import a field with dual keys, and both keys are stored as strings in the database, you could add this line to the SQL statement:

Then you could replace UNIQUE_ID1 and UNIQUE_ID2 with the key names.

If the keys are stored in numeric fields, first change the field type to string. Then combine the keys.

Using Aliases to Generate New Data

You can generate new fields by using an alias in the SQL statement. This SQL statement creates a field named Type and populates the Type field for all records with the value Customer:

Concatenating Separate Fields into a Single Field

You might need to join data from separate source fields into a single field. For example, the source data stores SFA Phone numbers in separate fields: area code, prefix, and line. You want to store this information in a single field in the application. You could use this SQL statement, which uses an alias named Phone, to achieve this:

Dividing a Field into Multiple Fields

You might need to separate data from a single source field into multiple fields. For example, the source data stores address information in a single, semicolon separated field: Addr1; Addr2; City; State. To store this information in separate fields in the application, you could use this SQL statement:

SELECT SUBSTRING(Address, 1, CHARINDEX(';', Address)-1) addr1,
SUBSTRING(Address,
     CHARINDEX(';', Address)+1,
     CHARINDEX(';', SUBSTRING(Address,CHARINDEX(';', Address)+1,100) )-1 ) addr2,
SUBSTRING(Address,
     CHARINDEX(';', Address) + CHARINDEX(';', SUBSTRING(Address, CHARINDEX(';', Address)+1,100) ) +1,
     CHARINDEX(';', SUBSTRING(Address, CHARINDEX(';', Address)+ CHARINDEX(';', SUBSTRING(Address,CHARINDEX(';', Address)+1,100) ) + 1,100) )-1 ) city, SUBSTRING(Address,
     CHARINDEX(';', Address) + CHARINDEX(';', SUBSTRING(Address, CHARINDEX(';', Address)+1,100) ) + CHARINDEX(';', SUBSTRING(Address, CHARINDEX(';', Address)+ CHARINDEX(';', SUBSTRING(Address,CHARINDEX(';', Address)+1,100) ) + 1,100) ) +1, CHARINDEX(';', SUBSTRING(Address,CHARINDEX(';', Address) + CHARINDEX(';', SUBSTRING(Address, CHARINDEX(';', Address)+1,100) ) + CHARINDEX(';', SUBSTRING(Address, CHARINDEX(';', Address)+ CHARINDEX(';', SUBSTRING(Address,CHARINDEX(';', Address)+1,100) ) + 1,100) ) +1,100))-1) state
FROM Data Source

Using the <<LastImportJob>> Meta Tag

When Aprimo executes a SQL statement, it substitutes the <<LastImportJob>> meta tag with the end time from the most recent log entry for the data source. This meta tag is commonly used to decrease the amount of time needed to process a data source.

In the following example, the data source contains a field that stores the last time a record was modified. The combination of the <<LastImportJob>> meta tag and this field selects only the records that have been added or changed since the last time the application processed the data source.

Importing Data from Excel

Worksheets are like tables in SQL. In the SQL statement, refer to the worksheet in this format:

For example, you want to import data stored in an Excel worksheet named "customers." You use this SQL statement to capture the data:

Consider these additional points when you import data stored in Excel:

• The Data Sources module assumes that the values in the first row are column names. This is helpful to keep in mind when you define the data source's field maps.
• If you import data from multiple Excel spreadsheets, copy the data into a single spreadsheet file. Create a separate worksheet for each spreadsheet file. You need to create only one database for the spreadsheet file. You can then create a separate data source for each worksheet.
• Be sure the Excel file is closed before you test or start the data source. If the file is open, you will receive errors.

Field Maps

You must map each source field you want to import to a destination Aprimo field.

For more information, see Defining Field Maps.

Field Map Mode Options

Group Leaders and Group Followers

If the data source's import type is Literal Matching or Aggressive Matching, the application handles certain groups of fields in a special manner to ensure that all fields in the group contain data from a single data source.

The Literal Matching import type and the Aggressive Matching import type attempt to match incoming records to existing Aprimo records. When a match is found, the incoming record is merged with the existing record. The field map's mode determines whether the source field's value overwrites the information in the destination field.

A group leader is a field that determines the mode of the other fields in the group. The other fields in the group are group followers. When you save the field maps, the application makes sure the mode is the same for a group leader and all of its group followers. This ensures that logically grouped fields are updated with data from a single data source.

Match Key (Last Name)

The Expression Item field contains an option titled Match Key (Last Name). If you select this option, the expression finds all records with a last name that is phonetically identical to the incoming record.

Matching Rules

This tab appears if the import type is Aggressive Matching. Aprimo uses the matching rules to perform a field-by-field comparison between an incoming record and each record in the candidate set. When comparing records, the Data Sources module tabulates a match score. The match score determines whether an incoming record and an existing record are merged.

For more information, see Customizing Matching Rules.

Match Requirement Options

Candidate Set Rules

This tab is visible only if the import type is Aggressive Matching. The candidate set rules determine which records the matching rules process. Each incoming record has its own candidate set.

When you use the Aggressive Matching import type, the application uses the matching rules to perform a field-by-field comparison between an incoming record and existing records in the application. Although this import type can reduce the creation of duplicate records during the import process, it is a data-intensive process. Restricting the comparison to the records that are most likely to match an incoming record decreases the amount of time the data source requires to complete.

The candidate set rules are an expression composed of Aprimo fields. To create the candidate set, the Data Sources module substitutes the value of each field in the expression with the values from the incoming record. The candidate set is composed of all records in the application that match the expression.

For more information, see Customizing Candidate Set Rules.

Company Matching Rules

This tab appears only if the company import type is Aggressive Matching and the target group is Audience Members. The application uses the company matching rules to perform a field-by-field comparison between an incoming record and each record in the company candidate set. When comparing records, the Data Sources module tabulates a match score. The match score determines whether an incoming record and an existing record are merged.

For many organizations, the company with which an audience member is associated is important. The process of importing audience members includes functionality that associates incoming audience member records with the existing company records in the application. This functionality reduces the creation of duplicate company records during the process of importing audience members.

For more information, see Customizing Company Matching Rules.

Company Candidate Set Rules

This tab appears if the company import type is Aggressive Matching and the target group is Audience Members. The company candidate set rules determine which records the company matching rules process. Each incoming record has its own company candidate set.

When you use the Aggressive Matching company import type, Aprimo uses the company matching rules to perform a field-by-field comparison between an incoming record and the existing Aprimo records. Although this company import type can reduce the creation of duplicate records during the import process, it is a data-intensive process. Restricting the comparison to the records that are most likely to match an incoming record decreases the amount of time required to process the data source.

The company candidate set rules are an expression composed of Aprimo fields. To create the candidate set, the Data Sources module substitutes the value of each field in the expression with the values from the incoming record. The company candidate set is composed of all records that match the expression.

For more information, see Customizing Company Candidate Set Rules.

Creating and Editing Data Sources

Consider these points when you create a data source:

• Each data source is associated with a database you create in the Databases module.
• A single data source can pull data from multiple tables in a given database. For more information, see Defining SQL Statements.
• You must create a separate data source for each target group.

After you save the data source's details, you must complete these steps:

• Define the SQL statement.
• Define the field maps.

For data sources that use the Aggressive Matching import type, you might also need to complete these steps:

• Customize the candidate set rules.
• Customize the matching rules.

For data sources with Audience Members as the target group, you might also need to complete these steps:

• Customize the company candidate set rules.
• Customize the company matching rules.
• Define the contact history field maps.

For instructions, see Creating Data Sources.

The schedule status determines whether you can edit the data source.

Editing Inbound Form Data Sources

For each inbound form with Audience Members as the target group, Aprimo creates a data source. You can edit this data source to refine the matching rules for when audience members submit inbound forms. This enables you to prevent duplication of audience member records if the information in an inbound form does not exactly match an existing audience member record.

Defining SQL Statements

The SQL statement tells Aprimo how to extract the data from the data source. The SQL statement is unique for each data source. The SQL statement specifies the fields to extract from the database. The result of the SQL statement is one record set for the data source.

When you define the SQL statement, remember these points:

• The SQL language has many variations. Use the variation that works with the database from which you are importing data.
  Tip:
  Oracle is case-sensitive.
• You can make joins in the SQL statement to access data from multiple tables.
• If Aprimo connects to the external database using a native database connection, the SQL statement must indicate the location, database, and database object.
• If Aprimo connects to the external database using a native database connection, you cannot use an asterisk (*) in a SELECT statement to capture all fields in a table. The SELECT statement must list every field name you want to import. The SQL statement window includes an SQL builder you can use to populate the SQL statement with the appropriate field names.

For more information, see the key fields for the SQL Statement tab.

Defining Field Maps

When you define the field maps, remember these points:

• Before creating field maps, be certain that you understand the contents of every source field. You should also be familiar with the Aprimo fields into which you will import data.
• A data source usually includes more information than you want to store in the Aprimo database. Include only the fields that contain data you want to use in Aprimo.
• If you want to import data into an extended attribute field, you must first create the extended attribute.
• If you want to create contact history while importing audience member data, you must first create the contact history type.
• In general, you cannot use a destination field more than once.
  Note:
  The exception is multiple selection attributes. You can map more than one source field to a multiple selection attribute, because multiple selection attributes can contain more than one value. The mode must be the same for all fields that you map to the attribute.
• Aprimo handles certain groups of fields in a special manner to ensure that the fields contain data from a single data source. For more information, see Group Leaders and Group Followers.

An easy way to start creating field maps is to click the AutoField button, which makes Aprimo automatically select the destination fields. To determine which field to choose, the AutoField function looks for an Aprimo field name that is similar to the source field's name. It also looks for common database field names and sets them accordingly.

After you click the AutoField button, carefully verify the results to make sure the information from the data source is added to the correct field in Aprimo. If necessary, you can edit the field maps.

Customizing Matching Rules

The Matching Rules Mode field determines whether you can customize the matching rules.

To customize the matching rules, you must specify a matching threshold on the data source's Details tab. The matching threshold is the lowest score that represents a match. If an existing record has a match score that exceeds the matching threshold, that record is merged with the incoming record. If more than one record has a match score that exceeds the threshold, the record with the highest match score is merged with the incoming record.

Remember these points about creating custom matching rules:

• The highest possible match score must be greater than or equal to the matching threshold. The highest possible match score is the sum of all positive impact values for all matching rules.
• You can run the data source in test mode to analyze the matching rules. The Test Output module shows detailed information about each match candidate.
• The rules work in combination to determine whether an incoming record matches an existing record.
• If you like the matching rules you created but find that too many or too few records are being matched, you can adjust the matching threshold.

For more information, see the key fields for the Matching Rules tab.

Customizing Company Matching Rules

The Company Matching Rules Mode field determines whether you can customize the company matching rules.

To customize the company matching rules, you must also specify a company matching threshold on the data source's Details tab. The company matching threshold is the lowest score that represents a match. If an existing record has a match score that exceeds the matching threshold, that record is merged with the incoming record. If more than one record has a match score that exceeds the threshold, the record with the highest match score is merged with the incoming record.

Remember these points about creating custom company matching rules:

• The highest possible match score must be greater than or equal to the company matching threshold. The highest possible match score is the sum of all positive impact values for all company matching rules.
• You can run the data source in test mode to analyze the company matching rules. The Test Output module shows detailed information about each match candidate.
• The rules work in combination to determine whether an incoming record matches an existing record.
• If you like the company matching rules you created but find that too many or too few records are being matched, you can adjust the company matching threshold.

For more information, see the key fields for the Company Matching Rules tab.

Customizing Candidate Set Rules

The Candidate Set Rules Mode field determines whether you can customize the candidate set rules.

To customize candidate set rules, use the Expression Item list to insert the fields you want to use. This list includes all destination fields used on the Field Maps tab. It also includes an option called Match Key (Last Name).

You can use parentheses to combine statements in the expression and to control the order in which Aprimo processes the statements.

For more information, see the key fields for the Candidate Set Rules tab.

You can run the data source in test mode to analyze the effectiveness of the candidate set rules. The Test Output module shows the number of records that match the candidate set rules for each incoming record.

Customizing Company Candidate Set Rules

The Company Candidate Set Rules Mode field determines whether you can customize the company candidate set rules.

To customize company candidate set rules, use the Expression Item list to insert the fields you want to use. This list includes all destination fields used on the Field Maps tab.

You can use parentheses to combine statements in the expression and to control the order in which Aprimo processes the statements.

For more information, see the key fields for the Company Candidate Set Rules tab.

You can run the data source in test mode to analyze the effectiveness of the company candidate set rules. The Test Output module shows the number of records that match the company candidate set rules for each incoming record.

Importing Data into Extended Attribute Fields

The application has a set of standard attributes for capturing information about audience members, companies, participants, and suppliers. Examples of standard attributes for audience members are Last Name and Day Phone Number.

Your organization can create extended attribute fields to capture additional information about these objects and to import data into these fields.

Extended Attribute Types

This table lists some of the types of extended attributes you can create, and the special considerations for importing data into those fields.

For more information about the types of extended attributes that you can create, see Data Types.

Secure Extended Attributes

There are special considerations for importing data into secure extended attributes. You can create secure extended attributes for audience member records. A secure extended attribute contains confidential information that should be available only to a select group of users. Aprimo imports information into a secure extended attribute without regard for the specific rights for that attribute. Any user with access to the Data Sources module can import information into secure extended attributes. However, a user could import information into secure extended attributes, and then not be able to see that information in the application.

Importing Participants

When you import participants, you can create user records for them.

When Aprimo creates new user records during the participant import process, it observes these rules:

• The new user record's default password is the same as its login ID.
• The new user is created in all domains specified in the data source.
• The new user has all portal rights selected in the data source.

Creating a new user cannot cause a license violation. If the number of new users exceeds the total number of user licenses, these actions occur:

• The user records are created, but an error is recorded in the data source log.
• If the new users are portal users, the users are created as active access users. They do not have access to the main Aprimo application.
• If the new users are not portal users, the users are created with Inactive status.

If you configure the data source to create user records, the import process updates existing user information under these conditions:

• The incoming participant record matches an existing participant record.
• The existing participant record is mapped to a user record.
• The incoming participant record contains a login ID that exactly matches an existing user record's login ID.
• The incoming participant record contains a mapped user ID that exactly matches an existing user record's login ID.

If these conditions are not met, the application creates a new user record.