Monday, December 14, 2020

Dynamic Dimension Parameters!

Have you ever wanted the user to have the ability to select which fields are utilized in a Data Studio chart? For example, suppose you wanted to look at the breakdown of a school's population by various dimensions, such as Special Education status. Gender, ELL status, High Needs status, Economically Disadvantaged status, and Title I status. In the past, you would have needed to create multiple charts, one for each dimension...making for a very busy report!

Parameters are a relatively new feature of Data Studio, and have a wide variety of uses. Essentially, they act like variables to make your report even more flexible and interactive. Here, I'm going to walk you through how parameters can be used to choose the dimension for a pie chart in a Data Studio report, allowing all the options above to be displayed in the space of a single chart. Take a look at the following simple example to see how this operates:

Cool, right? Here's how it's done.

The data source Sheet contains a list of students, plus columns for various demographics: Gender, Special Education Status, High Needs Status, Economically Disadvantaged Status, English Learner Status, and Title I Status.

I created a Parameter called Demographics (Resource menu, Manage added data sources, Edit, Add Parameter) and gave it a list of values with the same names as my fields.

Note that parameters appear as purple in the field list, whereas fields appear green.

Then, I created a calculated field (which I also called Demographics) which contained the parameter. I did this because I want to use it in a CASE statement, so it needs to be a field rather than a parameter.

Finally, I created a calculated field called Demographics Selection using the following CASE statement: 

To create the simple report linked above, I created a filter control (Fixed-size list) based on the Demographics parameter, and also a pie chart where Demographics Selection is the dimension and Record Count is the metric.

Interested in learning more about using parameters within Data Studio to dynamically change dimensions? Check out the following blog posts which are what helped me figure this out. How can you imagine using this feature? Let me know!


Monday, November 23, 2020

Happy Thankgiving!

There is always something to be #thankful for! Share your thanks with this fun #Thanksgiving dashboard using #GoogleDataStudio and #GoogleForms. 

Happy Thanksgiving!

Saturday, August 29, 2020

Christmas in August?!?

Anyone reading this who works in a school district is probably running around like crazy preparing for the start to a very different school year. You may not have seen all the AMAZING updates to Data Studio which have appeared over the last two weeks, but have no fear, I'm here to fill you in! (Note that there are other new features but they aren't necessarily relevant to the use of Data Studio in K-12 education so I haven't mentioned them here.)

New Filter Controls
Previously, if you added a filter control to a Data Studio report, the default was a drop-down list, which could then be configured to (among other things) a fixed-size list. Now, fixed-size lists are one of six filter control types, with new options including a text input box (which you can configure to use EQUALS, CONTAINS, STARTS WITH, etc.), slider, and checkbox. These various options can be used to make your reports even simpler and easier to use! more info here

I had a hard time wrapping my head around this one at first. Basically, it's a way to introduce a variable into your report that the user can change...which allowed me to create this cool algebraic Slope-Intercept explorer (which had been in my head a while but I had not figured out how to make it work with Data Studio.) The way this report works is that the data source is a range of x values, slope (m) and intercept (b) are parameters that the user can change, and then y is the result of a calculated field.

The report is just a scatter plot of x and y, which changes when the parameters are updated by the user. This is an example of a metric/numeric parameter - I am learning about some interesting ways to use dimensions/text as a parameter as well! more info here

Conditional Formatting in Pivot Tables
Previously, conditional formatting wasn't possible in Data Studio pivot tables, only regular tables. Now, pivot table conditional formatting options are single-color, so still limited, but it's certainly a huge improvement! more info here

Simplified CASE Statements for Calculated Fields
CASE statements are like IF/THEN statements in Data Studio that allow you to transform a field's values within a calculated field. In the past, you would write a CASE statement like this:

  WHEN field=value1 THEN result1
  WHEN field=value2 THEN result2
  WHEN field=value3 THEN result3

Now, you would write this:

