I am talking about a tool or tools that a tech coach can track how they use their time. I used to get to the end of the day and think 'What did I do today?'. Sometimes a complete stranger will ask 'what do you do?' I have my elevator pitch but then I usually get some more detailed questions. I would also like some stats if one day the head of School or board asks me to justify my job or even worse my wife and kids ask 'Why are you so busy?'
I also wanted to get some hard data on how I use my time so I could become a better coach. Which type of support do I offer the most? How much time am I spending in Elementary / Secondary? Are there better ways to get to more staff? How much time am I setting up systems or maintaining them? How much time do I spend on my own PD? How do these numbers change over time?
My natural thought was to use a Google form to track all I do each day and use the graphs or create my own. How to set it up? I know that how the questions are set up is important for how you view or use the data. A checkbox will allow you to have two words in the one cell (which can be a pain), It can be hard to organise data if you use a grid.
I wanted to be able to enter what I did, how long I did it, where I did it (elementary, secondary, whole school) and maybe some notes on what it was.
I ended up using a form with seven pages so that I could enter the same type of data multiple times. I then thought about the things I usually (or sometimes did), with some adjusting and rethinking (especially when I remembered something or thought something might be useful) I came up with this list.
I then added some extra questions like school division (I work across the entire school), some notes, date and how much time spent.
I also added a finished radio button that allowed me to submit the form without having to get to the end of the form
You can't share Google forms to be view only, so I made a copy of the form and you can see it in action here
Example Google Form
It is pretty basic, but it is all I need. You will need to build your own version, if you want to do something similar.
So now I have a Google form I fill out every day and then I get a series of three (hopefully soon to be more) interactive graphs that track what I do each day. It took a while and a heap of brain power but it was fun and I am finding it super useful.
Now for the fun
(it is also a bit complicated)
link to the response sheet with all the formulas etc
Once I got the data I had to work out how to make it useful. The form responses sheet came as a long row, with the selected date once in the row.
Because I wanted to use query language to auto sort my data I needed this data to be moved from a long horizontal row into five columns (date, amount of time spent, activity, notes and division). I also needed the date row which only appeared once in a row to be tagged or linked to each entry. That way I could sort and arrange the data (using a variety of functions and query language commands). I don't think it is possible to use multiple queries at the same time.
I am sure there is a clever way to do this, but the only solution I could find was to use the array formula function with a new function on row 1, 301, 601, 901 etc. (see sheet single col data)
'=arrayformula(data!B2:B299)' - this gives the date field (this stays the same for each new formula row 1, 301, 601 etc)
then in the next cell / column
'=arrayformula(data!H2:L299)' - this gives the data (time, activity, notes, division) from the original row (this needs to change for each new formula row 1, 301, 601 etc)
This drags the columns from the original sheet to a new sheet (see the sheet called single col data). It limits the number of responses to about 300 before you start getting errors, but because I am only tracking one year at a time it should give me plenty of scope. You could move the formulas to different rows to allow for more responses if you want.
Now that I have the data in a sheet with only five columns, I can start filtering and organising the data into a format that I can use.
I am not an expert in query language by any means, I know a little, what I do know is how to search the web to find answers. That is what I did, here are some of the resources I used.
This video is a bit complicated but after watching it and working through it multiple times I managed to grab some lines of code that worked well
An excellent site to get the basics (and the complicated)
This guys seems to know everything about query language and using it with google sheets
although sometimes the best way is to type your question in Google.
(see total time spent tab)
'=query('Single col data'!A1:E,"Select C, Sum(B) GROUP by C")'
I created a new sheet and used the above query, column C is the type of activity and B is the time spent, this query finds all the like activities and sums the time spent, then groups them by the type of activity.
Once I had the data, I selected the columns (A&B) and inserted a chart (pie graph) so I could visually see how my time is spent over all the entries.
(see split by division tab)
I wanted a drop down to select the activity (supporting staff 1-1, in class support etc) and then see a chart of how much time is spent in each division.
I first created a new sheet with a list of the activities (see lists sheet) then on another sheet I used data validation to create a drop down so that I could select an activity.
Then in cell A2
'=query('Single col data'!A2:AP,"Select A,B,C,D,E Where C contains " & "'" & A1 & "'")'
The trick is " & "'" & A1 & "'"
which allows you to get your query from the validated cell.
which allows you to get your query from the validated cell.
I found this solution in a couple of placesOnce again I used the insert chart function to create my nice pie graphs.
This allows the tab (sheet) to be interactive, it will change according to the activity selected.
'=query('Single col data'!A1:E, "Select A,B,C,D,E where A >= date """&TEXT(B2,"yyyy-mm-dd")&""" and A <= date """&TEXT(C2,"yyyy-mm-dd")&"""")'
I just copied it and used it, the start date is in B2 and the end date is in C2.
It worked great, once the cells B2 and C2 are recognised as Dates Google sheets even allows you to select dates from a calendar by double clicking on them.
WarningOne thing I found the hard way is that if you select a finish date before a start date, it messes up your spreadsheet big time. I got an error and couldn't delete the sheet and had to start again. I have since made a few copies just in case it happens again.
Track activities over time
The next thing I want to do is to be able to select an activity and then see a graph of how much time is spent on the activity over time. I would like to be able to see if there are times in the year when I spend more time maintaining systems, or supporting staff in departments, or visiting classes.
I haven't done this yet and I have a few ideas, I just need some time to play and explore.
Finally I cleaned up the spreadsheet and hid the columns I don't need, when I open my sheet I only display date range, time spent and split by division.
Now that I have all this information what do I do with it? Some of things I am noticing already
- 1-1 Staff support is my largest chunk of any week
- As the year progresses I am doing less setting up systems and maintenance and more 1-1 staff support
- I am spending more time working with Elementary staff than Secondary
- My hours spent at meetings has increased
- In class support is happening more in the Secondary
- I spend little to no time with non teaching staff
I need to now ask the why and how questions, why is this happening and how can I change things?
One positive is that this data now raises more questions.
- How much of my time is reactive vs being proactive?
- How can I better record drop ins?
- Should I start approaching teachers or focus more on my booking system?
- Are there ways to outsource the maintenance to free up time for staff