Google Data Studio
Data has become increasingly important in education because it not only determines allocations that schools receive, but it also helps them make informed decisions on student and teacher programs and support. The other week we discussed types of useful data, how you can review/analyze it and what you can do with it in this post. This week we will look at one specific (and free) tool that you can use in order to create custom reports that are visually appealing and easy to read.
There are four main steps to creating a new report with Data Studio:
Collect Data + add it to Google Sheets
Make data as raw as possible
Sign in to Google Data Studio and connect Google Sheet
Create report by adding graphs, charts and filters
Collect Data + add to Google Sheets
The first thing we need to do is collect the data and add it to Google Sheets. For this report, I will be looking at the 3-8 scores on the ELA and Math exams for New York State. I was able to download the data from a NYS website here.
Since there are 439,658 rows of data in this particular sheet, I will only take a select amount of data that shows state and regional information, and copy that to a Google Sheet. This will reduce the number of rows from 439,658 to 1,608, which is much more manageable.
Now that we have the data in our Google Sheet, we can clean it up a little by deleting unnecessary columns, and ensuring that the headings make sense to the user since this is what they will see. Therefore, I will change the headings from L1_PCT to Level 1 Percent so it is clear to whoever views the report.
Make data as raw as possible
Before we can connect the sheet to the Data Studio, we need to ensure that the data in the sheet is raw. Raw data means that the information has not been processed or revised, and most importantly, that it is in the form of numbers. Since Data Studio will take the raw data and "make sense" of the information by creating charts and graphs, it is important that the data is in the form of numbers so this can be done. For this particular sheet, much of the data is raw, so we will not need to edit anything.
However, one example of when you would need to edit the data and convert it from words to numbers would be if you had a list of teachers and their ratings. If the ratings were listed as highly effective, effective, developing and ineffective, you would need to convert all of the highly effectives to a 4, the effectives to a 3 and so on in order to have the data in the form of numbers.
However, for this data, we can leave it as is.
Sign in to Google Data Studio and connect sheet.
In order to create the report, you will need to log into Google Data Studio with the same account that you created the Google Sheet with.
Next, you will create a new report by pressing the blue + button in the bottom right hand corner.
Now you will need to connect the Google Sheet from earlier by clicking "Create New Data Source"
Then choose Google Sheets on the left hand side, select your sheet and press connect.
Now, you will be able to see all of the headers from the Sheet, and you will need to choose the type and aggregation for each field. If the heading contains text, there is no aggregation, however, if there is a number, you will need to select the correct type from the drop down. Once everything is correct, you can select add to report or done.
Create report by adding graphs, charts and filters
Now you are presented with a blank page where you can add the type of charts and graphs that you wish.
We can add a bar chart by clicking on the icon on the top, and then just clicking and dragging in the blank space. If you then select the graph by clicking on it, you can select the dimension, which are the headings that contain text and the metric, which are the headings that contain numbers.
You can also add multiple dimensions or metrics to the same chart in order to display additional information.
In order to edit the look of the graph, you need to click on the graph and then select style on the right. In here you can edit the color of the bars, axis, background and legend.
Finally, a great feature of the data studio is that you can add filters so the data will automatically change depending on the filter that you select. The filter button is second from the right and once added, you will need to select the dimension and metric.
You can then preview the report by clicking on the preview button in the top right hand corner.
When I first view the report, the graph displays the percent of level 1 scores for both ELA and Math exams from all the grades (3-8).
However, I can then select specific filters in order to view how those students performed. So, I can select only English Language Learners for ELA in grades 3&4, and the data will automatically change.
Although data is extremely useful and should be used to inform decisions, sometimes it is difficult to analyze and fully understand. By using Google's Data studio, you will be able to easily create interactive reports to display a vast amount of data that all staff will be able to use with ease.
Feel free to view the Google Sheet and interactive report that was used for this by clicking on the links below:
If you have a strategy or idea for our next blog or are interested in writing a blog post for us, message us at: info@aquilaeducation.com
