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

../../_images/xivocc_reporting_architecture.png

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:

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

../../_images/xivocc_reporting_db_schema.png

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:

../../_images/xivocc_reporting_call_legend.png

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

../../_images/xivocc_reporting_call_hold.png

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 )

../../_images/xivocc_reporting_call_direct_transfer.png

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 )

../../_images/xivocc_reporting_call_attented_transfer.png

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