NCOA

With members constantly on the move and relocate, mails cannot be delivered to the correct address. This has incurred additional cost in mailing and also delayed the delivery of important information to the members. NCOA (National Change of Address) is a service that tracks the status of addresses and provides the sender accurate information about a particular address. The NCOA service is provided by different vendors. Abila clients may select their own NCOA vendors to process the address information. Each vendor might implement this process differently. The file layout and the code value can be vendor specific.

Following is a list of benefits for clients using NCOA service:

  • Less return mail with out of date addresses
  • Less time spent updating return mail to update addresses
  • Savings on postage cost to send mail that is returned
  • Reduction in the amount of items clients prints for mailing. Not only does it save the organziation money it also helps its green status.
  • Greater faith in the accuracy of our data which impacts our ability to properly segment our markets and thereby better communicate with our various constituent groups
  • Members/Constituents also get the message sooner because the right address is used the first time rather than having to resend
  • Clean database reduces the time client takes to manage records that will yield no return
  • USPS offers greater discounts to bulk mailers who perform NCOA on their data
  • NCOA also standardizes addresses by USPS standards and provide zip +4 information
  • NCOA also provides Return Codes which indicate that a match was obtained accompanied by a new address or undeliverable status or they indicate a match was not obtained. These codes help clients better analyze their data.
  • To underscore the importance of NCOA, USPS estimates over 40 million Americans change their address annually. The larger the constituent list the higher potential to lose contact with people as their addresses change.

Abila has developed an add-on feature that is highly configurable way to minimize the effort in accomplishing this goal based on each vendor’s requirement.

The following list of action items describes the process at a high level.

  1. Client contacts Abila for this integration and understands the process. Client will sign agreement with Abila for the process and the consultation.
  2. Client selects and contracts a NCOA vendor.
  3. Client learns how the vendor processes the file and provides FTP site(s), login and password or file location, exchange frequency for the process to Abila. Client also provides the file layout (both import and export)
  4. Abila will configure the output file store procedure to match the export file provided by the client.
  5. Abila will configure the import file column mapping based on the import file layout provided by the client.
  6. Client will set up the schedule to run the import/export process.
  7. Client needs to create the email templates so email can be sent when the NetForum process succeeds or fails.
  8. The process will run according to the schedule for export and import. NetForum information will be updated when data is imported back from the NCOA vendor.

Client Tasks

  • Client should have selected a vendor to perform the NCOA process
  • Client works with the vendor to identify the export/import file layout and upload/download process that the vendor requires to process NCOA.
  • Client understands from the vendor regarding the result code and its related process (hold, update)
  • Client provides the import/export file layout and upload/download information from the vendor to Abila
  • Client works with Abila to configure the process (export store procedure, system opton and filed mapping)
  • Client works with Abila to perform a testing of 200 sample records
  • Client will set up the schedule for the import/export

Abila Tasks

  • Abila will deploy NCOA add on metadata and related components to the client site
  • Abila will work with client on configuring the NCOA process (export store procedure, system option, field mapping)
  • Abila will work with client in testing this process (200 sample records)

Usage and Instructions

This section describes how the integration works in NetForum:

NetForum NCOA Specific System Options

  • AvectraNCOAUpdateCodes

Purpose: This option lets the NCOA process know which imported NCOA records should update addresses in NetForum. Description: This option is vendor specific. The client should provide Abila with the update codes that should update addresses or set this option themselves. All values should be entered as comma separated.

  • AvectraNCOAHoldCodes

Purpose: This option will contain NCOA update codes that should put an address on hold in NetForum. Description: This option is vendor specific. If a client chooses to put addresses on hold for specific update codes, those codes should be entered in this system option separated by commas. If this option has values, the AvectraNCOAHoldTypeKey system option must be set as well.

  • AvectraNCOAHoldTypeKey

