OpStats Tables
OpStats Tables
Operator Statistics is a product offered to track calls, pages, messages, etc from the operators and paging modules to the system.
Each night, data regarding phone calls, messages, emails, faxes, and more is collected from various tables and rolled up into the "opstats" table. These include number of messages sent, average ring time that it takes to answer the phone, number of calls transferred, and so on. This can be broken down by half hour in some reports or more collectively in others or via a custom / manual report.
The reports pull from this table with the collected data.
This article primarily covers the information gathered from calls made and received by the operators.
The opstats table is added to daily from the existing rows in the other tables. The data for call tracking is built from four main tables.
1) When an operator logs in to Smart Console, a row is entered into the operator_session table with the login time and username (among other things).
2) When an operator logs in or out of the ACD, a row is created in the operator_event table.
3) When a call is presented on the phone, a row is created in the session_call table (once the call is complete). Each of these has its own primary sequence number. If the same person calls back in, this is a new row in that table.
4) If a call is handled other than just with a hangup, a row is put into the call_event table denoting what occurred.
The call event types are:
CONFERENCE
HOLD
PARK
TRANSFER
BRIDGE (Smart Speech only)
OPERATOR_SESSION
The operator_session table also shows which machine an operator logged in from, counts of messages sent in that session counts of pages, and more.
OPERATOR_EVENT
The operator_event table tracks the ACD "in" and ACD "offline" times for an operator's session.
The operator_session table links the operator_event and session_call tables with a specific session sequence, which is unique every time the user logs in. In this way, the operator's events and calls can be tracked back to a specific login and time.
SESSION_CALL
As stated above, the session_call table has a unique sequence number for every call, and each of these are linked to the operator_session login.
The session_call table also contains the call_type which is "I" for inbound or "O" for outbound. Every row should have one of these two values. There may be something set up incorrectly if any of these are null.
Every row also has an abandoned_flag of "T" or "F". There can never be an abandoned_flag of T if the call_type is O.
If the ring_start_time column is null, then this row was an outbound call. ALL outbound calls have a call_pickup_time, however.
If the call_pickup_time is null and the call_type is I, then the call was abandoned (abandoned_flag = T).
If the call_pickup_time is NOT null and the call_type is I, then the call was not abandoned (abandoned_flag = F)
(or, again, if the call_type is O, then it couldn't have been abandoned).
**quick note call_type: I, O abandoned_flag: T, F * never an abandoned T with a call_type O ring_start_time null --> Outbound call_pickup_time ALL outbounds have this call_pickup_time null abandoned = T call_type = I call_pickup_time not null abandoned = F call_type = I abandoned = F call_type = O
CALL_EVENT
When a call is transferred, held, etc, a row is put into the call_event table linking it to the session_call table. Any of these events can happen multiple times with any call. For example: the call is parked while a phone number is checked. Then a transfer is attempted but doesn't go through, so the operator puts the caller on hold. Then the call is transferred again, and so on.
If this data is in the session_call table when the rollup runs, it will be entered into the build tables. If the call is not in the rollup table after the rollup runs, then it wasn't in the session_call table when this ran.
When the rollup script collects the data from these tables, the default is to select those which started "yesterday" in relation to the run of the script (due to the time at which the script runs). However, due to the collection nature of the program, the row is put into the session_call table once the call is completed.
Therefore, if a call starts (inbound or outbound) before midnight and ends after the script runs (if it ends at 2 am for example), then it will not be processed in that rollup. It will also not be processed in the next night's rollup since the start date of that call is now 2 days prior to the script running. The script's default runtime is 12.30 am. If there are excessive missing rows due to this type of call running long, the opstats rollup can be run again sometime that same day to collect the values that began the day before.
Some of the math and calculations for the displayed information is done in the various reports, and some opstats columns are created from the columns of the other tables during the nightly rollup.
There are multiple steps to the rollup of the opstats data. From the initial tables, the data is placed into other combinations of tables before the end results are placed into the "opstats" table itself. The main tables are archived and purged in the same fashion as the normal archive and purge process, but the intermediate rollup tables are cleared and re-made each time the script runs.