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.