Entity Definition

Logical Name : CustomerMembershipEarnedRewardTier

A reference entity that identifies and names a customer membership earned reward tier or level used to stratify customers based on their level of net purchases, visits, RFM activity and other activities. This entity establishes ranges of values used to bin customers based on activity metrics captured and evaluated on a periodic basis. Activity information is aggregated for the period, compared to the range of values associated with a tier and the customer member account is assigned to the tier where its value falls into the tier high-low range. The term "earned" is used here because customers actions cause them to move between tiers. They may move up to higher-level, higher value tiers or move down to lower level lower value tiers. The CustomerMembershipEarnedRewardTier is different from the CustomerMembershipProgramLevel in that customer membership account level is a function of its activity not a choice made to join at a given level. The CustomerMembershipProgramLevel supports WarehouseClub and Coop arrangements that feature different levels of membership and are established indpendently of customer activities.

Data Definition

Attribute Description Physical Name Domain Data Type Foreign Key Table
CustomerRewardProgramTierCode (PK) A code that represents the relative value of a given CustomerRewardProgramTier CD_CT_RWD_PRGM_TR Code varchar(20)
MembershipProgramID (FK)(PK) Token ID for a CustomerRewardProgram instance. ID_CT_MBRSHP_PRGM Identity integer CustomerMembershipProgramLevel(CO_CT_MBRSHP_PRGM_LV)
LevelNumber (FK)(PK) A whole number that establishes the relative value of a membership program level. The higher the value, the greater the level and its associated rewards and, where applicable, fees. IC_LV_NMB Number integer CustomerMembershipProgramLevel(CO_CT_MBRSHP_PRGM_LV)
TierDescription Short business description this membership activity-based customer membership tier. DE_TR Name varchar(40)
TierAssignmentBasisCode A code that designates the type of measurement used to assign a customer to this CustomerRewardProgramTier. Typically tiers are based on the cumulative reward value earned by a customer. They may be in points, monetary value, miles or any other customer reward currency. CD_TR_ASGMT_BSS Code varchar(20)
TierMinimumAmount The threshold vallue which when reached results in a customer being assigned to this CustomerRewardProgramTier. MO_TR_MNM_AMT Money decimal(16,5)
TierMaximumAmount The threshold vallue which when exceeded results in a customer being outside of this CustomerRewardTier (and typically means they are assigned to a higher leve tier). MO_TR_MXM_AMT Money decimal(16,5)
TierValueUnitCode Indicates the units that apply to the TierMinimumAmount and TierMaximumAmount. Sample values include: MONETARY POINTS MILES GALLONS VISITS RFM_SCORES CD_TR_VL_UN Code varchar(20)
PointsAccrualTypeCode Designates whether point earned may be accrued for tier assignment of a customer or accrued only for subsequent redemption. Values: QUALIFYING - point earned are accrued and count towards meeting tier thresholds. For example buy X and get 50 pts towards becoming a GOLD customer. This is similar to what airlines do. REDEEMABLE - points earned are accrued for redemption. These are points that may be exchanged for discounts or free items. CD_PNT_ACRL_TYP Code varchar(20)
PointsExpirationPeriodDayCount The number of days after points are earned until they expire. 0 means the points never expire. EP_PNT_PRD_DY_CNT QuantityInteger integer
StatusCode Indicates the status of this CustomerLoyaltyAccountTierStatus. Valid values are: A = Active I = Inactive Only one instance of a CustomerLoyaltyAccountTierStatus may be active at at ime. CD_STS Code2Status char(2)
EffectiveDate Date customer account joins this tier status. DC_EF EffectiveDate date
ExpirationDate Date the customer account exits this CustomerLoyaltyAccountTierStatus DC_EP ExpirationDate date


Parent Entity Verb Phrase Child Entity
CustomerMembershipProgramLevel is further stratified into CustomerMembershipEarnedRewardTier
CustomerMembershipEarnedRewardTier Z_CustomerMembershipProgramPointsEarnedRule
CustomerMembershipEarnedRewardTier defines criteria for membership in CustomerAccountRewardTierStatus
CustomerMembershipEarnedRewardTier may depend on CustomerMembershipProgramAccountRewardDerivationRuleEligibiliy
CustomerMembershipEarnedRewardTier is a basis for PromotionalOffer
CustomerMembershipEarnedRewardTier ZPromotionalOfferRewardDiscount

Logical Views containing CustomerMembershipEarnedRewardTier

Logical View
Logical 01400 - Item Rewards Derivation - Transaction Level
Logical 07010 - Customer Account Information Extended