Table CUSTOMER_CAMPAIGN_RESPONSE_FACT

DescriptionFact entity that records the measures related to responses generated by a Campaign for each customer
The measures are fully identified by the dimensions, which are:
- Time (grain: day)
- Campaign (grain: campaign)
- Market segment
- Financial services product (grain: product)
- Channel role
- Communication medium

The measure sum insured tracks if the campaign generated new revenue and how much

Columns
COMMISSION_PAID_INCOMMISSION_PAID_IN
COMMISSION_PAID_OUTCOMMISSION_PAID_OUT
SUM_INSUREDSUM_INSURED
CALENDAR_DATE_IDCALENDAR_DATE_ID
COMMUNICATION_PROFILE_IDCOMMUNICATION_PROFILE_ID
CUSTOMER_IDCUSTOMER_ID
FINANCIAL_SERVICES_PRODUCT_IDFINANCIAL_SERVICES_PRODUCT_ID
GEOGRAPHIC_AREA_IDGEOGRAPHIC_AREA_ID
MARKET_SEGMENT_IDMARKET_SEGMENT_ID
FINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_IDFINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_ID
GEOGRAPHIC_AREA_MINI_DIMENSION_IDGEOGRAPHIC_AREA_MINI_DIMENSION_ID
POPULATION_TIMESTAMPPOPULATION_TIMESTAMP
CAMPAIGN_IDCAMPAIGN_ID
CAMPAIGN_MINI_DIMENSION_IDCAMPAIGN_MINI_DIMENSION_ID
RESPONSE_CODE_IDRESPONSE_CODE_ID
PERSON_IDPERSON_ID
PERSON_MINI_DIMENSION_IDPERSON_MINI_DIMENSION_ID

Foreign Key
CUSTOMER_C_CALE_FKCUSTOMER_C_CALE_FK
CUSTOMER_C_COMM_FKCUSTOMER_C_COMM_FK
CUSTOMER_C_CUST_FKCUSTOMER_C_CUST_FK
CUSTOMER__FINA_FKCUSTOMER__FINA_FK
CUSTOMER_C_GEOG_FKCUSTOMER_C_GEOG_FK
CUSTOMER_C_MARK_FKCUSTOMER_C_MARK_FK
CUSTOMER_C_FS_P_FKCUSTOMER_C_FS_P_FK
CUSTOME_GEOGRA_FKCUSTOME_GEOGRA_FK
CUSTOMER_C_CAMP_FKCUSTOMER_C_CAMP_FK
CUSTOMER_C_CAMP_F1CUSTOMER_C_CAMP_F1
CUSTOMER_C_CODE_FKCUSTOMER_C_CODE_FK
CUSTOMER_C_PERS_FKCUSTOMER_C_PERS_FK
CUSTOMER_C_PERS_F1CUSTOMER_C_PERS_F1

Primary Key
CUSTOMER_CAMPAIGN_RESPONSE_FACT_PKCUSTOMER_CAMPAIGN_RESPONSE_FACT_PK

Column Details

 COMMISSION_PAID_IN
DescriptionAmount received by the Insurance Company when selling third-party products.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 COMMISSION_PAID_OUT
DescriptionAmount paid out by the Insurance company when selling their products via Channels.

e.g. 2500 USD
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 SUM_INSURED
DescriptionThe total sum insured (policy face amount) of new policies that the company wrote as a result of a Campaign.
Data TypeDECIMAL(14 , 2)
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



 COMMUNICATION_PROFILE_ID
DescriptionIdentifies the Communication profile 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



 FINANCIAL_SERVICES_PRODUCT_ID
DescriptionIdentifies the Financial services product 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



 MARKET_SEGMENT_ID
DescriptionIdentifies the Role player 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



 FINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_ID
DescriptionIdentifies the Financial services product 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



 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



 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



 CAMPAIGN_ID
DescriptionThe unique identifier of the Campaign
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 CAMPAIGN_MINI_DIMENSION_ID
DescriptionThe unique identifier of the Campaign mini dimension
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 RESPONSE_CODE_ID
DescriptionThe unique identifier of the Code
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 PERSON_ID
DescriptionThe unique identifier of the Person
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 PERSON_MINI_DIMENSION_ID
DescriptionThe unique identifier of the Person mini dimension
Data TypeBIGINT
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse

