Jump to: navigation, search

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

Modified: 8.5.015.19 (PRODUCER_BATCH_ID added); 8.5.015.07 (record-creation behavior changed); 8.5.003 (RECORD_FIELD_41 through RECORD_FIELD_60 added); 8.5.003 (in Oracle, fields with VARCHAR data types use explicit CHAR character-length semantics)

In partitioned databases, this table is partitioned.


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).

Tip
To assist you in preparing supplementary documentation, click the following link to download a comma-separated text file containing information such as the data types and descriptions for all columns in this table: Download a CSV file.

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

Legend

Column Data Type P M F DV
CONTACT_ATTEMPT_FACT_KEY numeric(19) X X
TENANT_KEY int X X
CREATE_AUDIT_KEY numeric(19) X X
UPDATE_AUDIT_KEY numeric(19) X X
MEDIA_TYPE_KEY int X X
START_DATE_TIME_KEY int X X
END_DATE_TIME_KEY int X X
DIALING_MODE_KEY int X X
RESOURCE_KEY int X X
RESOURCE_GROUP_COMBINATION_KEY int X X -1
PLACE_KEY int X X
CAMPAIGN_KEY int X X
GROUP_KEY int X X
CPD_RESULT_KEY int X X
CALL_RESULT_KEY int X X
RECORD_TYPE_KEY int X X
RECORD_STATUS_KEY int X X
CALLING_LIST_KEY int X X
CONTACT_INFO_TYPE_KEY int X X
TIME_ZONE_KEY int X X
ATTEMPT_DISPOSITION_KEY int X X
CAMP_GROUP_SESS_FACT_SDT_KEY int X
CAMP_GROUP_SESSION_FACT_KEY numeric(19) X
CALLID varchar(64)
RECORD_FIELD_GROUP_1_KEY int X X
RECORD_FIELD_GROUP_2_KEY int X X
START_TS int
END_TS int
CALL_ATTEMPT_ID varchar(64)
RECORD_ID int
CHAIN_ID int
CHAIN_N int
CONTACT_INFO varchar(255)/nvarchar(255)
ATTEMPT_ORDINAL int
DAILY_FROM_SECONDS int
DAILY_UNTIL_SECONDS int
DAILY_FROM_TIME int
DAILY_UNTIL_TIME int
DAILY_FROM_TIME_KEY int
DAILY_UNTIL_TIME_KEY int
CONTACT_DAILY_FROM_TIME datetime
CONTACT_DAILY_UNTIL_TIME datetime
DIAL_SCHED_TIME int
DIAL_SCHED_TIME_KEY int
CONTACT_DIAL_SCHED_TIME datetime
OVERDIAL_FLAG numeric(1)
CONTACT_COMPLETE_FLAG numeric(1)
RPC_FLAG numeric(1)
CONVERSION_FLAG numeric(1)
CPD_DIAL_COUNT smallint 0
CPD_DIAL_DURATION_MS int 0
CPD_COUNT smallint 0
CPD_DURATION_MS int 0
CPD_TRANSFER_COUNT smallint 0
CPD_TRANSFER_DURATION_MS int 0
RECORD_FIELD_1 through RECORD_FIELD_10 numeric(14,4)
RECORD_FIELD_11 through RECORD_FIELD_30 int
RECORD_FIELD_31 through RECORD_FIELD_60 varchar(255)/nvarchar(255)
ACTIVE_FLAG numeric(1)
PURGE_FLAG numeric(1)
PRODUCER_BATCH_ID numeric(19)

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.
This page was last edited on June 22, 2022, at 21:50.
Comments or questions about this documentation? Contact us for support!