CASE field
  WHEN value1 THEN result1
  WHEN value2 THEN result2
  WHEN value3 THEN result3

Honestly, isn't that big of a difference, and most of the time when I write a CASE statement I have to look up the syntax anyway, but it's definitely simpler to write statements this way. more info here

Filled Maps
There is a new option for "Filled Areas" within the Google Maps visualization. I updated the Massachusetts Cities and Towns report to show how this might work. Tip: if you want to sound super smart, use the word "chloropleth" instead of "filled area map." more info here

Embedded Data Sources
As you may know, until now, Data Studio reports and data sources were shown as separate items when on your Data Studio home page. If you wanted to share both a report and a data source with someone - you needed to share both items with the other person. Now, you have two options:

a) Create your data source first, then create a report and attach it to your data source. In this case, the data source is reusable, which means it's a separate item from the report. It can be used elsewhere but would need to be shared explicitly with someone who needs access. You'll still see the data source listed on your Data Studio home page.
b) Create your report first, then create the data source from within the report. In this case, the data source is embedded, which means you can share the report and the data source all together. You won't see the data source listed separately on your Data Studio home page.

Just a note here that in either case, a person with view-only access to a report still does NOT have access to the data source, regardless of whether it's reusable or embedded! These really only come into play when you are sharing a report and data source for someone else to edit or copy. more info here


