This guide will walk you through the process for compiling a report of how many times each target was contacted within a multiple attempt calling list. If you want to review more details on multiple attempt calling, please review our guide Multiple Attempt Calling Overview.
To review these results, the process will require compiling data from the call results to perform a vlookup using a pivot table of the needed data for comparison. These instructions are for Excel.
Note: A vlookup is looking data up in a vertical column and returning the value found. 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 unique ID column is titled "Voter ID" in our reports.
Open the call results file:
Click on the upper corner triangle to select all cells:
Create a Pivot Table and choose open as “New Worksheet.”
Add the "Voter ID" to the ‘Rows’ field
Add the "Date Called" to the Columns field
Add the "Voter ID" to the ‘Values’ field
Make sure to set the field settings to ‘Count’
To update the setting to 'Count,' choose "Value Field Settings" and select "Count" before clicking "Ok."
In the Call results sheet, copy rows A,C,D, and E, and add them to a new sheet
- Create the new sheet by clicking the + sign at the button of the file.
- Copy A-E in the original file, paste into the new sheet, and then delete column B that includes the 'Unique ID Type' by clicking on that column B heading,
- For Mac: Press cmd+shift+down arrow, ctrl+click, and choose “delete.”
- For PC: Press ctrl+shift+down arrow, right click, and choose “delete.”
- Chose “Entire column” to delete.
- In the new sheet, create an additional row in the file titled “Calls made on ‘[Date]’” to column E1
- In Column E2, you’re going to create the vlookup function =VLOOKUP(A2,'Sheet Name'!A:B,2,FALSE) by pulling in the data from the Pivot Table sheet:
- Note: For adding additional days, you will need to select column A and the column that corresponds for the next day in the pivot table. For example, after choosing A:B,2,FALSE for 8/28 in the following image, we would then choose A:C,3,FALSE for 8/29.
- Type =VLOOKUP(D2, in Column E within the new sheet
- Click on the pivot table sheet and select columns A+B, which will add this data to your function, and then input ,2,FALSE to the end of function (2 is the number of columns checked and FALSE is used to check for an exact match)
- Within the newly created results sheet, copy column E2, then click shift+ctrl+down arrow, and click ctrl+v (PC) or cmd+v (Mac) to paste the formula to all contact rows.
And that’s it. This will bring in the number of reaches per contact in column E of your new sheet for you to review.