Self Tracking on Google Sheets: How to keep accountable for goals and resolutions

People who know me know I am a productivity junkie. They know I like to learn and improve, and that I track myself quite a lot, because that’s what self development is based on. That’s why they weren’t too surprised when I announced one day that I would be tracking my daily activities in half-hour increments. Since that day I’ve been tracking my time and noticing benefits including an understanding of my habits and behaviors, and an increased focus from being mindful of what I’m doing.

Self development starts with self awareness. So if you want to become more productive, or fitter, or better at a skill, you need to compare previous and subsequent measurements to see if there has been any improvements. Over the years I’ve used various apps to understand various aspects of my life. I’ve used Sense, an app that combines with a physical device to track my sleep and I’ve used Dollarbird, a spending app where you input details into categories and can see the data visualized over time.

Left: Sense, an app that is paired with a physical device to track your sleep habits. Right: Dollarbird, a money-tracking app that I also used to track how often I did non-monetary activities like taking the subway.

Aside from using Dollarbird to track my spending, I also used it to track non-money related events, such as counting the frequency of my subway rides. From my self-tracking I learnt that it was cheaper for me to buy individual swipes rather than monthly MTA passes, since I don’t take the subway often enough to warrant it. However, as my tracking grew more complex, I realized that certain things could not be tracked in an a money spending app. In particular I wanted to track how I spent my time throughout the day. Being a web developer, I spent three weeks creating an mobile web app called Timekeep where you could track events in categories and see charts of how you spent your time (the web app is mobile-only currently and will look strange on desktop). However, I eventually resorted to using Google sheets since it was much better established and flexible. After all, why reinvent the wheel?

Screens from my project, Timekeep, a mobile web app I built over three weeks to track how I spent my time.

Using Google Sheets

Google sheets is great. You can access it across all your devices and it functions basically like an MS Excel in the cloud, which is why I chose it over Excel. In Google sheets, I had each row represent one day. Each row contains a date column and columns for half hour intervals between 7am to 2am in which I input my activities. Activities are rounded up to the half hour. I had originally considered 15 minute intervals but quickly realized that would be way too many columns for me to comfortably handle.

Tracking your time has a number of benefits:

1. Keeping accountable
Got a goal or resolution? Track it. See if you’re actually putting in as much time at the gym as you aimed for. If you are, great job! If not, you’ll do better next week. This is feedback, and feedback is key to improvement.

2. Staying focused
Now that this has become a habit, I’ve become more mindful of what I spend time on. Whenever I start to slack off, I often catch myself and think “Oh no, I don’t want to record this as wasted time!” and promptly return to the task.

Google Sheets automatically rearranges data into charts.

3. Discovering interesting trends
You may stumble upon interesting trends while playing around with the data. For example, you can find your average weekday vs weekend sleeptimes. In addition, Google sheets also automatically generates graphs and charts based on data provided. Not all of this will always make sense given your goals, but sometimes Google Sheets surprises you.

Setting up your Google Sheets Time Tracking

Before setting up Google time sheets you need to figure out a few things:

Intervals

You should split your intervals into chunks of time that make sense for you. For me, it was 30 minutes each. I work well with the Pomodoro technique (25 minutes of focus plus 5 minutes of slacking). I have a friend who works in 20 minute intervals. Use what works best for you.

I also suggest your Google Sheets time structure reflect your waking period. I have mine go from 7am to 2am. I usually wake up at 7:30 and sleep around 1, although I’m hoping to sleep earlier (an opportunity for a Google Sheets-backed new years resolution!). Personally, writing sleep in 14 or so cells is between 1:30 to 7:30 isn’t too useful for me, so I avoid it.

Categories

In order to derive any meaning from your tracking, you need categories of activities to input into your spreadsheet cells. Creating these categories can be tough. Below are some of my recommendations:

1. Have an idea before you start
There are so many activities and things we do in our daily life, it can be a quite difficult to categorize them all. You can end up with literally hundreds of different activities. For this reason, it’s good to have some idea of how you will categorize it beforehand, perhaps based on your goals are. For example, my main goal when starting to self-track my time was to reduce time wasted on browsing the internet and social media, so I categorized these activities altogether as websurfing and tracked that in my equations.

