Table CAMPAIGN_SALES_FACT

DescriptionFact entity that records the measures related to sales generated by a Campaign.
The measures are fully identified by the dimensions, which are:
- Time (grain: day)
- Campaign (grain: campaign step)
- Market segment
- Specification (grain: product)
- Customer profile
- Channel role
- Payment method
- Party
- Policy event type
- Communication medium
- Contact strategy

For example, the measure Number of policies sold corresponds to the number of policies sold per day, per campaign step, per market segment, per Customer profile, per channel role who performed the sale, per payment method.

Columns
ADDITIONAL_PREMIUMS_TO_EXISTING_POLICIESADDITIONAL_PREMIUMS_TO_EXISTING_POLICIES
COMMISSION_PAID_INCOMMISSION_PAID_IN
COMMISSION_PAID_OUTCOMMISSION_PAID_OUT
NUMBER_OF_POLICIES_SOLD_TO_TARGETED_EXISTING_CUSTOMERSNUMBER_OF_POLICIES_SOLD_TO_TARGETED_EXISTING_CUSTOMERS
NUMBER_OF_POLICIES_SOLD_TO_TARGETED_NEW_CUSTOMERSNUMBER_OF_POLICIES_SOLD_TO_TARGETED_NEW_CUSTOMERS
NUMBER_OF_TARGETED_NEW_CUSTOMERS_GAINEDNUMBER_OF_TARGETED_NEW_CUSTOMERS_GAINED
PREMIUM_INCOME_FROM_NEW_CUSTOMERSPREMIUM_INCOME_FROM_NEW_CUSTOMERS
REVENUE_FROM_NEW_CUSTOMERSREVENUE_FROM_NEW_CUSTOMERS
REVENUE_FROM_NEW_POLICIESREVENUE_FROM_NEW_POLICIES
SUM_INSUREDSUM_INSURED
NUMBER_OF_EXISTING_CUSTOMERS_CROSS-SOLD_TONUMBER_OF_EXISTING_CUSTOMERS_CROSS-SOLD_TO
NUMBER_OF_TARGETED_EXISTING_CUSTOMERSNUMBER_OF_TARGETED_EXISTING_CUSTOMERS
NUMBER_OF_TARGETED_NEW_CUSTOMERSNUMBER_OF_TARGETED_NEW_CUSTOMERS
NUMBER_OF_POLICIES_SOLD_TO_NEW_CUSTOMERSNUMBER_OF_POLICIES_SOLD_TO_NEW_CUSTOMERS
NUMBER_OF_POLICIES_SOLD_TO_EXISTING_CUSTOMERSNUMBER_OF_POLICIES_SOLD_TO_EXISTING_CUSTOMERS
NUMBER_OF_NEW_CUSTOMERS_GAINEDNUMBER_OF_NEW_CUSTOMERS_GAINED
PREMIUM_INCOME_FOR_NEW_POLICIES_FROM_EXISTING_CUSTOMERSPREMIUM_INCOME_FOR_NEW_POLICIES_FROM_EXISTING_CUSTOMERS
CALENDAR_DATE_IDCALENDAR_DATE_ID
CHANNEL_ROLE_IDCHANNEL_ROLE_ID
COMMUNICATION_PROFILE_IDCOMMUNICATION_PROFILE_ID
FINANCIAL_SERVICES_PRODUCT_IDFINANCIAL_SERVICES_PRODUCT_ID
GEOGRAPHIC_AREA_IDGEOGRAPHIC_AREA_ID
MARKET_SEGMENT_IDMARKET_SEGMENT_ID
CHANNEL_ROLE_MINI_DIMENSION_IDCHANNEL_ROLE_MINI_DIMENSION_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

Foreign Key
CAMPAIGN_S_CALE_FKCAMPAIGN_S_CALE_FK
CAMPAIGN_S_CHAN_FKCAMPAIGN_S_CHAN_FK
CAMPAI_COMMUNIC_FKCAMPAI_COMMUNIC_FK
CAMP_FINANCIAL__FKCAMP_FINANCIAL__FK
CAMPAIGN__GEOGR_FKCAMPAIGN__GEOGR_FK
CAMPAIGN__MARK_FKCAMPAIGN__MARK_FK
CAMP_CHANNEL_RO_FKCAMP_CHANNEL_RO_FK
CAMP_FS_PRODUCT_FKCAMP_FS_PRODUCT_FK
CAMP_GEOGRAPHIC_FKCAMP_GEOGRAPHIC_FK
CAMPAIGN_S_CAMP_FKCAMPAIGN_S_CAMP_FK
CAMPA_CAMPAIGN__FKCAMPA_CAMPAIGN__FK

Primary Key
CAMPAIGN_SALES_FACT_PKCAMPAIGN_SALES_FACT_PK

Column Details

 ADDITIONAL_PREMIUMS_TO_EXISTING_POLICIES
