Problem: Call center agents and managers need to track call volume and talk
times. The phone system runs on a TeleVantage server, but the company has
insufficient licenses for everyone to run a copy on their workstation.
Solution: Make a webpage that shows the log.
Notes: The TeleVantage client software can run reports for "queue calls" but
it has no dedicated report for calls made directly to agents, called "DID" (Direct
Inbound Dialed) calls. The TeleVantage client software can also pull all of the raw call data, but this
report is not very useful unless you're looking for something specific, as it
contains many thousands of calls per day. These report all export to MS Excel.
The following system was set up on an unused
workstation in the server room.
- Excel: Using Excel, I created a new file that analyses the call log data on
the raw data report from TeleVantage. This Excel file searches the raw data for
any calls made to or placed by each call center agent and records the number of
calls and talk time for several different categories of call types. Calls made
from desk to desk or to voice mail are specifically excluded using Excel
- Save to Website: I created a shared folder
on the company intranet site so that the Excel file could be saved directly
onto the live site as an HTML file.
- Graphic Chart: I added a chart that visually
represents the pertinent data. It is exported separately as a .gif file,
which is a much higher quality image than the result of an image created by
the "Save As HTML" feature.
- Macro: All of the actions necessary to pull
the raw data and export the filtered data table and chart graphic were
scripted into a macro that is on an infinite loop.
- Combined Page: A PHP page containing both
the filtered data table and the chart graphic contains an HTML auto-refresh
so the page will refresh itself whenever it is left open.
- iFrame: Another page contains an iFrame that
points to the previously described combined page. Without this container,
the combined page would reposition itself scrolled all of the way up every
time the auto-refresh runs. With the iFrame, the page reloads while
retaining the current scroll position.
- Monitoring Script: Once in a while the Excel
file will jam while trying to access the shared folder. The simplest way to
get it going again is to close Excel and reopen it. (On Open macros start
all of the steps of the main loop). An AutoIT script monitors the
workstation and resets Excel whenever it jams
- Cron job: In addition to the AutoIT script,
a PHP cron script monitors the age of the live files. If they are more than
a few minutes old, the file is probably not running correctly and needs
attention. The cron script would then email me an alert. It sets a
"breadcrumb" to ensure that only one notification email is sent. The On Open
macro resets this breadcrumb.
Click the here for a
sample what the page looked like. Notice it refreshes while maintaining the