However, at the same time, your taxonomy needn’t be completely finished before you start tracking. You will always find things to add later. If it does turn out you want to consolidate several categories, you can always do a find-and-replace action to combine them later on, or just update the equation that analyzes the data. For example, in order to track how much time I spent websurfing, I set up my Google Sheets analysis equations to take the average amount of time of websurfing, but I later added a TV time to my data and updated my equation. It is always easier to go from more detail to less detail than the other way round, the same way you can save a photo from high resolution to low resolution, but not the other way round.

2. Have consistent terminology
Once you have a general idea of what you’ll be tracking, try to consolidate them into a consistent set of terms. If you track your meals with eat dinner, don’t also record it as ate dinner and eating dinner as well. While you can do a find-and-replace to consolidate the data later, it will be a hassle and a risk: you may forget about it thus skewing your data, and/or you will forget if there was any difference. Sticking to one consistent term keeps your tracking clean, simple, and accurate.

Some categories I use:

Travel: A significant portion of our time is spent traveling: commuting for some, walking for others, but we often forget about it. Knowledge of this may help you overcome chronic tardiness by learning to plan ahead of time.

Side project: I like building things and exercising my creativity. In the past I’ve made music videos for fun, but recently I’ve been making small web applications and websites. These all go into my side projects category.

Websurfing: This is a term I created for the act of surfing the web, which is something I want to track because most of my wasted time is spent reading articles online or browsing social media.

Guitar: As learning the guitar was my 2015 New Year’s resolution, it’s helpful for me to track how much time I spend on guitar practice. So far I’ve kept up with this resolution, practicing about 30 minutes every day.

Making sense of your data

As you gather your data, you might find it difficult to understand what it all means. Sure you have rows upon rows of data, but you can’t read all the rows all the time to understand what this means for you and your goals. Luckily there are ways to understand what your data points mean.

Key Performance Indicators for your personal life

In business, there is something called a KPI, a key performance indicator. It is a measure of how the business is performing. For a business, it might be the debt-equity ratio, or the price-earning ratio. Different companies have different KPIs depending on their business and strategy.

You too can make your own KPIs to track how you’re doing. Most, if not all, Google Sheets functions are identical to those in MS Excel. You will probably use a lot of SUMIF or COUNTIF functions to find out how your resolutions are going. If I were a writer, I could take the number of articles I churn out divided by the amount of time I’ve recorded on the spreadsheet that I spent writing. Or you may also simply want to track the average amount of time you spend daily, weekly, or monthly on a particular activity. Whether your personal KPI is complex or simple, the great thing about keeping data is that as long as your system is consistent and thorough enough, you can always go back and run more analyses on it.

Easing readability with Conditional Formatting

Sometimes you don’t want to see just the single averaged number that KPIs give and instead want to scan through data and see highlighted any important points of interest. You can do this with conditional formatting where you can highlight rows and columns whenever particular criteria are met. Examples include cells that are, empty, not empty, happen today, above/below a certain value, or contain a certain text.

I have a column for each row that highlights if the total amount of time spent unproductively exceeds two hours for that day.

Go forth and track yourselves!

Tracking yourself is by no means new, but it is becoming more popular. With tools such as Google Sheets and various phone apps, understanding your own behavior is becoming increasingly doable and useful. This is good for those of us who seek continual self improvement. Understanding where you were is important to understanding if you have improved.

In this post I have shown my particular method of self tracking for time using Google Sheets. While not as streamlined or sleek as other apps, it is highly flexible and robust. I will continue to use this to track myself and I hope you too can use this in the new year to track your resolutions, goals, and daily activities!

Edit (July 5, 2016):
I’ve since stopped tracking what I do every half hour on Google Sheets because of it being too much micromanagement. However I still do track myself, but focusing on activities rather than time. You can read about it on my short article about my two tracking methods that I’ve switched to.