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)

https://developers.google.com/chart/interactive/docs/reference#queryobjects

This guys seems to know everything about query language and using it with google sheets

http://yogi--anand-consulting.blogspot.my/

although sometimes the best way is to type your question in Google.

http://bfy.tw/2Xk1

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.

I found this solution in a couple of places
http://stackoverflow.com/questions/31613423/google-spreadsheet-query-to-dropdown-list
http://yogi--anand-consulting.blogspot.my/2011/10/yogiuse-query-function-with-search.html

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.

Warning

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

Google Photo's Rocks

Google Photos is new (or at least vastly improved) and there are still lots of things that need work, but every day that we use Google photos at IGBIS we find new uses and innovate ways to save ourselves time and dramas. The automatic syncing is worth the effort of moving over all on its own.

Once uploaded (or even better synced) the photos (or videos) are stored in one place, but there are at least five different ways to access or use your photos (for now).

1. Google Photos on the sidebar of your Google Drive

2. A folder on your Google Drive (which you can choose to have or not have)

3. Via Google+ photos

https://plus.google.com/u/0/photos/yourphotos

4. Via the Google photos web app (or the app on your phone or iPad) The Best option in my opinion

https://photos.google.com/

I think you can also get them via Picasa Web Albums

https://picasaweb.google.com/home

This makes it all very confusing so I recommend to all staff (especially staff who are not that competent) to use the Google Photos web app for at least three reasons. I don't know if Picasa web albums and Google+ photos will be maintained. The Web app looks the same as the iPad app, it allows you to do so much more and it is easy to use and share albums and photos.

At IGBIS some of the ways that Google photos saves us time, creates great stuff and is just plain cool include

1. Auto Syncing

Last year 

We used to use iCloud to automatically sync photos from teacher iPads and even some class iPads (all photos going to the teachers photos app) This didn't work for video, if a class or teacher took too many photos the free 5gb limit could fill very quickly and it was hard and time consuming to move the photos to our Google Apps (Blogger, Docs etc). If teachers had an Android phone this syncing wouldn't work.

This Year

We use Google Photo's on the iPad or teacher phones (IOS and Android), it is eay to set up (teachers just log into their Google Accounts), Video uploads automatically and we have unlimited storage (so no more messages saying the account is full) 

We have also set up class Google Accounts so that all the iPads in a class can be synced to the same account, this means students can see all photos and videos no matter what iPad they are on. It also means the teachers can access all the photos and videos the students take (we set up a Profile on Google Chrome with the class account). We can also log into the class account and share the Google photos folder directly with the teacher or students so they can have it on their own drives.

With iPads this is great because 99.5% of everything students make on an iPad either ends up as a photo or a Video.

2. Google Apps Integration

Last Year

Teachers would drag photos from iPhoto (or Photos) onto their desktop then drag them into Google Docs or upload them to Google Sites or their Blogger blog. For videos they would have to plug in their iPads, import them into iPhotos, export the videos and upload them to YouTube.

This Year 

Everything is just there. Go to insert an image in a Doc, Slides presentation, Drawing or sheet and your albums are one of the options.

In Gmail Insert Image gives you an option to use photos from your albums

Using Blogger? Insert directly from a Picasa web Album

On a Google site, you can import directly using the Insert / Google+ for both individual photos and albums. The only issue with this is that photos have to be in an album before you can search for them.

Videos are much easier to use as well, in YouTube when you click the upload video button you now get an option to import videos from Google Photos. (much easier than what we used to do)

Now with automatic syncing (especially syncing class iPads to a single account) and easily using the photos (and videos) in all GAFE apps the task of using photos and videos in simplified and sped up. 

But that is not all

3. Sharing Photos

Last Year

Teachers would drag photos from iPhoto (or Photos) onto their desktop then drag them into a Google Drive folder which they would then share or they would email photos. Videos had to be once again be imported onto a computer and then uploaded to a Google Drive Folder or sent directly to YouTube 

This Year

Teachers create an Album, we generate a sharing link to the album, this link is then placed on a Google site called photo sharing. If anyone in the school wants to see photos from grade 3 Art, or Kindergarten Music or Grade 5 Chinese they just go to the album. When a teacher wants to add photos they just add them to the appropriate album in Google photos (either on the iPad, Phone or on the web app)

Teachers can also set up albums for each student in their class, then share the album with the parents or the students, once again to update photos it can be done on the App or on the Web.

