Creating a New List Mapping Template
After analyzing your upload list and determining what changes need to be made in order to make the upload process easiest, you can begin creating your List Mapping Template.
The External Prospect List Mapping screen serves as the basis for the upload processing of purchased lists and formats. It also maps marketing lists for incorporation into Personify360.
The screen can also delineate a list and allow you to perform the following activities, all of which are described in the proceeding steps:
· Create and store a mapping scheme for the standardized lists
· Define default data-level mapping rules
· Provide duplicate checking against the prospect and customer database tables with optional verification against a prospect user field or customer field
· Define the rules for list usage
The mapping should have a name field (e.g., First Name, Last Name, Company Name, etc.), address 1 (if this field is not included in your mapping file, assign a Field Order of "0" and a Fixed Value of "[space]", address status code (if this field is not included in your mapping file, assign a Field Order of "0" and a Fixed Value of "BAD"), address type code (if this field is not included in your mapping file, assign a Field Order of "0" and a Fixed Value of "BLANK") at minimum to create the mapping and the same values should exist in the upload list.
To define the List Mapping template:
1. From the Personify360 main toolbar, select Marketing & Communication > Prospect List Mapping.
The External Prospect List Mapping search screen displays, as shown below.
2. Click Create New List Mapping.
The External Prospect List Mapping screen displays.
3. Select the List Source from the drop-down.
This might be the organization from which the list was purchased. Values in the drop-down are populated based on the non-fixed codes defined for the MKT "LIST_SOURCE" system type.
4. Enter the List Category.
This is the vendor’s designation for the list.
5. Select the Date Format from the drop-down.
This indicates the format of dates being passed into the program by the source file. Values in the drop-down are populated based on the fixed codes defined for the APP "DATE_FORMAT" system type.
6. If applicable, uncheck the Active checkbox.
7. In the Input File Information section, perform the following:
a. Select the Type from the drop-down.
This indicates whether the format is comprised of "FIXED" length records (requiring a specific begin and end position for each field in the mapping functions) or records "DELIMITED" by a comma, pipe, or other delimiter.
Currently, only DELIMITED input files are supported.
b. Select the Delimited by format from the drop-down.
This is a blank space, character, or other symbol that separates the beginning of the format or character string from the end. This field is only enabled if “Delimited” is selected as the Type.
The system only supports CSV and TXT input files.
c. If applicable, select the Enclosed by format from the drop-down.
For a comma-delimited file, this identifies the mark(s) that surround the fields or other file features. For example, the fields in the file may be enclosed by quotation marks. This field is only enabled if “Delimited” is selected as the Type.
d. Select the Load Records for customer type from the drop-down.
Values in the drop-down are populated based on the fixed codes defined for the MKT "LIST_RECORD_TYPE" system type. The value you select from this drop-down will determine which fields you can edit in other sections. This includes the following values:
· Individuals: The process creates or loads individual records (Record Type = I) where the name of the individual is not null. No company records are created.
· Companies: The process creates company records where the name of the company is not null. No individual records are created.
· Both Individuals and Companies: The process creates, where possible, both individual and company records, depending on whether the Label Name and Company Name fields are populated and available. Additionally, if options are defined in the "Individual to Company Relationship" section, the system will create a relationship between the individual and company records.
For example, any input record where a name and a company are specified, this results in two records with the same address but different record types. If both are loaded, then the defaults for the Classification and Status fields are user-entered for the company and individual record.
Selecting the "Both Individual and Companies" option assumes that the individual and company data is located in the same row/line. For example, take the following example:
Both the individual's name and company name are located in the same row. In this example, if the "Both Individual and Companies" option is selected, the system will create one record for individual "Amy Miller" and one record for company "Personify Inc.".
If you have an individual in row 2 and a company in row 3, the record will NOT be created and the process will fail.
e. If applicable, uncheck the Ignore Header checkbox.
Selection this option if the list you are using includes header information. When checked, the system ignores the information in the header. It is checked by default. For example, in the screenshot above, there is a header row. If you're using a source file similar to this, you would check this checkbox to ignore that row.
8. If you selected “Individual” or “Both Individual and Companies” from the "Load Records for" field, in the Individual Record Defaults section, perform the following:
a. Select the prospect class to be assigned to the individual customers from the Classification drop-down.
b. Select the prospect Status to be assigned to the individual customers from the drop-down.
9. If you selected “Company” or “Both Individual and Companies” from the "Load Records for" field, in the Company Record Defaults section, perform the following:
a. Select the prospect class to be assigned to company customers from the Classification drop-down.
b. Select the prospect Status to be assigned to the company customers from the drop-down.
10. In the Additional Duplicate Check section, you can define which fields you want to use when checking for duplicate records. For more information, please see the Prospect Duplicate Check section below.
a. Select the Prospect Field from the drop-down.
This lists all of the fields from the Prospect table for use in additional duplicate checking. For example, if the Social Security Number field is always uploaded into the Prospect Social Security number, then the social security number is compared to the other prospects as part of the duplicate checking process. If customers have the same email, for example, the duplicate record will be deleted.
b. Select the Corresponding Customer Field from the drop-down.
When the system performs duplicate checking, this field is compared against the Corresponding Customer field in the Customer table for duplicate checking.
c. If applicable, uncheck the Check Duplicate Addresses checkbox.
The system must at least perform address duplicate checking. This field is only enabled if the Prospect Field AND Corresponding Customer fields are selected. If checked, the process includes checking by address as well. If unchecked, you can use only the two Prospect and Corresponding Customer comparison fields. If both the Prospect and Corresponding Customer fields are null, then the Check Duplicate Addresses checkbox is checked and disabled.
11. If you selected "Both Individual and Companies" from the "Load Records for" field, in the Individual to Company Relationship section, perform the following:
a. Select the Relationship Type from the drop-down.
The relationship between the individual and the company is specified using the relationship type. For example, the relation type could be “Employment” with the individual working for the company. The Relationship Code and Reciprocal Code fields are enabled if a value is selected here.
b. Select the Relationship Code from the drop-down.
For example, if the Relationship Type is "Employment", then the Relationship Code would be "Employer".
c. Select the Reciprocal from the drop-down.
For example, if the Relationship Code is "Employer", then the Reciprocal code would be "Employee".
12. Click Save.
13. Now, you can add mapping function information, add special notes, and view upload history.
Prospect Duplicate Check
From the External Prospect List Mapping screen discussed above, you can define which fields you want to check for the finding of duplicate record:
· Prospect Field
· Corresponding Customer Field
Based on the above field value the upload process check,
1. Is there a record already exists based on the corresponding Prospect Field value in the mkt_prospect table? If yes, then this is a duplicate prospect.
2. Is there a record already exists based on the Corresponding Customer Field value in the customer table? if yes, then this is a duplicate customer.
3. If the record is not a duplicate prospect or customer, then the upload process checks to see if the Check Duplicate Address checkbox is checked. If the Check Duplicate Address = 'Y' then
a. If mstrListRecordType = "I" (individual) Or mstrListRecordType = "B" (both individual and company), then:
To identify the duplicate customer, the system check in the CUS_Address_Alias_vw for the following fields:
· substring(First_Name,1 ,1)
· Last_Name
· Address_1
· City
· State
· Postal_Code
To identify the duplicate prospect, the system check in the MKT_Prospect for the following fields:
· substring(First_Name,1 ,1)
· Last_Name
· Address_1
· City
· State
· Postal_Code
b. If mstrListRecordType = "C" (company) Or mstrListRecordType = "B" (both individual and company), then:
To identify the duplicate customer, the system check in the CUS_Address_Alias_vw for the following fields:
· Label_Name
· Address_1
· City
· State
· Postal_Code
To identify the duplicate prospect, the system check in the MKT_Prospect for the following fields:
· Label_Name
· Address_1
· City
· State
· Postal_Code
4. After the above the verification, the system again checks for the following fields in the current list (the uploaded text file) for duplicates:
· last_name
· First_Name
· Address_1
· City
· State
· postal_code
5. After the above the verification, the system again checks for the following fields for the current list (the uploaded text file) for duplicates:
· label_name
· City
· Address_1
· State
· postal_code