Create a Student Dropbox and Work Log with Google Tools

We have seen a number of examples where Google Data Studio has been used effectively to visualize and share data from a Google Form in multiple ways. Since Google Forms can capture the email address of the person filling out the form, the Filter by Email feature of Data Studio can be used to create a customized report with only the information that person submitted. Data Studio could also be used to display the same data for the teacher, or even for the public (if appropriate).

With remote learning happening in school districts all over the world, schools have taken advantage of technology tools to facilitate the delivery of learning materials and collection of student work. Many districts use Google Classroom or other Learning Management Systems to facilitate this process, but there may be situations where a teacher or school would want to collect and share student work in a different way.

I only recently learned (within the past year) that Google Forms has a File Upload question type, which requires the person filling out the form to be logged in to Google. It then places the uploaded files in a dedicated folder on the Form owner's Google Drive, and adds a column in the Form's Google Sheet results of links to each submitted file. Many folks have used Google Forms as a "drop box" of sorts since this feature was released. 

I started thinking about how we might use the File Upload/dropbox approach to Google Forms with Data Studio, especially if we were asking students to share materials that were visual images. I put together a sample Form which asks students to upload a picture based on a weekly assignment. We can then think about displaying the student-submitted responses and files in a variety of ways in Data Studio, but all with the same data source (the Form Sheet.)

• A student-specific work log, which allows a student to view only his or her responses and submitted work, like a portfolio of sorts. 
• A teacher- or class-specific work log, which allows the teacher to look at responses/work by student OR by assignment.
• A public display of student work, which might display images and descriptions, either with or without student names. 

Here's the example Data Studio Report for your exploration. Each page of the report shows a different version of the report (in actuality they would be separate report files), but all use the SAME data source.

Please read through the About notes on the report itself for some of the trickier things about putting this one together. Let me know if you want to give this a try yourself - I'm happy to help!

Google Maps in Data Studio

For a while now, Data Studio has had the ability to visualize data in map form. However, recently this feature was upgraded to be able to display "geo" data in Google Maps within a report. Geo data is essentially just data that represents a street address, city, country, etc. 

