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.

https://datastudio.google.com/reporting/21e456f3-3d89-4ef0-bc29-34d785553638/page/xVyEB

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.

Upcoming Google Data Studio Workshops:

• 3/31/2020 @ The Education Cooperative (Walpole, MA) 

Upcoming Conference Presentations:

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

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) 

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

Upcoming Conference presentations:

• 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 http://bit.ly/tiltondata.

Google Data Studio Report Catalog

I've been developing (and blogging about) Google Data Studio reports for Massachusetts schools for about a year now, and I realized it was becoming difficult to locate specific reports since they were embedded in blog posts. So I've created a searchable "report of reports" catalog with links, so now the individual reports are much easier to find!

 Please visit http://bit.ly/tiltondata and check it out! Please feel free to provide any feedback, or ideas for new reports and visualizations. You can find me on Twitter sharing additional resources @tiltondata.

School Attending Children Report - updated with 2019 data!

The School Attending Children report is submitted to MA DESE each February by each town's school department. Each town self-reports the enrollment of all full-time students whose parents or legal guardians are residents of the city or town (not just students for whom the district is financially responsible.) Categories include private and parochial schools, collaboratives, out-of-district public schools, home schooled, charter schools, and local public school districts (including regional and voc-tech districts).

DESE makes this data available online but it is challenging to look at the information longitudinally to understand how populations have changed over time. Google Data Studio can help! Click the image below to explore the data for any Massachusetts town.

School Accountability Percentile Breakdown Report

If you've been digging in to the state accountability data for your district and school, you know that it's very complex and that there are a lot of numbers to look at! Each school receives an overall percentile (as well as subgroup percentiles) which measures the performance of students in a school (overall or in a subgroup) compared to other schools (or subgroups) in the state in one of three categories: high schools, non-high schools, and middle-high or K12 schools. These percentiles, from 1 to 99, are calculated using the percentiles of each of the individual accountability indicators for a school or subgroup through a complex weighting process. The percentiles piece of the accountability system is called the normative component of the system.

There is lots of accountability data available in School and District Profiles, but the percentiles available there are just overall percentile and subgroup percentiles. The individual percentiles of the accountability indicators (like ELA performance, graduation rate, chronic absenteeism, etc.) for a given school or subgroup are harder to find. But wouldn't it be nice to know WHICH of these indicator percentiles is particularly low or particularly high for your school?

I've created a new report in Google Data Studio which will allow you to easily explore this data (which DESE makes available as several very large public spreadsheets) for a given school and group. When using this report, please make sure you choose one district, one school, and one group. If you do not select all three, the data displayed may not be correct.

Holy Data Gamechanger! MassCUE, Conditional Formats, and More

I had the opportunity to present at the MassCUE conference this week (Harnessing the Power of Google Data Studio in Education) and was thrilled that the session was standing room only! Educators are hungry for simpler, more powerful ways to explore their data, and Data Studio is an amazing solution. My favorite Twitter response to the session? "Holy Data Gamechanger!" Pretty accurate, I'd say!

Speaking of gamechangers, this week Google added conditional formatting to Data Studio! Now we can color code tables or scorecards in Data Studio based on the value. Currently only 10 conditional formatting rules can be applied, so adding the same formatting to science in the same table right now isn't possible...hopefully this will be expanded in the near future.

Speaking of Twitter, I hope you'll follow me @tiltondata for resources and ideas related to data use to improve teaching and learning - Google Data Studio and beyond!