•Creating and Executing the Data Management Job
•Data Management Configuration
Summary
Script Name |
Customer Load |
|
Minimum Supported Version |
7.6.0 7.12.0 - Updated 7.16.0 - Script definitions consolidated. 7.24.0 - Added affiliation_source_file |
|
Source Type |
Both Single and Multi Source |
|
Script Definitions |
As of 7.16.0: •Customer Load Prior to 7.16.0: •Customer Load: Using single source file •Customer Load: Using customer, contact and address files •Customer Load: Using customer, contact, address and user files •Customer Load: Using customer, contact, address and membership files •Customer Load: Using customer, contact, address and customer marketing files •Customer Load: Using customer, contact, address, customer marketing, user and membership files |
|
Use Case
The Customer Load script is used to load customer data with their associated contacts, addresses, users, marketing and membership data.
Configuration Prerequisites
If fields referenced in the script do not have 'Writable' set to Yes, the script will still run successfully but those fields will not be updated. The following table lists the required field configuration for this script to run properly. For more information, refer to Configuring Marketing Data Fields.
Source File Format
This script can be run by using a single source file or multiple .CSV or BI (saved as a text/csv 'Extract Tempate') source file(s).
The following source files are used with the Customer Load script:
affiliation_source_file
The following image illustrates the structure required for the affiliations_source_file:
The following table outlines the field mapping:
Column |
Required? |
Source Column Header |
Field Name |
UI Field Location |
1 |
No |
customer_reference_code |
Legacy Account Number |
Customer Services - Customer|Basic page |
2 |
No |
affiliation_name |
Affiliation |
Customer Services - Customer|Advanced page |
3 |
No |
affiliation_start_date |
Start Date |
Customer Services - Customer|Advanced page |
4 |
No |
affiliation_end_date |
End Date |
Customer Services - Customer|Advanced page |
5 |
No |
affiliation_comment1 |
Comment 1 |
Customer Services - Customer|Advanced page |
6 |
No |
affiliation_comment2 |
Comment 2 |
Customer Services - Customer|Advanced page |
7 |
No |
affiliation_description |
Description |
Customer Services - Customer|Advanced page |
8 |
Yes |
affiliation_parent_key |
Required to uniquely identify each row. |
N/A |
customers_source_file
The following image illustrates the structure required for the customers_source_file:
The following table outlines the field mapping:
Column |
Required? |
Source Column Header |
Field Name |
UI Field Location |
1 |
Yes |
customer_reference_code |
Legacy Account Number |
Customer Services - Customer|Basic page |
2 |
No |
organization_type |
Account Type |
Customer Services - Customer|Basic page |
3 |
No |
phone_type1 |
Phone Numbers |
Customer Services - Customer|Basic page |
4 |
No |
phone_number1 |
Phone Numbers |
Customer Services - Customer|Basic page |
5 |
No |
customer_class |
Customer Class |
Customer Services - Customer|Basic page |
6 |
No |
organization_name |
Organization Name |
Customer Services - Customer|Basic page (only available if 'Account Type' is set to Organization) |
7 |
No |
create_date |
Created On |
Customer Serivces - Customer pages |
If you're planning on using a single customer_source_file, you may add columns from other source files to your CSV. Keep in mind that you may only include a single address, contact, affiliation, etc. as there may only be a single row per customer. When configuring the execution job, set your source file in the customer_source_field field.
contacts_source_file
The following image illustrates the structure required for the contacts_source_file:
The following table outlines the field mapping:
Column |
Required? |
Source Column Header |
Field Name |
UI Field Location |
1 |
Yes |
contact_parent_key |
Required to uniquely identify each row |
N/A |
2 |
Yes |
customer_reference_code |
Required to link to the appropriate customer |
N/A |
3 |
No |
company |
Company Name |
Customer Services - Customer|Advanced page |
4 |
No |
title |
Title |
Customer Services - Customer|Advanced page |
5 |
No |
first_name |
First Name |
Customer Services - Customer|Advanced page |
6 |
No |
last_name |
Last Name |
Customer Services - Customer|Basic page |
7 |
No |
Customer Services - Customer|Advanced page |
||
8 |
No |
phone_number1 |
Home Phone |
Customer Services - Customer|Advanced page |
9 |
No |
phone_number2 |
Work Phone |
Customer Services - Customer|Advanced page |
10 |
No |
phone_number3 |
Mobile Phone |
Customer Services - Customer|Advanced page |
11 |
No |
phone_number4 |
Fax |
Customer Services - Customer|Advanced page |
12 |
No |
contact_data[1-5] |
Marketing Data [1-5] |
phone & email section of the Customer Services - Customer|Advanced page |
13 |
No |
birth_date (yyyy-mm-dd format) |
Date of Birth |
Customer Services - Customer|Advanced page |
14 |
No |
is_default Y, y, YES, yes and Yes are all acceptable values. |
default customer contact section |
Customer Services - Customer|Basic page |
address_source_file
The following image illustrates the structure required for the address_source_file:
The following table outlines the field mapping:
Column |
Required? |
Source Column Header |
Field Name |
UI Field Location |
1 |
Yes |
address_parent_key |
Required to uniquely identify each row |
N/A |
2 |
Yes |
customer_reference_code |
Required to link to the appropriate customer |
N/A |
3 |
No |
address_type |
Address Type |
Customer Services - Customer|Advanced page |
4 |
No |
address_data[1-5] |
Marketing Data [1-5] |
address section of the Customer Services - Customer|Advanced page |
5 |
No |
street |
Street |
Customer Services - Customer|Advanced page |
6 |
No |
city |
City |
Customer Services - Customer|Advanced page |
7 |
No |
state |
Province / State |
Customer Services - Customer|Basic page |
8 |
No |
country |
Country |
Customer Services - Customer|Advanced page |
9 |
No |
zip |
Postal Code |
Customer Services - Customer|Advanced page |
10 |
No |
is_default Y, y, YES, yes and Yes are all acceptable values. |
default customer contact section |
Customer Services - Customer|Basic page |
user_source_file
The following image illustrates the structure required for the user_source_file:
The following table outlines the field mapping:
Column |
Required? |
Source Column Header |
Field Name |
UI Field Location |
1 |
Yes |
user_parent_key |
Required to uniquely identify each row |
N/A |
2 |
Yes |
customer_reference_code |
Required to link to the appropriate customer |
N/A |
3 |
Yes |
user_name |
User Name |
Customer Services - Customer|Users page |
4 |
No |
language |
Language |
Customer Services - Customer|Users page |
5 |
Yes |
password |
Password |
Customer Services - Customer|Users page |
6 |
No |
role_id |
Role |
Customer Services - Customer|Users page |
marketing_source_file
The following image illustrates the structure required for the marketing_source_file:
The following table outlines the field mapping:
Column |
Required? |
Source Column Header |
Field Name |
UI Field Location |
1 |
Yes |
marketing_parent_key |
Required to uniquely identify each row |
N/A |
2 |
Yes |
customer_reference_code |
Required to link to the appropriate customer |
N/A |
3 |
No |
data1 |
Marketing Data 1 |
Customer Services - Customer|Basic page |
4 |
No |
data2 |
Marketing Data 2 |
Customer Services - Customer|Basic page |
5 |
No |
currency1 |
Currency 1 |
Customer Services - Customer|Basic page |
|
Hint There could be more marketing fields depending on which fields are provided in marketing_source_file. They should be made readable and writeable either through the Registry or by overwriting permissions before the job is executed. |
membership_source_file
The following image illustrates the structure required for the membership_source_file:
The following table outlines the field mapping:
Column |
Required? |
Source Column Header |
Field Name |
UI Field Location |
1 |
Yes |
membership_parent_key |
Required to uniquely identify each row |
N/A |
2 |
Yes |
customer_reference_code |
Required to link to the appropriate customer |
N/A |
3 |
Yes |
membership_name |
Membership Name |
Customer Services - Customer|Entitlements page |
4 |
No |
level_id |
Membership Level ID (either level or points are required) |
Customer Services - Customer|Entitlements page |
5 |
No |
points |
Total Points (either level or points are required) |
Customer Services - Customer|Entitlements page |
6 |
No |
start_date |
Start Date |
Customer Services - Customer|Entitlements page |
7 |
No |
expiry_date |
Expiry Date |
Customer Services - Customer|Entitlements page |
How the Script Works
The following table outlines the actions and expected results of the script:
Condition |
Action |
Expected Results |
The customer_reference_code must have a value that should refer to, for example. contact_parent_key or address_parent_key to create a customer. |
The customer_reference_code looks for corresponding parent_key from other source files which should match to the customer_reference_code to create a customer with information linked in all source files based on this key. |
The customer record will be created with all of the information provided in all of the source files which are linked with customers_source_file by customer_reference_code. |
Each customer should have at least one contact and address record in contacts_source_file and address_source_file, respectively. |
Script looks for each customers' corresponding contact and address record with all the required fields entered or an error is thrown if it is missing. |
A new customer with contact and address information is created. |
All fields set as 'Required' Yes in the Registry must have values in the source files. |
Script checked if all of the required files are received from the source data. |
If any fields that are set as 'Required' Yes in the Registry and are not supplied in the source data, the script will fail. |
Creating and Executing the Data Management Job
Script Definition Configuration
The following tables outline the script definition configuration:
•Script Definition Name: Customer Load
•Application Script: Customer Load
Source Section
|
||||
Source Name |
Source Type |
Source Key Column |
Parent Source Name |
Parent Key Column |
customers_source_file |
Comma Separated Values |
customer_reference_code |
blank |
blank |
•Script Definition Name: Customer Load
•Application Script: Customer Load
Source Section
|
||||
Source Name |
Source Type |
Source Key Column |
Parent Source Name |
Parent Key Column |
customers_source_file |
Comma Separated Values |
customer_reference_code |
blank |
blank |
contacts_source_file |
Comma Separated Values |
contact_parent_key |
customers_source_file |
customer_reference_code |
address_source_file |
Comma Separated Values |
address_parent_key |
customers_source_file |
customer_reference_code |
•Script Definition Name: Customer Load
•Application Script: Customer Load
Source Section |
||||
Source Name |
Source Type |
Source Key Column |
Parent Source Name |
Parent Key Column |
customers_source_file |
Comma Separated Values |
customer_reference_code |
blank |
blank |
contacts_source_file |
Comma Separated Values |
contact_parent_key |
customers_source_file |
customer_reference_code |
address_source_file |
Comma Separated Values |
address_parent_key |
customers_source_file |
customer_reference_code |
membership_source_file |
Comma Separated Values |
membership_parent_key |
customers_source_file |
customer_reference_code |
•Script Definition Name: Customer Load
•Application Script: Customer Load
Source Section |
||||
Source Name |
Source Type |
Source Key Column |
Parent Source Name |
Parent Key Column |
customers_source_file |
Comma Separated Values |
customer_reference_code |
blank |
blank |
contacts_source_file |
Comma Separated Values |
contact_parent_key |
customers_source_file |
customer_reference_code |
address_source_file |
Comma Separated Values |
address_parent_key |
customers_source_file |
customer_reference_code |
user_source_file |
Comma Separated Values |
user_parent_key |
customers_source_file |
customer_reference_code |
Data Management Configuration
The following outlines the data management job configuration:
•Data Management Job Name: Customer Load
•Script Definition: Customer Load
The following image is an example of the Data Management|Schedule page when creating a job using all of the available source files:
