Table COMMUNICATIONS_FACT

DescriptionFact entity that records the measures related to communications involving a customer which could be generated by a marketing campaign.

The measures are fully identified by the dimensions, which are:
- Time (grain: day)
- Campaign (grain: campaign)
- Customer
- Communication purpose
- Channel role who is the sender or recipient of the communication.
- Policy
- Communication profile
- Task
- location of customer
- Party score
- Risk Factor Score. Grain = Claimant communication score
- Risk Factor Score. Grain = Social media activity score
- Communication role
- Signor

For example, the measure Number of communications corresponds to the number of communications received per day, per campaign, per market segment, per communication purpose (for example, complaint), per communication channel role.
This may be use to analyse the number of complaints generated by a marketing campaign.

Columns
NUMBER_OF_COMMUNICATIONSNUMBER_OF_COMMUNICATIONS
TIME_BETWEEN_COMMUNICATIONTIME_BETWEEN_COMMUNICATION
CALENDAR_DATE_IDCALENDAR_DATE_ID
CUSTOMER_IDCUSTOMER_ID
GEOGRAPHIC_AREA_IDGEOGRAPHIC_AREA_ID
TASK_IDTASK_ID
GEOGRAPHIC_AREA_MINI_DIMENSION_IDGEOGRAPHIC_AREA_MINI_DIMENSION_ID
TASK_MINI_DIMENSION_IDTASK_MINI_DIMENSION_ID
POPULATION_TIMESTAMPPOPULATION_TIMESTAMP
COMMUNICATION_PROFILE_IDCOMMUNICATION_PROFILE_ID
COMMUNICATION_CONTENT_IDCOMMUNICATION_CONTENT_ID
COMMUNICATION_MINI_DIMENSION_IDCOMMUNICATION_MINI_DIMENSION_ID
CONTACT_POINT_IDCONTACT_POINT_ID
PERSON_IDPERSON_ID
PERSON_MINI_DIMENSION_IDPERSON_MINI_DIMENSION_ID
NUMBER_OF_COMPLAINTSNUMBER_OF_COMPLAINTS
NUMBER_OF_OPEN_COMPLAINTSNUMBER_OF_OPEN_COMPLAINTS

Foreign Key
COMMUNICAT_CALE_FKCOMMUNICAT_CALE_FK
COMMUNICAT_CUST_FKCOMMUNICAT_CUST_FK
COMMUNICA_GEOGR_FKCOMMUNICA_GEOGR_FK
COMMUNICAT_TASK_FKCOMMUNICAT_TASK_FK
COMMUNI_TASK_MI_FKCOMMUNI_TASK_MI_FK
COMM_GEOGRAPHIC_FKCOMM_GEOGRAPHIC_FK
COMMUN_COMMUNIC_FKCOMMUN_COMMUNIC_FK
COMMUN_COMMUNIC_F1COMMUN_COMMUNIC_F1
COMM_COMMUNICAT_FKCOMM_COMMUNICAT_FK
COMMUNICAT_CONT_FKCOMMUNICAT_CONT_FK
COMMUNICAT_PERS_FKCOMMUNICAT_PERS_FK
COMMUN_PERSON_M_FKCOMMUN_PERSON_M_FK

Primary Key
COMMUNICATIONS_FACT_PKCOMMUNICATIONS_FACT_PK

Column Details

 NUMBER_OF_COMMUNICATIONS
DescriptionThe total number of communications issued by parties contacted in the context of marketing activities.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 TIME_BETWEEN_COMMUNICATION
DescriptionThe number of days or time lapsed between a given communication and the last communication received.
Data TypeDECIMAL(20 , 6)
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



 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



 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



 TASK_ID
DescriptionIdentifies the Task 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



 TASK_MINI_DIMENSION_ID
DescriptionIdentifies the Task 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



 COMMUNICATION_PROFILE_ID
