Reporting and statistics
Introduction
Pack reporting is a part of the XivoCC. It aims at computing historical statistics, which are stored in the xivo_stats database. Sample reports based on them are accessible in SpagoBI.
Important
New tables in xivo_stats schema are available (prefixed with xc_). These tables are (so far) for internal use and can be changed without notice. Documentation will be available once this new statistic model will replace the existing one and fix the existing limitations of previous model described above.
Known limitations
Queue members should only be agents. If users are members of a queue, their statistics will be incomplete.
Configuration modifications on the XiVO (such as an agent deletion) are replicated on the statistics server, and their previous value is not kept. However, statistics history is preserved.
Calls longer than 4 hours are considered as unterminated calls and therefore communication time is set to 0 for these calls.
If two agents are associated to the same call, they will have the same hold time for this call.
Transfer statistics limitations in call_data table:
Given two queues Q1 and Q2, two agents A1 and A2, and an external caller C.
C calls Q1 and A1 answers
A1 transfers to Q2 and A2 answers
A2 transfers to the outside
Then the second transfer is seen as a transfer to the outside.
Given one queue Q1, Two agents A1 and A2 and an external caller C.
C calls Q1 and A1 answers
A1 transfers to A2 through internal phone number
A1 completes the transfer
Then the call between A2 and C is not computed at all in statistics.
Reporting Architecture
Attached Data
The reporting allows to attach as mush data as wished to a given call, in order to find them in the reporting database for future use. This data must be in the form of a set of key-value pairs.
To attach data to a call, you must use the dialplan’s CELGenUserEvent application:
exten = s,n,CELGenUserEvent(ATTACHED_DATA,my_key=my_value)
This will insert the following tuple in the attached_data table:
key |
value |
|---|---|
my_key |
my_value |
Using SpagoBi
Important
All report samples (see Upload Statistics Reports) bundled with XiVOCC are aimed to work with french date format. By default dates are picked in french format (day/month/year). Either you need to type them explicitly in your locale format either you can set Locale to french thanks to Flag menu in Spago. See #1662
Scheduling reports
Due to a SpagoBi limitation, when scheduling reports using string parameters, you need to enter manualy the parameter using comma and quotes to separate values. Example for queues support, technical and sales, the parameters of the schedulers must be filled as follow:
‘support’,’technical’,’sales’
You may use Jaspersoft® Studio to design you own reports.
Warning
Due to a limitation in SpagoBi do not use single quotes in your reports name, otherwise you will not be able to schedule your report (see #213).
Using Kibana
Kibana is a web tool used to compute statistics based on Elasticsearch content. The reports packaged with the Pack reporting give you an outline of your recent call center activity. Here is a Kibana sample panel:
Graphs are based on the queue_log table, enriched with agent names and agent groups, and inserted into an Elasticsearch index. It contains avents about calls placed on queues, and events about agent presences.
For each entry in the queue_log index, the following attributes are available:
queudis`downloads page playname : Queue display name
data1: basic queue_log data, with a different meaning according to the event
callid : Call unique identifier, generated by Asterisk
event : Call or agent status event - please see below
agentnumber: Agent number
queuename : Technical queue name
groupname : Agent group name
queuetime: Time of the event
agentname : Name of the agent, if available
The event can be one of the following (for a detailed explanation, please refer to https://wiki.asterisk.org/wiki/display/AST/Queue+Logs):
Call events:
FULL
CONNECT
EXITEMPTY
CLOSED
EXITWITHTIMEOUT
JOINEMPTY
ABANDON
ENTERQUEUE
TRANSFER
COMPLETEAGENT
COMPLETECALLER
RINGNOANSWER
Agent or queue event:
ADDMEMBER
PAUSEALL
PAUSE
WRAPUPSTART
UNPAUSE
UNPAUSEALL
PENALTY
CONFIGRELOAD
AGENTCALLBACKLOGIN
AGENTCALLBACKLOGOFF
REMOVEMEMBER
PRESENCE
QUEUESTART
Database schema
Glossary
- uniqueId :
unqiueId are Ids generated by asterisk for each call leg.
When A calls B we get a call leg for A and one for B, each one is a separate uniqueId.
in cel asterisk table you can find all event linked to a call leg by looking for it in the column uniqueId.
- linkedId :
the column linkedId in cel Asterisk table contains a uniqueId which links the two legs of the call.
The leg which initiates the call (A leg) will have the same value for both uniqueId and linkedId columns.
The other leg (B leg) will have the uniqueId of B in uniqueId column and will have the uniqueId of A in linkedId one.
Overview
call_data
Each line in call_data, correspond to a unique call and contains only one uniqueId of one call leg. This table is aggregated in near real time with the data of cel Asterisk table.
Important
End of a call is considered when phone is hung up, we don’t consider the time of transfers that can be done besides initial call.
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
|
uniqueid |
VARCHAR |
Call unique reference, generated by Asterisk |
dst_num |
VARCHAR |
Called number |
start_time |
TIMESTAMP |
Call start time |
answer_time |
TIMESTAMP |
Call answer time |
end_time |
TIMESTAMP |
Call end time (phone is hung up) |
status |
status_type |
Call status. Beware: only answered is properly filled. |
ring_duration_on_answer |
INTEGER |
Ring time of the endpoint answering the call, in seconds |
transfered |
BOOLEAN |
True if the call has been transfered |
call_direction |
call_direction_type |
Call direction (‘’incoming’’ : call from the outside, received by XiVO ; ‘’outgoing’’ : call to the outside, originated by an endpoint associated to XiVO ; ‘’internal’’ : call taking place entirely inside the XiVO) |
src_num |
VARCHAR |
Calling number |
transfer_direction |
call_direction_type |
Indicates the transfer direction, if relevant |
src_agent |
VARCHAR |
Agent originating the call |
dst_agent |
VARCHAR |
Agent receiving the call, if it is a direct call on an agent. Not filled when the call is destined to a queue |
src_interface |
VARCHAR |
Interface originating the call (in the Asterisk sense, ex : SCCP/01234) |
attached_data
Data attached to the call (cf. Attached Data)
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
|
id_call_data |
INTEGER |
Id of the associated tuple in call_data |
key |
VARCHAR |
Name of the attached data |
value |
VARCHAR |
Value of the attached data |
call_element
Part of a call matching the reaching of an endpoint
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
|
call_data_id |
INTEGER |
Id of the associated tuple in call_data |
start_time |
TIMESTAMP |
Time at which the endpoint was called |
answer_time |
TIMESTAMP |
Answer time for the endpoint |
end_time |
TIMESTAMP |
End time of this call part |
interface |
VARCHAR |
Endpoint interface |
call_on_queue
Calls on a queue
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
|
callid |
VARCHAR |
Call unique reference, generated by Asterisk |
queue_time |
TIMESTAMP |
Time of entrance in the queue |
total_ring_seconds |
INTEGER |
Total ring time, in seconds (includes ringing of non-answered calls) |
answer_time |
TIMESTAMP |
Answer time |
hangup_time |
TIMESTAMP |
Hangup time |
status |
call_exit_type |
Call status (full: full queue; closed: closed queue; joinempty: call arrived on empty queue; leaveempty : exit when queue becomes empty; divert_ca_ratio : call redirected because the ratio waiting calls/agents was exceeded ; divert_waittime: call redirected because estimated waiting time was exceeded; answered: call answered ; abandoned: call abandoned; timeout : maximum waiting time exceeded) |
queue_ref |
VARCHAR |
Technical queue name |
agent_num |
VARCHAR |
Number of the agent taking the call, if relevant |
hold_periods
Hold periods
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
|
linkedid |
VARCHAR |
Call unique reference, generated by Asterisk |
start |
TIMESTAMP |
Hold start time |
end |
TIMESTAMP |
Hold end time |
transfers
Table that follow transfers between calls
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
id of the transfer |
callidfrom |
VARCHAR |
initial call |
callidto |
VARCHAR |
destination call |
stat_queue_periodic
Statistics aggregated by queue and time interval (15 minutes)
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
|
time |
TIMESTAMP |
Start time of the considered interval |
queue |
VARCHAR |
Queue technical name |
answered |
INTEGER |
Number of answered calls |
abandoned |
INTEGER |
Number of abandoned calls |
total |
INTEGER |
Total number of calls received on the queue (which excludes the calls dissuaded before entering the queue) |
full |
INTEGER |
Number of calls arrived on a full queue (diversion before entering the queue) |
closed |
INTEGER |
Number of calls arrived on a closed queue, outside of the configured schedules (diversion before entering the queue) |
joinempty |
INTEGER |
Number of calls arrived on an empty queue (diversion before entering the queue) |
leaveempty |
INTEGER |
Number of calls redirected because of a queue becoming empty |
divert_ca_ratio |
INTEGER |
Number of calls arrived when the calls / available agents ratio is exceeded (diversion before entering the queue) |
divert_waittime |
INTEGER |
Number of calls arrived when the estimated waiting time is exceeded (diversion before entering the queue) |
timeout |
INTEGER |
Nombre of calls redirecting because maximum waiting time is exceeded |
stat_agent_periodic
Statistics aggregated by agent and time interval (15 minutes)
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
|
time |
TIMESTAMP |
Start time of the considered interval |
agent |
VARCHAR |
Agent number |
login_time |
INTERVAL |
Login time |
pause_time |
INTERVAL |
Pause time |
wrapup_time |
INTERVAL |
Wrap-up time |
stat_queue_specific
Statistics aggregated by queue, called number and time interval (15 minutes)
Column |
Type |
Description |
|---|---|---|
time |
TIMESTAMP |
Start time of the considered interval |
queue_ref |
VARCHAR |
Technical name of the queue |
dst_num |
VARCHAR |
Called number |
nb_offered |
INTEGER |
Number of presented calls |
nb_abandoned |
INTEGER |
Number of abandoned calls |
sum_resp_delay |
INTEGER |
Wait time, in seconds |
answer_less_t1 |
INTEGER |
Number of calls answered in less than t1 seconds |
abandoned_btw_t1_t2 |
INTEGER |
Number of calls abandoned between t1 and t2 seconds |
answer_btw_t1_t2 |
INTEGER |
Number of calls answered between t1 and t2 seconds |
abandoned_more_t2 |
INTEGER |
Number of calls answered in more than t2 seconds |
communication_time |
INTEGER |
Total communication time in seconds |
hold_time |
INTEGER |
Total hold time in seconds |
wrapup_time |
INTEGER |
Total wrap-up time in seconds |
The thresholds t1 and t2 are configurable:
in the table queue_specific_time_period for the default values in seconds. Installation values are t1=15 seconds and t2=20 seconds. Data is saved in the form of (name, seconds) pairs, for example : (‘t1’, 15).
in the table queue_threshold_time for values specific to a queue. Data is saved in the form of a tuple (queue name, t1, t2).
stat_agent_specific
Statistics aggregated by agent and time interval (15 minutes)
Important
Hold times are considered as conversation time and so are included.
Column |
Type |
Description |
|---|---|---|
time |
TIMESTAMP |
Start time of the considered interval |
agent_num |
VARCHAR |
Agent number |
nb_offered |
INTEGER |
Number of calls presented from a queue |
nb_answered |
INTEGER |
Number of calls answered from a queue |
conversation_time |
INTEGER |
Conversation time on incoming calls from a queue (ACD), in seconds |
ringing_time |
INTEGER |
Ringing time on incoming cals from a queue (ACD), in seconds |
nb_outgoing_calls |
INTEGER |
Number of calls emitted to the outside |
conversation_time_outgoing_calls |
INTEGER |
Conversation time in calls emitted to the outside, in seconds |
hold_time |
INTEGER |
Hold time for any kind of calls (ACD, internal and outgoing) in seconds |
nb_received_internal_calls |
INTEGER |
Number of received internal calls |
conversation_time_received_internal_calls |
INTEGER |
Conversation time on received internal calls, in seconds |
nb_transfered_intern |
INTEGER |
Number of calls coming from a queue and transfered to an internal destination |
nb_transfered_extern |
INTEGER |
Number of calls coming from a queue and transfered to an external destination |
nb_emitted_internal_calls |
INTEGER |
Number of emitted internal calls |
conversation_time_emitted_internal_calls |
INTEGER |
Conversation time on emitted internal calls, in seconds |
nb_incoming_calls |
INTEGER |
Number of received incoming calls |
conversation_time_incoming_calls |
INTEGER |
Conversation time on received incoming calls, in seconds |
stat_agent_queue_specific
Statistics aggregated by queue, called number, agent and time interval (15 minutes)
Column |
Type |
Description |
|---|---|---|
time |
TIMESTAMP |
Start time of the considered interval |
agent_num |
VARCHAR |
Agent number |
queue_ref |
VARCHAR |
Technical name of the queue |
dst_num |
VARCHAR |
Called number |
nb_answered_calls |
INTEGER |
Number of answered calls |
communication_time |
INTEGER |
Communication time, in seconds |
hold_time |
INTEGER |
Hold time, in seconds |
wrapup_time |
INTEGER |
Wrap-up time, in seconds |
agentfeatures
Gather information about agent profile
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
|
numgroup |
INTEGER |
Agent group number |
number |
VARCHAR |
Agent number (≠ line number) |
firstname |
VARCHAR |
Agent first name |
lastname |
VARCHAR |
Agent last name |
queuefeatures
Gather information about queue
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
Queue id |
name |
VARCHAR |
Technical name |
displayname |
VARCHAR |
Display name |
number |
VARCHAR |
Number to reach the queue |
userfeatures
Gather information about user profile
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
User id |
firstname |
VARCHAR |
User first name |
lastname |
VARCHAR |
User last name |
linefeatures
Gather information about line used by a user
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
Line id |
name |
VARCHAR |
SIP interface name |
number |
VARCHAR |
Phone number |
context |
VARCHAR |
Line context |
provisioningid |
INTEGER |
Number to provision a device |
agent_position
Line number used by agents
Column |
Type |
Description |
|---|---|---|
agent_num |
VARCHAR |
Agent number |
line_num |
VARCHAR |
Line number of the device used |
start_time |
TIMESTAMP |
Begin date of line use |
end_time |
TIMESTAMP |
End date of line use |
sda |
VARCHAR |
Line direct inward dial |
agent_states
Last state known for an agent
Column |
Type |
Description |
|---|---|---|
agent |
VARCHAR |
Agent number |
time |
TIMESTAMP |
Last state time |
state |
agent_state_type |
logged_on, logged_off, paused, wrapup |
agentgroup
Agent group ownership
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
Agent group Id |
groupid |
INTEGER |
Deprecated use id |
name |
VARCHAR |
Name of the group |
groups |
VARCHAR |
Deprecated |
commented |
INTEGER |
know if group is disabled |
deleted |
INTEGER |
know if group is deleted |
description |
TEXT |
description set in XiVO |
dialaction
Action to trigger when a number (extension) is matching some criteria (congestion, busy, no answer…)
Column |
Type |
Description |
|---|---|---|
event |
VARCHAR |
Type of criteria (answer, noanswer, busy, congestion, chanunavail, qwaittime, qwaitratio) |
category |
VARCHAR |
user, meetme, queue, incall, group, callfilter… |
categoryval |
VARCHAR |
Id applying to the category (user id if category is user) |
action |
VARCHAR |
What to trigger (can be custom or any existing category defined above) |
actionarg1 |
VARCHAR |
Value associated to action. e.g. if queue must be called, arg1 will contain the queue id |
actionarg2 |
VARCHAR |
Rarely used to define a second value if category chosen need it |
linked |
INTEGER |
Deprecated |
extensions
List of all numbers that you can call within XiVO
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
Extension Id |
commented |
INTEGER |
Define if extension is disabled |
context |
VARCHAR |
Context of your extension (xivo-features are hardcoded ones) |
exten |
VARCHAR |
Number to reach the extension |
type |
VARCHAR |
user, meetme, queue, incall, group, extenfeatures… |
user_line
Mapping table between a user, its line and associated extension
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
User line association id |
user_id |
INTEGER |
User id |
line_id |
INTEGER |
Line id |
extension_id |
INTEGER |
Extension id |
labels
Defines the list of labels that can be set to users
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
Label id |
display_name |
VARCHAR |
Name displayed for the label |
Description |
TEXT |
description set in XiVO |
userlabels
Defines the association between labels and users
Column |
Type |
Description |
|---|---|---|
id |
INTEGER |
id of the association |
user_id |
INTEGER |
User Id |
label_id |
INTEGER |
Label Id |
cel & queue_log
These tables are generated by Asterisk and cloned in stats repository, for more information about it see https://wiki.asterisk.org/wiki/pages/viewpage.action?pageId=5242932
Tables join
Tables call_data, call_on_queue and hold_periods can be linked together by doing a join on a column holding the call reference. The columns are the following:
Table |
Reference column |
|---|---|
call_data |
uniqueid |
call_on_queue |
callid |
hold_periods |
linkedid |
On the other hand, tables attached_data and call_element contains foreign key referencing the id column of call_data.
—
Tables call_on_queue, agentfeatures and agent_position can be linked together by doing a join on a column holding the agent number reference. The columns are the following:
Table |
Reference column |
|---|---|
call_on_queue |
agent_num |
agentfeatures |
number |
agent_position |
agent_num |
—
Tables agentgroups and agentfeatures can be linked together by doing a join on a column holding the agent group id reference. The columns are the following:
Table |
Reference column |
|---|---|
agentfeatures |
numgroup |
agentgroups |
id |
—
Tables transfers and call_data can be linked together by doing a join on a column holding the call uniqueid reference. The columns are the following:
Table |
Reference column |
|---|---|
transfers |
callidfrom / callidto |
call_data |
uniqueid |
Sample of statistic data from various call flow
This section gives an overview of what kind of data can be found in the different statistics tables when performing usual contact center call flow.
For the folowing diagrams, here the legend that would apply to understand interactions:
Single call put on hold
Let’s take the assumption that one Agent ( A1 with id 8000 and phone number 1000 ) calls an internal User ( U1 with phone number 1001 ).
This simple call flow generates in call_data 1 line (simplified table to keep only important fields):
id |
uniqueId |
dst_num |
answer_time |
end_time |
src_agent |
|---|---|---|---|---|---|
1 |
12345678.9 |
1001 |
2018-12-01 15:02:00 |
2018-12-01 15:22:00 |
8000 |
also get in hold_period 1 line:
id |
linkedId |
start |
end |
|---|---|---|---|
1 |
12345678.9 |
2018-12-01 15:13:00 |
2018-12-01 15:17:00 |
Statistics (done by pack-reporting for spago reports) generates then stat_agent_specific:
time |
agent_num |
nb_emitted_internal_calls |
conversation_time_emitted_internal_calls |
hold_time |
|---|---|---|---|---|
2018-11-29 15:15:00 |
8000 |
1 |
720 |
120 |
2018-11-29 15:30:00 |
8000 |
1 |
480 |
120 |
Single call, direct transfer then new call
Let’s take the assumption that one Agent (A1 with id 8000 and phone number 1000) calls an internal User ( U1 with phone number 1001 ). This call is transfered to Agent A2 (with id 8002 ) blindly. Then A1 calls internal another User ( U2 with phone number 1002 )
This call flow generates in call_data 2 lines (simplified table to keep only important fields) one for each call ( U1 and U2 ):
id |
uniqueId |
dst_num |
answer_time |
end_time |
src_agent |
dst_agent |
transfered |
transfer_direction |
|---|---|---|---|---|---|---|---|---|
1 |
12345678.9 |
1001 |
2018-12-01 15:02:00 |
2018-12-01 15:12:00 |
8000 |
8002 |
true |
internal |
2 |
12345679.0 |
1002 |
2018-12-01 15:13:00 |
2018-12-01 15:23:00 |
8000 |
and get in transfers 1 line (where callidto id the uniqueId of the leg of Agent A2 with user U1):
id |
callidfrom |
callidto |
|---|---|---|
1 |
12345678.9 |
98765432.1 |
Statistics (done by pack-reporting for spago reports) generates then stat_agent_specific:
time |
agent_num |
nb_emitted_internal_calls |
conversation_time_emitted_internal_calls |
hold_time |
|---|---|---|---|---|
2018-11-29 15:15:00 |
8000 |
1 |
720 |
0 |
2018-11-29 15:30:00 |
8000 |
1 |
480 |
0 |
Important
We don’t consider the time of the transfer besides initial call and only time of established calls.
Single call, attented transfer then new call
Let’s take the assumption that one Agent (A1 with id 8000 and phone number 1000) calls an internal User ( U1 with phone number 1001 ). This call is transfered to Agent A2 (with id 8002 ) after talking with him. Finally A1 calls internal another User ( U2 with phone number 1002 )
This call flow generates in call_data 3 lines (simplified table to keep only important fields) one for each call between U1 and U2 and one for A2:
id |
uniqueId |
dst_num |
answer_time |
end_time |
src_agent |
dst_agent |
transfered |
transfer_direction |
|---|---|---|---|---|---|---|---|---|
1 |
12345678.9 |
1001 |
2018-12-01 15:02:00 |
2018-12-01 15:12:00 |
8000 |
8002 |
true |
internal |
2 |
12345679.0 |
1002 |
2018-12-01 15:13:00 |
2018-12-01 15:23:00 |
8000 |
and get in transfers 1 line (where callidto id the uniqueId of the leg of Agent A2 with user U1):
id |
callidfrom |
callidto |
|---|---|---|
1 |
12345678.9 |
98765432.1 |
also get in hold_period 1 line:
id |
linkedId |
start |
end |
|---|---|---|---|
1 |
12345678.9 |
2018-12-01 15:07:00 |
2018-12-01 15:12:00 |
Statistics (done by pack-reporting for spago reports) generates then stat_agent_specific:
time |
agent_num |
nb_emitted_internal_calls |
conversation_time_emitted_internal_calls |
hold_time |
|---|---|---|---|---|
2018-11-29 15:15:00 |
8000 |
1 |
1020 |
300 |
2018-11-29 15:30:00 |
8000 |
1 |
480 |
0 |
Important
Conversation time can be over 900s (15mn) as hold time is included in this specific call flow.
Sample SQL statistic queries
This section describes some SQL query achievements done based on Database schema.
List all received agent calls
This query get the phone set number on which the agent took the call. It lists all calls answered by agent with line number on which he was logged in. The query here is limiting to all calls answered the first day of August, but it can be easily customized to your needs.
1SELECT cq.answer_time,
2 cq.hangup_time,
3 COALESCE(af.firstname, '') || ' ' || COALESCE(af.lastname, '') AS agent_name,
4 cd.src_num AS caller,
5 ap.line_number AS line_number
6FROM call_on_queue cq
7LEFT JOIN call_data cd ON cq.callid = cd.uniqueid
8INNER JOIN agentfeatures af ON cq.agent_num = af.number
9INNER JOIN agent_position ap ON cq.agent_num = ap.agent_num AND cq.answer_time between ap.start_time and ap.end_time
10AND to_char(cq.answer_time,'YYYY') = '2016'
11AND to_char(cq.answer_time,'MM') = '08'
12AND to_char(cq.answer_time,'DD') = '01'
13AND cq.agent_num IS NOT NULL;
This query will result to something like:
Answer time |
Hangup time |
Agent name |
Caller number |
Line number used |
|---|---|---|---|---|
2016-08-01 09:01:36.803 |
2016-08-01 09:02:38.916 |
Agent A |
xxxxxxxxxx |
101 |
2016-08-01 09:08:52.8 |
2016-08-01 09:09:31.97 |
Agent B |
xxxxxxxxxx |
102 |
2016-08-01 09:03:43.797 |
2016-08-01 09:07:18.452 |
Agent A |
xxxxxxxxxx |
101 |
2016-08-01 09:09:06.895 |
2016-08-01 09:09:56.549 |
Agent C |
xxxxxxxxxx |
103 |
Distribution of received call by month for a queue
This query aggregates all received call by month and by queue number.
1SELECT extract(year from cq.queue_time) as Year,
2 to_char(cq.queue_time,'Mon') as Month,
3 dst_num AS DID,
4 COUNT(CASE WHEN cq.status IN ('answered', 'abandoned', 'leaveempty', 'timeout', 'exit_with_key') OR cq.status IS NULL THEN 1 END) AS Presented,
5 COUNT(CASE WHEN cq.answer_time IS NOT NULL THEN 1 END) as Answered,
6 to_char(AVG(CASE WHEN cq.answer_time IS NOT NULL THEN cq.hangup_time - cq.answer_time END), 'HH24:MI:SS') as ACT,
7 COUNT(CASE WHEN cq.status = 'timeout' THEN 1 END) as Dissuaded,
8 COUNT(CASE WHEN cq.status = 'abandoned' THEN 1 END) as Hungup,
9 COUNT(CASE WHEN cq.status = 'closed' THEN 1 END) as Refused,
10 COUNT(CASE WHEN cq.status = 'abandoned' AND (cd.end_time - cq.queue_time) < '15 seconds'::interval THEN 1 END) as Abandoned_T1,
11 to_char(SUM(CASE WHEN cq.status = 'answered' THEN
12 EXTRACT(epoch FROM (cq.answer_time - cq.queue_time)) ELSE 0 END) /
13 NULLIF(COUNT(CASE WHEN cq.status IN ('answered', 'abandoned', 'leaveempty', 'timeout', 'exit_with_key')
14 OR cq.status IS NULL THEN 1 END),0) * INTERVAL '1 second', 'HH24:MI:SS') as AWT,
15 SUM(CASE WHEN cd.transfered THEN 1 ELSE 0 END) AS Transfered,
16 ROUND(COUNT(CASE WHEN cq.answer_time IS NOT NULL THEN 1 END)::numeric /
17 NULLIF(COUNT(CASE WHEN cq.status IN ('answered', 'abandoned', 'leaveempty', 'timeout', 'exit_with_key')
18 OR cq.status IS NULL THEN 1 END),0)::numeric * 100,2) as Accepted_ratio
19FROM call_on_queue cq
20LEFT JOIN call_data cd ON cq.callid = cd.uniqueid
21GROUP BY 1,2,3;
This query will result to something like:
Year |
Month |
DID |
Presented |
Answered |
Average Call Time |
Dissuaded |
Hangup |
Refused |
Abandonned |
Average Waiting Time |
Transfered |
Answered Rate |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
2016 |
Aug |
1101 |
2 |
2 |
00:04:49 |
0 |
0 |
0 |
0 |
00:00:03 |
0 |
100 |
2016 |
Aug |
1105 |
1 |
1 |
00:03:53 |
0 |
0 |
0 |
0 |
00:00:06 |
0 |
100 |
2016 |
Aug |
1106 |
331 |
306 |
00:03:11 |
10 |
15 |
38 |
3 |
00:00:17 |
5 |
92.45 |
2016 |
Aug |
1107 |
8 |
8 |
00:01:55 |
0 |
0 |
12 |
0 |
00:00:18 |
0 |
100 |
2016 |
Aug |
1114 |
1 |
1 |
00:04:20 |
0 |
0 |
0 |
0 |
00:00:06 |
0 |
100 |
2016 |
Aug |
1115 |
2 |
2 |
00:01:30 |
0 |
0 |
0 |
0 |
00:00:09 |
0 |
100 |
2016 |
Aug |
1118 |
53 |
49 |
00:01:20 |
1 |
3 |
2 |
3 |
00:00:17 |
1 |
92.45 |
2016 |
Aug |
1119 |
3 |
0 |
2 |
1 |
0 |
0 |
00:00:00 |
0 |
0 |
|
2016 |
Aug |
1120 |
1 |
1 |
00:00:51 |
0 |
0 |
0 |
0 |
00:00:42 |
0 |
100 |
Number of direct calls to user through its DID only
This query aggregates all received call through direct inward dial number. We are not counting here the internal calls of a user.
1SELECT
2 CASE
3 WHEN call_direction='outgoing'
4 THEN (select ext_a.typeval::integer from extensions ext_a where ext_a.type = 'user' and ext_a.exten=src_num)
5 WHEN call_direction='incoming'
6 THEN (select dl_a.actionarg1::integer from dialaction dl_a,extensions ext_b where dl_a.category = 'incall'
7 AND dl_a.action = 'user' and dl_a.categoryval = ext_b.typeval AND ext_b.type = 'incall' AND dst_num IN (ext_b.exten, '+' || ext_b.exten) )
8 ELSE 0
9 END as user_id,
10 sum(CASE WHEN call_direction= 'incoming' THEN 1 ELSE 0 END) AS nb_incoming_offered,
11 sum(CASE WHEN call_direction= 'incoming' and status = 'answer' THEN 1 ELSE 0 END) AS nb_incoming_answered,
12 sum(CASE WHEN call_direction= 'incoming' THEN 1 ELSE 0 END)-sum(CASE WHEN call_direction= 'incoming' and status = 'answer' THEN 1 ELSE 0 END) as nb_incoming_missed
13FROM
14 call_data
15GROUP BY user_id
This query will result to something like:
user_id |
nb_incoming_offered |
nb_incoming_answered |
nb_incoming_missed |
|---|---|---|---|
1 |
24 |
18 |
6 |