For camps we have set up camp websites with links to particular albums, once a photo is put in the Album it is automatically accessible from the Camp Site. As yet there is no way to collaborate on an album (like iCloud photo albums) so to get around this we have created Google Accounts for each camp and each iPad taken on camp will be logged into this account, this also means that all the photos taken on camp will be stored in one place, while teachers can choose the photos they want to share with the parents.

We are also testing students and teachers having direct access to the photos and videos in their Google Drive, we do this by sharing the Google Photos folder in a year level account with all the students and teachers in a year level, which they can then add to their drive.

This is only the start of how we share photos, I am sure there are many more possibilities that will come to us as the year progresses.

4. Organising Photos

Last Year

This was done in iPhoto and then maybe again on a Google Drive folder or using Picasa, teachers had to remember to update all the areas and photos were not always where you wanted them when you needed them. Videos were just as bad. 

This Year

With it all being done on Google Photos, in albums, it is much easier. 

Specialist teachers have albums for each grade level which contains all their photos and videos, this can easily be used to create portfolios or to share photos with the kids. They also then have a sharing album which they can share on class blogs or directly with the parents.

Class teachers usually have an album for each school week (which they can use in their weekly website newsletter) and an Album for each student (which they can use for Portfolios or sharing with the students)

5. Backing up Photos

Last Year

It was through iCloud (automatically) and maybe Google Drive which was fine except it didn't include video and it only gave us 5GB

This Year

It is all done through Google Photos automatically and we have unlimited storage

6. Extra Cool Stuff

Last Year

People used other apps, Pic collage, Sonic Pics etc which are all very cool, they still use them this year (students and teachers may have to save photos to the Camera roll first, which some teachers are not happy about)

This Year

People still use their favourite apps, but

Built into Google photos (mobile app) there also have some great tools including the ability to use photos to make a photo story, a movie (you can add titles and music), a collage and a gif. These features are not on the web app.

The Google photo assistant will sometime choose some of your photos and automatically make some of these things for you, but we find it is much better if the students or teachers can choose the images that go into the photo story or animation.

Teachers are creating photo stories on the bus on the way home from excursions and then sharing them with parents (a two minute job) once they get back to school. If they are using their phone they can add them to their website while still on the bus.

We are looking forward to the photo stories and videos that will be created and shared while the students are on camp in the coming weeks.

Photos can also be edited directly on the web app or the mobile app including adding descriptions.

It's not perfect

Nothing is, some complaints include teachers and students have are having to download the photos to use in other apps, sometimes the videos created in the Google photos app don't upload and if you don't open Google photos the photos and videos don't sync.

We feel though that Google photos has added a lot to our iPad program and the way we use photos and videos at our school, we are saving time, creating great products and have become much more collaborative.

Our students make a lot of videos and photos on the class iPads (as do the teachers) pretty much everything they create ends up as either a photo or a video therefore we need an easy solution.

The big pluses for us are

- auto syncing many devices including video

- ease of use in GAFE

- easily sharing photos including photo stories, collages and video 

- did I mention video is auto synced?

For a comparison between Google photos and Apple photos check out this article from Macworld UK

I would love to hear how others are using Google Photos in their schools.

Activities Activities Activities

As with any new school, we have the opportunity to set the way things are, we don't need to do something just because it has always been done that way.

With our after school activities this is a wonderful opportunity. We have just started season two and with effort, team work and trial and error we almost have a system we are happy with.

For Season One
1. We used a Google form for teachers to nominate which activities they would like to run.
2. We then used another Google form for the high school students to sign up,
3. Elementary students signed up on paper which then had to be added to our activities spreadsheets which was created manually.
4. Originally Elementary had two seasons while secondary had one
5. The roll for each activity was taken on another spreadsheet (which also had to be created manually)
6. There were lots of email floating around, messages on managebac for students to sign up etc

It was messy and thank goodness we only have 150 students.

After a few chats, a visit to the KL GAFE conference and some online research we greatly improved the process for season two and almost have it exactly how we would like it.

So how do we handle activities sign ups now?

This might seem a bit complicated, but it has automated a lot of the sign up process and provides a better experience for teachers, students, parents and the activities coordinator.

1. We created a google site for activities at IGBIS  This site has some embedded Google Docs so the activity coordinator can easily update activity information or news (he just has to update a Google doc that is in his starred area of his Google Drive)

