Civis takes a table of names and addresses, processes the records through USPS CASS-certified address standardization, and optionally matches records against the National Change of Address (NCOA) database. The Address Correction job can be found on the top navigation bar under the Tools>Enhancements tab.
Note about NCOA
NCOA allows you to make sure your mailing lists are up to date by comparing addresses to the USPS National Change of Address database. The service covers address changes up to the past 18 months. In order to obtain access to this feature, please contact firstname.lastname@example.org. USPS requires that your organization fill out a Processing Acknowledgement Form (PAF).
Source Table Details
- Database: Select the Redshift cluster containing the data you would like to standardize.
- Schema: Select the schema containing the table with the address records.
- Table: Select the table containing address records.
- WHERE clause: If you’d like to limit which records are standardized, you can do so here. Note: "where" will be automatically added at the beginning of your response. You will receive an error if you retype "where" into the box.
- Database: Select the Redshift cluster you would like the job output written to.
- Schema: Select the schema you would like the job output written to.
- Table: The name of the table the job will create. If left blank the table name will be the same as the source table with “cass_00000” appended, where “00000” represents a random string of numbers.
- Perform NCOA: Check this box to match your records against the NCOA database.
- Output Level: Determine which columns will appear in the output table. Note that all rows will be returned regardless of column output level.
- standardized addresses only: Standardized input addresses and appended USPS delivery information
- change-of-address fields only: New addresses (populated only for records that match the NCOA database), USPS delivery information, and NCOA-specific information, such as move type and effective date of the move
- coalesced: Standardized current address for each record (new address for NCOA-matching records, standardized input address for non-matching records)
- all fields: All of the above columns and all raw input columns. Note that this option results in many null fields and redundant data.
Select the column names for the corresponding fields.
Note: when “Perform NCOA” is checked, “Name” must be mapped to one of the following: a "Full Name" column, separate columns for "First Name" and "Last Name", or a "Company Name" column. For CASS-only jobs where names are not known, “Name” can remain unmapped.
Once the job is complete, the output will be a table with the primary key you specified and the columns that correspond to your selected output level.
Please reference the following attachments to this article for more detail on CASS/NCOA data:
- Column Definitions: columns in the output table of a CASS/NCOA job.
- DPV Footnotes: DPV footers and statuses.
- Error Status Codes: address error codes in the U.S. and Canada.
- Record Types: record type indicators for the assigned address.
- Return Codes: NCOA return codes.
Several files will be generated in the output.
- NCOA Link Report - NCOALink processing summary and match breakdown.
- CASS Report - Address coding and type summary.
- USPS Form 3553 - Official USPS form to certify mailing list was processed thru authorized CASS/NCOA software.
Note that the CASS/NCOA job splits up large input tables into chunks (currently, chunks of up to 250,000 records). Each of the output files will be produced for each chunk, meaning that there would be multiple USPS 3553 forms, for example. If you need to be able to associate a USPS 3553 form with a specific set of records, we recommend splitting up your input table into chunks of 250,000 or fewer records.