Individual Golden Record summary PII

individual_business_unit_id

No

Identifies unique association between an individual and business unit.

Distinct individual_business_unit_id from ir.MATCH_INSTANCE.

full_name

Yes

Full name.

Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.

prefix_name

Yes

A prefix portion of the name (Mr.).

Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.

title

Yes

Individual's title (Dr.).

Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.

first_name

Yes

Individual's first name.

Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.

middle_name

Yes

Individual's middle name.

Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.

last_name

Yes

Individual's last name.

Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.

suffix_name

Yes

Individual's suffix name (Jr.).

Source from ir.MATCH_INSTANCE by individual_business_unit_id. Then join to dbo.PARTY_PROFILE on match_instance_id and group by party_profile_type_code ASC and update_datetime DSC to retrieve the latest record with populated first and last name for customer then prospect. Take first valid for each name component.

email_address

Yes

Email address that has been determined as the best email for this individual/business unit.

Select email_id(s) and email_address(s) from INDIVIDUAL_EMAIL_XREF that is in the same record as individual_business_unit_id:

• IF one record returned, assign this email.

• IF more than one email has been found, then LEFT join to CONTACT_AUTH_EMAIL_SUMMARY on email_id:

  • IF only one email found that has OPT_IN value in auth_code, select that email.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL (no contact auth rec) join to EMAIL_SUMMARY on email_id  with (max(last_open_datetime);
    or max(last_click_thru_datetime) - email that was most recently opened/clicked else IF email_id is not found in EMAIL_SUMMARY pick the one from INDIVIDUAL_EMAIL_XREF with max(original_create_datetime).

home_phone_num

Yes

Home phone that has been determined as the best home phone for this individual/business unit.

Select phone_num from INDIVIDUAL_PHONE_XREF that is in the same record as individual_business_unit_id and phone_type_code = home and deactivated_ind <> y:

• IF one record returned, assign this phone.

• IF more than one phone has been found, then LEFT join to CONTACT_AUTH_PHONE_SUMMARY on phone_id:

  • IF only one phone found that has OPT_IN value in auth_code, select that phone.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_PHONE_XREF with max(create_datetime).

mobile_phone_num

Yes

Mobile phone that has been determined as the best home phone for this individual/business unit.

Select phone_num from INDIVIDUAL_PHONE_XREF that is in the same record as individual_business_unit_id and phone_type_code = mobile and deactivated_ind <> y:

• IF one record returned, assign this phone.

• IF more than one phone has been found, then LEFT join to CONTACT_AUTH_SMS_SUMMARY on phone_id:

  • IF only one phone found that has OPT_IN value in auth_code, select that phone.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_PHONE_XREF with max(create_datetime).

work_phone_num

Yes

Work phone that has been determined as the best home phone for this individual/business unit.

Select phone_num from INDIVIDUAL_PHONE_XREF that is in the same record as individual_business_unit_id and phone_type_code = business and deactivated_ind <> y:

• IF one record returned, assign this phone.

• IF more than one phone has been found, then LEFT join to CONTACT_AUTH_PHONE_SUMMARY on phone_id:

  • IF only one phone found that has OPT_IN value in auth_code, select that phone.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_PHONE_XREF with max(create_datetime).

other_phone_num

Yes

Other phone number that has been determined as the best phone to contact and is not home/mobile/work for this individual/business unit.

Select phone_num, phone_type_code from INDIVIDUAL_PHONE_XREF that is in the same record as individual_business_unit_id and phone_type_code not in (home, mobile, business) and deactivated_ind <> y:

• IF one record returned, assign this phone.

• IF more than one phone has been found, then LEFT join to CONTACT_AUTH_PHONE_SUMMARY on phone_id:

  • IF only one phone found that has OPT_IN value in auth_code, select that phone.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_PHONE_XREF with max(create_datetime).

other_phone_num_type_code

No

Identifies type of the phone number associated with the individual_business_unit that is other than home/mobile/work that has been determined as the best phone to contact.

Select phone_num, phone_type_code from INDIVIDUAL_PHONE_XREF that is in the same record as individual_business_unit_id and phone_type_code not in (home, mobile, business) and deactivated_ind <> y:

• IF one record returned, assign this phone.

• IF more than one phone has been found, then LEFT join to CONTACT_AUTH_PHONE_SUMMARY on phone_id:

  • IF only one phone found that has OPT_IN value in auth_code, select that phone.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_PHONE_XREF with max(create_datetime).

address_1

Yes

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

address_2

Yes

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

address_3

Yes

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

address_4

Yes

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

address_5

Yes

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

address_6

Yes

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

address_7

Yes

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

suite

Yes

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

city

No

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

state_province

No

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

postal_code_1

No

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

postal_code_2

No

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

county_name

No

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

iso_2_country_code

No

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

country_name

No

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

latitude

No

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).

longitude

No

Address information that has been determined as the best mailing address for this individual/business unit.

Select address_id from INDIVIDUAL_ADDRESS_XREF that is in the same record as individual_business_unit_id and address details from ADDRESS with the same address_id:

• IF one record returned, assign this address.

• IF more than one address has been found, then LEFT join to CONTACT_AUTH_ADDRESS_SUMMARY on address_id:

  • IF only one address found that has OPT_IN value in auth_code, select that address.

  • IF more than one found with auth_code = OPT_IN or auth_code = NULL pick the one from INDIVIDUAL_ADDRESS_XREF with max(create_datetime).