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 or if syncing you'll need to request your past call results from your admin console to 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 submissions file, the call results file, and the caller details file. When you are syncing your results, the nightly report does not include the "call results" file, so you have to request past call results via the UI to receive the "call results" file you need. Once you have that past results request in and receive the on-demand report that includes the "call results" file continue.
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.