DescriptionThat part of the new premiums (annual and single), purchased during the campaign by existing customers, which relates to increases to existing policies or investment plans.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 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



 NUMBER_OF_POLICIES_SOLD_TO_TARGETED_EXISTING_CUSTOMERS
DescriptionThe number of new products sold to customers that were previously identified as campaign targets, who bought through the campaign, and, who owned one or more products prior to the campaign.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_POLICIES_SOLD_TO_TARGETED_NEW_CUSTOMERS
DescriptionThe number of new products sold to customers that were previously identified as campaign targets, who bought through the campaign, and, who did not own active products beforehand.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_TARGETED_NEW_CUSTOMERS_GAINED
DescriptionNumber of targeted new customers (sales leads) that are actually engaged in the sales execution process. Independent of whether the lead results in a sale.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 PREMIUM_INCOME_FROM_NEW_CUSTOMERS
DescriptionThe total new premium (annual and single) sold to customers who bought through the campaign and who did not own active products beforehand.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 REVENUE_FROM_NEW_CUSTOMERS
DescriptionTotal amount of actual income produced by new business that is concluded with new customers.
Data TypeDECIMAL(14 , 2)
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 REVENUE_FROM_NEW_POLICIES
DescriptionTotal amount of actual income produced by new business from new products.
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



 NUMBER_OF_EXISTING_CUSTOMERS_CROSS-SOLD_TO
DescriptionThe number of customers who owned one or more products prior to the campaign, who either increased their holdings / premiums or who purchased further products.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_TARGETED_EXISTING_CUSTOMERS
DescriptionOf the customers included in the campaign, the number who had policies (i.e. played an ownership role on one or more in-force or active policies).
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_TARGETED_NEW_CUSTOMERS
DescriptionThe number of new customers in the campaign target list.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_POLICIES_SOLD_TO_NEW_CUSTOMERS
DescriptionThe number of new products sold to customers who bought through the campaign and who did not own active products beforehand.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_POLICIES_SOLD_TO_EXISTING_CUSTOMERS
DescriptionThe number of new products sold to customers who owned one or more products prior to the campaign, who purchased further products during the campaign.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_NEW_CUSTOMERS_GAINED
DescriptionThe number of customers who bought through the campaign and who did not own active products beforehand.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 PREMIUM_INCOME_FOR_NEW_POLICIES_FROM_EXISTING_CUSTOMERS
DescriptionThat part of the new premiums (annual and single), purchased during the campaign by existing customers, which relates to the purchase of new policies or investment plans.
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



 CHANNEL_ROLE_ID
DescriptionIdentifies the Channel role 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



 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



 CHANNEL_ROLE_MINI_DIMENSION_ID
DescriptionIdentifies the Channel role 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



 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

Foreign Key Details

 CAMPAIGN_S_CALE_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCAMPAIGN_SALES_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



 CAMPAIGN_S_CHAN_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCAMPAIGN_SALES_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnCHANNEL_ROLE_ID [BIGINT]
Parent TableCHANNEL_ROLE
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintCHANNEL_ROLE_PK



 CAMPAI_COMMUNIC_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCAMPAIGN_SALES_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



 CAMP_FINANCIAL__FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCAMPAIGN_SALES_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



 CAMPAIGN__GEOGR_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCAMPAIGN_SALES_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



 CAMPAIGN__MARK_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCAMPAIGN_SALES_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



 CAMP_CHANNEL_RO_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCAMPAIGN_SALES_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnCHANNEL_ROLE_MINI_DIMENSION_ID [BIGINT]
Parent TableCHANNEL_ROLE_MINI_DIMENSION
Parent Role Nameis dimension of
Parent Multiplicity0..1
Unique ConstraintCHANNEL_ROLE_MINI_DIMENSION_PK



 CAMP_FS_PRODUCT_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCAMPAIGN_SALES_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



 CAMP_GEOGRAPHIC_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCAMPAIGN_SALES_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



 CAMPAIGN_S_CAMP_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCAMPAIGN_SALES_FACT
Child Role NameVerb1
Child Multiplicity*
Key ColumnCAMPAIGN_ID [BIGINT]
Parent TableCAMPAIGN
Parent Multiplicity1
Unique ConstraintCAMPAIGN_PK



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

Primary Key Details

 CAMPAIGN_SALES_FACT_PK
DescriptionThe unique identifier of the Campaign sales Fact
MemberCALENDAR_DATE_ID [BIGINT]
MemberCHANNEL_ROLE_ID [BIGINT]
MemberCOMMUNICATION_PROFILE_ID [BIGINT]
MemberFINANCIAL_SERVICES_PRODUCT_ID [BIGINT]
MemberGEOGRAPHIC_AREA_ID [BIGINT]
MemberMARKET_SEGMENT_ID [BIGINT]
MemberCAMPAIGN_ID [BIGINT]