One word that never ceases to fail in making district admins either cringe in anticipation or squeal with delight is data. It’s never an easy task to compile and analyze the hundreds of datasets at a district’s disposal, but once you have it, data analysis gives you an unadulterated view of your district – a baseline by which to measure growth, an indication of the success or failure of district initiatives, and early detection of under-performing or at-risk students.
There is an abundance of data analysis products out there, many of which are geared towards enterprise organizations, or wildly cost-prohibitive for education clients. If you’re looking for an easy way to get started with data visualization, or you don’t have the time to implement a rigid software solution, Google has you covered.
Data Studio is Google’s free data visualization tool that is compatible with a variety of common data sources (and some less common ones, too). Whether you are a tech specialist who wants to keep a pulse on Chromebook usage or a superintendent who wants to analyze student behavior and assessments, Data Studio can provide a quick, easy way to bring your data to life. Check out this example Attendance Equity dashboard to get a feel for the kind of data analysis that’s possible with Data Studio.
Why Data Studio?
There are a wide variety of paid solutions for data visualization in the education market, so why would school districts want to use Data Studio? Many of those I have worked with have reported that big name products are unwieldy, and require a lot of time be spent on data cleansing and manipulation. In addition, the built-in analyses are often rigid and difficult to customize to meet their district’s needs. Enter Data Studio.
Data Studio pulls from data sources that you already have at your disposal – csv’s, Google Sheets, Google Analytics, BigQuery, and more. Reports are stored in Drive and are shared like Drive documents with view or edit access. Additionally, you control who can access your Data Sources, which are stored and shared separate from the Reports. You can also embed a report into an internal or external website, giving end users the ability to explore the report without having direct access to it or your data.
Creating a new report is as simple as clicking on the “+” icon and attaching a data source. Once you’ve started the report, you can add additional data sources and manipulate your data in order to create tables, graphs, and scorecards that meet your needs. Below, I’ll explore what you need to know about connectors and data manipulation in order to quickly and successfully create meaningful reports for your district.
Data Studio Connectors
Connectors refer to the type of data that you are bringing in to Data Studio. The most widely-used connectors in education are File Upload, Google Sheets, and Google BigQuery. As you can see in the graphic below, there are dozens more connectors available.
First, let’s talk about File Upload. This is a great connector to start with, as it’s as simple as attaching a csv of data from your SIS, state data management platform, or finance system. But it’s more than that – you can attach multiple csvs, essentially building a mini cloud database of relative data. As long as the headers in each file are identical, you can build a repository of data that can be analyzed across a variable, like a date (think daily attendance). You will run into some size limitations if you have particularly large data uploads, and of course, uploaded data is static.
If you want to play around with dynamic data, the Sheets connector is a great place to start. Once you connect to the report, any changes that you make in the Sheet will be reflected in the graphs that you create. When you connect a Sheet, each individual tab in the Sheet is a separate data source, so you may have to create multiple data sources per Google Sheet in order to access all of its data. Like the File Upload connector, you will begin to run into size limitations with Google Sheets that will affect the performance speed of your report.
If you have large amounts of data that you want to connect to Data Studio, the easiest way to do so is by using one of the Google Cloud Platform (GCP) connectors, like Google BigQuery. BigQuery is a cloud database that allows you to easily store and query very large data sets. It utilizes SQL command line to create tables and views that can then be individually connected to Data Studio, illustrating only the data you need in the report. One thing to note about BigQuery is that it is not a free solution – GCP services are paid based on use. So for BigQuery, you don’t pay for storage (up to 2TB), but you do pay for queries on your data. You can learn more about GCP and sign up for PO-based billing with Amplified IT.
Something else to consider when using BigQuery is how you’re going to get your data into tables. We recently released an update to our automated data delivery application, Local Hero, that solves this problem by allowing you to push local data, like SIS exports, to existing BigQuery tables. Check out the Labs site to find out more about how Local Hero connects to BigQuery, or request a quote and trial of Local Hero for your district.
Amplified Labs Gopher Connectors
Data Studio connectors are not relegated to native Google platforms – partner connectors are created by Google partners and produce templated reports around specialized sets of data. Amplified Labs currently has two partner connectors – Gopher for Chrome and Gopher for Users.
With just a few clicks, the Gopher for Chrome connector creates three different dashboards using data gathered by the tool – Chromebook Utilization, OS Version, and Auto Update Expiration. Each of these dashboards are powered by auto-refreshing domain reports created by the Gopher for Chrome Sheets Add-on and requires a trial or paid version of the tool. Request a quote or a trial of Gopher for Chrome if you want to get started creating dynamic Chromebook dashboards – a great way to get your feet wet with Data Studio!
Like the Gopher for Chrome connector, the Gopher for Users connector quickly creates an auto-refreshing dashboard around your G Suite user data. This dashboard contains user Login Latency, Admin Security, and Address Book Visibility reports. This connector also requires a trial or paid version of the Add-on – request a quote or trial of Gopher for Users to get started.
One of the best things about Data Studio is the ability to easily manipulate the data without altering the original data set. You can apply filters to only show certain data, use functions to create the breakdowns that you need, and even utilize SQL syntax to manipulate the data output. Let’s break this down.
Filters in Data Studio have two functions. Report-level (or page-level) filters allow you to filter the view of all of the affected data on the page in order to return only the data that you want to see across multiple graphics, or to compare multiple data points at the same time. This type of filter is great for analyzing the performance of students in a certain school site, or for comparing students across all of your elementary schools. This is essentially a filtered view of your report.
Data filters are another way that you can manipulate your data set to return only the data that you want to see for any given individual chart. For example, your data may contain “null” returns that you do not want to illustrate in your graph. In this case, you can apply a data-level filter to easily include or exclude data based on a set parameter, like “Exclude where Column A is Null.” You can then apply this data-level filter to multiple graphs in the report.
When your data just barely falls short of containing everything that you need for your dashboard, calculated fields can be an easy way to clear the final hurdle by creating custom aggregations of your data. These are essentially creating new columns in your data set, but only in Data Studio. The original data, whether it’s an upload or a connection to a Sheet or BigQuery table, is not altered.
Many of the basic spreadsheet functions, like arithmetic functions, COUNT, and COUNT DISTINCT can be extremely helpful in creating data breakdowns that are needed for district dashboards. Think attendance percentage (total days in attendance/total enrolled days). I’ve found that most student information systems do not provide these big-picture numbers, but do give you the basic data points that you need in order to arrive at them – calculated fields can do the rest!
Taking it a bit further, you can also use some basic SQL syntax, like CASE statements, to manipulate your data output. You can clean up your data for digestible end-user consumption (like transcribing demographic codes from the SIS into their alphabetic counterparts), or merge existing data points to create a new breakdown (classifying all students in grades K-5 into elementary students).
A recent beta addition to Data Studio, the Data Explorer, allows you to explore your data set without actually creating a report. This can be a great way to gain insight into and get comfortable with your data before you spend the time on creating a new report or modifying an existing one, and they are private. If you find that you want to use an exploration in one of your reports, you can easily export it as a chart.
Data Studio Updates and New Features
Since Data Studio’s general release in September, there have been a couple of big updates that translate into the education space – PDF report downloading and blended data. If you want to keep your finger on the pulse and also review previous updates to Data Studio, you can see the complete Data Studio Release Schedule in Google’s help center.
PDF Report Downloads
A lot of times, you may find that you (or end-users) want to capture a static snapshot of your dashboard. You can now save your report as a PDF, define the pages to include and their order, and add password protection to the download. You can learn more about this update in the Data Studio Help Center.
This is a big one! This summer, Data Studio released the ability to blend up to five datasets together to create a unified Data Source. Blended data uses a left outer join operation based on a common join key (think student ID – connecting multiple SIS table exports), or multiple keys. You can apply filters to a blended dataset, but you cannot create calculated fields in the Blended Data Source, though calculated fields from any of the individual data sources can carry over to the blend. To learn more about blended data, check out the Data Studio Help Center.
If you want to see how all of this comes together, Andrew Stillman, Chief Product Officer here at Amplified Labs, gave a great introductory deep dive into Data Studio this past summer. It gives you a glimpse under the hood and a walk through of some of the features I mentioned in this post.
Check out our Gopher for Chrome and Gopher for Users connectors to get started creating quick and easy dashboards. If you want to take it a step further and create automated delivery of your local data to Sheets or BigQuery, check out our Local Hero app.
And finally, if you’re interested in how we can help you with your own Data Studio builds, check out our website and express interest in custom dashboard support. I’ll get in touch with you, and we can explore how Amplified Labs can help you create your own district dashboards!
Find this article useful? Share it!
Data, Implementation, and Engagement Consultant
About the Author:
Melanie lives in Virginia and is based in Amplified IT’s home office located in Norfolk. One of the first members to join the Amplified IT team, Melanie has worn many hats at the company. She most enjoys interfacing with customers and helping them implement tools that solve common pain points and frustrations. Today she leads the onboarding and interfacing with Labs tool clients, making lives easier and breezier one implementation at a time.