Foreign Key Details

 CUSTOMER_C_CALE_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_CAMPAIGN_RESPONSE_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_C_COMM_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_CAMPAIGN_RESPONSE_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnCOMMUNICATION_PROFILE_ID [BIGINT]
Parent TableCOMMUNICATION_PROFILE
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintCOMMUNICATION_PROFILE_PK



 CUSTOMER_C_CUST_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_CAMPAIGN_RESPONSE_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__FINA_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_CAMPAIGN_RESPONSE_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnFINANCIAL_SERVICES_PRODUCT_ID [BIGINT]
Parent TableFINANCIAL_SERVICES_PRODUCT
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintFINANCIAL_SERVICES_PRODUCT_PK



 CUSTOMER_C_GEOG_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_CAMPAIGN_RESPONSE_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_C_MARK_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_CAMPAIGN_RESPONSE_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnMARKET_SEGMENT_ID [BIGINT]
Parent TableMARKET_SEGMENT
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintMARKET_SEGMENT_PK



 CUSTOMER_C_FS_P_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCUSTOMER_CAMPAIGN_RESPONSE_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnFINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_ID [BIGINT]
Parent TableFS_PRODUCT_MINI_DIMENSION
Parent Role Nameis dimension of
Parent Multiplicity0..1
Unique ConstraintFS_PRODUCT_MINI_DIMENSION_PK



 CUSTOME_GEOGRA_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCUSTOMER_CAMPAIGN_RESPONSE_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_C_CAMP_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_CAMPAIGN_RESPONSE_FACT
Child Role NameVerb1
Child Multiplicity*
Key ColumnCAMPAIGN_ID [BIGINT]
Parent TableCAMPAIGN
Parent Multiplicity1
Unique ConstraintCAMPAIGN_PK



 CUSTOMER_C_CAMP_F1
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCUSTOMER_CAMPAIGN_RESPONSE_FACT
Child Role NameVerb2
Child Multiplicity*
Key ColumnCAMPAIGN_MINI_DIMENSION_ID [BIGINT]
Parent TableCAMPAIGN_MINI_DIMENSION
Parent Multiplicity0..1
Unique ConstraintCAMPAIGN_MINI_DIMENSION_PK



 CUSTOMER_C_CODE_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_CAMPAIGN_RESPONSE_FACT
Child Role NameVerb3
Child Multiplicity*
Key ColumnRESPONSE_CODE_ID [BIGINT]
Parent TableCODE
Parent Multiplicity1
Unique ConstraintCODE_PK



 CUSTOMER_C_PERS_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCUSTOMER_CAMPAIGN_RESPONSE_FACT
Child Role NameVerb4
Child Multiplicity*
Key ColumnPERSON_ID [BIGINT]
Parent TablePERSON
Parent Multiplicity1
Unique ConstraintPERSON_PK



 CUSTOMER_C_PERS_F1
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCUSTOMER_CAMPAIGN_RESPONSE_FACT
Child Role NameVerb5
Child Multiplicity*
Key ColumnPERSON_MINI_DIMENSION_ID [BIGINT]
Parent TablePERSON_MINI_DIMENSION
Parent Multiplicity0..1
Unique ConstraintPERSON_MINI_DIMENSION_PK

Primary Key Details

 CUSTOMER_CAMPAIGN_RESPONSE_FACT_PK
DescriptionThe unique identifier of the Campaign sales Fact
MemberCALENDAR_DATE_ID [BIGINT]
MemberCOMMUNICATION_PROFILE_ID [BIGINT]
MemberCUSTOMER_ID [BIGINT]
MemberFINANCIAL_SERVICES_PRODUCT_ID [BIGINT]
MemberGEOGRAPHIC_AREA_ID [BIGINT]
MemberMARKET_SEGMENT_ID [BIGINT]
MemberCAMPAIGN_ID [BIGINT]
MemberRESPONSE_CODE_ID [BIGINT]
MemberPERSON_ID [BIGINT]