In the Column List:
- P = Primary key
- M = Mandatory field
- F = Foreign key (where the term is used loosely to indicate a surrogate key reference to a field in another table, not a formal constraint)
- DV = Default value
- In the Data Type column, varchar/nvarchar means that the data type is varchar except in multi-language databases that use Unicode, in which case the data type is nvarchar.
Table CONTACT_ATTEMPT_FACT
Description
Each row in this table describes an Outbound Contact Server (OCS) processing attempt for an outbound campaign contact. An attempt may or may not include dialing; an example of an attempt that did not include dialing would be a preview record that is retrieved but then canceled without dialing.
Starting with release 8.5.015.07, you can control whether Genesys Info Mart creates separate CONTACT_ATTEMPT_FACT (CAF) records or a single, aggregated CAF record for calls dialed in the context of the same CALL_ATTEMPT_GUID. The default is a single, aggregated record. Prior to release 8.5.015.07, Genesys Info Mart always created separate records for each call attempt dialed in the context of the same CALL_ATTEMPT_GUID. If you want to retain the prior behavior, set the ocs-caf-aggregates-calls option to false.
The grain of the fact is an accumulating snapshot that represents the duration of the attempt. Record-based columns are populated with data from the first record associated with the contact attempt. Rows are inserted only when the attempt is completed, and they are not updated.
The CALL_ATTEMPT_ID enables you to link a CAF record with the associated Interaction Resource Fact (IRF).
Hint: For easiest viewing, open the downloaded CSV file in Excel and adjust settings for column widths, text wrapping, and so on as desired. Depending on your browser and other system settings, you might need to save the file to your desktop first.
Column List
CONTACT_ATTEMPT_FACT_KEY
The primary key of this table.
TENANT_KEY
The surrogate key that is used to join the TENANT dimension to the fact tables.
CREATE_AUDIT_KEY
The surrogate key that is used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data creation. This value can be useful for aggregation, enterprise application integration (EAI), and ETL tools — that is, applications that need to identify newly added data.
UPDATE_AUDIT_KEY
The surrogate key that is used to join to the CTL_AUDIT_LOG control table. The key specifies the lineage for data update. This value can be useful for aggregation, enterprise application integration (EAI), and ETL tools — that is, applications that need to identify recently modified data.
MEDIA_TYPE_KEY
The surrogate key that is used to join the MEDIA_TYPE dimension to the fact tables.
START_DATE_TIME_KEY
Identifies the start of a 15-minute interval in which the contact attempt began. Use this value as a key to join the fact tables to any configured DATE_TIME dimension, in order to group the facts that are related to the same interval and/or convert the START_TS timestamp to an appropriate time zone.
END_DATE_TIME_KEY
Identifies the start of a 15-minute interval in which the contact attempt ended. Use this value as a key to join the fact tables to any configured DATE_TIME dimension, in order to group the facts that are related to the same interval and/or convert the END_TS timestamp to an appropriate time zone.
DIALING_MODE_KEY
The surrogate key that is used to join the DIALING_MODE dimension to the fact tables.
RESOURCE_KEY
The surrogate key that is used to join the RESOURCE_ dimension to the fact and aggregate tables in order to identify the person who indicated that this contact attempt is processed. Note that this resource is not necessarily the same resource that handled the outbound call.
RESOURCE_GROUP_COMBINATION_KEY
The surrogate key that is used to join records in this table to a specific combination of resource groups in the RESOURCE_GROUP_COMBINATION dimension. This field identifies the groups of which the Agent resource was a member when the contact attempt started. This field references the default "No Group" (-2) value if the Agent does not belong to a group. This field references the "UNKNOWN" (-1) value for the records that are associated with a discarded group combination.
PLACE_KEY
The surrogate key that is used to join the PLACE dimension to the fact tables.
CAMPAIGN_KEY
The surrogate key that is used to join the CAMPAIGN dimension to the fact tables.
GROUP_KEY
The surrogate key that is used to join the GROUP_ dimension to the fact tables.
CPD_RESULT_KEY
The surrogate key that is used to join the CALL_RESULT dimension to the fact tables for the dialer result.
CALL_RESULT_KEY
The surrogate key that is used to join the CALL_RESULT dimension to the fact tables.
RECORD_TYPE_KEY
The surrogate key that is used to join the RECORD_TYPE dimension to the fact tables.
RECORD_STATUS_KEY
The surrogate key that is used to join the RECORD_STATUS dimension to the fact tables.
CALLING_LIST_KEY
The surrogate key that is used to join the CALLING_LIST dimension to the fact tables.
CONTACT_INFO_TYPE_KEY
The surrogate key that is used to join the CONTACT_INFO_TYPE dimension to the fact tables.
TIME_ZONE_KEY
The surrogate key that is used to join the TIME_ZONE dimension to the fact tables. It specifies the time zone of the contact.
ATTEMPT_DISPOSITION_KEY
The key that uniquely identifies the disposition. The key value combines the state and the descriptor that provides additional details. The first eight bits identify the cause of the contact attempt termination. The key can be used to join the ATTEMPT_DISPOSITION table to the fact table.
CAMP_GROUP_SESS_FACT_SDT_KEY
The value of the START_DATE_TIME_KEY field of the record in the CAMPAIGN_GROUP_SESSION_FACT table. On a partitioned database, CAMP_GROUP_SESS_FACT_SDT_KEY in combination with CAMP_GROUP_SESSION_FACT_KEY forms a value of the composite primary key for the CAMPAIGN_GROUP_SESSION_FACT table.
CAMP_GROUP_SESSION_FACT_KEY
The value of the primary key of the CAMPAIGN_GROUP_SESSION_FACT table. This surrogate key is used to join this contact attempt fact to its campaign group session fact. In other words, this key places the contact attempt within the context of a campaign group session.
CALLID
The unique ID of the interaction, as retrieved from the CALLID field of the GOX_CHAIN_CALL IDB table. The referenced interaction depends on the campaign dialing mode. For example, for Push Preview dialing mode, CALLID refers to the multimedia interaction that is used to push the preview record to an agent.
If Genesys Info Mart has been configured to create a single, aggregated record for multiple call attempts dialed in the context of the same CALL_ATTEMPT_GUID, the CALLID refers to the last dialed call. (This is the default behavior starting with release 8.5.015.07.)
RECORD_FIELD_GROUP_1_KEY
The surrogate key that is used to join the RECORD_FIELD_GROUP_1 dimension to the fact tables. It optionally specifies a combination of configured field values for a contact attempt.
RECORD_FIELD_GROUP_2_KEY
The surrogate key that is used to join the RECORD_FIELD_GROUP_2 dimension to the fact tables. It optionally specifies a combination of configured field values for a contact attempt.
START_TS
The UTC-equivalent value of the date and time at which the contact attempt began.
END_TS
The UTC-equivalent value of the date and time at which the contact attempt ended.
CALL_ATTEMPT_ID
The ID that is assigned to this processing attempt by OCS.
This value allows you to associate interaction details with contact attempt details using the following references:
- IRF_USER_DATA_GEN_1.GSW_CALL_ATTEMPT_GUID = CONTACT_ATTEMPT_FACT.CALL_ATTEMPT_ID
- IRF_USER_DATA_GEN_1.INTERACTION_RESOURCE_ID = INTERACTION_RESOURCE_FACT.INTERACTION_RESOURCE_ID
RECORD_ID
The unique identifier for the record in the calling list.
CHAIN_ID
The chain identifier of the record that is being attempted.
CHAIN_N
The order of the record that is being attempted within the chain.
For example, a customer, represented by CHAIN_ID=5, could have the following order of attempts defined in this table:
- The first link in the chain (CHAIN_N = 1) could represent the customer’s home telephone number (RECORD_ID = 10).
- The second link in the chain (CHAIN_N = 2) could represent the customer’s work telephone number (RECORD_ID = 11).
CONTACT_INFO
The contact_info of the record that is being attempted. The CONTACT_INFO_TYPE dimension value indicates the type, such as HomePhone.
ATTEMPT_ORDINAL
The attempt number of the calling list record.
DAILY_FROM_SECONDS
Indicates the start of the time frame during which this record can be called (allowed calling window); this value is measured in seconds from midnight.
DAILY_UNTIL_SECONDS
Indicates the end of the time frame during which this record can be called (allowed calling window); this value is measured in seconds from midnight.
DAILY_FROM_TIME
The UTC-equivalent value that corresponds to the start of the time frame during which this record can be called.
DAILY_UNTIL_TIME
The UTC-equivalent value that corresponds to the end of the time frame during which this record can be called.
DAILY_FROM_TIME_KEY
Identifies the start of a 15-minute interval that corresponds to the start of the allowed calling window. Use this value as a key to join the fact tables to any configured DATE_TIME dimension.
DAILY_UNTIL_TIME_KEY
Identifies the start of a 15-minute interval that corresponds to the end of the allowed calling window. Use this value as a key to join the fact tables to any configured DATE_TIME dimension.
CONTACT_DAILY_FROM_TIME
The starting date and time of the time frame during which this record can be called, in the time zone of the contact.
CONTACT_DAILY_UNTIL_TIME
The ending date and time of the time frame during which this record can be called, in the time zone of the contact.
DIAL_SCHED_TIME
Modified: 8.5.116.26 (behavior changed)
The UTC-equivalent value of the date and time of the scheduled call. Starting with release 8.5.116.26, the ocs-dial-sched-time option enables you to specify whether the value represents the scheduled time of the next call attempt or the time that was scheduled for the current call attempt. The default behavior is to record the next call attempt.
DIAL_SCHED_TIME_KEY
Identifies the start of a 15-minute interval that corresponds to the scheduled time of the call, as specified in the DIAL_SCHED_TIME field. Use this value as a key to join to any configured DATE_TIME dimension, in order to group the facts that are related to the same interval and/or convert the START_TS timestamp to an appropriate time zone.
CONTACT_DIAL_SCHED_TIME
The date and time of the scheduled call, in the time zone of the contact.
OVERDIAL_FLAG
A flag to indicate whether this attempt was overdialed, meaning that a contact was reached, but no agent or IVR was available to handle the call: 0 = No, 1 = Yes.
CONTACT_COMPLETE_FLAG
A flag to indicate whether this attempt led to the contact being completed: 0 = No, 1 = Yes.
RPC_FLAG
Indicates whether the right person was contacted during this processing attempt: 0 = No, 1 = Yes.
CONVERSION_FLAG
Indicates whether a conversion was made during this processing attempt: 0 = No, 1 = Yes.
CPD_DIAL_COUNT
Indicates whether dialing duration was provided by OCS: 0 = No, 1 = Yes.
CPD_DIAL_DURATION_MS
The time, in milliseconds, between the moment when dialing was initiated and the moment when the dialed call was answered by the called party or when the call that did not reach the called party was released.
Note that the time when the call was answered by the called party is available only when Call Progress Detection (CPD) Server is used for dialing.
CPD_COUNT
Indicates whether this contact attempt had call progress detection performed against it: 0 = No, 1 = Yes.
CPD_DURATION_MS
The time, in milliseconds, from the moment when the call was answered by the called party until the moment when CPD was done.
Note that both time stamps are available only when CPD Server is used for dialing.
CPD_TRANSFER_COUNT
Indicates whether a transfer was used to deliver the call from the point of call progress detection to the Agent or IVR.
CPD_TRANSFER_DURATION_MS
The time, in milliseconds, between the moment when CPD was completed and the moment when the call was established on the Agent’s DN or IVR DN.
Note that the time when CPD was completed is available only when CPD Server is used for dialing.
RECORD_FIELD_1 through RECORD_FIELD_10
Value of custom record fields 1 through 10, respectively. These fields are a numeric data type.
RECORD_FIELD_11 through RECORD_FIELD_30
Value of custom record fields 11 through 30, respectively. These fields are a numeric data type.
RECORD_FIELD_31 through RECORD_FIELD_60
Introduced: Release 8.5.003 (RECORD_FIELD_41 through RECORD_FIELD_60)
Value of custom record fields 31 through 60, respectively. These fields are a character data type.
ACTIVE_FLAG
Indicates whether the contact attempt is currently active: 0 = No, 1 = Yes.
PURGE_FLAG
This field is reserved.
PRODUCER_BATCH_ID
Introduced: Release 8.5.015.19
Reserved for internal use.
Index List
| CODE | U | C | Description |
|---|---|---|---|
| I_CAF_SDT | Improves access time, based on the Start Date Time key. | ||
| I_CAF_TNT | Improves access time, based on the Tenant. | ||
| I_CAF_CGSF | Improves access time, based on the Campaign Group Session Fact key. | ||
| I_CAF_CID | Improves access time, based on the Call ID. |
Index I_CAF_SDT
| Field | Sort | Comment |
|---|---|---|
| START_DATE_TIME_KEY | Ascending |
Index I_CAF_TNT
| Field | Sort | Comment |
|---|---|---|
| TENANT_KEY | Ascending |
Index I_CAF_CGSF
| Field | Sort | Comment |
|---|---|---|
| CAMP_GROUP_SESSION_FACT_KEY | Ascending |
Index I_CAF_CID
| Field | Sort | Comment |
|---|---|---|
| CALLID | Ascending |
Subject Areas
- Contact_Attempt — Represents outbound campaign contact record attempts. An attempt may or may not include dialing.
- Facts — Represents the relationships between subject area facts.