If you have ever had to calculate payroll or figure out the hours someone worked to pay an hourly canvasser you know how much of a challenge that can be. Well, let me fill you in on a little secret - SPOTIO has a weekly payroll summary excel template that will do the hard work for you.
Yup, that’s right! SPOTIO’s template will do the math and figure out your employees’ start times, stop times and the number of hours per day and how many hours per week they worked. This can be a big help to anyone who is responsible for managing canvassers.
How does SPOTIO do this? We time stamp each status change which means when your rep records their first status change for the day we will time stamp that and then we will time stamp their last. We will even record if they visit a house multiple times in the same day, That is the beauty of a historical report.
There are two parts of in calculating payroll:
Part 1: Running a historical report inside SPOTIO
Step 1: Select a new historical report
Step 2: Adding in Fields - for it the Weekly Payroll Summary report to populate correctly we need to make sure we have the following columns in our report: Status, Location Verification, User Distance, Address, City, Zip, State, UserName, Updated Date, Updated Time
Step 3: Add Filter - we are going to be adding a filter for ‘Historical Status Date’. The Weekly Payroll Summary can take a week's worth of data so we will apply a filter for the 7 days.
Step 4: Run Report
Step 5: Export Report
Once you have exported your data, there may be some clean up that you need to do. If your reps revisit a pin more than once in the same week, then when you look at the CSV that you exported you will want to make sure you review the Historical 2 columns (or Historical 3, 4 or so on) to make sure they aren’t the later knocks. If they are, you will want to insert a line and copy and paste the later date and time and into the new line below so the rep gets credit for the second visit.
BONUS TIP: You can save this report as a template so you don’t have to recreate it each week. That way each time you are ready to go in and pull the report you can go in and hit the saved report section you just must adjust the dates and hit ‘run report’.
Part 2: Inserting the data into the ‘Weekly Payroll Summary’ excel file
Spotio has created an excel file template where they can go in and copy and paste the data from your exported report from Spotio into.
Step 1: Make sure that your columns from the exported SPOTIO report are in the same order as the excel file template, if not rearrange it to match the excel file
Step 2: Copy the data from the exported report and paste it into the excel ‘Weekly Payroll Summary’ data sheet
Step 3: Once the information has been pasted, you will want to switch to the Summary sheet and in the UserName column [c] start typing out the names of the reps that were working during the date range such as Paris Geller, Richard Gilmore. As soon as you type the name and hit enter, the hidden formulas will pull the information from the data sheet and find the start and end time for that user and populate it for each day for you.
- Spotio does look at the first-time stamp and the last time stamp so if your reps may start their day with an appointment where they are there for a while this will not give them no credit for time they are in the appointment. One thing we have seen many customers do is create a ‘Start/Stop’ Status and have their reps apply this status when they are approaching their first home or appointment, perhaps pinning where they left their car or where the van dropped them off.
- Only pay if PIN’s are verified? No problem - you can add that as a filter to your report to only show the PINS that are verified and then SPOTIO will only calculate the hours worked from those PINs versus from all PINs