Reporting in Google Classroom

[Gonk]

Morning all,

 

I hope that i find you all well and coping with the current situation.

 

I've been asked if i can produce some data on usage of the google platform while we're under lockdown, so I'm after some advice / help with the best ways to do it.

 

I've been asked by the SLT if i can get the following information:

 

Username, Real Name, log on time, log off time, Classroom(s) accessed, Time spent (in Classroom), MAT status, FSM Status, Year Group, Form Group.

 

50% of what I need I can get via SIMS so its just a case of extracting the Username, log on time, log off time, Classroom(s) accessed, Time spent (in Classroom) from GSuite.

 

IS anyone already doing this?

 

All suggestions/advice greatly accepted!

 

Ross

[keyboards]

Not sure you can go that granular, almost sounds a bit, big brother

 

In the google admin console, if you go audit, then login, you can see the ip and time of every time a user logged in ie [email protected]

[Ditto]

I did look at something along these lines when I was GSuite Admin. The legal basis for processing is one worth considering - I'd recommend talking it through with your DPO. Our interest was to consider if we wanted to identify pupils working at extreme times of day, from a well-being and safe-guarding perspective. The GSuite audit logs will give you most of what you need if you do go ahead.

[dhicks]

I've been asked by the SLT if i can get the following information:

 

Username, Real Name, log on time, log off time, Classroom(s) accessed, Time spent (in Classroom), MAT status, FSM Status, Year Group, Form Group.

 

If you have GAM set up, you can run something along the lines of:

 

gam report user services classroom

 

This will return CSV data in the format:

 

email,date,classroom:last_interaction_time,classroom:num_courses_created,classroom:num_posts_created,classroom:role

 

So, something like (for each user):

 

[email protected],2020-04-12,2020-04-01T09:11:15.000Z,0,0,teacher

 

From that, you should be able to match up usernames with full names / pupil IDs.

 

As the previous posts have pointed out, this is a rather crude metric for "How much is Google Classroom being used (by user X)?", but it should allow you to spot things like access times past a given time (if classroom:last_interaction_time > 10pm, etc) or if a pupil has never managed to log in to Classroom or hasn't logged in for, say, a week or so.

 

Beyond that, a user could, of course, log in to Classroom first thing of a morning, ignore it for the day, then log out in the evening, so log in / log out times aren't going to tell you that much. Nor will num_posts_created - you'll need teaching staff to be evaluating what pupils are doing.

[jthompson]

Beyond that, a user could, of course, log in to Classroom first thing of a morning, ignore it for the day, then log out in the evening, so log in / log out times aren't going to tell you that much. Nor will num_posts_created - you'll need teaching staff to be evaluating what pupils are doing.

 

Indeed. It sounds like SLT are after some measure of productivity, which they're not going to be able to get from the G Suite admin. Depending on the nature of assignments that teachers are setting within Classroom, a hard working pupil might only need to be 'active' for a matter of seconds: long enough to log in and submit a 10,000 word essay. They might not even need to log in to Classroom at all to pick up the assignment brief, since they can get that from an email notification.

[Ditto]

Indeed. It sounds like SLT are after some measure of productivity, which they're not going to be able to get from the G Suite admin. Depending on the nature of assignments that teachers are setting within Classroom, a hard working pupil might only need to be 'active' for a matter of seconds: long enough to log in and submit a 10,000 word essay. They might not even need to log in to Classroom at all to pick up the assignment brief, since they can get that from an email notification.

This is true, but the analysis might highlight other issues. Ideally, you let the data try to tell the story, rather than see if the data fits a theory. Ultimately, the teachers should be able to tell how well pupils are working, but the work submitted. What the data might show however, is how different teachers use the technology to varying degrees. For example, if a particularly class of children were showing very low activity on Google classroom, and classroom was integral to the schools online teaching strategy, it might highlight a training need for some staff.

[ITGuyNW]

I've installed GAM and I'm looking into it now. Should you be able to do stuff with courses, like add students, if the course name has a space in it. For example GAM TEST .

[dhicks]

Should you be able to do stuff with courses, like add students, if the course name has a space in it. For example GAM TEST .

 

Yes - just remember to put the course name in quotes, i.e. "GAM TEST" or 'GAM TEST'.

[Pickle17]

Is GAM safe in terms of GDPR?

[Ditto]

GAM's only really exercising Google's APIs, so if you are a GSuite for education school and you are happy they are GDPR compliant, I'd say you are fine for GAM. Perhaps completing a DPIA would be good practice.

[Fratopolis]

I Created this last month. an example of what gam can help you create and updates itself automatically (there is a tab for each building showing which students are not engaging) obviously names and other data blocked out for privacy reasons

 

The main tab has all the information and continues down for thousands of rows

Edited May 12, 2020 by Fratopolis

