Call Center Report Modifier

            For a few months I did computer support for the Global Service Desk (GSD). This was basically a call center for the Church of Jesus Christ of Latter Day Saints. The call center took a number of first and second level support calls from church employees, and others associated with the Church. While employed, the structure of the call center was examined, and changed to better meet demand. The call center went from having 3 main groups, general first level support, general second level support, and account management specific second level support, to having multiple focus groups, to better specialize employees and expedite call turnovers. It turned into a great idea. Second level support became a function of first level support, as these tasks became more integrated, and a simple phone tree allowed users calling in to be directed to the team that would best be able to resolve their issue. While this did improve efficiency, the company lost its initial ability to track phone calls. The company purchased software that would provide a report with details of the number of calls that were presented, the number of calls that were handled, the number abandoned, the number dequeued, as well as other data relating to these statistics. The problem with the report is that, in its raw form, it was unusable, which introduces the need for my project. Basically, my program takes the raw data from the report, and breaks it down into a format that allows you to get value out of the report, and in turn, is a very valuable tool for the GSD.

            The report that is run is basically a data dump. It returns a number of sheets using the maximum amount of rows allowable. The problem is that every other row only contains one item, but that one item is crucial to identifying the row above, so it can’t simply be deleted. The first step of this program runs through all of the sheets on the page, inserts a column, and brings that one cell of data up to the row above, where it can be used as an identifier. It then deletes the row that this identifier came from, as it now has no more information. This data is further broken down, and redundant and excess data is removed. The program then sorts each of these rows of data based on the team that the call was directed toward; creating a new tab for each team that has data. The next phase of this program takes the sorted data, and inserts a pivot table into each of the worksheets, and from that pivot table, it creates charts, to better see the data. These charts help management to see which teams need to be more fully staffed, or teams that might be staffed too fully at different points in the day. The users have the option of entering different parameters that they would like to be graphed. This basically just changes what the pivot table charts, but allows for more flexibility for the parameters that wants to look at. 

• http://files.gove.net/shares/files/12f/tw265/ProjectFinal.xlsm
• http://files.gove.net/shares/files/12f/tw265/Project_writeup.pdf