Howto:IQM Statistical CSV Data for Agent and Queue

From innovaphone wiki
Revision as of 13:41, 2 February 2022 by Kwa (talk | contribs) (→‎Related Articles)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

In previous releases (before build 80160) the iQM server did a data logging using Excel sheets. While this method has the great advantage that the output is a ready to use Excel Chart including formulas, front pages and different sheets there are also some limitations. For example it is very difficult support all kind of OS and all kind of Excel and Office Versions on the iQM Server while the real typically is done on “normal” PC´s where Excel is installed.

Therefore the Data logging is done writing data in a CSV format. This format can be natively loaded from Excel. The difference is that there are no single sheets for days, but all data are of a month in one single Excel Sheet. Also Formulas or filter if required can be added, but there are not included in the source file.

Applies To

This information applies to

iQM Build 80160 or higher

Excel is not required on the iQM Server


More information

QM provide two type of statistical data, data regarding individual Agents and data regarding the general status of the WQ. The usage is different: while individual Agent data are used to evaluate the operating of the single Agents, the WQ data are used for resource planning. See more details and explanation in the following chapters. Statistical data are transmitted via Email to the supervisor automatically (daily or monthly). The can also be requested manually pressing the relative button on the iQM server:

IQM214.png

If requested on demand the actual data of today of cause will be just from midnight until the moment of the request. During transmission the relative buttons will shown:

IQM215.png

Please note that MS has timeouts in all applications and therefore it could take some time until you will find the statistic in your mailbox. Note: if in the setup a valid IP-Adress is indicated in the e-mail server (iQM does no control if not) iQM performs a ping check before processing the email. If like in the example the Ping result is negative warning message is displayed, each 20 seconds are done further checks.

IQM209.png

The e-mails are buffered (max. 6), also in case of other email error a relative warning message is displayed (and stored in the log file). Statistical data can also be transmitted automatically each day at midnight and at the end of the month. Please note that a IQM Server tracks always data form “his” primary WQ while the data of the second WQ and processed by the second iQM Server.

Files

Each time a file is send, regardless if automatically or requested by a user, a copy of the original file is stored in a directory created automatically by the iQM server. The directory named “CSV” is a sub-directory where the log files are in.

Example: if Your logfile path is “c:\asdf\“ the CSV data are stored in “c:\asdf\CSV”

Please note that a copy of files is done even if no email address is indicated.

Therefore also a logging without email is possible, just working with files.

As usual it is recommended to work only with the file in the CSV subdirectory and not with the original files in the log directory.

The files stored in the CSV subdirectory and received via email can be opened with any software, in the following examples we open with Excel for further processing.

Please note that the csv files in the logging directory should not be opened and they have a write protection switched on while the files in the CSV subdirectory and the one attached to the emails have no write protection.

Once opened the file could be processed, modified, stored and renamed as you like, see later chapters.

File Names

The filename of automatic generated files (typically at the beginning of the month) is the following:

The CSV filename has the format “iQM_SS_L_YYYY_MM_DD_HHMMSS.csv” where:

SS = source (“AG” for agent and “WQ” for Waiting queue”)

L = Labe of the iQM sever

YYYY = Year

MM = month

DD = Day

HHMMSS = hour, minute, second

If a file is send automatically by email the file name of the attached file is “iQM_SS_ll_YYYY_MM.csv”, so no day and timestamp is in the filename.

Examples:

“iQM_AG_MyFinanceGroup_2016_04_18_152709.csv” filename of the file stored in the CSV subdirectory at time 15:27:09.

“iQM_AG_MyFinanceGroup_2016_04.csv” automatic created file attached to the email

“iQM_AG__2016_04_000009.csv” filename of the file stored in the CSV subdirectory. Note that there is no label in this example and the 00:00:09 indicated that this file was stored automatically at midnight at the end of the month.

e-mail format

The email shows in the object field the type and source of the Statistic in the format iQM-Report, “AG” for the Agent Statistics and “WQ” for the Waiting queue Statistic. On top the name of the iQM server is in the object, usefully if more iQM are operating.

Example of email Subject:

“iQM-Report Statistic WQ MyFinanceGroup” data of the Waiting Queue of the iQM Server “MyFinanceGroup”

iQM-Report Statistic AG MyFinanceGroup” data of the Agent of the iQM Server “MyFinanceGroup”

The e-mail body shows the source path of the attached file and name of the attached file.

Example for email body:

C:\asdf\iQM_AG_Myxlabel_2016_04.csv Automatic generated report - Do NOT answer!

The CSV Data Files are attached in the mail.

Manual data dump

If a statistic File is dumped (requested) manually the data are partial. If for example Agent data are requested in day 6.12 at 14:00, data will be complete for the days 1-6, but data of the day 6 are just logged from 00:00 to 14:00. Similar happens to the queue data: if a request is done at 12:35 the quarterly hour from 12:30 to 12:45 will contain partial data.

