...
Broadsoft CC Fact Tables | |||||
BroadsoftCCQueueCallFact | |||||
| Column Name | Azure Type | Snowflake Type | Description | References | Indexed |
|---|---|---|---|---|---|
| qcf_id | int | NUMBER(38,0) | Primary key | ||
| qcf_agentSkillFirst | int | NUMBER(38,0) | First agent skill associated with the call (if any) | ||
| qcf_agentSkillLast | int | NUMBER(38,0) | Last agent skill associated with the call (if any) | ||
| qcf_preservedOfferedCount | int | NUMBER(38,0) | Preserved offered count from previous queue (if any) | ||
| qcf_preservedBouncedCount | int | NUMBER(38,0) | Preserved bounced count from previous queue (if any) | ||
| qcf_preservedWaitTime | int | NUMBER(38,0) | Preserved wait time from previous queue (if any) | ||
| qcf_agentUserFk | int | NUMBER(38,0) | Foreign key to the call agent user | BroadsoftUserDimension | Yes |
| qcf_dnisFk | int | NUMBER(38,0) | Foreign key to the call DNIS | BroadsoftCCDNISDimension. | Yes |
| qcf_callCenterFk | int | NUMBER(38,0) | Foreign key to the Call Center. | BroadsoftCallCenterDimension | Yes |
| qcf_callReleasedDuration | int | NUMBER(38,0) | Future use | ||
| qcf_callReleasedDateUTC | int | NUMBER(38,0) | Foreign key to the call released date in UTC | DateDimension | Yes |
| qcf_callReleasedTimeUTC | int | NUMBER(38,0) | Foreign key to the call released time of day in UTC | TimeOfDayDimension | Yes |
| qcf_callReleasedDateLocal | int | NUMBER(38,0) | Foreign key to the call released date in local time | DateDimension | Yes |
| qcf_callReleasedTimeLocal | int | NUMBER(38,0) | Foreign key to the call released time of day in local time | TimeOfDayDimension | Yes |
| qcf_callOfferedCount | int | NUMBER(38,0) | The number of times the call was offered to an agent from this queue | ||
| qcf_callBouncedCount | int | NUMBER(38,0) | This is the number of calls that bounced and remained in the queue, which were presented to agents | ||
| qcf_callQueueDuration | int | NUMBER(38,0) | The total call wait time in seconds in this queue | ||
| qcf_callCreationDateUTC | int | NUMBER(38,0) | Foreign key to the call creation date in UTC | DateDimension | Yes |
| qcf_callCreationTimeUTC | int | NUMBER(38,0) | Foreign key to the call creation time of day in UTC | TimeOfDayDimension | Yes |
| qcf_callCreationDateLocal | int | NUMBER(38,0) | Foreign key to the call creation date in local time | DateDimension | Yes |
| qcf_callCreationTimeLocal | int | NUMBER(38,0) | Foreign key to the call creation time of day in local time | TimeOfDayDimension | Yes |
| qcf_status | varchar(32) | VARCHAR(32) | Call status. Possible values:
| ||
| qcf_key | varchar(162) | VARCHAR(162) | Future use | ||
| qcf_accountId | int | NUMBER(38,0) | Foreign key to the account associated with this queue call | AccountDimension | Yes |
| qcf_callId | varchar(162) | VARCHAR(162) | The unique call identifier from the VoIP platform | Yes | |
| qcf_networkCallId | varchar(162) | VARCHAR(162) | Network call identifier from the VoIP platform | ||
| qcf_remoteNumber | varchar(162) | VARCHAR(162) | The calling / called number (depending on whether the call was inbound or outbound respectively) | ||
| qcf_numberCalled | varchar(162) | VARCHAR(162) | The dialed number | ||
| qcf_creationTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The call creation timestamp in UTC | Yes | |
| qcf_lastUpdateTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Future use (currently equals to creation timestamp) | ||
| qcf_callAddedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp in which the call was added to the queue in UTC | ||
| qcf_callOfferedFirstTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp in which the call was first offered in UTC | ||
| qcf_callOfferedLastTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp in which the call was last offered in UTC | ||
| qcf_callAnsweredTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The answered timestamp in UTC | ||
| qcf_callBouncedFirstTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp in which the call was first bounced in UTC | ||
| qcf_callBouncedLastTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp in which the call was last bounced in UTC | ||
| qcf_callBounceTransferredTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp (in UTC) in which the call was transferred automatically because the caller was placed on hold for too long by an agent. | ||
| qcf_callAbandonedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp (in UTC) in which the call was removed from the queue as a result of the caller hanging up | ||
| qcf_callEscapedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp (in UTC) in which the call was removed from the queue because the caller chose the voice mail option. | ||
| qcf_callOverflowedByTimeTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp (in UTC) in which the call was transferred to an alternative destination because the callers waited too long in queue | ||
| qcf_callReleasedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Call was released timestamp in UTC | ||
| qcf_callStrandedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The time (in UTC) that the call was transferred to an alternative destination because no agents were logged into the queue | ||
| qcf_callStrandedUnavailableTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | If the call stranded policy is applied, this timestamp will be populated (time in UTC). | ||
| qcf_callTransferredTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | If the call has been transferred out of the queue by a supervisor, this timestamp will be populated (time in UTC) | ||
| qcf_callOverflowedBySizeTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | If the call overflowed from the queue due to too many calls in the queue, this timestamp will be populated (time in UTC) | ||
| qcf_eipId | varchar(16) | VARCHAR(16) | The account identifier for this queue call | Yes | |
| qcf_deflected | bit (1=true/0=false) | BOOLEAN | Indicates whether the call is deflected or not. When a pre-queue policy is applied to a call instead of queueing it, the call is said to be “deflected”. | ||
| qcf_callNightServiceAppliedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | If the night service policy is applied, this timestamp will be populated (time in UTC) | ||
| qcf_deflectedCallReason | varchar(32) | VARCHAR(32) | The reason that the call was deflected. Possible values are:
| ||
| qfc_redirectAddress | varchar(64) | VARCHAR(64) | The redirected address if the call redirected to another party | ||
| qcf_agentCallId | varchar(162) | VARCHAR(162) | If an agent answer the call, this field will be populated with the agent call identifier. | Yes | |
| qcf_parentEipId | varchar(16) | VARCHAR(16) | The parent account identifier | ||
| qcf_handledAgentSkill | int | NUMBER(38,0) | If an agent answered the call and an agent skill was applied, this field will be populated with the agent skill level | ||
| qcf_handledCallHeldDuration | int | NUMBER(38,0) | If an agent answered the call and the call was placed on hold, this field will be populated with the total hold duration (in seconds) | ||
| qcf_handledCallRingDuration | int | NUMBER(38,0) | If an agent answered the call, this field will be populated with the total ring duration (in seconds) | ||
| qcf_handledCallTalkDuration | int | NUMBER(38,0) | If an agent answered the call, this field will be populated with the total talk duration (in seconds) | ||
| qcf_handledWrapUpDuration | int | NUMBER(38,0) | If an agent answered the call and went in to wrap-up state after the call ended, this field will be populated with the total wrap-up duration (in seconds) | ||
| qcf_handledDispositionCode | varchar(256) | VARCHAR(256) | If an agent answered the call and enter disposition code, this field will be populated | ||
| qcf_handledCallTotalDuration | int | NUMBER(38,0) | If an agent answered the call, this field will be populated with the total call duration (in seconds) | ||
| qcf_userId | varchar(161) | VARCHAR(161) | The broadsoft user fully qualified identifier | ||
| qcf_lastUpdatedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The record updated timestamp | ||
| qcf_updated | bit | BOOLEAN | Indicates whether the record was updated | ||
| acf_escalation | varchar(12) | VARCHAR(12) | |||
BroadsoftCCAgentCallFact | |||||
| Column Name | SQL Type | Snowflake Type | Description | References | Indexed |
| acf_id | int | NUMBER(38,0) | Primary Key | ||
| acf_agentSkill | int | NUMBER(38,0) | The agent skill level for an answered ACD inbound call | ||
| acf_agentUserFk | int | NUMBER(38,0) | Foreign key to the agent user | BroadsoftUserDimension | Yes |
| acf_dnisFk | int | NUMBER(38,0) | Foreign key to the DNIS | BroadsoftCCDNISDimension | Yes |
| acf_callCenterFk | int | NUMBER(38,0) | Foreign key to the Call Center | BroadsoftCallCenterDimension | Yes |
| acf_callHeldCount | int | NUMBER(38,0) | The total amount of times the call was placed on hold by the agent | ||
| acf_callHeldDuration | int | NUMBER(38,0) | The total amount of time the call spent in a on hold state (in seconds) | ||
| acf_callTotalDuration | int | NUMBER(38,0) | The total amount of time since the call was created until the call was release (in seconds) | ||
| acf_callRingDuration | int | NUMBER(38,0) | The total amount of time this call was ringing for the handling agent (in seconds) | ||
| acf_callTalkDuration | int | NUMBER(38,0) | The duration of the call in seconds from the answer event to the release event | ||
| acf_callStartedDateUTC | int | NUMBER(38,0) | Foreign key to the call started date in UTC | DateDimension | Yes |
| acf_callStartedTimeUTC | int | NUMBER(38,0) | Foreign key to the call started time of day in UTC | TimeOfDayDimension | Yes |
| acf_callStartedDateLocal | int | NUMBER(38,0) | Foreign key to the call started date in local time | DateDimension | Yes |
| acf_callStartedTimeLocal | int | NUMBER(38,0) | Foreign key to the call started time of day in local time | TimeOfDayDimension | Yes |
| acf_callReleasedDateUTC | int | NUMBER(38,0) | Foreign key to the call released date in UTC | DateDimension | Yes |
| acf_callReleasedTimeUTC | int | NUMBER(38,0) | Foreign key to the call released time of day in UTC | TimeOfDayDimension | Yes |
| acf_callReleasedDateLocal | int | NUMBER(38,0) | Foreign key to the call released date in local time | DateDimension | Yes |
| acf_callReleasedTimeLocal | int | NUMBER(38,0) | Foreign key to the call released time of day in local time | TimeOfDayDimension | Yes |
| acf_status | varchar(16) | VARCHAR(16) | The status of the call. The only possible status is 'Completed' | ||
| acf_key | varchar(162) | VARCHAR(162) | Future use | ||
| acf_accountId | int | NUMBER(38,0) | Foreign key to the account associated with this agent call | AccountDimension | Yes |
| acf_callId | varchar(162) | VARCHAR(162) | The unique call identifier from the VoIP platform | Yes | |
| acf_networkCallId | varchar(162) | VARCHAR(162) | Network call identifier from the VoIP platform | ||
| acf_remoteNumber | varchar(162) | VARCHAR(162) | The calling / called number (depending on whether the call was inbound or outbound respectively) | ||
| acf_numberCalled | varchar(162) | VARCHAR(162) | The dialed number | ||
| acf_creationTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The call creation timestamp in UTC | Yes | |
| acf_lastUpdateTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Future use (currently equals to creation timestamp) | ||
| acf_callAnsweredTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp representing the call answer event (in UTC) | ||
| acf_callStartedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp representing the call started event (in UTC) | Yes | |
| acf_callHeldTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Represents the timestamp the call was first placed on hold by the agent (in UTC) | ||
| acf_callResumedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Represents the timestamp the call was first resumed after call was placed on hold by the agent (in UTC) | ||
| acf_callReleasedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp representing the call release event by the agent (in UTC) | ||
| acf_eipId | varchar(16) | VARCHAR(16) | The account identifier | Yes | |
| acf_nonCallCenterCall | bit (1=true/0=false) | BOOLEAN | If the agent call was not associated with the call center, this field will be set to 1. | ||
| acf_direction | varchar(12) | VARCHAR(12) | The call direction. Possible values are:
| ||
| acf_callType | varchar(20) | VARCHAR(20) | This is the call type. Possible values:
| ||
| acf_redirectLocation | varchar(30) | VARCHAR(30) | Redirect location if the call was redirected to another party. Possible values:
| ||
| acf_redirectNumber | varchar(164) | VARCHAR(164) | The redirect number if the call was redirected to another party | ||
| acf_wrapUpDuration | int | NUMBER(38,0) | Total time the agent spent in the Wrap-Up state in seconds | ||
| acf_wrapUpExitTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp in which the call was Wrapped-Up by the agent in UTC | ||
| acf_parentEipId | varchar(16) | VARCHAR(16) | The parent account identifier | ||
| acf_bounced | bit (1=true/0=false) | BOOLEAN | Indicates whether the call was bounced (1) or not (0) | ||
| acf_userId | varchar(161) | VARCHAR(161) | The broadsoft user fully qualified identifier | ||
| acf_lastUpdatedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The record updated timestamp | ||
| acf_updated | bit | BOOLEAN | Indicates whether the record was updated | ||
BroadsoftCCAgentCallDispositionFact | |||||
| Column Name | Azure Type | Snowflake Type | Description | References | Indexed |
| acdf_id | int | NUMBER(38,0) | Primary key | ||
| acdf_agentUserFk | int | NUMBER(38,0) | Foreign key to the agent user | BroadsoftUserDimension | Yes |
| acdf_dnisFk | int | NUMBER(38,0) | Foreign key to the DNIS | BroadsoftCCDNISDimension | |
| acdf_callCenterFk | int | NUMBER(38,0) | Foreign key to the Call Center | BroadsoftCallCenterDimension | |
| acdf_dispositionEntryDateUTC | int | NUMBER(38,0) | Foreign key to the the disposition entry date in UTC | DateDimension | Yes |
| acdf_dispositionEntryTimeUTC | int | NUMBER(38,0) | Foreign key to the disposition entry time of day in UTC | TimeOfDayDimension | Yes |
| acdf_dispositionEntryDateLocal | int | NUMBER(38,0) | Foreign key to the disposition entry date in local time | DateDimension | Yes |
| acdf_dispositionEntryTimeLocal | int | NUMBER(38,0) | Foreign key to the disposition entry time of day in local time | TimeOfDayDimension | Yes |
| acdf_status | varchar(16) | VARCHAR(16) | The status of the call. The only possible status is 'Completed' | ||
| acdf_key | varchar(162) | VARCHAR(162) | Future use | ||
| acdf_accountId | int | NUMBER(38,0) | Foreign key to the account associated with this call | AccountDimension | Yes |
| acdf_callId | varchar(162) | VARCHAR(162) | The unique call identifier from the VoIP platform | Yes | |
| acdf_code | varchar(256) | VARCHAR(256) | The disposition code for this call | ||
| acdf_creationTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The call creation timestamp in UTC | ||
| acdf_lastUpdateTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Future use (currently equals to creation timestamp) | ||
| acdf_dispositionEntryTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The disposition entry timestamp in UTC | ||
| acdf_eipId | varchar(16) | VARCHAR(16) | The account identifier for this call | Yes | |
| acdf_parentEipId | varchar(16) | VARCHAR(16) | The parent account identifier | ||
| acdf_userId | varchar(161) | VARCHAR(161) | The broadsoft user fully qualified identifier associated with this agent | ||
| acdf_lastUpdatedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The record updated timestamp | ||
| acdf_updated | bit | BOOLEAN | Indicates whether the record was updated | ||
BroadsoftCCAgentStaffingFact | |||||
| Column Name | Azure Type | Snowflake Type | Description | References | Indexed |
| astf_id | int | NUMBER(38,0) | Primary key | ||
| astf_agentUserFk | int | NUMBER(38,0) | Foreign key to the agent user | BroadsoftUserDimension | Yes |
| astf_callCenterFk | int | NUMBER(38,0) | Foreign key to the Call Center | BroadsoftCCDNISDimension | |
| astf_agentStaffedDuration | int | NUMBER(38,0) | The duration between the agent transition from one staffed state to unstaffed state and vise versa | ||
| astf_agentStaffedStartedDateUTC | int | NUMBER(38,0) | Foreign key to the agent staffed/unstaffed start date in UTC | DateDimension | Yes |
| astf_agentStaffedStartedTimeUTC | int | NUMBER(38,0) | Foreign key to the agent staffed/unstaffed start time of day in UTC | TimeOfDayDimension | Yes |
| astf_agentStaffedStartedDateLocal | int | NUMBER(38,0) | Foreign key to the agent staffed/unstaffed start date in local time | DateDimension | Yes |
| astf_agentStaffedStartedTimeLocal | int | NUMBER(38,0) | Foreign key to the agent staffed/unstaffed start time of day in local time | TimeOfDayDimension | Yes |
| astf_agentStaffedEndedDateUTC | int | NUMBER(38,0) | Foreign key to the agent staffed/unstaffed end date in UTC | DateDimension | Yes |
| astf_agentStaffedEndedTimeUTC | int | NUMBER(38,0) | Foreign key to the agent staffed/unstaffed end time of day in UTC | TimeOfDayDimension | Yes |
| astf_agentStaffedEndedDateLocal | int | NUMBER(38,0) | Foreign key to the agent staffed/unstaffed end date in local time | DateDimension | Yes |
| astf_agentStaffedEndedTimeLocal | int | NUMBER(38,0) | Foreign key to the agent staffed/unstaffed end time of day in local time | TimeOfDayDimension | Yes |
| astf_status | varchar(16) | VARCHAR(16) | The status of the staffed/unstaffed activity. The only possible status is 'Completed' | ||
| astf_state | varchar(32) | VARCHAR(32) | Agent staffed states. Possible values:
| ||
| astf_skill | int | NUMBER(38,0) | The agent's skill level | ||
| astf_key | varchar(162) | VARCHAR(162) | Future use | ||
| astf_accountId | int | NUMBER(38,0) | Foreign key to the account | AccountDimension | Yes |
| astf_lastUpdateTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Future use | ||
| astf_agentStaffedStartedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp of the staffed/unstaffed start activity (in UTC) | Yes | |
| astf_agentStaffedEndedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp of the staffed/unstaffed end activity (in UTC) | ||
| astf_eipId | varchar(16) | VARCHAR(16) | The account identifier | Yes | |
| astf_parentEipId | varchar(16) | VARCHAR(16) | The parent account identifier | ||
| astf_userId | varchar(161) | VARCHAR(161) | The broadsoft user fully qualified identifier associated with this agent | ||
| astf_lastUpdatedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The record updated timestamp | ||
| astf_updated | bit | BOOLEAN | Indicates whether the record was updated | ||
BroadsoftCCAgentStateFact | |||||
| Column Name | Azure Type | Snowflake Type | Description | References | Indexed |
| asf_id | int | NUMBER(38,0) | Primary key | ||
| asf_agentUserFk | int | NUMBER(38,0) | Foreign key to the agent user | BroadsoftUserDimension | Yes |
| asf_agentStateDuration | int | NUMBER(38,0) | The duration between the agent transition from one state to another | ||
| asf_agentStateStartedDateUTC | int | NUMBER(38,0) | Foreign key to the agent state start date in UTC | DateDimension | Yes |
| asf_agentStateStartedTimeUTC | int | NUMBER(38,0) | Foreign key to the agent state start time of day in UTC | TimeOfDayDimension | Yes |
| asf_agentStateStartedDateLocal | int | NUMBER(38,0) | Foreign key to the agent state start date in local time | DateDimension | Yes |
| asf_agentStateStartedTimeLocal | int | NUMBER(38,0) | Foreign key to the agent state start time of day in local time | TimeOfDayDimension | Yes |
| asf_agentStateEndedDateUTC | int | NUMBER(38,0) | Foreign key to the agent state end date in UTC | DateDimension | Yes |
| asf_agentStateEndedTimeUTC | int | NUMBER(38,0) | Foreign key to the agent state end time of day in UTC | TimeOfDayDimension | Yes |
| asf_agentStateEndedDateLocal | int | NUMBER(38,0) | Foreign key to the agent state end date in local time | DateDimension | Yes |
| asf_agentStateEndedTimeLocal | int | NUMBER(38,0) | Foreign key to the agent state end time of day in local time | TimeOfDayDimension | Yes |
| asf_status | varchar(16) | VARCHAR(16) | The status of the agent state activity. The only possible status is 'Completed' | ||
| asf_state | varchar(32) | VARCHAR(32) | The agent's state. Possible values:
| ||
| asf_code | varchar(256) | VARCHAR(256) | The agent state code if applicable | ||
| asf_key | varchar(162) | VARCHAR(162) | Future use | ||
| asf_accountId | int | NUMBER(38,0) | Foreign key to the account | AccountDimension | Yes |
| asf_lastUpdateTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | Future use | ||
| asf_agentStateStartedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp in which the agent enters into one of the states (in UTC) | Yes | |
| asf_agentStateEndedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The timestamp in which the agent switch to another state (exit from the previous state) in UTC | ||
| asf_eipId | varchar(16) | VARCHAR(16) | The account identifier | Yes | |
| asf_parentEipId | varchar(16) | VARCHAR(16) | The parent account identifier | ||
| asf_userId | varchar(161) | VARCHAR(161) | The broadsoft user fully qualified identifier associated with this agent | ||
| asf_lastUpdatedTimestamp | datetime2(7) | TIMESTAMP_NTZ(9) | The record updated timestamp | ||
| asf_updated | bit | BOOLEAN | Indicates whether the record was updated | ||
...