Individual Golden Record summary PII

individual_business_unit_id

Identifies unique association between an individual and business unit.  

Distinct individual_business_unit_id from ir.MATCH_INSTANCE.

full_name

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

A prefix portion of the name (Mr.).

title

Individual's title (Dr.).

first_name

Individual's first name.

middle_name

Individual's middle name.

last_name

Individual's last name.

suffix_name

Individual's suffix name (Jr.).

email_address

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

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

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

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

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

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. 

address_1

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

address_3

address_4

address_5

address_6

address_7

suite

city

state_province

postal_code_1

postal_code_2

county_name

iso_2_country_code

country_name

latitude

longitude