I look forward to seeing how YOU use these new features! (Particularly parameters which have TONS of potential!) As always, if you have new reports to share, please submit them for inclusion in the K-12 Data Studio Report Collection (recently added to Google's Data Studio Gallery) so we can all learn from each other. Best wishes for a smooth and healthy start to the school year!

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.


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.


Friday, July 17, 2020

Custom Bookmark Links in Data Studio Reports

When I first started using Data Studio a few years ago, the idea of creating a single report that could deliver different information to different users was really just a pipe dream. Since then, there have been some fantastic approaches developed through the use of BigQuery and more recently through the Filter by Email feature built into Data Studio. 

One downside to both of these approaches is that the data source needs to list one user per row of data. So, if I want two people to have access to a particular set of data, I have to double the number of rows of data....three people, triple...and so on. Not ideal if I want to make a subset of my data accessible to multiple people.

One useful feature of Data Studio is found in under File > Report Settings, where there is an option to "Enable viewer filters in report link" in order to create custom bookmark links. Essentially what checking this box does is modify the report's URL so that it reflects any filter controls that are selected. When the modified URL is sent to a viewer, the viewer sees the report with the selected filters in place.

It's a great way to share a specific report view with a targeted group of people. I've enabled custom bookmark links in my Massachusetts Cities and Towns report as an example. This report allows us to choose any town in Massachusetts to see information including its location on Google Maps and town seal. 

The "main" URL of this report is as follows:

However, if we choose Plymouth from the list of towns, the URL changes:

That's an awful lot of gobbledygook, but you can see Plymouth now appears in the URL. If I sent that link to a colleague in Plymouth, it would take them directly to the Plymouth-selected page. 

Now, imagine that you had a report which contained data for a number of schools in your district, and you wanted to create a link specific to each school to send to that school's principal. You could enable custom bookmark links which would then allow you to create a link specific to each school.

However, if you didn't want one school to have the ability to access another school's data, it gets a little more complicated. There would have to be a filter control on the original report allowing you (the report author) to choose the school in order to create the custom report link. You could make that filter control inaccessible by either moving it off the report canvas OR you could put a rectangle over the top so that it couldn't be clicked and changed. (You would do those things after you had generated all the school-specific URLs.)

But not being able to access the filter control still doesn't totally solve the issue. A savvy user might notice that the URL contains the name of the school (the way Plymouth appears in the URL above) and could still change it within the URL to get different data. (You can try it above by copying and pasting the Plymouth URL and substituting "Plymouth" with another Massachusetts town name.)

There's a way around this, if you set up the report to pass "secret codes" instead of filter values, and set up a default value to avoid URL tampering, both of which are explained further in this article. I set up a "proof of concept" report here if you'd like to take a look. Although I am not explaining the nuances of this advanced approach in this blog post, I hope these resources are helpful to anyone who wants to explore this concept further. I'm happy to share more info, just ask!


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, June 17, 2020

Fun with Filters!

One of the most valuable features of Data Studio for looking at education data is the ability to include filters which allow users to "slice and dice" the information on the screen to answer their own questions. However, there are several features within Data Studio that reference the term "filter" and so I thought I would write up some clarification plus a sample report, since it can get a little confusing.

Dimension Filter icon Filter Controls are elements that can be added to any Data Studio report, using the Filter Control button in the toolbar. The default Filter Control is a multi-select pop-up type, but it can be configured in the Style pane with a number of options. Filter Controls can be single-select or multi-select, can include a Search box, can be a pop-up or clickable type, and can include filter operators such as EQUALS or CONTAINS. Of course, color, font, and size are all customizeable as well.
Data Studio Help: Filter Controls

Chart Interaction Filters allow report authors to turn charts into interactive filters. For example, if your report contained a pie chart, this could be made into an interactive filter allowing the user to click a section of the pie chart to filter the information in the report.
Data Studio Help: Chart Interaction Filters
Treemap as filter menu from Mehdi Oudjida

Filters are a way to restrict the data appearing within a report or report element on the "back end." Filters themselves are not interactive (although you'll often hear people refer to a Filter Control as a Filter, hence the confusion.) For example, if you wanted to include a Scorecard element which showed a count of only the students who scored in a particular category, you could put a Filter on the Scorecard to include only those students, and the count would reflect it.
Data Studio Help: About Filters

Filter by Email is a relatively new feature of Data Studio that uses the user's Google account to display a custom data set based on an Email field in the data source.
Data Studio Help: Filter by email address

I created this sample report (also linked to the image above) to show examples of all the elements discussed here (except Filter by Email), so you can see how these different filters work. Obviously, you wouldn't use them all in one report for the same dimension, so this report is really just for fun.

Let me know.. how do YOU like to filter?

Thursday, June 4, 2020

Visualizing Plans for the Class of 2020

Many high schools publish a list of the plans of their graduates, but wouldn't it be fun to visualize this list on a map, to see where a school's students will disperse after graduation?

Here's a very simple template for any high school who wishes to display the plans of their 2020 graduates in a fun visual map format. Clicking on a row in the table will zoom in on the Google map to show the location, and the user can even use the "Pegman" to take a look around at Street View. Clicking the row again will bring the user back to the entire list/map.

To make your own version of this report, first create a simple Google Sheet with a single column with a header of "List." This column should have a single entry related to each student in the class, although the list would not include student names. So, for example, if two students are attending Cape Cod Community College, then "Cape Cod Community College" would appear twice in the first column of the spreadsheet. It doesn't matter what order the list is in. Most high school guidance departments would be able to export this list from Naviance or other college and career platform.

 After the Sheet is put together, you can make a copy of the report by clicking Use Template in the upper right corner of the report link. You can then connect your new Sheet as the data source for your copy of the report. If you need additional information about how to connect a report to a data source, please refer to these instructions.

You will want to check that the List field is of the type Geo > Address. You can check this through the Resource menu > Manage added data sources > Edit and check the Type column for the List field.

A few notes: Some Sheet entries may need to be tweaked to display correctly. For example, there may be multiple colleges with the same name (like Trinity College) so you may need to add a city/state in order for it to display correctly. Non-location generic entries such as "2yr college", "Employment," etc. will not display on the map but will display in the list.

You can then customize the colors, logos, etc. of the report to make it your own. (Tip: Try Extract Theme from Image using your school's logo!) I would love to see what you come up with - and congratulations to the class of 2020!

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

Wednesday, April 29, 2020

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!

Tuesday, April 21, 2020

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. 

Friday, April 17, 2020

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 (, @tiltondata on Twitter)

Cost: Free!


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

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

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:

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.

Thursday, January 23, 2020

In-Field and Out-of-Field Educator Designation Report

Over the past week, school districts in Massachusetts received information as to whether their teachers are designated as "in-field" or "out-of-field" for the courses they are teaching. Under new requirements under the Every Student Succeeds Act (ESSA), the Department of Elementary and Secondary Education annually summarizes this information and posts it in School and District Profiles and School and District Report Cards.

This information was delivered in an Excel spreadsheet to the DESE Security Portal > Drop Box Central > EPIMS File Exchange, and has the file name like In_field_data_2019_xxxxxxxx.xls. If you convert this file to a Google Sheet, you can use it as the data source for a simple report I've created to explore this data.

This report uses a pivot table as a filter, so that you can click anywhere in the summary table to filter the data. In the example below, I've clicked the "N" column for Sample1 School, and the list below updates to show the 6 educators who are Out-of-Field and why.

Feel free to check out the report template (which contains sample data), and follow the instructions to connect your own Google Sheet as the data source to create your own copy of the report. As always, please make sure the link sharing settings on your reports and data sources are are turned off, so that if you need to share the file you are sharing only with designated, authorized individuals.


Upcoming Google Data Studio workshops:
2/11/2020 @ Southeastern Massachusetts Educational Collaborative (Dartmouth, MA)
3/31/2020 @ The Education Cooperative (Walpole, MA) 

Upcoming Conference presentations:
2/28/2020 @ METAA CTO Clinic (Milford, MA)
3/6/2020 @ MASCD and MassCUE Leadership Conference (Worcester, MA)
3/27/2020 @ Medfield Design Your Learning Day (Medfield, MA) 

Tuesday, January 14, 2020

Simple School or District Staff Directory Tool

Here's an idea for a simple Google Data Studio tool that your school or district may find useful! Not all staff members have access to the Staff module of your SIS (such as Aspen), so answering questions like, "Who are all the reading specialists in the district?" or "Who are the members of the math department?" or "What is Mrs. Smith's room number?" might not be all that easy to answer without asking a secretary or an administrator. Here's an example of a district/school directory using Google Data Studio. Users can search/filter by school, name, department, or job title, or sort by clicking the column headers.

Want your own version of this directory? This is a great project for anyone new to Google Data Studio.

First, create a data source by pulling the following fields from the Staff listing in your SIS and placing them in a Google Sheet (you don't need to share it). Make sure the column headers match this list exactly (feel free to make a copy of my sample data source to use as a template):
  • First
  • Last
  • Email
  • Ext
  • Room
  • Position
  • Department
  • School
Next, access the example above and click the Use Template button in the upper right corner of the screen. Follow these instructions to connect your Google Sheet to the report.

Finally, you need to decide who you would like to access your new report. Use the "Share" button to share as view-only with specific individuals, with everyone in your GSuite domain, or with anyone with the link. If this is a district or school directory, I would suggest sharing it with the domain, so that only members of your school or district will have access to the directory when they are logged into their school accounts.

As the editor of the report, you can then modify the color scheme to match your district's colors, add a logo or text, or make other modifications as desired. You can also embed the report into a web page for easy access.

You will need to manually update the Google Sheet on the "back end" periodically to make sure the report is up to date. I hope to be able to share in the near future a solution for automatically updating a Google Sheet from your SIS, so stay tuned!


Upcoming Google Data Studio workshops:
  • 2/11/2020 @ Southeastern Massachusetts Educational Collaborative (Dartmouth, MA)
  • 3/31/2020 @ The Education Cooperative (Walpole, MA) not yet open for registration
  • 2/28/2020 @ METAA CTO Clinic (Milford, MA)
  • 3/27/2020 @ Medfield Design Your Learning Day (Medfield, MA) 
Lately, I have been tweeting more and blogging less, so please follow me on Twitter @tiltondata to stay in touch! As a reminder, you can find my complete Google Data Studio report catalog at