48 MONTH NCOA Inputs and outputs and Reports in mSQL

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 
and 
MiddleNames
and 
ncoaLastName

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:
New Address Provided
A - COA Match
91 - COA Match: secondary number dropped from COA
92 - COA Match: secondary number dropped from input

Found COA
01 - COA Match: Foreign Move
02 - COA Match: Moved left no address
03 - COA Match: PO box closed no forwarding address
05 - COA Match: A new address can not be provided
14 - COA Match: New address would not convert
19 - Found COA: New address not ZIP + 4 or DPV confirm

Cannot Match COA
00 - No COA Match 
04 - Cannot Match COA: Street address with secondary
06 - Cannot Match COA: Middle name conflict
07 - Cannot Match COA: Gender conflict
08 - Cannot Match COA: Conflicting instructions
09 - Cannot Match COA: Highrise default
10 - Cannot Match COA: Rural default
11 - Cannot Match COA: Insufficient COA name
12 - Cannot Match COA: Middle name test failed
13 - Cannot Match COA: Gender test failed
15 - Cannot Match COA: Individual name insufficient
16 - Cannot Match COA: Secondary number discrepancy
17 - Cannot Match COA: Other insufficient name
18 - Cannot Match COA: General delivery
20 - Cannot Match COA: Conflicting directions

"Daily Delete" Matches
66 - Daily Delete

ncoaCoaFound

COA Match Code
A = NCOALink Match
B = No exact match to NCOALink - examine NCOALink
Return Code for possible reasons
C = No Match
M = PCOA Match
N = Canadian NCOA Match
O = No exact Match
P = PCOA Plus Match

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
A = LACSLink record match
00 = No match
09 = match to old address, no new address provided
14 = found LACSLink record, but couldn't convert the
data to a deliverable address
92 = LACSLink record matched after dropping the
secondary number from the input address
blank = no lookup attempted

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
address line

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.

L - The address triggered a DPV lock

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.

L - The address triggered a DPV lock

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.

Intentionally left blank for backwards compatiability - if an L is returned in the ncoaDPVAnswer or ncoaDpvCMRA then that would indicate you hit an address on the Seed Table.

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
but the secondary address does not match

F1 - Input address matches Military address

G1 - Input address matches General Delivery address:

N1 – Input address primary number matches DPV file
but high-rise address is missing secondary address

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
This field can be used as a guide to determine which
records to mail, and which records not to mail. Your
judgment should always be used; this field is to serve
as a guide only.
1 = Accurate, mailable address
3 = Might Be Deliverable
Record might have moved, examine NCOALink Return Code
4 = Probably Undeliverable
5 digit ZIP Code confirmed but record did not
receive a ZIP + 4, OR missing secondary address and
located in a CMRA (CMRA analysis only available with
DSF2 / DPV)
5 = Undeliverable
Record has moved with no forwarding address
available, or 5 digit ZIP not confirmed

ncoaCorrections

The type of address that was verified/corrected
F = firm
G = general delivery
H = high rise
P = PO Box
R = Rural Route or Hwy Contract
S = Street
M = Military
blank = unassigned

ncoaErrorMessage

COA Delivery Code
D = NCOALink Daily Delete
F = Foreign Move
G = PO Box Closed
K = Moved, Left No Address
M = Moved, forwarding address provided
P = Moved, however new address could not be verified.
No new address returned

ncoaWasEWSCoded

Early Warning System
If a record matches to this file it means that even
though a ZIP + 4 code was not returned the address is
valid, and a ZIP + 4 will be available soon - typically
within the next monthly CASS update.
Y = Matched
N = No Match

ncoaWasDPVCoded

DPV Status code
Y = confirmed delivery point
N = not a valid delivery point
S = primary range is valid, secondary range wrong
D = no secondary range
L = address triggered a DPV lock
blank = could not assign

ncoaDSF2NoStats

No Stat indicator
No Stat means that the address is a vacant property, it
receives mail as a part of a drop, or it does not have
an established delivery yet.
Y = Address is No Stat
N = Address is not No Stat
blank = no match to DSF2 file

ncoaVacant

Vacant address flag
Y = vacant address
N = not a vacant address
blank = no match to DSF2 file

ncoaSuiteLink

A = SuiteLink match - Secondary address returned
00 = Lookup attempted but no matching record found:

ncoaSource

COA Address Source indicator
N = COA address was obtained from NCOA process
P = COA address was obtained from PCOA process
C = COA address was obtained from Canadian process
blank = no COA information present, or PCOA was not run

ncoaQueryName

NCOALink Query Name
This is the exact name data that was used to make the
match to the NCOAlink database. If an individual or
business match was made, the full name will appear
here. If a family match was made then only the last
name will be present.

ncoaPCOAConfidence

PCOA Confidence Score
1 = High confidence
2 = Confident
3 = Less Confident
4 = Low Confidence
With codes 3 and 4 we do not recommend using the COA
address, unless you have experienced return mail for
the input address in the past and would like the
opportunity to re-establish contact.

 

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.