Showing posts with label Filter by Email. Show all posts
Showing posts with label Filter by Email. 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 27, 2020

Filter By Email: Try it for yourself!

By now you probably know how much I love the Filter by Email feature of Data Studio, which was released in February 2020. This feature allows us to create Data Studio reports which ONLY display specific data based on the logged-in user. I realized, however, that I hadn't really posted any concrete "how-to" documents illustrating how it works - mostly because any example would need to be customized to include a user's email address in order for that user to see how it works.

So, I've put together an example data set and example report, with instructions on how to copy these and modify them so you can try this feature out for yourself! For this example, we will use some student reading log data (submitted via Google Form), and use it to create a reading log that only displays data for the logged in user.


Instructions:

1. Make your own copy of the example data set (and name it so that you know that this copy is yours!) This data set contains student reading log data for all students in a single class.

2. Check out the sample report and note that it currently displays all of the books read by the entire class (82 books). 
3. Make a copy of the report by clicking the Copy button in the upper right corner. 

4. The "Copy this report" dialog box will appear. This is where you'll connect your copy of the data to make your own copy of the report. To do this, under "New Data Source." select the drop-down menu and select "Create New Data Source."

5. Select the Google Sheets option, then select your copy of the example data set (see screen shot below), and click Connect in the upper right corner of your screen.


6. On the next screen, you will see a list of all the fields from your Sheet that are available for your report. Select "Filter by Email," check off "Filter data by viewer email," and click the button that says "Select Email Field." 

7. Choose Email as the field you would like to use and click Done and then Close in the upper right corner.

NOTE: If, when you click "Select Email Field," in Step 6, you are not able to choose Email as the desired field, then uncheck "Filter data by Viewer Email" and click Done and then Close in the upper right corner. Then, from the Resource menu, choose "Manage Added Data Sources," and Edit the data source to return to the screen for Step 6 and try again.

8. When asked whether you'd like to grant consent to access your email address, choose Allow. This step is what permits the report to use your logged-in email address to display specific data.

9. Click the View button in the upper right corner of the report, and note that the report contains "No Data." This is because you are logged into your own Google account, but the sample data set does not contain your email address anywhere in the Email field. Let's change that!

10. Open your copy of the data source Sheet. Locate all rows that contain Lana Mulherin's reading log information (they are colored yellow so you can find them easily.) Replace Lana's email address with your own in all 8 places. If you want to test this with an additional Google account, replace Sergio Morton's (blue) email address with a different email address.

11. Go back to the Data Studio report (which should still be in View mode) and click the refresh data button (round arrow) in the upper right corner of the screen. The report should now display the data for only the rows where your email address appears (Lana's list).

12. If you like, share the report with your second Google account (as view only) and see how the list that appears is different (Sergio's list.) 

13. Note that the report viewer does not need to be granted access to the spreadsheet, only the report. If the report viewer's email address is not contained in the Sheet, the report viewer will see no data. However, I would still recommend sharing the report with specific Google accounts (and/or just a domain) rather than "anyone with the link can view." Also, before you ask, the feature does not support using multiple email addresses in a cell.

I am sure you can think of lots of ways this can be used in K-12 education, particularly with Sheets that results from a Google Form where the user's email address is captured automatically. This example uses student email addresses, but you could also use teacher emails in order to only share data with a single teacher! Please let me know what you come up with.

Resources:

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!

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!



Friday, February 14, 2020

Public K-12 Report Collection / Data Studio Filter by Email

I realized that it's been a few weeks since I posted an entry on the blog; mostly because I have been sharing small updates more regularly on Twitter. Please be sure to follow me @tiltondata for additional updates related to using data in support of teaching and learning, particularly with ideas related to the use of Google Data Studio!

Speaking of Data Studio, I have launched a new public K-12 Data Studio Report collection with entries from a number of districts and organizations nationwide. (I previously shared information about a collection of just my own reports, but wanted to expand this idea to a larger library.) I hope you'll check it out, and maybe even submit an entry of your own! http://bit.ly/k12datastudio

We received exciting news from the Google Development team yesterday, and that is the introduction of the Filter by Email feature in Data Studio. This feature provides "row-level" security to the data source that underlies the report, when the data source has a column/field that contains email addresses (one per row is currently supported). This means that we can create dashboards that will change based on WHO is logged in! Previously, the way to accomplish user-specific dashboards required using BigQuery as a data source and was a much more complex process, so this is a very welcome new feature!

You can read more about this new feature in the Google Help documentation here: https://support.google.com/datastudio/answer/9713766. 

I invite you to try out my very basic "proof of concept" dashboard which displays the data resulting from a Google Form in both a filtered and unfiltered way. (You will need to be logged into a Google account to access it.) I hope to share additional (more edu-specific) ideas and templates in the coming weeks.

Want to learn more about using Google Data Studio in support of teaching and learning? Check out the list of events below, or reach out if you'd like to host a workshop for your district or collaborative.