Call Center Audit Form in VBA & Excel

What is Call Center Transaction Monitoring?

Call Center Transaction Monitoring, also known as call audit, is an ongoing process of closely monitoring of some of the random inbound/outbound calls and sharing feedback with agents basis on audit result. It helps agents in following the best practices and identifying the areas of opportunities. Call audits are done by Quality Analyst (QA) or Team Leader (TL) using call center audit forms. It helps company in increasing productivity and quality by identifying the area of improvement and providing required coaching to the employees.

Why Excel based monitoring form?

Microsoft Excel is a platform which allows us to develop a fully automated and customized template, tools and reports for our business requirement. We don’t need to get an additional license for that. Also, we don’t need to invest in acquiring some additional tools for monitoring. In call center, almost all employees have MS Excel installed at their machine and this is the added advantage. We can simply download the Monitoring Template and start using after making some adjustment, if required.

About Call Center Quality Audit Form provided by TheDataLabs

TheDataLabs team has developed a completely free and downloadable Call Center Transaction Monitoring form in Excel & VBA. It has all the features e.g. Monitoring Form, Dashboard, Auto Email for Feedback and Audit Database.

Our team has tried to developed this form in such a way that it can be easy for any of the companies to start using the template in day to day call monitoring, creating repository of all the audits and sending feedback to agents with auto-feedback facility.

Understanding the Audit Form, Structure and VBA Code
Audit Form

Audit form has five different sections: 1. Basic Information, 2. Buttons and Controls, 3. Audit Score Summary, 4. Audit Details (Parameters, Sub-Parameters and Audit Results) and 5. Overall Feedback

The first section, ‘Employee & Other Details‘, is for basic information updated by QA/TL while auditing the calls. It has all the basic fields which are required for a call audit and providing feedback. In this section, we have fields like ‘Employee ID’, ‘Employee Name’, ‘Employee Email ID’, ‘Client Name’, ‘Call Date’, ‘Transaction ID’, ‘Auditor’s Name’, ‘Audit Date’ and ‘Feedback Shared’. All of the fields are free text input except ‘Feedback Shared’. ‘Feedback Shared’ field is a drop-down field with value ‘Yes’ and ‘No’. Auditor’s Name and Audit Date are auto populated fields but both can be changed by auditor, if required.

Basic information

The second section has all the controls to submit the call monitoring details to Audit Dump sheet, creating an email for feedback and resetting the entire form.

VBA Controls and Buttons

The third section provides the category-wise and overall scores. It generates real-time summary and calculates score basis the audit inputs provided by auditors.

Call Audit Score summary

The fourth section is ‘Audit Details’. It has all the parameters and sub-parameters on which auditor will perform the call audits. This template has Categories (Greeting, Courtesy & Communication, Resolution, Documentation and Process Requirement) and Sub-Categories (as mentioned in below image) along with Weightage, Audit Result (input fields) and Comments (input fields).

Audit details

The fifth section is for overall feedback. Auditor can update the overall feedback, opportunity areas and other remarks. It will be saved in ‘Audit Dump’ sheet so that anyone can refer the data in future or to prepare report, if required.

Feedback section
Structure of Audit Form

In this audit form, we have three different Worksheets. 1. Form, 2. Audit Dump and 3. Email Summary.

Form sheet has all the input fields, buttons and others to perform the audits and share the feedback.

Audit Dump sheet is to maintain the repository of all the audits done by auditor. Once, user will click on ‘Save’ button to transfer the audit data and feedback, VBA code will update the details below the respective columns in ‘Audit Dump’ sheet.

The third sheet, Email Summary has been used for internal purpose only. VBA utilized this sheet to auto-populate all the required data and then draft an email and embed this sheet data as picture to send the audit feedback to respective agent.

VBA Code used in the Call Center Quality Audit Form

In this project, we have used three different modules and some event based VBA procedure to perform all the required actions.

VBA Projects

Feedback Email Code – Below code has been used to draft an email to send the feedback to agent along with all the required summary in mail body.

VBA code to convert Audit details to HTML

VBA Code to Initialize or Reset the Form

VBA Function to take the confirmation before resetting the form

VBA function to Validate the Audit Form entries

VBA Function to Transfer the Audit data for Audit Dump

VBA Function to get confirmation before submitting the data and call all the required functions before resetting the form

This is all about the Call Center Transaction Monitoring Form in Excel and VBA. As this is free of cost, you can download the template after clicking on below button. Worksheet, Workbook and VBA password is thedatalabs .

Please watch our YouTube tutorials. Thanks!

Click on below button to download the template.

Download
Click to download

LEAVE A REPLY

Please enter your comment!
Please enter your name here