2. We have a Google Account 'activities@igbis.edu.my' that all sites, forms and documents are created through. That way the resources are not tied to a teacher but to a role. It also means any emails that are sent are sent from activities@igbis.edu.my. We forward all emails sent to this address to our actual activities coordinator

3. Two to three weeks before students sign up, teachers complete a Google form letting us know what activities they want to run. We have this information in a google spreadsheet which is easy to manipulate.

4. Once the form is closed we then use the Google sheets add on "save as doc"  to generate a list of all the activities including their description. The Activity coordinator then tidies the doc up and it becomes our list of current activities on the website. The doc is then published to the web and embedded on the site so that any changes that happen to the doc appear on the website.

5. We then use this information to create a google form for student sign up. The form is put on the website. This is done manually with a drop down for each day an activity is offered. That way a student can't sign up for two activities at the same time.

We would have liked to use a students log in details but we need elementary parents to also fill out the form and they don't have school google accounts (yet).

Now the fun starts

5. Once the online form is filled in, we then use ultradox trigger to automatically generate an email to the parents letting them know what their kids have signed up for.  Parents know what is happening and they can confirm that they want their kids to attend an activity that is provided by an outside provider (with the extra cost). The activities coordinator doesn't have to do anything and the parents stay informed. We also know when the parents or students type in a wrong email address as we get a bounce back message.

This took a bit of fiddling to get it just right (because we used drop down menus the first item on each drop down had to be a blank so that ultradox didn't think the first option on the drop down was selected).

6. On the Google form responses sheet I created a tab for each activity. I then used the query language to automatically move the student detail into the correct activity tab. This means that when a student signs up for an activity the list for that activity is automatically created. See this video for detail on how I did it

The activities coordinator now has a list of who has signed up for each activity, this is created as each response is made. The coordinator can keep an eye on popular activities or see when an elementary student has signed up for a high school activity and visa versa.

7. Once the sign up is closed we then close the sheet, the coordinator can check the sign ups for numbers and make sure all sign ups are legitimate. He can reopen the sheet and manually add any extra students if we have new students in the school or someone was away during sign up etc. He can also remove students or send emails to parents and students if we don't have enough students to run an activity.

It is fine to delete students from the original form responses sheet, they will also be removed from the other sheets. It is important that if we want to add an extra student we use the sign up form and don't just manually add the student to the sheet. (we don't want to mess up activity names, which will then break the sheet)

8. This sheet is then shared with staff so they can see who has signed up for their activity.

9. Once all activities are finalised we duplicate the form responses sheet and use yet another mail merge to send a final email to parents confirming the activities that are running.

10. Using the original responses we then create three other sheets (One for each afternoon of activities) and use this template to create an attendance sheet for each activity at the bottom. Here it is important to copy and paste the values only, so that teachers can add students or remove students once the activity has started. 

The sheets are then linked to the staff area of the website, even though the website is open the sheets and documents on the staff page (which contain student information) are protected because they are google docs that are only shared with staff at school.

The teachers can now take attendance for their activities. I encourage teachers to make a repeating calendar event with a link to the attendance sheet. That way they get a pop up reminder (on their computer / iPad / Phone) that reminds them they need to take attendance with a direct link to the attendance sheet. One click and they are ready to take attendance.

I would really like to find a better way to take attendance, but haven't found anything that works like attendance on Managebac or Powerschool.

11. We then duplicated the list of activities and used this nice little google sites gadget Awesome table to create an interactive table so anyone on staff can search the activities by student name, activity or day.

This is very handy for booking busses and for finding out where a particular student is.

All of this took a bit of trial and error, I quickly changed the attendance sheet once it was shown to the staff. Originally all activities were on one sheet and the sheet contained too much information and we were asking the staff to add their own attendance dates etc.

That is about it, we still have some work to do, but we are streamlining the process and have come a long way in less than six months and starting from scratch. A major advantage is that because much of the workflow is streamlined and automated as the school grows the amount of work doesn't increase at the same rate. 

Some improvements that we already know we need / want include

• parent Google accounts to allow them to log into our domain
• links on the school web page / creation of a splash page for students / staff / parents
• a workflow / diagram so all parties know the procedures including dates for each stage of the process
• a streamlined attendance function
• an automated email gets sent to the activities coordinator when students are marked absent.
• a way for students / parents to find out what activities they have signed up for

plus whatever else we think of as we go along. 

Maybe by the start of next year it will be perfect

Yeah right!