[dhicks]

I Created this last month. an example of what gam can help you create and updates itself automatically

 

Ah, interesting, thanks - I just saw your other post saying which reports you get from GAM (user's lastlogintime, gmail:last_interaction_time and classroom:last_interaction_time). I'm doing pretty much the same, but not currently using GMail activity as we've only just started using email with some of our older pupils (we're a prep school, our oldest are currently Year 8).

 

How are you getting the data from the GAM report to the spreadsheet you show above? I'm processing the data with a Python script and outputing various reports, are you going straight from CSV to Sheets somehow?

[Fratopolis]

gam print users query "orgUnitPath='/OUPATHIS YOU WISH'" firstname lastname lastlogintime todrive tdtitle "GOOGLESHEETNAMEGOESHERE" tduser GOOGLEEMAILACCOUNTTOSENDTOGOESHERE tdupdatesheet tdfileid GOOGLESHEETIDGOESHERE tdsheet NAMEOFTABINGOOGLESHEETGOESHERE

-

gam report users parameters gmail:last_interaction_time todrive tdtitle "GOOGLESHEETNAMEGOESHERE" tduser GOOGLEEMAILACCOUNTTOSENDTOGOESHERE tdupdatesheet tdfileid GOOGLESHEETIDGOESHERE tdsheet NAMEOFTABINGOOGLESHEETGOESHERE

-

gam report users fields 'classroom:last_interaction_time' todrive tdtitle "GOOGLESHEETNAMEGOESHERE" tduser GOOGLEEMAILACCOUNTTOSENDTOGOESHERE tdupdatesheet tdfileid GOOGLESHEETIDGOESHERE tdsheet NAMEOFTABINGOOGLESHEETGOESHERE

 

 

I then use this to convert the timestamp to readable format. Will need to be adjusted for your sheets and cannot be put in the tab where your data is written as the whole tab gets overwritten on next eport. So if this makes sense you create anoter tab next to each one and query the tab you want to adjust and add a column with this formula

 

=--TEXT(SUBSTITUTE(SUBSTITUTE(GAM!D2,"T"," "),".000Z","")-5/24,"mm/dd/yy")

Edited May 13, 2020 by Fratopolis

[Fratopolis]

Now I can show real interactions/engagement with the classes. The first with green color coding shows engagement in a joint of both/either Google classroom and/or Google Meet.

 

The data is there. But manipulating it to matter and make sense and be usable and automating it is craaaaaaaaaaazy. Three different google sheets and a total of 37 sheet tabs combined to make this work lol.

 

Still finding more data to pull as well. Wish we could pull clever and seesaw data per student.

 

[Fratopolis]

I then use this to convert the timestamp to readable format. Will need to be adjusted for your sheets and cannot be put in the tab where your data is written as the whole tab gets overwritten on next eport. So if this makes sense you create anoter tab next to each one and query the tab you want to adjust and add a column with this formula

 

=--TEXT(SUBSTITUTE(SUBSTITUTE(GAM!D2,"T"," "),".000Z","")-5/24,"mm/dd/yy")

 

Also remember to change the -5/24 section to match your timezone. Since it pulls universal time I needed to take 5 hours off of it to match our time zone.

[fiza]

Has anyone used or seen Classroom Zoom for reporting on engagement in Google Classroom? Being offered free at the moment. Have just applied for free access but wanted to see if anyone else has come across it. Saw it over on Google+.

 

https://chrome.google.com/webstore/detail/classwork-zoom-for-google/flkiihnalkdcocknmgjolnbhfaigngfp?hl=en

Edited June 24, 2020 by fiza

[dhicks]

Has anyone used or seen Classroom Zoom for reporting on engagement in Google Classroom?

 

Thanks, I'll take a look - if nothing else, looks like we've found 2020's winner for "most confusing product name"!

[paulkerton]

Has anyone used or seen Classroom Zoom for reporting on engagement in Google Classroom? Being offered free at the moment. Have just applied for free access but wanted to see if anyone else has come across it. Saw it over on Google+.

https://chrome.google.com/webstore/detail/classwork-zoom-for-google/flkiihnalkdcocknmgjolnbhfaigngfp?hl=en

 

It's very good, but be aware it is a teacher level product, not one for Admins, or for collating data across your organisaion really.

[GrahamBowman]

We have a product called ClassReporter which sounds like it achieves some of the things that you are looking for. It will collect the data from Google Classroom about teachers, students, work set and work submitted (or not) by students. The data is collected into Google Sheets with an accompanying Data Studio connector and Template. Year Group can be pulled from the students' OU but you could also connect another Sheet with your MAT status, FSM Status, Form Group and any other data to display alongside it in Data Studio.

 

It is a commercial product with a free trial to test it out. The pricing is available at https://tablet-academy.com/classreporter/