Note: In agent data, where for each day a line for each agent is stored, a manual dump cause double lines in the day where the system starts up the very first time. On further days this will not happen.

Example:

“iQM_AG_MyFinanceGroup_2016_04_18_152709.csv” indicate that the request was done day 18, hour 15:27:09

In the Queue Table the minute of the snapshot is indicated:

IQMcsv01.png


In the example the data dump was done at 15:33 and therefore line 4 contains partial data.

Excel Import

The CSV Data can be imported in Excel, the CSV character is semi column (“;”).

Agent Data

The headline (localized, in all examples in English) indicates the following columns:

IQM N 14.png


A: Day of month

B: Month

C: Name of Agent. Numbers in a line regards this single agent: a call is counted as such if the phone of the agent rings, not if a call is in the queue.

D: Number of incoming calls. If in the setup of the iQM server is flagged “log only calls from WQ” just incoming calls form the Waiting Queue are counted: if not checked all calls (even if not from the Waiting Queue) are counted.

E: Number of outgoing calls

F: Time in seconds of the phone in ringing status. You can calculate the average time of answer for this agent divide this value with the number of calls in (F/D). If in the setup of the iQM server is flagged “log only calls from WQ” just incoming calls form the Waiting Queue are counted: if not checked all calls (even if not from the Waiting Queue) are counted.

G: Number of abandoned calls = calls not answered while the agent phone rings. This means that the phone has to ring and the agent did not answer. If a caller gives up during the queuing in the Waiting queue (ando no Phone is ringing) this is not counted. Note that the abandoned calls counter in the iQM Server (that count abandoned calls in any situation) cannot be compared with this counter.

H: Conversation time in seconds for incoming calls.If in the setup of the iQM server is flagged “log only calls from WQ” just incoming calls form the Waiting Queue are counted: if not checked all calls (even if not from the Waiting Queue) are counted.

I: Conversation time in seconds of outgoing calls. Outgoing calls are all calls done in a active mode (so even internal active calls)

J: Wrap up Time in seconds. Shows how long a Agent was in the wrap up status.

K: Time in seconds being in Group, shows how long a Agent was logged in the group.

L: Number of threshold overflow. Each time the phone rings longer than the seconds indicated in the iQM Setup “Ring TH” (the agent phone is ringing longer than) this counter is increased.


Description Example (assuming that in the setup “only calls from Waiting Queue” is flagged).

The Phone of Agent Valentina (line 3) in day 19 October (the year is indicated in the filename) rings 14 times for calls coming from the WQ, 4 times longer than 10 Seconds before answering the call. 3 calls where not answered unless the phone rings, the total time of phone ringing was 72 seconds, the conversation time for the incoming calls 154 seconds. The Agent was 70 seconds in wrap up and logged in the group 11884 seconds. Valentina did 1 outgoing call speaking 36 seconds.


Please note that the format of the counters is generally well done, for example the value “month” is in two digit format (in the example “04”): but the view in Excel depends on the setup of your Excel sheet.

Loggin rules

The following rules regard the iQM statistics for second’s calls. A “second call” is a call done from agent while the first call is on hold. A typical situation is a consultation with another extension.

Please note that an outgoing call is a call from the agent to a destination, regardless if the called number is, from the PBX point of view, an extension or an external destination.

Observe also that the iQM is not a reporting and the following described rules can cause different counter status if compared. While a reporting is simple tracing each call as an independent event the iQM statistics follow a callcenter logic. If the independent call counting is requested simple use the reporting tool.

1. incoming call

When the incoming call comes in the queue statistic incoming call counter is increased, if the agent answers the agent incoming call counter; a timestamp for call duration is saved.

If the agent put this first call on hold and is doing an outgoing call the agent client will show the second number while a timestamp for the second call is saved. The agent talks with the second destination and transfers the call. In the Queue Statistic the call transfer counter is increased. In the agent statistic the outgoing call counter is not increased, the entire call duration is booked on the incoming call duration timer.

The same behaviour is given if the call is transferred without consultation (the agent just transfer the call without waiting that the far destination answers the call). The wrap up will trigger when the call is transferred.

If the agent transfers the call to the Waiting Queue the call duration will continue to count on that agent until the call is answered. Please note that the client will show the pending call. If group wrap up is on the agent will not be logged in the group until the call is answered or the agent picks up the phone, then wrap up will trigger. If queue wrap up in switched on wrap up will trigger, after the wrap up the call is offered again to the agent.

A second option is that the agent releases the second call and speaks again with the first original incoming call. The outgoing call counter is increased when the agent releases the second call and the outgoing call duration is increased using the timestamp of the second call. Please note that the client will show the second number until the call is terminated. Wrap up will trigger at the end of the call.

A third option is that the agent toggles between the two calls and release the first (the incoming) call.

The wrap up will just trigger when the second call is released. The outgoing call counter is not increased; the entire call duration is assigned to the incoming call. If the first incoming caller releases while the agent is in conversation with the second outgoing call the entire call is considered as incoming, the total time amount is booked on the incoming call and the outgoing call counter is not increased.

