Individual Golden Record summary

individual_business_unit_id

Identifies unique association between an individual and business unit.  

An aggregation record is created in the system for each unique individual_business_unit_id in ir.MATCH_INSTANCE.

individual_id

Uniquely identifies the individual record that can be an individual, a prospect, or a lead and represents an integrated view of the individual.   

This is the individual_id portion of the individual_business_unit_id from ir.MATCH_INSTANCE.

business_unit_code

A code that uniquely identifies the brand and region within the organization. Default value is enterprise. 

This is the business_unit_id portion of the individual_business_unit_id from ir.MATCH_INSTANCE.

individual_profile_type_code

The type of customer profile: customer, prospect, etc.

Set to customer if any of the records with this individual_business_unit_id in dbo.PARTY_PROFILE contain customer in dbo.PARTY_PROFILE.PARTY_PROFILE_type_code; else, set value to dbo.PARTY_PROFILE.PARTY_PROFILE_type_code that is not NULL of the record with the max dbo.PARTY_PROFILE.source_rec_create_datetime from the group of records with the same individual_business_unit_id.   

household_id

A household is one or more individuals that share the same address ID and last name.

This is the household_id associated with the individual_business_unit_id for this record.

head_of_household_ind

When Y, indicates that this individual is a head of the household. Households with only one member will be assigned Primary. If more than one individual is associated with the household, the individual with the highest sum of purchases is assigned as the head of the household.

Set to Y when for the only individual_id per household_id in ir.MATCH_INSTANCE; else, IF multiple individual_ids, set to Y for individual_business_unit_id of the record of female gender code. If no gender codes exist, use the most recent record.

age

Individual's age, in years, calculated at the time the summary record is created.

Calculate based upon birthdate AS (datediff(year,[birthdate],run_date)), where run_date is the run_date as determined by the API; defaults to the current date and time if null.

gender_code

A code that identifies individual's gender (F, M, or U).  

Join to dbo.PARTY_PROFILE and MATCH_INSTANCE on individual_business_unit_id and select MATCH_INSTANCE.gender_code from the record with PARTY_PROFILE.max(source_rec_create_datetime).  

birth_date

Individual's date of birth. 

Join to dbo.PARTY_PROFILE and ir.MATCH_INSTANCE on  individual_business_unit_id and select MATCH_INSTANCE.birth_date from the record with PARTY_PROFILE.max(source_rec_create_datetime).  

profile_origin_name

Name of the system that originally captured customer profile. For example, the system where the customer signed up through the email signup page, or the eComm system where customer signed up for profile, etc. creation.  

Select profile_origin_name from PARTY_PROFILE joined on individual_business_unit_id with min(source_rec_create_datetime).

pref_language_code

A standard language ISO 639-3 code that customer indicated as a preferred communication language (eng).   

Select language_code from PARTY_PROFILE joined on individual_business_unit_id with min(source_rec_create_datetime).

email_id

Uniquely identifies the email address record 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 and bu_code. 

  • 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.

• IF email_id is not found in EMAIL_SUMMARY, pick the one from INDIVIDUAL_EMAIL_XREF with max(original_create_datetime).    

email_domain

An email domain is the web address that comes after the @ symbol in an email address (e.g., gmail.com).

Select email_domain from EMAIL, joined on email_id. 

home_phone_country_code

Country dial-in code of the customer's home telephone. Telephone dialing prefix for the member countries or regions of the International Telecommunication Union (ITU).

Select phone_id 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 and bu_code: 

  • 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).                                

home_phone_id

Uniquely identifies the home phone record that has been determined as the best home phone to contact for this individual/business unit.

mobile_phone_country_code

Country dial-in code of the customer's home telephone. Telephone dialing prefix for the member countries or regions of the International Telecommunication Union (ITU).

Select phone_id 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 and bu_code:

  • 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_id

Uniquely identifies the mobile phone record that has been determined as the best mobile phone to contact for this individual/business unit. 

work_phone_country_code

Country dial-in code of the customer's home telephone. Telephone dialing prefix for the member countries or regions of the International Telecommunication Union (ITU).

Select phone_id 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 and bu_code:

  • 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_id

Uniquely identifies the work phone record that has been determined as the best work phone to contact for this individual/business unit. 

other_phone_country_code

Country dial-in code of the customer's home telephone. Telephone dialing prefix for the member countries or regions of the International Telecommunication Union (ITU).

Select phone_id, 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 and bu_code:

  • 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_id

Uniquely identifies the other phone record that has been determined as the best phone to contact and is not home/mobile/work for this individual/business unit. 

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_id

Uniquely identifies the address record 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 and bu_code.

  • 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_hash_key

This field will be an MD5 hash of all the address fields in this table.

city

Non PII portion of address record that has been determined as the best mailing address for this individual/business unit. 

state_province

postal_code_1

postal_code_2

county_name

iso_2_country_code

country_name

latitude

GPS coordinates of an address or a place (latitude and longitude).

longitude

employee_ind

When value is Y, indicates that the individual is also an employee.

Set to Y if any records in PARTY_PROFILE for this individual_id have employee_id populated or PARTY_PROFILE.employee_ind = Y. 

employee_id

Employee number.

Join to PARTY_PROFILE on individual_id and select employee_id from the record with max(source_rec_create_datetime).  

prior_employee_ind

Indicates that the individual was an employee in the past when the value is set to Y.

Join to PARTY_PROFILE on individual_id and select prior_employee_ind from the record with max(source_rec_create_datetime).