Howto:IQM Statistical Excel Data for Agent and Queue
From Build 80176 on this feature is no longer available.
Use CSV Data instead, see
From Build 80160 on the Excel Logging will not be longer continued. Excel, in his continuously development on different OS and versions on top of different languages and wrong user handling causes in some cases problems. For compatibility reasons the Excel logging is not switched off, but for all new installations or installations with problems in Excel logging the new format is recommended. Support and fixes will be done just on the new format.
For compatibility reasons this article is available and the relative setup in the iQM software can be activated. See Related Articles for the description of the new logging mode or follwo this link
This information applies to
iQM version 2.00 RC10 or higher
Excel Office 2007 or higher (since build 80021)
iQM can record statistical data for each Agent and general statistical data of the Waiting Queue. The Feature can be switched on or off. Once activated the data will be transmitted automatically each month or, if the customer prefer, each day (at midnight). Calls going “over “midnight (beginning one day and terminating another) will be spitted regarding the duration while in the number of calls will be a count in each day.
iQM 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:
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:
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.
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).
Note: The Source Excel files containing the statistical data are transmitted via email to the user. Direct access to those files can cause different problems, up to a loss of data. Do never access directly to the statistic Excel files, a least make a copy of them and open the copy. So do not open the source files. The basic problem is that an opened Excel file cannot be modified, so if iQM shall access to store data errors will follow. Remember that iQM is working in real time mode and cannot simply “wait” storing or loading data.
iQM send Excel files containing the statistical data. A file contains always data of one month, so 12 files a year for Agents and 12 files a year for the Queue are created. The format of those files is a standard MS-Office Excel. This files are created inside of the iQM software and includes also formula. Different target languages in formulas are supported.
Each Excel file contains a top page and one sheet for each day of the month (so from day 1 up to 28 - 31). The top page (first page) will show the sum of the month, so off all daily pages.
The Agent statistical files are named "iQM_AGNT_yyyy_mm.xlsx" where yyyy is the year and mm the month.
Example: "iQM_AGNT_2012_08.xlsx" contains the Agent statistical data of the month August 2012.
The object of the email will be “IQM-Report Statistic Agents”, the Text “Automatic generated report - Do NOT answer!” while the Excel File is attached.
The Queue statistical files are named "iQM_STAT_yyyy_mm.xlsx" where yyyy is the year and mm the month.
Example: "iQM_STAT_2012_08.xlsx" contains the statistical Queue data of August 2012.
The object of the email will be “IQM-Report Statistic WQ”, the Text “Automatic generated report - Do NOT answer!” while the Excel File is attached.
The received Excel Files can be manipulated as you like, so feel free to add formulas, modify layout, add graphics etc.
Please note that if a daily transmitting is requested during the month always the same file will be transmitted, of cause with each day more and more data in. If you open the file the sheet of the actual (or past) day will be selected automatically.
Observe that the name of the first sheet indicate Year and Month, in the example the July 2012 while the other sheets are the days (1-28, 1 to 29, 1 to 30 or 1 to 31).
Contend of all sheet is identically, the only different of the first page is that there are no data in but just formulas (showing summarize of all sheets).
Detail information statistical Agent Excel data
The excel file contains operating data of each Agent. The following article explains how it works and the meaning of the singe cells. In first page is indicated the date of creation of the File/Data Sheet (cell A1) and the date until there are data in (cell B1):
In the example the file was created the first of June and contains data until the 31, so data of the full month. This is typical for automatic generated files while a manual dump will show the date when the request was done. If you start up a new iQM during the month and the file is created that data will appear in A1.
A Excel day sheet contains data for each Agent in one line.
Line 1 to 3 are reserved for description of the columns, line 4 display the total of each column, form line 5 on the Agent data are displayed.
The following list explains the singe columns:
A: Name of the Agent
B: Number of incoming calls
C: Number of outgoing calls
D: Total Number of calls (B+C)
E: Total Ringing time of the Agent set in seconds
F: Number of abandoned Ring. Please note that this value has nothing to do with the abandoned calls indicated in the iQM. Abandoned Rings are calls yet delivered to the Agent (his set is ringing) but he did not answer.
G: Average Time to answer = Ringing time / number of incoming calls (E/B)
H: Duration in minutes of incoming calls (duration under 0.5 minutes are indicated as “0”, but if you select the cell you can see the duration)
I: Duration in minutes of outgoing calls
J: Total Agent speaking time (H+I)
K: Average incoming conversation time (H/B) in seconds
L: Average outgoing conversation time (H/B) in seconds
M: Average conversation time (D/J) in seconds
N: Time in wrap up status (in seconds)
O: Time logged in minutes. Note that this time excludes the wrap up time. So the overall time while an Agent was working is N+O.
Not shown in the example is column “Q”, showing the quantity of ringing threshold overflow.
The ring threshold in seconds is set in the setup of the iQM server (Tab “Agent Excel Logging”, filed “Ring TH”).
Each time a call rings more than xx seconds this counter is increased. The time to answer is an average counter over all calls while this feature shows single long unattended calls.
Each time you stop correctly iQM all the statistical data will be saved and loaded again after start up. Just data during down-time are going lost.
Agents are displayed in an alphabetical order in the Excel sheet. But if during the month you add a new Agent and restart iQM his data will be shown in the last line. In column P (new) the creation date is indicated:
Of cause if you compare total data of that agent with his colleagues he will have probably a minor amount of processed calls etc.
In the following month he will appear again in the alphabetical list like the other Agents.
Detail information statistical Queue data
The excel Queue file contains the operating data of the Waiting queue in 15 minute time frames.
Those data typically are used to plan resources. It could be interesting for example how many calls comes in during the morning and when. If for example in the time span from 7:00 to 8:00 300 calls comes in, it could be that from 7:00 to 7:30 just 20 calls arrive, while from 7:30 to 8:00 the rest needs to be served. So for reaching a good performance value it could be a good idea increase the number of agents from 7:30 on. While it would be wasted activate additional agent from 7:00 to 7:30. For that reason 15 Minutes timeframe in contact center are common time span for statistics.
In the main window of the iQM server the 15 minute time span counters are displayed.
The time span indicator shows the 15 minute interval, so for example from 10:00 to 11:00 it will be displayed 10:00, 10:15, 10:30 and 10:45. When a time interval ends the counters are stored in the Excel file and reset to zero.
14:30 means that the counter shows data are from 14:30 on.
The numbers of incoming calls are the calls coming in the WQ while the numbers of outgoing calls are calls done by the Agents. If a call came to an Agent, he answer and transfer this call, it is counted as a single incoming call (and not as an incoming + outgoing call).
So while calls (in/out/abandoned) are simple items, the number of logged Agents is not so simple to calculate and should be explained. Of cause also an Agent is in or out, but during the time period this value can change. If for example for 2 minutes 10 Agents are logged, then for 8 minutes just 1, than again 3 etc. an average has to be considered. To solve this problem iQM is doing a scan each second how many Agents are logged, and calculate the average. Where “1.49” is displayed “1” and “1.51” as “2”. Please note that this calculation is not done in the Total row of the single day.
The following example shows the first page of the Excel statistical queue data. (HF6 Build 8015701)
The following list explains the singe columns:
A: Time period
B: Number of incoming calls
C: Number of transferred calls. Transferred calls are calls from the WQ answered by an Agent but then not simply terminated but transferred to another extension. The transferred calls are a subset of the incoming calls. In the example there where 7 incoming calls, 2 of them where transferred.
D: Total calls are the sum of the incoming calls + the transferred calls. Please note that in the example where just 7 calls coming in, but two of them causes an additional call or workload. Please note also that the peak value for transferred calls is not calculated.
E: Number of abandoned calls
F: Percentage of served calls (E/B)
G: Number of Logged Agents
H: Number necessary only for doing correct calculation
I: Served calls is the opposite value of the abandoned calls, so I = 100%-F
J: Average waiting time in seconds
K: Peak waiting time in second
L: Threshold waiting overflow in events. In the setup of the iQM Server a threshold time in seconds can be defined. If a call waits longer than this value, this counter will be increased.
3: line peak: peak value (except for transferred calls and valid periods(n.a.)
Note: While individual Agent data are stored and reloaded in case of shut done, the Queue statistica data of the current 15 minute time frame are not stored nor loaded in start-up. Therefore if you stop the iQM server the data of the 15 minute period are lost.
Note: In the setup of the iQM Server can be defined a threshold in seconds defining “short calls”. Short Calls are not counted as abandoned calls at all (not just in this WQ statistic but also in all other circumstances). “Wrong” calls or calls that anyway could not be served can be excluded using this feature.
To switch on the feature just enable in the setup in the Panel “AGENT LOGGING” the option “Logging ON” and, if required also the option “Send Agent Data each day””. Of cause a valid e-mail account must also be indicated in the Panel “DATA LOGGING”.
Please note that if excel is not present on the iQM server PC the relative setup section is disabled and a warning message is displayed:
Note: for this statistic feature a constant and correct date and time on the iQM server PC is essential. Changing Data can cause incorrect data or loss of data.
Doesn't work with versions previous to Excel 2007.