Showing posts with label Google Forms. Show all posts
Showing posts with label Google Forms. Show all posts

Friday, February 12, 2021

Google Forms + Data Studio = A Match Made In Heaven!

Since it's almost Valentine's Day, I thought I'd highlight one of my favorite couples - Google Forms and Google Data Studio! 

Google Form data (saved in a Google Sheet) is a GREAT starting point for educators looking to get started creating a Google Data Studio report. Why? One main reason is that the structure of the data works well as a Data Studio data source, since it has just a single header row, no merged cells, no blank rows, and consistency in field values.


Here are a few tips for developing Google Forms to use with Data Studio:

  • Use very short questions. Try using just 1-2 words in the Google Forms question field, then and putting the full details of the question in the description. These questions become your column headers in Sheets and thus your field names in Data Studio, and this makes them much more manageable.
  • Avoid "Check all that apply" type questions. Try using a multiple choice grid type question (with yes/no answers) instead. This makes the data more compatible with filtering in Data Studio.
  • Build a sort order into your responses. If the responses to a question have a particular order (like Daily, Weekly, Monthly), try including a sort order in the responses (1-Daily, 2-Weekly, 3-Monthly) so that when you sort in Data Studio they sort correctly. (Yes, you could do this with a calculated field in GDS too, but why make things harder if you don't have to?) Linear scale questions are a good option for this too, especially if you want to report on the average response number.
  • Build your report first based on sample data. I will often build my report BEFORE sending out the Form, using the Form Responses tab with some rows of sample data (I usually try to add enough sample data that all possible answers are in the data set for each question.). I then remove my sample data once real data starts coming in.
  • Take advantage of "live" updating. If you've built your Data Studio report, and the data continues to come in via the Form, the report will continue to update (automatically every 15 minutes). This is great for "leaderboard" type reports when you've built out the report ahead of time (see previous bullet)
  • Capture email addresses. If your data source contains email addresses, you can use the Filter by Email feature to create customized reports. Or, you can use the email addresses as a unique identifier to pull in additional information on top of what's collected in the Form.
Here are some of my favorite examples of Data Studio projects that use Google Form data:

Do you have any other great examples? Please feel free to submit them to the K-12 Data Studio Report Collection!

Oh, and Happy Valentine's Day! ♥

Monday, July 13, 2020

Simple Row-Level Display Control


We've seen a number of Data Studio report examples that display information that might be collected via a Google Form. But what if you don't want the Form information to display in your Data Studio report immediately? What if it needs to be "approved" first, or what if you want to be able to show/hide different entries from your data source at different times?

Some examples of reports that could benefit from this functionality might be:
Fortunately, there's an easy solution for this - built-in report filters! As I outlined in my Fun with Filters blog entry, built-in filters (not filter controls) are a way to restrict the data appearing within a report or report element on the "back end."

I'll use the Staff Directory report as an example. To control which entries from my Sheet data source are displayed in my report, I needed to do two things.

Step 1: I added a column called "Include?" to my Google Sheet data source, and I marked the rows I want to include with "y". In this case, I didn't include a "y" next to Al Banks, Director of Support, although his data is still included in my source Sheet.

Step 2: I added a filter to the report to specify that only rows where "Include?" is equal to "y" should be included in my report. I did this from File > Report Settings > Add a Filter, which will put the filter on the entire report (alternatively, the filter could instead be added to individual report elements like tables and filter controls.)



You'll notice that now the report does not include Al Banks, Director of Support, even though his information is included in the data source, since this filter has been applied. If I wanted to include him in the list, I would simply add a "y" in the "Include?" column in the data source, and Voila! He'll be back in the report.

This is a very simple, but very useful, way to control the data that is displayed in a report at the row level on the back end. BTW, I use this simple approach for the entries that are included in the K-12 Data Studio Report Collection, which allows me to approve form-submitted entries before they are included.

Wednesday, May 27, 2020

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!

Friday, May 15, 2020

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




Thursday, April 16, 2020

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!

Thursday, February 20, 2020

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!