•Creating and Executing the Data Management Job
Summary
Script Name |
Customer Address Update (FKA NCOA – Customer Updates) |
|
Minimum Supported Version |
7.0.0 7.14 - Updated |
|
Source Type |
Single Source |
|
Script Definitions |
Customer Address Updates |
|
Use Case
The Customer Address Updates script is used to update customer addresses that have been provided to the National Change of Address (NCOA) for updating/cleansing.
The script receives a .CSV file containing the processed customer address records. After the script is executed, the customer addresses will be correct and up-to-date based on the NCOA cleansing. The status of the NCOA update can be tracked using the ‘Change of Address Status’ field on the Customer Services - Customer|Advanced page if the status is provided. For more information, refer to Configuration Prerequisites.
To update/cleanse customer address records:
1.Extracting the customer address record information that needs to be sent to NCOA for processing.
This can be achieved by using the Customer Address Extract BI. The information should be extracted using the Business Intelligence CSV Report Extract (text/csv) 'Extract Template'. This will result in creation of a CSV with customer address records. The 'First Name', 'Last Name' and 'Company' must be included in the extract file (as these columns are necessary for NCOA processing).
|
Hint 1.The Customer Address Extract BI should be modified to apply a filter based on a field which can determine the subset of customer addresses that need to be sent to NCOA before the extract is performed as there is no filter criteria applied to the BI by default. 2.The extract should be run using the Business Intelligence CSV Report Extract (text/csv) extract template as the script uses the 'Address ID' column in the .CSV source file to uniquely identify the records to process and update the corresponding customer address records. |
2.The extracted .CSV file will be bulk updated outside of the system. It is recommended that the following standards are followed when the bulk updates on the file are performed:
a.The column headers used by the Customer Address Extract BI extract must not be altered; however, some new columns may be added to the .CSV. If you intend to store the address record return code in the database, that column must be called ‘Return Code’. In case multiple street values need to supplied by the source file after the NCOA updates, the headers for the CSV have to be ‘Street’, ‘Street1’ and ‘Street2’ respectively. The script will concatenate these three values and set that as the street value. Any other new columns will be ignored by the script.
b.The columns called ‘Customer Number’ and ‘Address ID’ must not be altered in any way, as these columns are used to identify customers and their address records.
3.The script will use the updated .CSV source file as the input to the script job in Utilities - Data Management|Schedule page, to update customer addresses.
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:
Field Name |
Registry Location |
Field Configuration |
Comments |
change_of_address_status |
Registry::EN::Business Objects:: TScustomerBO::Addresses |
Lists: None Mode: •Unbound: Default •Persistable: Default •Optional: Default •Masked: Default •Readable: Yes •Writeable: Yes Subtype: None |
This field is only required if you want the 'Return Code' value to be saved to the database. |
Source File Format
This script is run using a single .CSV or BI (saved as a text/csv 'Extract Tempate') source file.
The following two images illustrate the structure required for the source file used for the script.
NCOA_BI_Extract.CSV
The following image is an example of the BI extract that would be provided to NCOA:
NCOA_Cleansed_Data.CSV
The following image is an example of the data that would be returned from NCOA:
The following table outlines the field mapping for the fields that will be updated by the script:
Column |
Source Column Header |
Field Name |
UI Field Location |
1 |
City |
City |
Customer Services - Customer|Basic and Customer|Advanced pages |
2 |
State |
State |
Customer Services - Customer|Basic and Customer|Advanced pages |
3 |
Postal/Zip Code |
Postal Code |
Customer Services - Customer|Basic and Customer|Advanced pages |
4 |
Country |
Country |
Customer Services - Customer|Basic and Customer|Advanced pages |
5 |
Return Code |
Change of Address Status |
Customer Services - Customer|Advanced page |
6 |
Street, Street1, Street2 |
Street (concatenated value from the three source columns) |
Customer Services - Customer|Basic and Customer|Advanced pages |
The following table outlines the fields contained in the source file that will not be updated by NCOA:
Column |
Source Column Header |
Purpose of the column |
1 |
Customer Number |
This is used to find the customer whose address records need to be updated. |
2 |
Default First Name |
This is required for the NCOA cleansing process and is not updated by the script. |
3 |
Default Last Name |
This is required for the NCOA cleansing process and is not updated by the script. |
4 |
Default Company |
This is required for the NCOA cleansing process and is not updated by the script. |
5 |
Address ID |
The unique identifier for the address record for a given customer. This value uniquely identifies the address record which may or may not be the 'Default Customer Address'. This value is not updated by the script. |
6 |
Rank |
The script uses this column to help uniquely identify records to process and update addresses for a given customer as they can have multiple addresses in the system. |
7 |
Total |
This comes from the BI extract template and determines the total number of records. |
How the Script Works
The following table outlines the actions and expected results of the script:
Condition |
Action |
Expected Results |
A valid 'Customer Number' is provided in the source file. |
Load the customer based on the 'Customer Number' in the source file. |
The customer is loaded and the address records that may need updates are determined. If a customer with the given 'Customer Number' is not found, then the script does nothing although the backend would report an error. |
A value is provided in the 'Address ID' column in the source file. |
The script will update the address with this ID for the customer. If the Address ID value is not provided by the source file, then the customer’s default address is updated. |
An address node with either the provided ID or the default address record gets updated. |
Creating and Executing the Data Management Job
Script Definition Configuration
The following tables outline the script definition configuration:
•Script Definition Name: Customer Address Updates
•Application Script: NCOA – Customer Updates (text/plain)
Source Name |
Source Type |
Source Key Column |
Parent Source Name |
Parent Key Column |
NCOA_customer_source_file |
Comma Separated Values |
Address ID |
None |
None |
Data Management Configuration
The following outlines the data management job configuration:
•Data Management Job Name: NCOA Customer Address Updates (for example)
•Script Definition: NCOA – Customer Updates
•Concurrent Runs: 8
The following image is an example of the Data Management|Schedule page when creating a job using all of the available source files:
