Howto:IQM Statistical CSV Data for Agent and Queue: Difference between revisions

From innovaphone wiki
Jump to navigation Jump to search
No edit summary
Line 232: Line 232:
(2)    If an Agent does not terminate the call but is doing a call transfer this counter is incremented.
(2)    If an Agent does not terminate the call but is doing a call transfer this counter is incremented.


(3)    Active calls are calls done from the agent.
(3)    Active calls are done from the agent.


===Known Problems===
===Known Problems===

Revision as of 06:18, 27 September 2016

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 not that MS has timeouts in all applications and therefore it could require some minute 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:

IQMcsv02.png


A: Day of month

B: Month

C: Name of Agent

D: Number of incoming calls (1) (2)

E: Number of outgoing calls

F: Time to answer in seconds (1) (3)

G: Number of abandoned calls (4)

H: Conversation time in seconds of incoming calls (1) (2)

I: Conversation time in seconds of outgoing calls

J: Wrap up Time in seconds

K: Time in seconds being in Group

L: Number of threshold overflow (5)


Notes:

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

(2) Numbers in a line regards one single agent: a call is counted as such if the phone of the agent rings: not if a call is in the queue.

(3) The Time to answer in this table is the time span from ringing of the phone to the moment when the Agents answers the calls or the caller gives up. Not counted are calls giving up during waiting in the WQ while the phone of the agent is not jet ringing.

(4) Abandoned calls are calls not answered by the agent phone. 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 this is not counted. Therefore the abandoned calls counter in the iQM Server cannot be compared with this counter.

(5) Each time a calls waits longer than the seconds indicated in the iQM Setup “Ring TH” (the agent phone is ringing longer than) this counter is increased.

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.

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 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:


IQMcsv03.png


A: Day of month

B: Month

C: Hour

D: Minute

E: Number of incoming calls

F: Number of abandoned calls

G: Percentage of served calls

H: Percentage of abandoned calls

I: Waiting time in seconds

J: Number of agents logged in the group

K: Number of transferred calls (2)

L: Peak call duration in seconds

M: Average duration of calls in seconds

N: Number of threshold overflow (1)

From Build 80178:

O: Number of active calls done from the agents


Notes:

(1) Each time a calls waits longer than the seconds indicated in the iQM Setup “Waiting TH” this counter is increased.

(2) If an Agent does not terminate the call but is doing a call transfer this counter is incremented.

(3) Active calls are done from the agent.

Known Problems

Related Articles

Howto:IQM_Statistical_Excel_Data_for_Agent_and_Queue

Howto:Queue_Monitor_-_Overview