Purpose: Sets the address hold type for addresses placed on hold by the NCOA process. Description: This option is client specific. Client should setup address hold types in NetForum and choose a specific address hold type for the NCOA process. Then the key value (GUID) for the address hold type the client identifies should be entered in this system option.

  • AvectraNCOAImportProcessWorkingFolder

Purpose: Client sets this folder on the local server to allow the download of the NCOA import files from FTP. Description: Relative path from the binaries folder to place import files from a FTP location. Default value is “..\tempfiles\iweb\temp”. Location needs to be accessible to the TaskAdminUser.

Baseline System Options that should be set for NCOA

Since the NCOA schedule process is run as a windows service, client needs to set up the system options below so process can be executed at the time specified.

The following values are set up by Abila:

  • ScheduledTaskAccountName

This is the Windows or SQL login name that is privileged to create scheduled tasks. If the ScheduledTaskProvider option equals "SQL", then an SQL login is assumed, else a Windows login is assumed. The ScheduledTaskProvider will be set up as “Windows” for NCOA purpose.

  • ScheduledTaskAccountPwd

This is the password of the Windows or SQL Server account that is privileged to execute scheduled tasks (identified by the ScheduledTaskAccountName system option). This will be the Windows account password for the ScheduledTaskAccountName password.

  • ScheduledTaskComputerName

This is the computer name where NetForum scheduled tasks (e.g. MembershipRenewals.exe) are run from. The default is the local web server computer.

FTP Setup

If the NCOA vendor requires FTP file upload/download for processing of data, then FTP site information needs to be configured for this integration. Client needs to provide a URL, username, and password for the ftp site(s). A FTP record can be created in the admin module of NetForum.

Follow the steps below to create an FTP record:

I. Go to admin module, expand the advance menu, expand the Abila FTP menu and select Add

II. Enter in the URL, username, and password provided

Note: Passwords are encrypted and are un-encrypted during the NCOA process.

NCOA File Setup

NCOA files configuration can be setup from the CRM module by expanding the Abila NCOA menu.

Export Setup

Client will create export records in this table to configure the file location and layouts. This information will be used in the automated process to generate the output file.

Export of NCOA files by default will use the stored procedure client_avectra_ncoa_output. This store procedure should be customized to produce the dataset required by the NCOA vendor that the client selects. Export file format should be either in TXT or CSV. To produce an export of another file type will be considered a customization and it is not part of the integration discussed in this document.

The output file has the following columns in sequence:

  • process_date
  • cst_member_flag
  • cst_recno
  • cxa_key
  • ind_prf_code
  • ind_first_name
  • ind_mid_name
  • ind_last_name
  • ind_sfx_code
  • adr_line1
  • adr_line2
  • adr_city
  • adr_state
  • adr_post_code

The export record will utilize the data returned by the store procedure and apply the configuration definition and export to the location specified in the table.

Configuring a file export record

  • Enter in a code name for the export file definition.
  • If column names are in the first row of the file, check the “include header row” check box.
  • Select action “export” in the dropdown.
  • Chose a file format for the export file CSV, or text (TXT). Please note that only CSV or TXT are applicable to the Export File.
  • Select a FTP location for the file if needed.
  • Enter in a relative file path. If using FTP the folder should use a forward slash (/), if using a location on the web server us a backslash (\). Please note that if the export file is being saved on the web server (Not FTP option), client needs to VPN in to access the file and provide it to the vendor.
  • Enter in a file name without the extension.
  • Select an email successful template (used when process succeeds). The template should be based on the Abila NCOA File schedule to send out general information.
  • Select an email failure template (used when process fails)
  • Enter in a delimiter between values (default is a comma)
  • Enter in text start and end qualifiers if values should be encapsulated by a character or string(default is a quote)

Import Setup

Client will need to set up the import file configuration so NetForum will bring the NCOA processed records into the NetForum table to update the address records.

