Previous Article | matchIT SQL Index | Next Article |
Input Fields
The following fields must be input to the NCOA process, and will be written to the output table:
Name |
Description |
FullName or |
Fullname field. Optional if an organization is specified |
FirstNames and MiddleNames and LastName |
If FullName is not used, then both Firstnames and Lastname fields are required. Middlenames is optional. |
Organization |
Can be used to specify the company name. You must enter in a Name or a company for NCOA, or you can submit both a name and a company. Optional if a Fullname or Firstnames/Lastname is specified. |
Street |
Sets the “Street” field of the lookup record. Use this field type to pass all information relating to the primary street address, including the street name, house number, directional’s, and street suffix. You can also use this field type to pass all other acceptable forms of primary address information, such as PO Box numbers, rural route numbers, and highway contract numbers. In addition, suite and apartment information can be passed with primary address information through the “Street” field. For example, “123 Main St” and “123 Main St, Apt A” are both acceptable. |
Street2 |
Optional. Can be used to specify any suite and apartment information, if these are in a separate column from the street part of the lookup address (see Address1 above). |
Urbanization |
Optional. In Puerto Rico, identical street names and address number ranges can be found within the same ZIP Code. In these cases, the urbanization name is the only element that correctly identifies the location of a particular address. |
City |
Specifies the city. |
State |
Specifies the state. |
ZipPlus4 or |
Specifies the 9-digit ZIP code. (also use this if your data has a mix of 5 and 9 digit zips in the same column) |
Zip5 and Plus4 |
Specifies the 5-digit ZIP code and the ZIP+4, if these are in separate columns. |
Output Fields
The following fields are written to the output table:
Name |
Description |
ncoaFullName or |
Only if the fullname was specified on input. |
ncoaFirstNames |
Only if the fullname was not specified on input. |
ncoaCompany |
Optional. Retrieves the company name. This is only output if Organization was passed in via the input fields, or if this optional field is enabled (see Optional Output Fields below). |
ncoaStreet |
Retrieves the street part of the output address. |
ncoaSuite |
Optional. Retrieves the suite or apartment information of the output address. This is only output if street2 was passed in via the input fields, or if this optional field is enabled (see Optional Output Fields below). |
ncoaCity |
Retrieves the city from the output address. |
ncoaState |
Retrieves the output state abbreviation. |
ncoaZipplus4 |
Retrieves the output ZIP Code. |
2.3.3 Optional Output Fields
Any of the following fields can be written to the output table: ncoaCoaCode, ncoaCoaFound, ncoaMoveEffectiveDate, ncoaDPV, ncoaMoveType, ncoaresult, and ncoaCorrections are all chosen automatically when first setting up an SSIS task
Name |
Description |
ncoaCOACode |
NCOALink Return Code Summary: |
ncoaCoaFound |
COA Match Code |
ncoaMoveEffectiveDate |
COA move effective date (YYYYMM) |
ncoaCompany |
Retrieves the company name that was entered through the input “organization” field, if any. The 48 Month NCOA process will also verify company moves based off this the company field. |
ncoaSuite |
Retrieves output secondary street information, such as suite and apartment information, if such information was entered through the input “street2” field. |
ncoaPlus4 |
Retrieves the output ZIP+4 Code. The ZIP+4 Code is the 4-digit extension only. You can retrieve the 5-digit ZIP Code using the ncoaZIP field type described earlier in this table. |
ncoaUrbanization |
Retrieves output urbanization information for Puerto Rican addresses. |
ncoaDP |
This string consists of a 2-byte delivery point code and the 1-byte checkdigit code |
ncoaCRRT |
Retrieves the output carrier route code. This is a 4-digit code assigned to each address on a mail carrier’s route. |
ncoaLotCode |
Retrieves the output Line of Travel identifier consisting of a 4-digit number, plus a 1-character sequence code (either "A" for Ascending or "D" for Descending which is in the ncoaLotDir). The 4-digit number indicates the order in which delivery will be made within a given ZIP+4. The 1-character sequence code indicates whether delivery will be made in ascending or descending order. Once a LOT code is appended to your data file records, you can use it to presort your mailings so that they qualify for Enhanced Carrier Route rates. |
ncoaLotDir |
The order in which the mail carrier delivers mail within a given carrier route. When you include the Line of Travel information, your mail may be eligible for the USPS Standard Mail Non-Automation Basic Enhanced Carrier Route Presort Rate. |
ncoaCountyNumber |
Retrieves the output county number. The Federal Information Processing Standard Publication is a five-digit Federal Information Processing Standards code which uniquely identified counties and county equivalents in the United States, certain U.S. possessions, and certain freely associated states. |
ncoaCountyName |
Retrieves the output county name. |
ncoaCongDistrict |
Retrieves the output congressional district code. This is a 2-digit identifier for the United States congressional district to which the input address belongs. |
ncoaLACS |
LACSLink return code |
ncoaHouseNumber |
Retrieves the house number for the output street address. For example, if the output street address is “123 Main St,” the house number is “123.” |
ncoaPreDirectional |
Retrieves the pre-directional for the output street address. For example, if the output street address is “123 E Main St,” the predirectional designator is “E.” |
ncoaStreetName |
Retrieves the street name for the output street address. For example, if the output street address is “123 Main St,” the street name is “Main.” |
ncoaStreetSuffix |
Retrieves the street suffix for the output street address. For example, if the output street address is “123 Main St,” the street suffix is “St.” |
ncoaPostDir |
Retrieves the post-directional for the output street address. For example, if the output street address is “123 Main St N,” the postdirectional is “N.” |
ncoaSUD |
Retrieves the secondary unit designator (SUD) for the output street address. For example, if the output street address is “123 Main St Apt 12,” the SUD is “Apt.” |
ncoaUnitNum |
Retrieves the unit number for the output street address. For example, if the output street address is “123 Main St Apt 12,” the unit number is “12.” |
ncoaLeftovers |
Extra non-address information found below or in the |
ncoaPMB |
Retrieves the output Public Mailbox address |
ncoaDPV |
Retrieves the 5-byte DPV (Delivery Point Verification) match codes. The match codes indicate whether or not the address is valid (and if not, why not), whether or not the address is within a Commercial Mail Receiving Agency (CMRA), and whether or not the address was flagged as a False Positive. The “DPV” field returns a separate code in each position of the 3-byte result string, as follows: BYTE 1 blank – could not assign, likely invalid Y – All delivery point components of the address were DPV validated. D – The address’s building number was DPV validated, but required unit-level information is missing. S – The address’s building number was DPV validated, but the unit number is invalid. N – The address’s building number is invalid. BYTE 2 blank – The address was not coded by CASS process and therefore no DPV processing was performed. Y – The address was found in the CMRA (Commercial Mail Receiving Agency) table. N – The address was not found in the CMRA (Commercial Mail Receiving Agency) table. BYTE 3 blank – Intentionally left blank for backwards compatiability - BYTE 4
|
ncoaDPVAnswer |
blank – The address was not coded by the CASS process and therefore no DPV processing was performed. Y – All delivery point components of the address were DPV validated. D – The address’s building number was DPV validated, but required unit-level information is missing. S – The address’s building number was DPV validated, but the unit number is invalid. N – The address’s building number is invalid. |
ncoaDpvCMRA |
Commercial Mail Receiving Agency |
ncoaDpvFalsePositive |
This is a seed table used by the Delivery Point Verification Service (DPV). It is used by the service to guard against the possibility of mailers manufacturing artificial mailing lists from the data in the DPV database of every valid delivery point in the U.S. |
ncoaDpvFootnotes |
Retrieves the 8-byte DPV (Delivery Point Verification) Footnotes string. This field returns up to four 2-character codes that supplement the DVP field codes (described above) providing additional information about the DPV match/mismatch. Up to four of the following 2-character codes will be returned: AA –Input address matches to ZIP + 4 file A1 – Input address does not match ZIP + 4 file. BB – All input address components match DPV file. CC – Input primary number matches DPV file F1 - Input address matches Military address G1 - Input address matches General Delivery address: N1 – Input address primary number matches DPV file M1 – Input address primary number is missing:. M3 – Input address primary number is invalid. P1 – Input address is missing the PO, RR or HC number P3 - Input address PO, RR or HC number is invalid: RR – Input address matches (CMRA) Commercial Mail Receiving Agency . R1 – The input address was identified by DPV as a Commercial Mail Receiving Agency (CMRA), but required unit-level information is missing. U1 - Input address matches Unique ZIP Code: |
ncoaLastLine |
Retrieves the output ‘last line’ string. This output ‘last line’ string is a formatted city/state/ZIP string that includes the correct ZIP+4 Code. For example, the ‘last line’ information would be like “Agoura Hills, CA 91301-4301.” |
ncoaMoveType |
Indicates the move as Family, Business, or Residential. |
ncoaResult |
COA Action Code |
ncoaCorrections |
The type of address that was verified/corrected |
ncoaErrorMessage |
COA Delivery Code |
ncoaWasEWSCoded |
Early Warning System |
ncoaWasDPVCoded |
DPV Status code |
ncoaDSF2NoStats |
No Stat indicator |
ncoaVacant |
Vacant address flag |
ncoaSuiteLink |
A = SuiteLink match - Secondary address returned |
ncoaSource |
COA Address Source indicator |
ncoaQueryName |
NCOALink Query Name |
ncoaPCOAConfidence |
PCOA Confidence Score |
Reports
Five reports are created after NCOA processing:
- Address Quality Report (PDF); A breakdown of the Zip+4 coding, the address type, SuiteLink and LACSLink processing as well as the DPV Status
- CASS Report (PDF)
- Data Quality Summary (PDF); A basic summary of the address quality and NCOALink hits
- Layout Report (PDF); A summary of the layout of the raw data returned from the server - this data will be imported into SQL as a new table and then
- NCOALink® Report (PDF);
When processing has completed, the reports are moved to the reports folder (as specified by the path attribute of the outputSettings/reports node in the configuration file).
These reports can not be disabled and are separate from the reporting that you choose at the beginning of the installer. If you run through a number of records greater than your file size limit set in the advanced options, then you will get back multiple NCOA reports.
Previous Article | matchIT SQL Index | Next Article |