The nightly reporting comes in a few different files. This guide will help you combine the files as needed for comparison purposes.
You'll create this file using your nightly report. Locate all the files needed for comparison. We'll also use a vlookup formula and a pivot table. These instructions are for Excel. You need the script results file, the call results file, and the caller details file. You can also use the VAN sync error report.
This will show you how to use a vlookup formula to analyze results.
- Download all the files in the email.
- Use the move or copy on the tab to move them all into 1 file to make the next steps easier.
- Once they are in one file, go to the tab with script submissions.
- In the top row, you can insert a column after time called or next to the start question.
- Give the column a header of "Call Result".
- In the second cell down, we will be performing a vlookup function.
- Vlookup is looking something up in a vertical column and returning a value.
- We will be using the unique ID for our vlookup function, as it is unique per contact, which is needed for vlookup to work.
- The vlookup formula looks up a value, in this case, we used a Unique ID from the script results file to look it up, A2.
- Second, we pick the column with the unique ID on the call results and the other columns that we want to pull data from, A:H on the other tab with the call results, I told it 9, which is the column I want to pull the data in from (the ninth one over), and always put false.
- This will bring in the call result. You’ll want to copy this formula down the column for every row with a script result. Tip: use command shift down to find the bottom of the results and copy up from there.
- Use the exact same formula to bring in the Caller Login for each call except this time we want data from the 8th column one over.
- Once both of these are brought in from the call results, you can use a pivot table to break down the results by caller login or by the starting question.