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.
- Copy one of the results file and click "New Sheet" on the other results file 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. Click on the column to the right of either of those and click "insert":
- Give the new column a header of "Call Result."
- In the second cell down, we will be performing a vlookup function, which 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 a vlookup to work.
- The vlookup formula looks up a value from a chosen cell and, in this case, we used a Unique ID >from the script results file to look it up, which will start with the first contact on row 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), and I input 9, which is the column I want to pull the data in from (the ninth one over), and always put false at the end for an exact match.
- 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 ctrl+shift+down (PC) or cmd+shift+down (Mac) 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 (insert > PivotTable) to break down the results by caller login or by the starting question.
And that's it. You have now successfully matched up the script and call results.