DescriptionThe unique identifier of the Communication profile
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 COMMUNICATION_CONTENT_ID
DescriptionThe unique identifier of the Communication content
Data TypeBIGINT
Default ValueNone
Is Primary Keytrue
Is Identityfalse
Is Nullablefalse
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



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



 CONTACT_POINT_ID
DescriptionThe unique identifier of the Geographic Area
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



 NUMBER_OF_COMPLAINTS
DescriptionThe total number of communications issued by parties contacted in the context of marketing activities.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse



 NUMBER_OF_OPEN_COMPLAINTS
DescriptionThe total number of communications issued by parties contacted in the context of marketing activities.
Data TypeINTEGER
Default ValueNone
Is Primary Keyfalse
Is Identityfalse
Is Nullabletrue
Is Generatedfalse
Is Row Change Timestampfalse
Is hiddenfalse

Foreign Key Details

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



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



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



 COMMUNICAT_TASK_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCOMMUNICATIONS_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnTASK_ID [BIGINT]
Parent TableTASK
Parent Role Nameis dimension of
Parent Multiplicity1
Unique ConstraintTASK_PK



 COMMUNI_TASK_MI_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCOMMUNICATIONS_FACT
Child Role Namehas for dimension
Child Multiplicity*
Key ColumnTASK_MINI_DIMENSION_ID [BIGINT]
Parent TableTASK_MINI_DIMENSION
Parent Role Nameis dimension of
Parent Multiplicity0..1
Unique ConstraintTASK_MINI_DIMENSION_PK



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



 COMMUN_COMMUNIC_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCOMMUNICATIONS_FACT
Child Role NameVerb1
Child Multiplicity*
Key ColumnCOMMUNICATION_PROFILE_ID [BIGINT]
Parent TableCOMMUNICATION_PROFILE
Parent Multiplicity1
Unique ConstraintCOMMUNICATION_PROFILE_PK



 COMMUN_COMMUNIC_F1
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCOMMUNICATIONS_FACT
Child Role NameVerb2
Child Multiplicity*
Key ColumnCOMMUNICATION_CONTENT_ID [BIGINT]
Parent TableCOMMUNICATION_CONTENT
Parent Multiplicity1
Unique ConstraintCOMMUNICATION_CONTENT_PK



 COMM_COMMUNICAT_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshipfalse
Child TableCOMMUNICATIONS_FACT
Child Role NameVerb3
Child Multiplicity*
Key ColumnCOMMUNICATION_MINI_DIMENSION_ID [BIGINT]
Parent TableCOMMUNICATION_MINI_DIMENSION
Parent Multiplicity0..1
Unique ConstraintCOMMUNICATION_MINI_DIMENSION_PK



 COMMUNICAT_CONT_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCOMMUNICATIONS_FACT
Child Role NameVerb4
Child Multiplicity*
Key ColumnCONTACT_POINT_ID [BIGINT]
Parent TableCONTACT_POINT
Parent Multiplicity1
Unique ConstraintCONTACT_POINT_PK



 COMMUNICAT_PERS_FK
Referential Integrity: On UpdateNO_ACTION
Referential Integrity: On DeleteNO_ACTION
Is Identifying Relationshiptrue
Child TableCOMMUNICATIONS_FACT
Child Role NameVerb5
Child Multiplicity*
Key ColumnPERSON_ID [BIGINT]
Parent TablePERSON
Parent Multiplicity1
Unique ConstraintPERSON_PK



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

Primary Key Details

 COMMUNICATIONS_FACT_PK
DescriptionThe unique identifier of the Communications Fact
MemberCALENDAR_DATE_ID [BIGINT]
MemberCUSTOMER_ID [BIGINT]
MemberTASK_ID [BIGINT]
MemberGEOGRAPHIC_AREA_ID [BIGINT]
MemberCOMMUNICATION_PROFILE_ID [BIGINT]
MemberCOMMUNICATION_CONTENT_ID [BIGINT]
MemberCONTACT_POINT_ID [BIGINT]
MemberPERSON_ID [BIGINT]