Friday 30 October 2015

Tech Coach Tracking

No I am not talking about putting a GPS tracker on your tech coach to find out where s/he eats dinner or which bar they frequent.

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?

I started taking written notes, then transferred them to a Google Sheet, but they ended up being a heap of text that wasn't that useful. I wanted numbers, something I could make pretty graphs out of and track visually how much time I am spending on what.

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.

Query Language

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.

Total Time Spent

(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.

Split by Division

(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.
Once 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.

Date Range

Next I wanted to have an interactive tab where I could pick a date range and see my activity. Using the above video I found this query command, The guy in the video even shares his sheet so you can play with it.

'=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.

Once again I inserted a chart which allowed me to build my pie graph.

One 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.

What Now

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

1 comment:

  1. Love it! I've been working on similar things at my school. Nice to see that others are using data to drive decision making.