Loyalty Account BU summary

loyalty_account_id

Uniquely identifies customer account generated by RPG systems.

Select customer_account_id from dbo.CUSTOMER_ACCOUNT where account_type_code = loyalty.

source_loyalty_account_id

Uniquely identifies customer account in the legacy system.

Select source_customer_account_id from dbo.CUSTOMER_ACCOUNT join on customer_account_id where account_type_code = loyalty.

business_unit_code

A code that uniquely identifies the brand and region within the organization (e.g., ABC_NA, XYZ-NA, ABC-US, etc.). 

Select business_unit_code from dbo.CUSTOMER_ACCOUNT join on loyalty_account_id = customer_account_id.

main_individual_business_unit_id

Identifies unique association between an individual and business unit that is defined as main for the account.  

Select individual_business_unit_id from dbo.CUSTOMER_MEMBERSHIP where main_profile_ind = Y join on loyalty_account_id = customer_account_id.

main_individual_id

Unique ID generated by matching process of the individual that is defined as main for the account.

Select individual_id from dbo.CUSTOMER_MEMBERSHIP where main_profile_ind = Y join on loyalty_account_id = customer_account_id.

main_party_profile_id

Unique identifier of the Party Profile that is defined as main for the account.

Select party_profile_id from dbo.CUSTOMER_MEMBERSHIP where main_profile_ind = Y join on loyalty_account_id = customer_account_id.

email_id

Uniquely identifies best email of the main member from member's IGR.

Select email_id from dbo.INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY where  INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY.individual_business_unit_id =  main_individual_business_unit_id. 

email_domain

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

Select email_domain from dbo.INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY where  INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY.individual_business_unit_id =  main_individual_business_unit_id.

mobile_phone_country_code

Standard 2-character country code for the phone.

Select mobile_phone_country_code from dbo.INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY where  INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY.individual_business_unit_id =  main_individual_business_unit_id.

mobile_phone_id

Uniquely identifies mobile phone of the main member from member's IGR.

Select mobile_phone_id from dbo.INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY where  INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY.individual_business_unit_id =  main_individual_business_unit_id.

address_id

Uniquely identifies mailing address of the main member from member's IGR.

Select address_id from dbo.INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY where  INDIVIDUAL_BU_GOLDEN_RECORD_SUMMARY.individual_business_unit_id =  main_individual_business_unit_id.

account_open_date

Membership enrollment date received from loyalty system.

Select account_open_date from dbo.CUSTOMER_ACCOUNT join on loyalty_account_id = customer_account_id.

account_close_date

Account close date.

Select account_close_date from dbo.CUSTOMER_ACCOUNT join on loyalty_account_id = customer_account_id.

account_close_reason

Text value indicating the reason for account closure (duplicate, expired, etc.).

Select account_close_reason from dbo.CUSTOMER_ACCOUNT join on loyalty_account_id = customer_account_id.

account_status_code

Current status of loyalty account (active, merged, inactive, etc.).

Select account_status_code from dbo.CUSTOMER_ACCOUNT join on loyalty_account_id = customer_account_id.

last_account_campaign_date

The date of the most recent campaign by any channel to any member associated with the account.

max(campaign_event_datetime) from dbo.CAMPAIGN_EVENT_CUSTOMER_MAP where CAMPAIGN_EVENT_CUSTOMER_MAP.individual_business_unit_id = main_individual_business_unit_id.

last_account_response_date

The date of the most recent response event by any channel from any member associated with the account.

max(response_event_datetime) from dbo.CAMPAIGN_EVENT_CUSTOMER_MAP where CAMPAIGN_EVENT_CUSTOMER_MAP.individual_business_unit_id = main_individual_business_unit_id.

balance_points_qty

Account's current points balance (the total rewards earned minus total rewards redeemed).

Select balance_points_qty from dbo.LOYALTY_ACCOUNT_DETAIL join on loyalty_account_id = customer_account_id.

enrollment_location_id

Identifies the location (store or website) where enrollment took place.

Select enrollment_location_id from dbo.CUSTOMER_ACCOUNT join on loyalty_account_id = customer_account_id.

current_loyalty_tier_code

The tier that loyalty account had attained.

For example:

• Base=$0-$399.99

• Plus=$400-$1199.99 and 2 visits

• VIP=$1200+ and 2 visits

Select loyalty_account_tier_code from dbo.LOYALTY_ACCOUNT_DETAIL join on loyalty_account_id = customer_account_id.

next_loyalty_account_tier_code

Next tier that loyalty account would attain.

Select next_loyalty_account_tier_code from dbo.LOYALTY_ACCOUNT_DETAIL join on loyalty_account_id = customer_account_id.

previous_loyalty_account_tier_code

Previous tier that loyalty account had attained.

Select previous_loyalty_account_tier_code from dbo.LOYALTY_ACCOUNT_DETAIL join on loyalty_account_id = customer_account_id.

loyalty_program_id

Uniquely identifies a customer loyalty program in the legacy system. A structured and long-term marketing effort which provides incentives to repeat customers who demonstrate loyal buying behavior.

Select loyalty_program_id from dbo.LOYALTY_ACCOUNT_DETAIL join on loyalty_account_id = customer_account_id.