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! ♥