Table CUSTOMER_LIFE_EVENTS_FACT

DescriptionFact entity that records measures related to CRM events in the life-cycle of customers or prospects.

The measures are fully identified by the dimensions, which are:
- Time (grain: calendar day)
- Type (grain: life event type)
- Customer (grain: Customer)
- Party (grain: Party)
- Party name (grain: Unstructured name]
- External organisation [grain: social media network]
- Communication content
- Other category = event category

The measures are additive on the time dimension.

Columns
NUMBER_OF_EVENTSNUMBER_OF_EVENTS
CALENDAR_DATE_IDCALENDAR_DATE_ID
CRM_EVENT_IDCRM_EVENT_ID
CUSTOMER_IDCUSTOMER_ID
EVENT_CATEGORY_IDEVENT_CATEGORY_ID
GEOGRAPHIC_AREA_IDGEOGRAPHIC_AREA_ID
PERSON_IDPERSON_ID
GEOGRAPHIC_AREA_MINI_DIMENSION_IDGEOGRAPHIC_AREA_MINI_DIMENSION_ID
PERSON_MINI_DIMENSION_IDPERSON_MINI_DIMENSION_ID
POPULATION_TIMESTAMPPOPULATION_TIMESTAMP
CUSTOMER_AGECUSTOMER_AGE

Foreign Key
CUSTOMER_L_CALE_FKCUSTOMER_L_CALE_FK
CUSTOMER_L_CUST_FKCUSTOMER_L_CUST_FK
CUSTOMER_L_GEOG_FKCUSTOMER_L_GEOG_FK
CUSTOMER_L_EVEN_FKCUSTOMER_L_EVEN_FK
CUST_GEOGRAPHI_FKCUST_GEOGRAPHI_FK
CUSTOMER_L_EVEN_F1CUSTOMER_L_EVEN_F1
CUSTOMER_L_PERS_FKCUSTOMER_L_PERS_FK
CUSTOMER__PERSO_FKCUSTOMER__PERSO_FK

Primary Key
CUSTOMER_LIFE_EVENTS_FACT_PKCUSTOMER_LIFE_EVENTS_FACT_PK

Column Details

 NUMBER_OF_EVENTS
DescriptionNumber of Customer events of the same type that happened on the Calendar day for a Party and for a Customer profile.

e.g. New customers
e.g. New marriages
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CALENDAR_DATE_ID
DescriptionIdentifies the Calendar date dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CRM_EVENT_ID
DescriptionIdentifies the Event dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CUSTOMER_ID
DescriptionIdentifies the Customer dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 EVENT_CATEGORY_ID
DescriptionIdentifies the Event category dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 GEOGRAPHIC_AREA_ID
DescriptionIdentifies the Geographic Area dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 PERSON_ID
DescriptionIdentifies the Person dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 GEOGRAPHIC_AREA_MINI_DIMENSION_ID
DescriptionIdentifies the Geographic Area Mini Dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 PERSON_MINI_DIMENSION_ID
DescriptionIdentifies the Person mini dimension of this Fact Entity.
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 POPULATION_TIMESTAMP
DescriptionA timestamp that identifies when this Fact Entity's instance was populated
Data TypeTIMESTAMP
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CUSTOMER_AGE
DescriptionAge of the customer at the time the event happened.
Note: This is a degenerate dimension and not a foreign key
Data TypeINTEGER
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse

Foreign Key Details

 CUSTOMER_L_CALE_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_LIFE_EVENTS_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnCALENDAR_DATE_ID [BIGINT]
Parent TableCALENDAR_DATE
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintCALENDAR_DATE_PK



 CUSTOMER_L_CUST_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_LIFE_EVENTS_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnCUSTOMER_ID [BIGINT]
Parent TableCUSTOMER
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintCUSTOMER_PK



 CUSTOMER_L_GEOG_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_LIFE_EVENTS_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnGEOGRAPHIC_AREA_ID [BIGINT]
Parent TableGEOGRAPHIC_AREA
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintGEOGRAPHIC_AREA_PK



 CUSTOMER_L_EVEN_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_LIFE_EVENTS_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnCRM_EVENT_ID [BIGINT]
Parent TableEVENT
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintEVENT_PK



 CUST_GEOGRAPHI_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCUSTOMER_LIFE_EVENTS_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnGEOGRAPHIC_AREA_MINI_DIMENSION_ID [BIGINT]
Parent TableGEOGRAPHIC_AREA_MINI_DIMENSION
Parent Role Nameis dimension of
Parent Multiplicity0..1
Unique ConstraintGEOGRAPHIC_AREA_MINI_DIMENSION_PK



 CUSTOMER_L_EVEN_F1
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_LIFE_EVENTS_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnEVENT_CATEGORY_ID [BIGINT]
Parent TableEVENT_CATEGORY
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintEVENT_CATEGORY_PK



 CUSTOMER_L_PERS_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_LIFE_EVENTS_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnPERSON_ID [BIGINT]
Parent TablePERSON
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintPERSON_PK



 CUSTOMER__PERSO_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCUSTOMER_LIFE_EVENTS_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnPERSON_MINI_DIMENSION_ID [BIGINT]
Parent TablePERSON_MINI_DIMENSION
Parent Role Nameis dimension of
Parent Multiplicity0..1
Unique ConstraintPERSON_MINI_DIMENSION_PK

Primary Key Details

 CUSTOMER_LIFE_EVENTS_FACT_PK
DescriptionThe unique identifier of the Customer life events Fact
MemberCALENDAR_DATE_ID [BIGINT]
MemberCUSTOMER_ID [BIGINT]
MemberGEOGRAPHIC_AREA_ID [BIGINT]
MemberCRM_EVENT_ID [BIGINT]
MemberEVENT_CATEGORY_ID [BIGINT]
MemberPERSON_ID [BIGINT]
MemberCUSTOMER_AGE [INTEGER]