2. Outgoing call All operation described before are possible also doing as first call an outgoing call, then put that call on hold and do a second outgoing call. Outgoing calls without involvement of the waiting queue will not cause any wrap up.

In case of an Call transfer there is just one call add in the statistic, the entire call duration is add to the outgoing call duration.

If the second call is released the outgoing counter is increased, so when the agent released two outgoing calls are counted. The entire call duration is added.

If the agent toggles between the calls the display on the client will not follow but showing always the second call. If the agent releases the first call the counter of outgoing call will not be increased while the entire call duration is added to the outgoing call duration timer.

If the first called user releases while the agent is in conversation with the second outgoing call the entire call is considered as one single outgoing call, the total time amount is booked on the outgoing time counter.

3. Short Calls

In the iQM Setup it is possible to set a minimum duration time in seconds. Calls shorter that the seconds defines are not logged in the missed call list of the agent. Calls also not are considered as valid calls for the statistic. Please note that short calls are not counted in the statistics if:

- The ringing time is shorter the the threshold and the agent will not answer.

- The ringing time is shorter that e threshold, the agent answer the call but the conversation time is shorter than the threshold. This is valid independently if the caller or the agent releases the call.

Please note that the reporting tool will not track very short calls (<=1 second) while a threshold in the iQM could cause that the reporting reports the call while the iQM will not.

Please note also that the counter of the waiting queue are not affected by this filter option, therefore the queue counter will count all calls, even the short one.


Formulas

Being native Excel sheets any type of formulas (for example sums) can be added and also Filter can be applied with a few klick (marking all and adding “Filter”).

In the Excel format there are some columns not available automatically in the CSV logging, but it is easy to add a column with the relative formulas.

Here some example for additional formulas:

Time in Minutes = time in Seconds / 60

Average Time to Answer = F: Time to answer in seconds / D: Number of incoming calls

Average duration of incoming calls = H: Conversation time in seconds of incoming calls / D: Number of incoming calls

Average duration of outgoing calls = I: Conversation time in seconds of outgoing calls / E: Number of outgoing calls

Average duration of calls = (H: Conversation time in seconds of incoming calls + I: Conversation time in seconds of outgoing calls) / ( D: Number of incoming calls + E: Number of outgoing calls)

Queue Data

Queue Data are data regarding the waiting Queue and the group of agents, not single agents.For example there is no difference for a call being queued or ringing on a phone.

Queue data are stored in 15 Minutes intervals. Therefore each hour has 4 entries: 00, 15, 30 and 45. Please note that always the past quarter is indicated. For example in “10:00” are data from 09:45 to 10:00, in “10:15” are in data from “10:00” to “10:15”.

If a queue report is requested manually an odd timestamp will appear (for example “10:04”) indicating the snapshot time for the current quarter.

The headline (localized, in the example in English) indicates the following columns:


IQM N 15.png


A: Day of month

B: Month

C: Hour

D: Minute

E: Number of incoming calls on the Waiting Queue

F: Number of abandoned calls (in any situation)

G: Percentage of served calls (calls answered)

H: Percentage of abandoned calls (calls not answered)

I: Waiting time in seconds = total waiting time of calls in the waiting queue

J: Number of agents logged in the group (Agent Group)

K: Number of transferred calls: If an Agent does not terminate the call but is doing a call transfer this counter is incremented.

L: Peak call duration in seconds = longest waiting time

M: Average Waiting time of calls in seconds (I/E)

N: Number of threshold overflow. Each time a calls waits longer than the seconds indicated in the iQM Setup “Waiting TH” this counter is increased.

O: Number of active calls done from the agents (Build 80178)


In the time frame from 16:30 to 16:45 the 19 October (the year is indicated in the filename) 10 calls came in to the Waiting Queue, 2 calls where not served (abandoned), so 80% of the calls are served while 20% not. The total waiting time of those 10 calls was 167 seconds, at 16:30 there where 2 Agents logged into the group. 1 call was transferred to other destinations while 9 calls served from the Agent. The Peak Waiting time in this period was 36 seconds, the average waiting time 17 seconds. One time a call waits longer than 30 seconds. The Agents did 2 outgoing calls in this time frame.

Please note that all columns refer to the waiting queue except column O: this column shows the number of outgoing calls of the agents during the quarter hour timeframe. This value is interesting to evaluate the activity of the call enter during the timeframe.


Known Problems

Some customer wants additional columns (for example adding two cells or doing other type of mathematical calculation), hiding columns, modify header, using filter and similar. In Excel all that is very simple and can be done in an automatic mode with one click. This Wiki is not a Excel tutorial, anyway some hint you can find here:

Howto:IQM_Server#Data_processing


Related Articles

Reference9:IQM_Statistic_Help_English

Howto:IQM_Statistical_Excel_Data_for_Agent_and_Queue

Howto:Queue_Monitor_-_Overview