Spelunking Timesheets

“We’ve done a Facebook integration before, haven’t we?” “How long did we spend setting up and managing our last LinkedIn campaign?” Agencies that track their time can answer these questions by digging through their time sheets. 

Like spelunking through a cave, digging through timesheets is like fumbling around in the dark, feeling your way along. It can yield fascinating discoveries, but you’re just as likely to bump your head or walk away with a soaked foot. 

We learn from past experience. Individually this is pretty straightforward. A freelancer knows that a logo project might take her 25 hours with revisions, on average. But what about an agency’s website project that involves an account manager, project manager, 2 designers, 1 copywriter, and 1 developer? How long will it take to implement two-factor authentication? 

If you can reliably leverage your institutional memory, your time estimates will be very accurate. Since you sell your time, knowing how much your work costs is critical to your profitability.

The challenge to spelunking your timesheets is quickly finding your way through tunnels of time data when you can’t see where you’re going. If you make a mistake, your incorrect estimate could cost you thousands.

During customer discovery, I spoke with dozens of agency managers about this problem. Many of them told me they look at past timesheets to help with project estimates. However, when I pressed further, it turned out they don’t look very often at all. Why? They told me:

• They can’t remember which project(s) they should look at to find this information
• They don’t trust the data that was collected
• The time spent on a feature can be scattered across several people, and many days or weeks
• Their tools make doing this research very tedious

On rare occasions spelunking through time sheets is worth the effort

Most agencies use day-by-day time trackers that don’t offer a search feature. That means your first step is to export your time sheets as a CSV file. You’ll want to export all of your time data. With the CSV file and some good Excel chops, you can slice and dice your data. This is where the real work begins.

Once the file is loaded in Excel, start by searching for some keywords related to the feature you’re seeking. Using our two-factor authentication example, you might want to search for “two-factor”, “authentication”, “auth”, “Google Authenticator”, “2FA”, “MFA”, or other tech-specific words that might apply.

With each search result, take note of who logged the time, when they logged it, and how much. Pay attention to the words they use in their descriptions. The same individual may have used different words to refer to the same feature across multiple days.

Ensure that you’re only looking at one project’s time data at a time 

Now you have a list of people who worked on the feature and the time that each of them spent. Look at each entry and see if the description might be combined with a few other things. For instance, a 4.5-hour entry might read “login changes; start on 2fa implementation; discussion with Damian about login UX; update home page copy”. Make an educated guess as to how much of the 4.5 hours was spent on the two-factor authentication feature and adjust the time accordingly.

Work never sits in isolation. Look at the other time that individual logged that day. Was it just one project, or several? Were they being pulled in a lot of different directions? Is it possible that the time was over-reported or under-reported? 

If a particular task spans multiple days, and there’s evidence someone worked on a feature on Monday, Wednesday, and Thursday, it’s possible they also worked on it on Tuesday or Friday. Look at the intervening days, as well as the days before and after, in case there’s evidence that they worked on the feature without noting it as such.

Compare the time each individual logged in relation to their colleagues. Is it possible that someone else contributed to the feature but failed to describe their time accurately? 

At this point, you have a pretty good idea how long each worker spent on the feature you’re researching. Now you need to account for the project and account management time. Both of these roles are typically billable, so you’ll need to apply your internal management-to-work ratio.

Add all the numbers, and you’ll have a real indication of how long a feature took for one project.

Now do all these steps again, once for each project where this feature appeared. Compare how long the feature took each time. Account for any discrepancies: who did the work? Are they good or bad at entering their time? Is there a difference in individual skill levels? Has technology improved to make it easier to resolve the problem?

Finally, average the time for this feature across all the projects, accounting for changing skills and technologies. Use this number in your estimate.

This is a lot of work to find out the cost of a single feature. The work might be worth the effort if the feature is big enough or critical enough to the success of a project. However, the reality is that most project estimates will continue to be based on best guess and gut feel.

Digging through your timesheet history is a lot of tedious work, but it can help you make sure you cover your costs without charging your client more than the feature is worth.

At Clockk we’re exploring how we can better help you leverage the information buried in your timesheets. We’re working on a timesheet explorer to get you the information above really quickly. We’d love to hear your thoughts! 

Ready to see how Clockk can make your life easier by capturing all your time accurately and easily Sign up for your free 14-day trial.

Want to learn more about how to better track time at your agency? Sign up for our mailing list below.