Import file format can be TXT, CSV, or excel (2003 xls or 2007 xlsx). All imported records are first inserted into “client_avectra_file_ncoa_process” table.

Table structure for client_avectra_file_ncoa_process:  

NetForum will use the information in this table to update NetForum address related tables. The tables that are updated by the NCOA process are:

co_customer_x_address co_customer_x_address_ext co_address co_addres_ext

Please note that when the above tables are updated from the NCOA process, the column names will be updated from the column names in client_avectra_file_ncoa_process table without the prefix of zze_. For example, if an address record is being updated, the value in the adr_line1 will be updated from the value in zze_adr_line1, the value in adr_post_code will be updated from the column zze_adr_post_code.

Configuring a file import record

  • Enter in a code name for the import file.
  • If column names should be included in the import file check the “include header row” check box.
  • Select action “import”
  • Chose a file format for the export file (CSV, TXT, XLS, or XLSX).
  • Select a FTP location for the file if needed.
  • Enter in a relative file path. If using FTP the folder should use a forward slash (/), if using a location on the web server us a backslash (\). Please note that if the import file is being saved on the web server (Not FTP option), client needs to VPN in to access the file and provide it to the vendor.
  • Enter in a file name without the extension.
  • Select an email successful template (used when process succeeds).
  • Select an email failure template (used when process fails). The template should be based on the Abila NCOA File schedule to send out general information.
  • Enter in a delimiter between values (default is a comma)
  • Enter in text start and end qualifiers if values should be encapsulated by a character or string (default is a quote)

The NCOA import process needs to know the content of the import file since the file layout is vendor specific. Client needs to configure column mapping between the import file and the NCOA process table (client_avectra_file_ncoa_process). On the import code profile page, there is a child form called “NCOA import columns”. This child form should be used for import codes and it is not used for “export” codes.

Adding a file import column record by clicking on the “+” (add) icon:

  • Select a column from the NCOA process table dropdown.
  • If column headers are included in the file enter in the column header exactly as it appears in the file.
  • If column headers are not included in the import file, then leave the column name in file box blank. Enter the column order in the “order” box that map to the NCOA column name.
  • Continue this process until all columns in the import file are mapped to the NCOA process table. The purpose is to configure the import process so all the records in the import file will be inserted into the NCOA process table and then be updated against the NetForum address related tables.

If in the import file there is column heading, system will read the definition and try to map the column (based on the import file column name) and update the NCOA column name. If the import file does not have column headings, then system will use the “order” to match the column in the import file.

Receiving Email Notifications

If a success or failure correspondence template is selected for import/export file, recipients should be added using the email list child form on the NCOA file profile.

An email address should be entered and the check box for send email on success and/or failure should be checked. There can be multiple email addresses entered in this child form. All email addresses in the list will receive emails upon success or failure of the process based on the configuration.

The email content will be based on the template that was defined at the NCOA code

Scheduling an Import or Export

The import/export process needs to be scheduled. Due to the mass volume of data being processed and its associated complexity, tt is not designed to be a process that staff will click a button and get executed immediately. Each time the process needs to run a record should be created in the “schedule” child form. If the record in the profile is an “import” process, then the schedule will mean when the “import” will take place. If the record in the profile is an “export” process, then the schedule will define when the “export” will happen.

A new “schedule” record should be created by using the “+” (add) icon.

When a schedule record is added, a start time and date must be entered. The start date is defaulted to the current date. Client should anticipate that the process will take more than 4 hours (based on the volume of data) and all schedules should be run during non-business hours. Sometimes an import file can have more than 200K records and it will take more than 8 hours to run and it is recommended even to be run over the weekend while minimum traffic is expected.

Error Reporting

NetForum tracks errors during the import process. All errors will be logged in this child form. This child form is intended for “import” process only and does not apply to “export” process.

If there are errors in the import process, client should review the errors and either manually update the NetForum information or client can fix the import file and create another import schedule. The error log will be reset at the next import process.