| Dimensional Warehouse Model Data Model |
| Description | Measures campaign success from the perspective of the volume and values of policies and premiums sold. This includes: new members gained, existing members cross-sold to, members who purchased new products but cancelled prior policies, members contacted who did not buy new products, and the premiums earned from each category. This analysis is applicable to group or individual member types. |
| Primary Key | |
Campaign Sales Analysis PK |
|
| Dependencies | |
NONE |
|
| Reverse Dependencies | |
|
|
| Attribute Details |
Additional Premiums To Existing Policies Purchased By Existing Members
| Description | That part of the new premiums, purchased during the campaign by existing members that relates to increases to existing policies. May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Calendar Dk
| Description | A numeric surrogate key used to uniquely identify entities in the dimensional model. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG] |
| Is Part Of PrimaryKey | true |
| Is Required | true |
| Is Derived | false |
| Is Surrogate Key | false |
Campaign Dimension Dk
| Description | A numeric surrogate key used to uniquely identify entities in the dimensional model. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG] |
| Is Part Of PrimaryKey | true |
| Is Required | true |
| Is Derived | false |
| Is Surrogate Key | false |
Geographic Area Dk
| Description | A numeric surrogate key used to uniquely identify entities in the dimensional model. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG] |
| Is Part Of PrimaryKey | true |
| Is Required | true |
| Is Derived | false |
| Is Surrogate Key | false |
Group Dk
| Description | A numeric surrogate key used to uniquely identify entities in the dimensional model. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG] |
| Is Part Of PrimaryKey | true |
| Is Required | true |
| Is Derived | false |
| Is Surrogate Key | false |
Health Plan Product Dk
| Description | A numeric surrogate key used to uniquely identify entities in the dimensional model. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG] |
| Is Part Of PrimaryKey | true |
| Is Required | true |
| Is Derived | false |
| Is Surrogate Key | false |
Intermediary Dimension Dk
| Description | A numeric surrogate key used to uniquely identify entities in the dimensional model. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG] |
| Is Part Of PrimaryKey | true |
| Is Required | true |
| Is Derived | false |
| Is Surrogate Key | false |
Lead Generation To First Contact Cycle Time
| Description | The elapsed time between generation of a lead and making first contact with the prospect or member. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
New Member Acquisition Rate
| Description | Indicator of the success of the new member acquisition process whether aimed at group or individual level. Expressed as the ratio between the number of new members gained (including cross-sales) and the number of targeted new members. Formula: [A/B] (see dependencies and their labels for measures used in the formula) |
| Data Type | Standards - Data Domains.ddm/Data Domains/Decimal Float [FLOAT(15)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
New Premiums For New Policies Purchased By Existing Members
| Description | That part of the new earned premium, purchased during the campaign by existing members that relates to the purchase of new policies. May be applied to group or individual members as appropriate to the campaign.. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
New Premiums Purchased By Existing Members
| Description | The total earned premium purchased by members who owned one or more products prior to the campaign, who either increased their premiums or who purchased further products. May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
New Premiums Purchased By New Members
| Description | The total new earned premium due from members who bought through the campaign and who did not own active products beforehand. May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Campaign Complaints
| Description | The number of complaints made by members that can be directly attributable to the campaign activity. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Campaign Phone Calls
| Description | Number of telephone calls that can be directly attributable to the campaign activity. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Communications
| Description | The total number of communications issued by parties contacted in the context of marketing activities. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Existing Members Canceling Existing Policies
| Description | The number of members who owned one or more products prior to the campaign, who canceled their premium on these existing products following cross-selling. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Existing Members Cross-sold To
| Description | The number of members who owned one or more products prior to the campaign, who either increased their holdings / premiums or who purchased further products. May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Inbound Contacts
| Description | Total number of contacts made between the member or prospect and the insurance company within a campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of New Members Gained
| Description | The number of members who bought through the campaign and who did not own active products beforehand. May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Outbound Contacts
| Description | Total number of contacts made between the insurance company and the member or prospect within a campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Policies Cancelled By Existing Members
| Description | The number of products canceled by members who owned one or more products prior to the campaign, following cross-selling. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Policies Sold To Targeted Existing Members
| Description | The number of new products sold to members that were previously identified as campaign targets, who bought through the campaign, and, who owned one or more products prior to the campaign. May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Policies Sold To Targeted New Members
| Description | The number of new products sold to members that were previously identified as campaign targets, who bought through the campaign, and, who did not own active products beforehand. May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Products Sold To Existing Members
| Description | The number of new products sold to members who owned one or more products prior to the campaign, who purchased further products during the campaign. May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Products Sold To New Members
| Description | The number of new products sold to members who bought through the campaign and who did not own active products beforehand. May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Targeted Existing Members
| Description | Of the members included in the campaign, the number who had policies (i.e. played an ownership role on one or more in-force or active policies). May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Targeted New Members
| Description | The number of new members in the campaign target list (including cross-sales targets). May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Number Of Targeted New Members Engaged
| Description | Number of targeted new members (sales leads including cross-sales) that are actually engaged in the sales execution process, independent of whether the lead results in a sale. May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Organization Dk
| Description | A numeric surrogate key used to uniquely identify entities in the dimensional model. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG] |
| Is Part Of PrimaryKey | true |
| Is Required | true |
| Is Derived | false |
| Is Surrogate Key | false |
Percentage Of Existing Members In The Targeted List
| Description | Number of existing members in the targeted list as a percentage of the number of targeted members overall. May be applied to group or individual members as appropriate to the campaign. Formula: [(A/B)*100] (see dependencies and their labels for measures used in the formula) |
| Data Type | Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Percentage Of New Premiums From New Products Sold To Existing Members
| Description | Of the new earned premiums from new products sold during the campaign, the percentage sold to existing members. Gives an indication of whether existing members tend to purchase smaller premiums in follow-on purchases. May be applied to group or individual members as appropriate to the campaign. Formula: [(A/(B+C))*100] (see dependencies and their labels for measures used in the formula) |
| Data Type | Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Percentage Of New Products Sold To Existing Members
| Description | Of the new products sold in the campaign, the percentage that was sold to existing members. May be applied to group or individual members as appropriate to the campaign. Formula: [(A/(A+B))*100] (see dependencies and their labels for measures used in the formula) |
| Data Type | Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Percentage Penetration For New Premium Sales To Existing Members
| Description | Measures the percentage of total new premium sold to existing members, including increases to existing policies. May be applied to group or individual members as appropriate to the campaign. Formula: [A*100/(B+A)] (see dependencies and their labels for measures used in the formula) |
| Data Type | Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Percentage Penetration For New Product Sales To Existing Members
| Description | The number of existing members who purchased new products in the campaign expressed as a percentage of the number of existing members in the targeted list. May be applied to group or individual members as appropriate to the campaign. Formula: [(A/B) *100] (see dependencies and their labels for measures used in the formula) |
| Data Type | Standards - Data Domains.ddm/Data Domains/Percentage [FLOAT(2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Plan Member Dk
| Description | A numeric surrogate key used to uniquely identify entities in the dimensional model. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Surrogate Key Large [LONG] |
| Is Part Of PrimaryKey | true |
| Is Required | true |
| Is Derived | false |
| Is Surrogate Key | false |
Premium Income Amount
| Description | The total amount of premium income written during the period. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Premium Income From New Members Amount
| Description | Total amount of actual income produced by new business that is concluded with new members (including cross-sales). May be applied to group or individual members as appropriate to the campaign. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Premium Income From New Products Amount
| Description | Total amount of actual income produced by new business from new products. |
| Data Type | Standards - Data Domains.ddm/Data Domains/Currency Amount [DECIMAL(14,2)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Revenue Ratio For New Members
| Description | The proportion of the revenue that is produced by acquiring new members. May be applied to group or individual members as appropriate to the campaign. Formula: [A/B] (see dependencies and their labels for measures used in the formula) |
| Data Type | Standards - Data Domains.ddm/Data Domains/Number Decimal [DECIMAL(10,4)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Revenue Ratio For New Products
| Description | The proportion of the revenue that is produced by selling new products. Formula: [A/B] (see dependencies and their labels for measures used in the formula) |
| Data Type | Standards - Data Domains.ddm/Data Domains/Number Decimal [DECIMAL(10,4)] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
Total Number Of Targeted Members
| Description | Total number of existing and new members in the campaign target list. May be applied to group or individual members as appropriate to the campaign. Formula: [A+B] (see dependencies and their labels for measures used in the formula) |
| Data Type | Standards - Data Domains.ddm/Data Domains/Count [INTEGER] |
| Is Part Of PrimaryKey | false |
| Is Required | false |
| Is Derived | false |
| Is Surrogate Key | false |
| Relationship Details |
Campaign Sales Analysis_Calendar Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Campaign Sales Analysis |
| Child Multiplicity | ZERO_TO_MANY |
| Child Referential Integrity: On Delete | NONE |
| Child Referential Integrity: On Insert | NONE |
| Child Referential Integrity: On Update | NONE |
| Parent Table | Calendar Dimension |
| Parent Multiplicity | ONE |
| Parent Referential Integrity: On Delete | NONE |
| Parent Referential Integrity: On Insert | NONE |
| Parent Referential Integrity: On Update | NONE |
Campaign Sales Analysis_Geographic Area Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Campaign Sales Analysis |
| Child Multiplicity | ZERO_TO_MANY |
| Child Referential Integrity: On Delete | NONE |
| Child Referential Integrity: On Insert | NONE |
| Child Referential Integrity: On Update | NONE |
| Parent Table | Geographic Area Dimension |
| Parent Multiplicity | ONE |
| Parent Referential Integrity: On Delete | NONE |
| Parent Referential Integrity: On Insert | NONE |
| Parent Referential Integrity: On Update | NONE |
Campaign Sales Analysis_Group Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Campaign Sales Analysis |
| Child Multiplicity | ZERO_TO_MANY |
| Child Referential Integrity: On Delete | NONE |
| Child Referential Integrity: On Insert | NONE |
| Child Referential Integrity: On Update | NONE |
| Parent Table | Group Dimension |
| Parent Multiplicity | ONE |
| Parent Referential Integrity: On Delete | NONE |
| Parent Referential Integrity: On Insert | NONE |
| Parent Referential Integrity: On Update | NONE |
Campaign Sales Analysis_Plan Member Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Campaign Sales Analysis |
| Child Multiplicity | ZERO_TO_MANY |
| Child Referential Integrity: On Delete | NONE |
| Child Referential Integrity: On Insert | NONE |
| Child Referential Integrity: On Update | NONE |
| Parent Table | Plan Member Dimension |
| Parent Multiplicity | ONE |
| Parent Referential Integrity: On Delete | NONE |
| Parent Referential Integrity: On Insert | NONE |
| Parent Referential Integrity: On Update | NONE |
Campaign Sales Analysis_Campaign Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Campaign Sales Analysis |
| Child Multiplicity | ZERO_TO_MANY |
| Child Referential Integrity: On Delete | NONE |
| Child Referential Integrity: On Insert | NONE |
| Child Referential Integrity: On Update | NONE |
| Parent Table | Campaign Dimension |
| Parent Multiplicity | ONE |
| Parent Referential Integrity: On Delete | NONE |
| Parent Referential Integrity: On Insert | NONE |
| Parent Referential Integrity: On Update | NONE |
Campaign Sales Analysis_Intermediary Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Campaign Sales Analysis |
| Child Multiplicity | ZERO_TO_MANY |
| Child Referential Integrity: On Delete | NONE |
| Child Referential Integrity: On Insert | NONE |
| Child Referential Integrity: On Update | NONE |
| Parent Table | Intermediary Dimension |
| Parent Multiplicity | ONE |
| Parent Referential Integrity: On Delete | NONE |
| Parent Referential Integrity: On Insert | NONE |
| Parent Referential Integrity: On Update | NONE |
Campaign Sales Analysis_Health Plan Product Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Campaign Sales Analysis |
| Child Multiplicity | ZERO_TO_MANY |
| Child Referential Integrity: On Delete | NONE |
| Child Referential Integrity: On Insert | NONE |
| Child Referential Integrity: On Update | NONE |
| Parent Table | Health Plan Product Dimension |
| Parent Multiplicity | ONE |
| Parent Referential Integrity: On Delete | NONE |
| Parent Referential Integrity: On Insert | NONE |
| Parent Referential Integrity: On Update | NONE |
Campaign Sales Analysis_Organization Dimension_FK
| Is Identifying Relationship | true |
| Child Table | Campaign Sales Analysis |
| Child Multiplicity | ZERO_TO_MANY |
| Child Referential Integrity: On Delete | NONE |
| Child Referential Integrity: On Insert | NONE |
| Child Referential Integrity: On Update | NONE |
| Parent Table | Organization Dimension |
| Parent Multiplicity | ONE |
| Parent Referential Integrity: On Delete | NONE |
| Parent Referential Integrity: On Insert | NONE |
| Parent Referential Integrity: On Update | NONE |
| Primary Key Details |
Campaign Sales Analysis PK
| Key Attribute | Plan Member Dk |
| Key Attribute | Calendar Dk |
| Key Attribute | Group Dk |
| Key Attribute | Campaign Dimension Dk |
| Key Attribute | Geographic Area Dk |
| Key Attribute | Intermediary Dimension Dk |
| Key Attribute | Health Plan Product Dk |
| Key Attribute | Organization Dk |
| Dimensional Warehouse Model Data Model |