SQL Table Definitions
The Softdial Publisher™ SQL table schema provides what Sytel believes is a best-fit normalisation of call center event data relating to:
- media sessions (typically phone calls)
- agent sessions (login / availability)
- agent activity
- queue activity
- campaign activity
Each tenant requires their own set of tables. This means that either a separate database instance must be created per tenant, or each tenant needs to have their own schema within a database instance.
Schema-per-tenant is the model most common with users. This minimises DBMS licensing costs and it makes for a simpler model for database replication as there is only 1 instance to replicate for multiple tenants.
For setup details, see Data Feeds.
Show table definitions
Below are details of each of the reporting tables populated by Softdial Publisher™.
agentavailability
Contains 1 row for each period of an agent being ACD available. In the course of an agent login session, the agent may go available and unavailable several times. This will result in multiple rows being output to the agentavailability table.
Agents may also be made unavailable (and available again) under program control for automated call blending. When this occurs, it results in completion of an agent's availability on one campaign and the start of availability on another. This will result in a row being output to the agentavailability table.
| Derived from | Agent Data [AZ] |
| Updated |
If Not a Follow Up Call, i.e: Old State (OS) != 8 |
| Populated by | history az.xslt |
Show fields
| Field | Parameter | Description |
|---|---|---|
| campaign_name | Campaign Name (CN) | The campaign that the agent was logged in to for this login session |
| agent_name | Agent Name (AN) | The Agent Identifier |
| avail_timestamp | Z0 | The time the agent went available (OADateTime) |
| unavail_timestamp | Z1 | The time the agent went unavailable (OADateTime) |
| time_available | Z2 | The duration of this available session |
| agent_extension | Agent extension (AE) | The agent extension |
| tenant | Tenant ID (TD) | The tenant to which the agent is logged in. |
| free_reason | Free Reason (FR) | A user defined coded representing the reason why an agent went unavailable |
| free_description | Free Reason Description (FD) | A short text explanation for the accompanying Free Reason |
agentlogins
A list of all agent login and logout events, by tenant, campaign and agent
| Derived from | Agent Data [AZ] |
| Updated | Every time an agent logs out, i.e: Old State (OS) = 2 (Logged In and Unavailable) |
| Populated by | history az.xslt |
Show fields
| Field | Parameter | Description |
|---|---|---|
| campaign_name | Campaign Name (CN) | The campaign that the agent was logged in to for this login session |
| agent_name | Agent Name (AN) | The agent identifier |
| avail_timestamp | Z0 | The time the agent logged in (OADateTime) |
| unavail_timestamp | Z1 | The time the agent logged out (OADateTime) |
| time_available | Z2 | The duration of this login session (secs) |
agentoutcomes
Agent disposition code / name cross-reference. Should replicate the settings in
agents
A list of all agents who have logged in to Softdial CallGem™, by tenant.
| Derived from | Agent Data [AZ] |
| Updated | When an agent logs in for the first time, i.e: Detail State (DS) = 2 (Logged in and Unavailable) |
| Populated by | history az.xslt |
Show fields
| Field | Parameter | Description |
|---|---|---|
| agent | Agent Name (AN) | The agent Identifier of an agent that has logged in to Softdial CallGem™ |
| description | Agent Description (AD) | The agent description |
agent_reports
The agent_reports table contains periodical agent reports. A periodical agent report describes a 15 minutes period of agent activity. It is a result of aggregation and compression of AgentData [AZ] messages into single time slice in order to achieve better query performance. This table is accessible through PHPConsole and used by Softdial Reporter™ 2.
agent_states
From V10.6.7 - Timestamp and duration details for the various agent states (e.g. waiting, talking, wrapping, previewing etc) by tenant, campaign and agent.
| Derived from | Agent Data [AZ] |
| Populated by | history az.xslt |
Show fields
| Field | Parameter | Description |
|---|---|---|
| tenant | Tenant ID (TD) | The tenant to which the agent is logged in. |
| campaign_name | Campaign Name (CN) | The campaign that the agent was logged in to for this login session |
| agent_name | Agent Name (AN) | The agent identifier |
| start_global | Z0 | If the agent is logged in and unavailable, Z0 contains the timestamp for the agent login; otherwise it contains the timestamp for the agent going available. (OADateTime) |
| stop_global | Z1 | If the agent is logged in and unavailable, Z1 contains the current timestamp. (OADateTime) |
| duration_global | Z2 | Contains the time difference between Z0 and Z1. (secs) |
| state_global | Agent State (ZA) | The Agent State - 0 = Not logged in 1 = Logged In and Available or Working 2 = Logged in and Unavailable |
| start | Z3 | Contains the timestamp of the last agent state change. (OADateTime) |
| detail_state | Detail State (DS) | The detailed agent state. See the Agent Data [AZ] message for options. |
| old_state | Old State (DS) | The previous or old agent state - See the Agent Data [AZ] message for options. |
| duration | Z4 | Contains the time difference between Z3 and the timestamp of the last event Timestamp (TS) parameter. (in secs) |
| agent_description | Agent Description (AD) | The agent description |
| extension | Agent Extension (AE) | The agent extension |
| reason | Free Reason (FR) | A user defined coded representing the reason why an agent went Unavailable |
| description | Free Reason Description (FD) | A short text explanation for the accompanying Free Reason |
| data | Data (DT) | Data associated with this session |
| campaign_session | Campaign Unique ID (YC) | An internally generated unique session id for the campaign identified in the Campaign Name (CN) parameter |
| agent_session | Agent Unique ID (YA) | An internally generated unique session id for the agent identified in the Agent Name (AN) parameter |
| login_time | Agent Login Time (W0) | The timestamp of the last Agent Login [AL] message (OADateTime) |
| avail_time | Agent Extension (AE) | The timestamp of last Agent Available [AA] message (OADateTime) |
callcycles
Contains normalised data on a call session. Each call session processed through Softdial CallGem™ will result in one row being written to a callcycles table.
The table contains
- event timestamps for all major call events
- permuted times for
- setup
- ring
- progress
- talk
- wrap
These enable MIS applications to produce meaningful reports without having to process the timestamp data.
| Derived from | Session Data [IZ] |
| Populated by | history iz.xslt |
Show fields
| Field | Parameter | Description |
|---|---|---|
| ci_timestamp | Timestamp1 (Z1) | The timestamp of the Call Initiate [CI] event. (OADateTime) |
| sessionid | Session ID (SI) | The ID of the current call session. |
| phonenum | Telephone Number (TN) | The telephone number of the source call.In inbound terms this is the CLI/ANI. |
| campaign | Campaign Name (CN) | The campaign that the agent was logged in to for this login session. |
| agent | Agent Name (AN) | The Agent Identifier. |
| team | - | Reserved for future use. |
| turret | - | The turret to which the agent was logged in for this session |
| media_type | - | The media type of this session |
| callyear | - | The 'year' component derived from the Z1 timestamp. |
| callmonth | - | The 'month' component derived from the Z1 timestamp. |
| callday | - | The 'day' component derived from the Z1 timestamp. |
| callhour | - | The 'hour' component derived from the Z1 timestamp. |
| callminute | - | The 'minute' component derived from the Z1 timestamp. |
| callsecond | - | The 'second' component derived from the Z1 timestamp. |
| proceeding_timestamp | Timestamp2 (Z2) | The Setup Complete time derived from the Delay Notification [DN] message. (OADateTime) |
| physicalconn_timestamp | Timestamp7 (Z7) | The timestamp of the actual connect (before AMD). If the call fails, set to Disconnect time derived from the Now Disconnected [ND] message. (OADateTime) |
| outcome_timestamp | Timestamp3 (Z3) | The Call Outcome timestamp derived from either the Line Connected [LC] message or the Call Failed [CF] message. (OADateTime) |
| ac_timestamp | Timestamp4 (Z4) | The time that the call was connected to an agent. Derived from the Agent Connect [AC] message. (OADateTime) |
| z8_timestamp | Timestamp8 (Z8) |
Timestamp of a successful or failed transfer. If successful, derived from the Transfer Acknowledge [TA] message If the transfer fails, derived from the Now Disconnected [ND] message. (OADateTime) |
| sw_timestamp | Timestamp5 (Z5) | The timestamp of the agent going into wrap. Derived from the Now Disconnected [ND] message. (OADateTime) |
| tc_timestamp | Timestamp6 (Z6) | The timestamp of the completed call session. Derived from the Transaction Complete [TC] message. (OADateTime) |
| cd_timestamp | Timestamp5 (Z5) | The timestamp of the call being disconnected. Derived from the Now Disconnected [ND] message. (OADateTime) |
| ao_parameter | Agent Outcome (AO) | The agent disposition associated with an agent-classified call. |
| ca_parameter | Reason (RE) | This code indicates the outcome of all calls whether successful or not. See the Session Data [IZ] message for details of valid Reason codes. |
| waiting_launch_time | TimeInStateAwaitingLaunch (J0) | The total amount of time spent in an 'awaiting launch' state. |
| launched_not_offering_time | TimeInStateLaunched (J1) | The total amount of time spent in an 'launched' state prior to any signaling from the network to indicate acceptance, progress or connection. |
| proceeding_time | TimeInStateProceeding (J2) | The total amount of time spent between the network accepting the call and the call being signaled as ringing or connected. |
| ring_time | TimeInStateAlerting (J3) | The total amount of time spent ringing |
| connect_signaled_parked_time | TimeInStateConnectSignaled (J4) | The total amount of time the call spent in a connected state prior to being enqueued or connected directly to an agent. |
| queued_time | TimeInStateEnqueued (J5) | The total amount of time spent in a queued state awaiting dequeue to an agent. |
| expired_time | TimeInStateExpired (J6) | The total amount of time spent in an expired state awaiting requeue or drop. |
| agent_offering_time | TimeInStateOffering (J7) | The total amount of time spent in an 'offering to agent' state before the agent accepts. |
| agent_connected_time | TimeInStateConnected (J8) | The total amount of time spent connected to an agent. |
| hold_time | TimeInStateOnHold (J9) | The total amount of time spent on hold. |
| mute_time | TimeInStateMuted (K0) | The total amount of time spent with one or more parties to the call muted. |
| transfer_offer_time | TimeInStateTXOffer (K1) | The total amount of time spent offering a transfer to a third party. |
| transfer_conference_time | TimeInStateTXConference (K2) | The total amount of time spent in conference during a supervised transfer or 3 party conference. |
| disconnect_requested_time | TimeInStateDisconnectRequested (K3) | The total amount of time spent waiting for a call disconnect request to be accepted. |
| disconnected_time | TimeInStateDisconnected (K4) | The total amount of time spent after the call is disconnected, prior to the call being finally disposed. |
| setup_time | - | The call setup time. Outbound - proceeding_timestamp(Z2) - ci_timestamp(Z1). (secs). Inbound - 0 |
| ring_time | - | The ring time. Outbound - If Z7 is zero - outcome_timestamp(Z3) - proceeding_timestamp(Z2). (secs) If Z7 is not zero - physicalconn_timestamp(Z7) - proceeding_timestamp(Z2). (secs) Inbound - 0 |
| queue_time | - | The queueing time. Outbound - ac_timestamp(Z4) - outcome_timestamp(Z3). (secs) Inbound - If Z4 is not zero - ac_timestamp(Z4) - ci_timestamp(Z1). (secs) If Z4 is zero - sw_timestamp(Z5) - ci_timestamp(Z1). (secs) |
| talk_time | - | The agent talk time. Outbound - z8_timestamp - ac_timestamp(Z4). (secs) Inbound - If Z4 is not zero - z8_timestamp - ac_timestamp(Z4). (secs) If Z4 is zero - 0 |
| wrap_time | - | The agent wrap time. Derived from tc_timestamp(Z6) - sw_timestamp(Z5). (secs) |
| wait_time | - |
The agent wait time. If (Z0)is zero - 0 If (Z0)is not zero - ac_timestamp(Z4) - |
| success_param | Success Rate (SR) | Used to record a success rating for reporting purposes. Valid values are 0 to 100. |
| conversion_param | Conversion (CO) | Used to indicate the scale of conversion or commitment. |
| trunkid | Trunk Identifier (TI) | The trunk identifier for the trunk used for this call session. |
| record_filename | Recording Filename (RF) | The filename of the recording created for this call session. |
| original_agent | Agent Alternative Identifier (AG) | Used in agent specific callbacks to specify an agent other than the original agent. |
| ts | Timestamp (TS) | The timestamp for this event. (OADateTime) |
| id | - | |
| queue | Group Address (GA) | The queue associated with this call session. |
| ya | Agent Unique ID (YA) | An internally generated unique session id for the agent identified in the Agent Name (AN) parameter |
| yc | Campaign Unique ID (YC) | An internally generated unique session id for the campaign identified in the Campaign Name (CN) parameter. |
callcyclesbusinessdata
From V10.5.52 - Customised call session data (requires customisation of Softdial Publisher -
| Derived from | Outcome Data [OZ] |
| Populated by | history oz.xslt |
Show details
| Field | Parameter | Description |
|---|---|---|
| transaction_timestamp | Timestamp (TS) | The timestamp of the current event (OADateTime) |
| sessionid | Session ID (SI) | The ID of the current call session. |
| phonenum | Telephone Number (TN) | The telephone number of the source call. In inbound terms, this is the CLI/ ANI. |
| campaign | Campaign Name (CN) | The campaign that the agent was logged in to for this login session |
| agent | Agent Name (AN) | The agent identifier. |
| ao_parameter | Agent Outcome (AO) | The agent disposition associated with an agent-classified call. |
| Field1 | User Customisable - Data must be taken from the Outcome Data [OZ] message Data (DT) parameter (formatted as XML) | |
| Field2 | ||
| Field3 | ||
| Field4 | ||
| Field5 |
campaign_reports
The campaign_reports table contains periodical campaign reports.
A periodical campaign report describes a 15 minute period of campaign activity. It is a result of aggregation and compression of SessionData [IZ] messages into single time slice in order to achieve better query performance. This table is accessible through PHPConsole and used by Softdial Reporter™ 2.
chat_event_data
From V10.7.1297 - A list of all chat events
| Derived from | Chat Data [XD] |
| Populated by | history xd.xslt |
Show fields
| Field | Parameter | Description |
|---|---|---|
| agent | AgentIdentifier (AN) | The agent endpoint name associated with the chat session. |
| campaign | CampaignName (CN) | The campaign name associated with the chat session. |
| data | Data (DT) | The chat data that has given rise to this event. |
| user | UserIdentifier (QI) | The user associated with the chat session |
| RS | ResourceStatus (RS) | The type of the message. For possible values and their meaning, see Chat Data [XD] |
| session | SessionIdentifier (SI) | The session identifier associated with the message. |
| tenant | TenantDescriptor (TD) | The tenant name associated with the chat session. |
| time_stamp | TimeStamp (TS) | The exact time that the event was processed by CallGem. |
| transaction | TransactionIdentifier (TX) | Used by CallGem to identify to which request this message is responding. |
| agent_unique_id | AgentUniqueId (YA) | The agent endpoint GUID associated with the chat session. |
| campaign_unique_id | CampaignUniqueId (YC) | An internally generated unique session id for the campaign identified in the CN parameter. |
| session_unique_id | SessionUniqueId (YS) | An internally generated unique session id for the call session identified in the SI parameter. |
| time_stamp0 | TimeStamp0 (Z0) | Timestamp of the previous agent-to-customer message. |
| time_stamp1 | TimeStamp1 (Z1) | Timestamp of the previous customer-to-agent message. |
| time_stamp2 | TimeStamp2 (Z2) | Timestamp of the previous customer starts typing message. |
| time_stamp3 | TimeStamp3 (Z3) | Timestamp of the previous customer stops typing message. |
| time_stamp4 | TimeStamp4 (Z4) | Timestamp of the previous agent starts typing message. |
| time_stamp5 | TimeStamp5 (Z5) | Timestamp of the previous agent stops typing message. |
queuestatistics
Contains a snapshot of statistics (e.g. calls queued, dropped, overflowed, within SLA etc.) for each inbound queue. 1 row is output for each queue at a predetermined time frequency (which can be varied by editing the Publisher configuration).
The statistics recorded for the queue are statistics from the point that the queue was created.
Queues may come into and go out of service several times during their lifetime. A queue is normally created against an inbound
| Derived from | Queue Metrics [MZ] |
| Populated by | sql mz.xslt |
Show fields
| Field | Parameter | Description |
|---|---|---|
| tx_timestamp | Timestamp (TS) | The timestamp of the current event (OADateTime) |
| tenant_id | Tenant Descriptor (TD) | The tenant ID |
| campaign_name | Session ID (SI) | The ID of the current call session. |
| queue_address | Group Address (GA) | The queue for which metrics are being reported. |
| queue_desc | - | Reserved for future use. |
| queue_length | - | The number of calls currently waiting in the queue. |
| active_agents | Active Agents (Z1) | The number of agents actively able to handle calls from the queue. |
| total_agents | Total Agents (Z2) | The number of agents who are members of the queue, including those who cannot currently handle calls. |
| queued_count | Queued Count (Z3) | The number calls that have enetered the queue. |
| dequeued_count | Dequeued Count (Z4) | The number calls that have left the queue and been transferred to an agent. |
| abandon_count | Abandon Count (Z5) | The number of calls that were abandoned by the caller |
| oflow_count | Overflow Count (Z6) | The number calls that overflowed to another queue |
| drop_count | Drop Count (Z7) | Number calls that were dropped because the timeout was reached. |
| mean_answer_time | Average Answer time (Z8) | Average time in seconds that calls took to be answered by an agent (of the calls that were successfully answered). |
| pct_calls_within_sla | Percentage Calls Within SLA (Z9) | The percentage of calls that were answered within the SLA specified time period (of the calls that were successfully answered) |
| mean_answer_time_window | WindowAverageAnswerTime (Y0) | Average time in seconds that calls took to be answered by an agent (of the calls that were successfully answered), within the recent period as configured with the Time Window (TW) parameter. |
| pct_calls_within_sla_window | WindowPercentageCallsWithinSLA (Y1) | Percentage of calls that were answered within the SLA specified time period (of the calls that were successfully answered), within the recent period as configured with the Time Window (TW) parameter. |
| id | ||
| y2 | QueuedCountRecent (Y2) | The number of calls that have entered the queue since the last update. |
| y3 | DequeuedCountRecent (Y3) | The number of calls that have left the queue and been transferred to an agent since the last update. |
| y4 | AbandonCountRecent (Y4) | The number of calls that were abandoned by the caller since the last update. |
| y5 | OverflowCountRecent (Y5) | The number of calls that overflowed to another queue since the last update. |
| y6 | DropCountRecent (Y6) | The number of calls that were dropped because the timeout was reached since the last update. |
queuetransactions
Records each call session (or media session) that passes through a queue.
For each call session:
- the enqueue time, dequeue attempts and final dequeue/ expiry time are recorded
- queued_time and agent_time are permuted
| Derived from | Queue Data [QZ] |
| Populated by | sql qz.xslt |
Show fields
| Field | Parameter | Description |
|---|---|---|
| session_guid | - | Concatenation of Tenant Descriptor (TD), Session ID (SI), and Timestamp (TS) parameters in the form TD_SI_TS. |
| tx_timestamp | Timestamp (TS) | The timestamp of the current event. (OADateTime) |
| sessionid | Session ID (SI) | The ID of the current call session. |
| cli | Telephone Number (TN) | The telephone number of the source call.In inbound terms this is the CLI/ ANI. |
| dnis | Dialed Number (DN) | The number that the caller dialed. |
| tenant_id | Tenant Descriptor (TD) | Tenant ID. |
| campaign_name | Campaign Name (CN) | The campaign that the agent was logged in to for this login session. |
| queue_address | Group Address (GA) | If specified, the queue (on the system |
| dequeue_agent | Agent Name (AN) | The agent to whom the call is dequeued. |
| enqueue_timestamp | Timestamp0 (Z0) | The time the call entered the queue. |
| first_offer_timestamp | Timestamp1 (Z1) | The timestamp of the transfer offer. |
| final_offer_timestamp | Timestamp2 (Z2) | The timestamp of the transfer accept. |
| accept_timestamp | Timestamp3 (Z3) | The timestamp of the completion of the transfer. |
| expiry_timestamp | Timestamp4 (Z4) | The timestamp if the transfer was dropped. |
| expiry_reason | Resource Status (RS) |
Indicates the outcome of the transfer attempt |
| queued_time | - | If RS = 0 (Z3 - Z0) If RS = 1 (Z4 - Z0) If RS = 2 (TS - Z0) |
| agent_time | - | The agent time (Z4 - Z3) |
switchoutcomes
Telephony outcome code / name cross-reference.
teams
Team ID / name cross reference. Reserved for future use
transfers
Stores event data relating to a single call transfer. A call session may be transferred to several different parties through its lifetime. The transfers table stores a row for each time a call is transferred from an agent to another party.
For each transfer:
- all event timestamps are recorded
- ring and conference times are permuted
| Derived from | TransferData [XZ] |
| Populated by | sql xz.xslt |
Show fields
| Field | Parameter | Description |
|---|---|---|
| session_guid | Campaign Name (CN) | Concatenation of tenant description, session identifier, and timestamp parameters in the form TD_SI_TS. |
| tx_timestamp | Timestamp (TS) | The timestamp of the current event (OADateTime) |
| sessionid | Session ID (SI) | The ID of the current call session. |
| phonenum | Telephone Number (TN) | The telephone number of the source call. In inbound terms, this is the CLI/ ANI. |
| campaign_name | Campaign Name (CN) | The campaign that the agent was logged in to for this login session |
| src_agent_name | Agent Name (AN) | The agent initiating the transfer. |
| dest_agent_name | Second Agent (A2) | The agent that the call session is being transferred to. |
| transfer_address | - | Reserved for future use. |
| dest_type | - | Reserved for future use. |
| dest_address | Address (EX) | The transferee address |
| offer_timestamp | Timestamp1 (Z1) | The timestamp of the transfer offer. |
| accept_timestamp | Timestamp2 (Z2) | The timestamp of the transfer accept. |
| complete_timestamp | Timestamp3 (Z3) | The timestamp of the completion of the transfer. |
| drop_timestamp | Timestamp3 (Z3) | The timestamp if the transfer was dropped. |
| outcome | Resource Status (RS) | Indicates the outcome of the transfer attempt. 0 = Success 1 = Rejected 2 = Failed |
| ring_time | - | The ring time for the transfer (Z2 - Z1) |
| conference_time | - | The conference time (Z3 - Z2) |
| talk_time | Timestamp3 (Z4) | The time at which the agent who received the call finished with it (due to it ending or a subsequent transfer). |
For ease of table creation, the table definitions above are also provided in ready-to-execute form, on the SCC web server (by default) at :
C:\Softdial\WebServer\home\admin\www\downloads\schemas
Files are available for the following databases:
| Database | Filename |
|---|---|
| MS SQL Server Data Engine* | reporter.msde.sql |
| MS SQL Server* | reporter.mssql.sql |
| MySQL | reporter.mysql.sql |
| Oracle | reporter.oracle.sql |
| Postgres | reporter.postgres.sql |
There are additional tables in the schema provided to support Softdial Reporter™. These are not in any normalised form and are used solely to deliver the data storage needs of Softdial Reporter™.
* Note that MS SQL Server Express has a data file size limit of 10Gb. Log files can be any size.
Customisation
This schema is as per the default implementation. It is possible to modify every detail of this implementation.
Common possibilities are:
- keying all tables by tenant and having a single output database across all tenants
- making additional permuted data to optimise MIS reporting
- extracting line-of-business information from the session data (assuming consistent format across campaigns) to add to the reporting model