I had previously created a travel log report for a family member, which allowed them to update a Google Sheet with different locations they had visited, and it marked them on a map in the report. (Yes, I sometimes create reports for fun. Don't judge.) The travel log allows filtering by year, trip name, or trip operator, and I updated it to include the Google Maps functionality when it became available. Now, the map narrows in on an area of the globe when a trip is selected AND allows the user to zoom to street view and look around. Not education data, I know, but a fun way to revisit past trips until we can travel again. Check out a sample copy here (including directions to make your own). 

I started wondering if there were ways a report using this feature could become part of a student learning activity. I played around with exploring map data of Civil War battles using data I found online, but didn't get too far with it. I ended up creating a Massachusetts Cities and Towns report (based on Wikipedia entries) that allows the user to explore information like population, government type, county, and town seal. 

While I did create the data source for this report myself, I realized that it could just have easily been created by students submitting data via a Google Form. Wouldn't that be fantastic? Say your third grade has 350-ish students, and you ask them each to research one of 351 Massachusetts cities or towns, gathering population, government type, county, and a link to the town seal. Students put their findings into a Google Form, which results in a Sheet that becomes the source for a report like this one!

I would LOVE to hear some other ideas for learning activities like this, where data is collected by students, submitted via a form and then displayed on a map. If you've got an idea, I'll help you pull it off!

Google Maps in GDS: Examples and Resources

• Google Data Studio Help: Google Maps
• How to Use Google Maps in Google Data Studio from Michael Howe-Ely
• Travel Log from Laura Tilton
• Massachusetts Cities and Towns from Laura Tilton
• Road Trip Photo Album from Riccardo Zagaglia
• World Development Indicators from Michael Howe-Ely

Do You Hear the Angels Sing?

Thank you for all the interest in my recent "Getting Started with Google Data Studio" webinars. Between the three sessions, there were over 150 participants from all over the US and the world! As I told those that attended, the first time I saw Data Studio in action, I heard angels singing! My hope in offering these webinars was to help you all hear the angels sing too.

As promised, I am sharing the recorded webinar, the 'slide deck' (actually a GDS report), and the list of getting started resources, for those who would like to review the materials or share with colleagues. Additional resources are regularly posted here on my blog as well as on Twitter @tiltondata. I am also available to provide consulting services in the area of Data Studio professional development as well as customized dashboard development.

Please feel free to reach out if you have follow-up questions, resources to share, ideas for new visualization tools, or suggestions for future blog posts.  Thank you and stay well!

Tips for Structuring Your GDS Data Source in Google Sheets

When educators are first getting started with Google Data Studio, one of the most challenging things to learn is the best way to structure a data source (for example, in Sheets) so that it works well with Data Studio. The problem is that the way people tend to USE spreadsheets can be very different than the way Data Studio prefers them to be set up in order to visualize the data effectively.

Here's a simple example. Schools often store data within a structure like the one below, because it makes more sense when we are looking at it in a spreadsheet. We can look at and/or enter student scores over time, and we can see all of a single student's data in one row. Logical, right?



However, this type of structure may not allow us to display the data in a desired format, because you essentially have three sets of metrics for each student. Restructuring the data so that we have a dimension (Administration) and a metric (Score) can help, as in the example below.

This allows us to graph the data in a useful way:

I see a lot of Sheets where there is one tab (or Sheet) per school, grade level, classroom, etc. Data Studio wants to see your data in a single Sheet, with columns to identify school, grade level, classroom, etc. which will then allow you to filter the data by those criteria. You can use an IMPORTRANGE or QUERY formula to bring data together from multiple tabs or Sheets. 

A few other tips for structuring data sources:

• If you're concerned your existing data needs some restructuring in order to work well with Data Studio, you can try taking a look at Ben Collins' approach for 'unpivoting' data in Sheets. 
• Life is easier if your data source Sheet has a single header row in the first row of the Sheet. Merged cells or multi-row headers will need cleanup or workarounds in order to use them in Data Studio. 
• Avoid summary rows, extra text on the sheet, and blank rows or columns (blank columns will not be brought as part of a data source even if they have a header). 
• Cells should contain a single value where possible. I try to avoid 'check all that apply' type questions in a Google Form for this reason. I found this resource on CATA questions from Sheila B. Robinson very helpful. 
• If you will want to do any sort of data blending with multiple sources, make sure your Sheet includes a column with some sort of unique identifier, such as student ID number.

Many of the CSV files we get from DESE here in Massachusetts work really well with Data Studio just the way they are. (Note: I always bring CSV files into Sheets rather than using the 'Upload CSV' data connector.) Raw exports from a district's student information system or assessment database also tend to work very well, even though they might be a challenge to wrap our heads around visually. Spreadsheets such as the one below are ugly, but Data Studio loves them!

Bottom line: If you are using a Google Sheet as a data source, don't try to to make it look pretty! Spend some time ensuring your Sheet is set up to play nicely with Data Studio and it will make all the difference. A single header row with data underneath is all you need. 

Webinar: Getting Started with Google Data Studio in K-12 Education

With all of my workshops and conference presentations cancelled this spring I am really missing being able to share the joy of Google Data Studio with others! I am offering a free webinar (two date/time options) entitled "Getting Started with Google Data Studio in K-12 Education" which will be very similar to overview sessions I have presented at recent conferences. Feel free to share with colleagues too!

------------------------------

Google Data Studio, a free data visualization tool designed for business, can be used by educators to turn unwieldy data into visual and interactive dashboards. A variety of examples related to assessment, demographic, attendance, accountability, and other types of data will be presented. An overview of the steps to get started using this tool and create a simple dashboard will be shared. While this is not intended to be a hands-on session, some "getting started" resources will be provided so that participants can try out the tool on their own after the session.

Thursday, April 23, 2020, 10:00 am EST - 11:00 am EST 
Friday, April 24, 2020, 11:00 am EST - 12:00 pm EST
Tuesday, April 28, 2020, 4:00 pm EST - 5:00 pm EST

Instructor: Laura Tilton (http://tiltondata.blogspot.com, @tiltondata on Twitter)

Cost: Free!

Registration: https://forms.gle/idsxeMFDWcRQJEHw5

The session will be held via Zoom and the link will be emailed to registrants the day before the session. If you have any questions please contact me at tiltondata@gmail.com.

Collecting and Viewing Student Check-In Data

Wow! I realized it's been almost a month since my last blog post. So many things happening in the world these days as we try to reinvent education as we know it! I know many educators/schools are looking to collect information from students (and their families) as they learn remotely. I have previously written about how Google Data Studio is a great tool for visualizing data that comes from Google Forms.

If a school needed to 'take attendance' during remote learning, they might do so with a "question of the day" setup like this one that I recently created. It could also be used as just a fun daily check-in to build community or engage students. This report embeds the Google Form, and an automatically updated Question of the Day (from a Google Sheet) right into the report, so it's a one stop shop for collecting and displaying information from students.

When used with the Filter by Email feature of Data Studio, the results of a student survey could be used to display information back to a student, similar to the way Matt Heusmann, ESU 6 in Nebraska, used three different forms (check-in, journaling, and exit ticket) to populate this sample Data Studio Report.

You might also choose to check in on how students are feeling, which you could do with a tool similar to the Personality Quiz example created by Michael Howe-Ely.

Other data collected and visualized during remote learning might be usage data from a district's Learning Management System or family surveys providing feedback on the remote learning process. Jordan Benedict has an interesting blog article on 4 Types of Data to Collect During Distance Learning...what information are you collecting? Do you have any other valuable resources on this topic? Please share here on the blog, connect with me on Twitter @tiltondata, or drop me a line at tiltondata@gmail.com. Hope you are well and staying safe and sane!

A Whole New World for Google Form Data

Think about all the different ways we use Google Forms in education...assessments, exit tickets, surveys, RSVPs, logs, and more! Well, Google Forms feed Google Sheets. And Google Sheets feed Google Data Studio!

While Google Forms does have its own basic reporting tools (bar charts and pie graphs), if we attach the Google Sheet results of a Google Form to Google Data Studio, we can do so much more with the data! Not only in terms of visualizing the data, but also in terms of sharing the data. I want to present two examples that illustrate how amazing this can be.

First, an example from Craig Sheil who used Data Studio to visualize the results of a student survey about social media use at Bedford (NH) High School. In previous years, the results of the survey were shared with students via Google Slides (using screen shots of the graphs from the Form results). This year, the results were compiled into a Data Studio report, including some interactive filters, allowing for a dynamic exploration of the data.

The second example is based on work by Elissa Malespina who created a Google Data Studio report of student reading log data back in October of 2018 (while at Somerville Middle School in NJ). When I saw her example, I was really excited to see Data Studio being used with students (and amazed at how ahead of the times Elissa was!). However, I knew that some of the Data Studio updates since 2018 could really enhance what she had already started. I reached out and she agreed to share the (anonymized) data with me, so that I could update her report with some new features.

I've extended the example to illustrate how one data set could be used to display data to different audiences. The first page shows the school-level data, with lots of built-in interactive filters. The subsequent pages show classroom-level data and student-level data, which could be built with either report-level filters or using the new Filter By Email feature in Data Studio.

What Google Form data do you have that might lend itself to a Data Studio report? I'd love to find some more examples of student-submitted data to use to build reports! Or, if you've already built a report, please submit it to the K-12 Data Studio Report Collection!