| Insurance Data Model |
Description | Fact 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 strategyFor 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. |
Primary Key | |
![]() |
Column Details |
Description | That 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 Type | DECIMAL(14 , 2) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Amount received by the Insurance Company when selling third-party products. |
Data Type | DECIMAL(14 , 2) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Amount paid out by the Insurance company when selling their products via Channels. e.g. 2500 USD |
Data Type | DECIMAL(14 , 2) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The 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 Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The 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 Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Number 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 Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The total new premium (annual and single) sold to customers who bought through the campaign and who did not own active products beforehand. |
Data Type | DECIMAL(14 , 2) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Total amount of actual income produced by new business that is concluded with new customers. |
Data Type | DECIMAL(14 , 2) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Total amount of actual income produced by new business from new products. |
Data Type | DECIMAL(14 , 2) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The total sum insured (policy face amount) of new policies that the company wrote as a result of a Campaign. |
Data Type | DECIMAL(14 , 2) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The 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 Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Of 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 Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The number of new customers in the campaign target list. |
Data Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The number of new products sold to customers who bought through the campaign and who did not own active products beforehand. |
Data Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The 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 Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The number of customers who bought through the campaign and who did not own active products beforehand. |
Data Type | INTEGER |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | That 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 Type | DECIMAL(14 , 2) |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Calendar date dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Channel role dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Communication profile dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Financial services product dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Geographic Area dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Role player category dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Channel role mini dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Financial services product mini dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | Identifies the Geographic Area Mini Dimension of this Fact Entity. |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | A timestamp that identifies when this Fact Entity's instance was populated |
Data Type | TIMESTAMP |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The unique identifier of the Campaign |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | true |
Is Identity | false |
Is Nullable | false |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Description | The unique identifier of the Campaign mini dimension |
Data Type | BIGINT |
Default Value | None |
Is Primary Key | false |
Is Identity | false |
Is Nullable | true |
Is Generated | false |
Is Row Change Timestamp | false |
Is hidden | false |
Foreign Key Details |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | CAMPAIGN_SALES_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | CALENDAR_DATE_ID [BIGINT] |
Parent Table | CALENDAR_DATE |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | CALENDAR_DATE_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | CAMPAIGN_SALES_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | CHANNEL_ROLE_ID [BIGINT] |
Parent Table | CHANNEL_ROLE |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | CHANNEL_ROLE_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | CAMPAIGN_SALES_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | COMMUNICATION_PROFILE_ID [BIGINT] |
Parent Table | COMMUNICATION_PROFILE |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | COMMUNICATION_PROFILE_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | CAMPAIGN_SALES_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | FINANCIAL_SERVICES_PRODUCT_ID [BIGINT] |
Parent Table | FINANCIAL_SERVICES_PRODUCT |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | FINANCIAL_SERVICES_PRODUCT_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | CAMPAIGN_SALES_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | GEOGRAPHIC_AREA_ID [BIGINT] |
Parent Table | GEOGRAPHIC_AREA |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | GEOGRAPHIC_AREA_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | CAMPAIGN_SALES_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | MARKET_SEGMENT_ID [BIGINT] |
Parent Table | MARKET_SEGMENT |
Parent Role Name | is dimension of |
Parent Multiplicity | 1 |
Unique Constraint | MARKET_SEGMENT_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | CAMPAIGN_SALES_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | CHANNEL_ROLE_MINI_DIMENSION_ID [BIGINT] |
Parent Table | CHANNEL_ROLE_MINI_DIMENSION |
Parent Role Name | is dimension of |
Parent Multiplicity | 0..1 |
Unique Constraint | CHANNEL_ROLE_MINI_DIMENSION_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | CAMPAIGN_SALES_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | FINANCIAL_SERVICES_PRODUCT_MINI_DIMENSION_ID [BIGINT] |
Parent Table | FS_PRODUCT_MINI_DIMENSION |
Parent Role Name | is dimension of |
Parent Multiplicity | 0..1 |
Unique Constraint | FS_PRODUCT_MINI_DIMENSION_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | CAMPAIGN_SALES_FACT |
Child Role Name | has for dimension |
Child Multiplicity | * |
Key Column | GEOGRAPHIC_AREA_MINI_DIMENSION_ID [BIGINT] |
Parent Table | GEOGRAPHIC_AREA_MINI_DIMENSION |
Parent Role Name | is dimension of |
Parent Multiplicity | 0..1 |
Unique Constraint | GEOGRAPHIC_AREA_MINI_DIMENSION_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | true |
Child Table | CAMPAIGN_SALES_FACT |
Child Role Name | Verb1 |
Child Multiplicity | * |
Key Column | CAMPAIGN_ID [BIGINT] |
Parent Table | CAMPAIGN |
Parent Multiplicity | 1 |
Unique Constraint | CAMPAIGN_PK |
Referential Integrity: On Update | NO_ACTION |
Referential Integrity: On Delete | NO_ACTION |
Is Identifying Relationship | false |
Child Table | CAMPAIGN_SALES_FACT |
Child Role Name | Verb2 |
Child Multiplicity | * |
Key Column | CAMPAIGN_MINI_DIMENSION_ID [BIGINT] |
Parent Table | CAMPAIGN_MINI_DIMENSION |
Parent Multiplicity | 0..1 |
Unique Constraint | CAMPAIGN_MINI_DIMENSION_PK |
Primary Key Details |
Description | The unique identifier of the Campaign sales Fact |
Member | CALENDAR_DATE_ID [BIGINT] |
Member | CHANNEL_ROLE_ID [BIGINT] |
Member | COMMUNICATION_PROFILE_ID [BIGINT] |
Member | FINANCIAL_SERVICES_PRODUCT_ID [BIGINT] |
Member | GEOGRAPHIC_AREA_ID [BIGINT] |
Member | MARKET_SEGMENT_ID [BIGINT] |
Member | CAMPAIGN_ID [BIGINT] |
| Insurance Data Model |