Calculated Fields: Figuring it out!

Calculated fields are a critical feature of Data Studio, and allow you to transform your data in a number of different ways. If you're at all familiar with functions and formulas in spreadsheets, you're more than halfway there to understanding calculated fields in Data Studio.

As of right now, Google Data Studio supports 82 different functions for calculated fields in seven different categories: 

• Aggregation (such as AVG to calculate an average)
• Arithmetic (such as ABS to return the absolute value of a number)
• Conditional (such as IF to return values based on conditional statements)
• Date (such as MONTH to return just the month of a date field)
• Geo (such as TOCITY to return a city name)
• Text (such as CONCAT to combine/concatenate text)
• Miscellaneous (such as HYPERLINK to create a clickable hyperlink in a table)

Calculated fields can be created either at the data source or the chart level in a Data Studio report. Data source calculated fields can be used over and over throughout any report that uses that data source, whereas chart-level calculated fields are created for use in a single graph, table, filter control or other element. more info

To create a data source calculated field, go to Resource > Manage added data sources, choose Edit for the data source to which you want to add the field, and then click + Add a Field in the upper right corner of the page.

To create a chart-specific calculated field in say, a selected table, look in the Data pane of the table's properties panel on the right, and click +Add dimension or +Add metric (depending on the kind of field you want to create.)

In both cases, you'll then need to give the field a name and then write a formula. Here are examples of calculated fields using two of my favorite functions:

• 
  Combining: Suppose your data source has fields for firstname and lastname, but you want to have a field that just gives a student's full name. You could use the CONCAT function to create a new field called Full Name. (Note that the example includes a comma and space between the two names.)
  

• 

  Transforming: Suppose you have a boolean field called IEP that is a 1 if a student has an IEP, and a 0 if they don't have an IEP, but you want the information to display more descriptively than just 0 or 1. You could use a CASE statement to create a new field called IEP Status.

        

• You could also use the IF function to do something similar.

For my spreadsheet friends: Please note that you don't need to type "=" before using any of these functions, like you do in Sheets or Excel (this took me some time to get used to!)

You might be asking, "If my data source is a Google Sheet, why wouldn't I just create these new fields as columns in my Sheet?" This is definitely another option, however, using calculated fields means you can do all the work on the Data Studio end without having to refresh the data source every time you add a new field, or "fill" Sheet formulas every time new rows are added to the spreadsheet.

Google has a simple tutorial to walk you through creating a few different types of calculated fields, if you want to try it out. 

Google Forms + Data Studio = A Match Made In Heaven!

Since it's almost Valentine's Day, I thought I'd highlight one of my favorite couples - Google Forms and Google Data Studio! 

Google Form data (saved in a Google Sheet) is a GREAT starting point for educators looking to get started creating a Google Data Studio report. Why? One main reason is that the structure of the data works well as a Data Studio data source, since it has just a single header row, no merged cells, no blank rows, and consistency in field values.

Here are a few tips for developing Google Forms to use with Data Studio:

• Use very short questions. Try using just 1-2 words in the Google Forms question field, then and putting the full details of the question in the description. These questions become your column headers in Sheets and thus your field names in Data Studio, and this makes them much more manageable.
  
• Avoid "Check all that apply" type questions. Try using a multiple choice grid type question (with yes/no answers) instead. This makes the data more compatible with filtering in Data Studio.
  
• Build a sort order into your responses. If the responses to a question have a particular order (like Daily, Weekly, Monthly), try including a sort order in the responses (1-Daily, 2-Weekly, 3-Monthly) so that when you sort in Data Studio they sort correctly. (Yes, you could do this with a calculated field in GDS too, but why make things harder if you don't have to?) Linear scale questions are a good option for this too, especially if you want to report on the average response number.
  
• Build your report first based on sample data. I will often build my report BEFORE sending out the Form, using the Form Responses tab with some rows of sample data (I usually try to add enough sample data that all possible answers are in the data set for each question.). I then remove my sample data once real data starts coming in.
• Take advantage of "live" updating. If you've built your Data Studio report, and the data continues to come in via the Form, the report will continue to update (automatically every 15 minutes). This is great for "leaderboard" type reports when you've built out the report ahead of time (see previous bullet)
  
• Capture email addresses. If your data source contains email addresses, you can use the Filter by Email feature to create customized reports. Or, you can use the email addresses as a unique identifier to pull in additional information on top of what's collected in the Form.

Here are some of my favorite examples of Data Studio projects that use Google Form data:

Surveys & Voting

• Social Media Use Survey

• Christmas Cookie Contest Leaderboard

Resource Collections

• SEL Resource Collection

• Digital Learning Tools Training Materials Collection

• Approved/Unapproved Resource List

Student Work

• Remote Learning Project Log

• Massachusetts Cities and Towns Research Project

• Student Reading Log

Do you have any other great examples? Please feel free to submit them to the K-12 Data Studio Report Collection!

Oh, and Happy Valentine's Day! ♥

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!

Resources:

• Dynamic Change of Dimension using Google Data Studio Parameter 
  by Mimoune Djouallah (@mim_djo)
• Google Data Studio secondary dimension like inside Google Analytics
  by Mehdi Oudjida (@wissi_analytics)
• Use Parameters to change Column Dimensions in Google Data Studio
  by Michael Howe-Ely (@michaelhoweely)

Happy Thankgiving!

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

Happy Thanksgiving!

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

Parameters

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:

CASE

  WHEN field=value1 THEN result1

  WHEN field=value2 THEN result2

  WHEN field=value3 THEN result3

END

Now, you would write this:

CASE field

  WHEN value1 THEN result1

  WHEN value2 THEN result2

  WHEN value3 THEN result3

END

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.

OR

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!

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.

Instructions:

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.

Resources:

• Data Studio Help: Filter by email address
• Ideas for Reports using Filter by Email (in the K-12 Data Studio Report Collection)
• Remote Learning Project Log (Laura Tilton)
• Student Check-In Dashboard (Matt Heusman)
• Mehdi Oudjida's Analytics Blog post
• Michael Howe-Ely's Blog Post

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:

https://datastudio.google.com/u/0/reporting/597b2d3d-3f91-4870-8940-beb747027d5c/page/M7pPB

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

https://datastudio.google.com/u/0/reporting/597b2d3d-3f91-4870-8940-beb747027d5c/page/M7pPB?params=%7B%22df4%22:%22include%25EE%2580%25800%25EE%2580%2580IN%25EE%2580%2580Plymouth%22%7D

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!

Resources:

• Google Data Studio Help: Create custom bookmark links with report settings
• Stephane Hamel's article on advanced bookmark link approaches