0
The Most Fun I Had Hacking...
16 May 2011
In a job I had a long time ago in a galaxy far, far away, I was a queue manager for a large call center. My job was to make sure that calls from customers got answered in as quick a timeframe as possible with the most efficient use of people as possible.
This meant I had several metrics I had to work with, but the two big ones were:
1. Service level: at least 80% of the incoming calls had to be answered in 20 seconds or less
2. Productivity: The amount of time agents were not taking calls for the entire day had to be under 20% (so, 80% productivity. Agents waiting for a phone call=the company not making money.)
To meet these objectives I was given 3 suites of tools: Microsoft Office, Aspect Eworkforce Management, and Avaya CMS Supervisor 13.
Now, when I am given a task, I follow my dad's old adage: "Son, when you decide to do this, you have two choices: You can choose to try and succeed, or you can plan things out so you cannot fail. If you choose the first option, you always admit the possibility of failure."
Well, I do not like failure, so I started researching the tools I had.
Everyone is familiar with MS Office. I was using the 2003 version for this job, and I would send spreadsheets to everyone in the admin group every hour to keep them appraised of our metrics and any tasks I required of them to meet our goals (example, if we had low productivity due to high staffing and/or low call volume, I'd tell them to get volunteers to head home early. In an office of 500 people, this wasn't hard.
)
Well, to send this hourly report out I had to pull a historical report from CMS Supervisor and paste the queue data for each queue I was watching into separate tabs.
So 1PM, open spreadsheet, open CMS Supervisor report. copy, alt-tab, paste, move to next tab, alt-tab, repeat 20 times.
Riveting.
This meant I was spending 10 minutes of each hour of each day copying/pasting into a spreadsheet rather than working on average.
Well, I figured I would see just how fast I could type that above routine on my dinosaur of a computer I was given to work on.
In a couple months after getting used to the reports, I was the fastest person at that job, getting reports in at 3 minutes after the hour, with a personal record of pasting all that data in within one minute and emailed out.
Well, I just gained 9 more minutes an hour, and I sure wasn't going to waste that. With that spare hour a day I discovered avayausers.com, a forum where like-minded people discuss how to better their avaya knowledge and usage.
And I discovered that Avaya CMS supervisor is merely a VB frontend for an SQL link to the avaya servers.
Now at this point, I didn't know VB, and I didn't know SQL. But one thing I learned incredibly well over the years is how to ask questions, and how to use google.
I started playing with VBA in excel to modify data. I did this by turning on the macro recorder, doing stuff, then turning it off to look at the code the recorder generated. I started looking at code snippets on avayausers for examples of automation.
In the end, I got to the point where at the top of the hour, CMS Supervisor would run all my historical reports and dump them to CSV. Then excel would start up and pull the data from those csv files into the spreadsheet. Then the spreadsheet would save itself, email itself, and close. No human interaction required. Strange as it seems, that little act of regaining 1 hour of each day for myself made me feel somewhat like God.
By then the scripting bug had well and truly bitten me. I felt that my entire job, so very metric based, didn't really require me, or the several other people in the department. It was math, pure and simple, and the only human component truly required was to enforce that people actually checked the numbers and the directives we sent out.
And so I started playing with forecasting data for queues. I overheard one of the scheduling analysts use the term "Erlang," and when I asked for details got very little. It didn't matter to me. I have internet access, and I know how to use it! So I gave myself a crash course in queue theory based on wikipedia. Then I threw myself into learning Eworkforce, our scheduling/forecasting program.
After getting through the ins and outs of that program, I decided it was functioning properly, yet some days our forecasts were off, and the actual numbers would be in serious non-agreement with what the scheduling team thought we needed.
Well, my hourly spreadsheet had a % of forecast column, so I added another tab in the spreadsheet that would take the forecasted hard data, compare it to the % of forecast data for the day each hour, and use that to create a revised forecast. That revised forecast was simply the forecasted data number times the percent of forecast. I added another tab to calculate productivity. I kept adding more and more functions...
Soon, this one little spreadsheet that could was capable of doing the work of the entire department. It talked to text files, it talked to Avaya, it talked to Outlook, it talked to everything except Eworkforce, only because Eworkforce had so little publicly available documentation I couldn't figure out how to safely interface with it, and trial/error on a production server of that magnitude wasn't a good idea in my mind.
When I left that job, the VBA code page in the spreadsheet was 19 pages long. I was given Avaya Report Designer access and I could build custom queries for whatever data I wanted. I am now fully aware that anything that needs to be done in a major office numberwise could be done in Excel to resolve anything and everything.
Then I discovered that SQL servers need not be proprietary like the one on the Avaya server. And I realized as I started building my first SQL database at home...
I thought I knew so much, manipulating the systems and data to my needs at work... Yet what I had done... had barely scratched the surface of what these machines we use daily are capable of.
It was a humbling feeling.
This meant I had several metrics I had to work with, but the two big ones were:
1. Service level: at least 80% of the incoming calls had to be answered in 20 seconds or less
2. Productivity: The amount of time agents were not taking calls for the entire day had to be under 20% (so, 80% productivity. Agents waiting for a phone call=the company not making money.)
To meet these objectives I was given 3 suites of tools: Microsoft Office, Aspect Eworkforce Management, and Avaya CMS Supervisor 13.
Now, when I am given a task, I follow my dad's old adage: "Son, when you decide to do this, you have two choices: You can choose to try and succeed, or you can plan things out so you cannot fail. If you choose the first option, you always admit the possibility of failure."
Well, I do not like failure, so I started researching the tools I had.
Everyone is familiar with MS Office. I was using the 2003 version for this job, and I would send spreadsheets to everyone in the admin group every hour to keep them appraised of our metrics and any tasks I required of them to meet our goals (example, if we had low productivity due to high staffing and/or low call volume, I'd tell them to get volunteers to head home early. In an office of 500 people, this wasn't hard.
)Well, to send this hourly report out I had to pull a historical report from CMS Supervisor and paste the queue data for each queue I was watching into separate tabs.
So 1PM, open spreadsheet, open CMS Supervisor report. copy, alt-tab, paste, move to next tab, alt-tab, repeat 20 times.
Riveting.
This meant I was spending 10 minutes of each hour of each day copying/pasting into a spreadsheet rather than working on average.
Well, I figured I would see just how fast I could type that above routine on my dinosaur of a computer I was given to work on.
In a couple months after getting used to the reports, I was the fastest person at that job, getting reports in at 3 minutes after the hour, with a personal record of pasting all that data in within one minute and emailed out.
Well, I just gained 9 more minutes an hour, and I sure wasn't going to waste that. With that spare hour a day I discovered avayausers.com, a forum where like-minded people discuss how to better their avaya knowledge and usage.
And I discovered that Avaya CMS supervisor is merely a VB frontend for an SQL link to the avaya servers.
Now at this point, I didn't know VB, and I didn't know SQL. But one thing I learned incredibly well over the years is how to ask questions, and how to use google.
I started playing with VBA in excel to modify data. I did this by turning on the macro recorder, doing stuff, then turning it off to look at the code the recorder generated. I started looking at code snippets on avayausers for examples of automation.
In the end, I got to the point where at the top of the hour, CMS Supervisor would run all my historical reports and dump them to CSV. Then excel would start up and pull the data from those csv files into the spreadsheet. Then the spreadsheet would save itself, email itself, and close. No human interaction required. Strange as it seems, that little act of regaining 1 hour of each day for myself made me feel somewhat like God.
By then the scripting bug had well and truly bitten me. I felt that my entire job, so very metric based, didn't really require me, or the several other people in the department. It was math, pure and simple, and the only human component truly required was to enforce that people actually checked the numbers and the directives we sent out.
And so I started playing with forecasting data for queues. I overheard one of the scheduling analysts use the term "Erlang," and when I asked for details got very little. It didn't matter to me. I have internet access, and I know how to use it! So I gave myself a crash course in queue theory based on wikipedia. Then I threw myself into learning Eworkforce, our scheduling/forecasting program.
After getting through the ins and outs of that program, I decided it was functioning properly, yet some days our forecasts were off, and the actual numbers would be in serious non-agreement with what the scheduling team thought we needed.
Well, my hourly spreadsheet had a % of forecast column, so I added another tab in the spreadsheet that would take the forecasted hard data, compare it to the % of forecast data for the day each hour, and use that to create a revised forecast. That revised forecast was simply the forecasted data number times the percent of forecast. I added another tab to calculate productivity. I kept adding more and more functions...
Soon, this one little spreadsheet that could was capable of doing the work of the entire department. It talked to text files, it talked to Avaya, it talked to Outlook, it talked to everything except Eworkforce, only because Eworkforce had so little publicly available documentation I couldn't figure out how to safely interface with it, and trial/error on a production server of that magnitude wasn't a good idea in my mind.
When I left that job, the VBA code page in the spreadsheet was 19 pages long. I was given Avaya Report Designer access and I could build custom queries for whatever data I wanted. I am now fully aware that anything that needs to be done in a major office numberwise could be done in Excel to resolve anything and everything.
Then I discovered that SQL servers need not be proprietary like the one on the Avaya server. And I realized as I started building my first SQL database at home...
I thought I knew so much, manipulating the systems and data to my needs at work... Yet what I had done... had barely scratched the surface of what these machines we use daily are capable of.
It was a humbling feeling.
