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

Simple Row-Level Display Control

We've seen a number of Data Studio report examples that display information that might be collected via a Google Form. But what if you don't want the Form information to display in your Data Studio report immediately? What if it needs to be "approved" first, or what if you want to be able to show/hide different entries from your data source at different times?

Some examples of reports that could benefit from this functionality might be:

• a staff directory report (might want to show only current staff but keep other names in the data source)
• a digital tools learning organizer (where staff are submitting ideas via Form but the instructional technology lead is "approving" them for inclusion in a shared list)
• a remote learning project sharing report (where students are submitting images via Form but the teacher needs to mark them "approved" in order to include them in a public listing)

Fortunately, there's an easy solution for this - built-in report filters! As I outlined in my Fun with Filters blog entry, built-in filters (not filter controls) are a way to restrict the data appearing within a report or report element on the "back end."

I'll use the Staff Directory report as an example. To control which entries from my Sheet data source are displayed in my report, I needed to do two things.

Step 1: I added a column called "Include?" to my Google Sheet data source, and I marked the rows I want to include with "y". In this case, I didn't include a "y" next to Al Banks, Director of Support, although his data is still included in my source Sheet.

Step 2: I added a filter to the report to specify that only rows where "Include?" is equal to "y" should be included in my report. I did this from File > Report Settings > Add a Filter, which will put the filter on the entire report (alternatively, the filter could instead be added to individual report elements like tables and filter controls.)



You'll notice that now the report does not include Al Banks, Director of Support, even though his information is included in the data source, since this filter has been applied. If I wanted to include him in the list, I would simply add a "y" in the "Include?" column in the data source, and Voila! He'll be back in the report.

This is a very simple, but very useful, way to control the data that is displayed in a report at the row level on the back end. BTW, I use this simple approach for the entries that are included in the K-12 Data Studio Report Collection, which allows me to approve form-submitted entries before they are included.

Fun with Filters!

One of the most valuable features of Data Studio for looking at education data is the ability to include filters which allow users to "slice and dice" the information on the screen to answer their own questions. However, there are several features within Data Studio that reference the term "filter" and so I thought I would write up some clarification plus a sample report, since it can get a little confusing.

 Filter Controls are elements that can be added to any Data Studio report, using the Filter Control button in the toolbar. The default Filter Control is a multi-select pop-up type, but it can be configured in the Style pane with a number of options. Filter Controls can be single-select or multi-select, can include a Search box, can be a pop-up or clickable type, and can include filter operators such as EQUALS or CONTAINS. Of course, color, font, and size are all customizeable as well.
Data Studio Help: Filter Controls

Chart Interaction Filters allow report authors to turn charts into interactive filters. For example, if your report contained a pie chart, this could be made into an interactive filter allowing the user to click a section of the pie chart to filter the information in the report.
Data Studio Help: Chart Interaction Filters
Treemap as filter menu from Mehdi Oudjida

Filters are a way to restrict the data appearing within a report or report element on the "back end." Filters themselves are not interactive (although you'll often hear people refer to a Filter Control as a Filter, hence the confusion.) For example, if you wanted to include a Scorecard element which showed a count of only the students who scored in a particular category, you could put a Filter on the Scorecard to include only those students, and the count would reflect it.
Data Studio Help: About Filters

Filter by Email is a relatively new feature of Data Studio that uses the user's Google account to display a custom data set based on an Email field in the data source.
Data Studio Help: Filter by email address

I created this sample report (also linked to the image above) to show examples of all the elements discussed here (except Filter by Email), so you can see how these different filters work. Obviously, you wouldn't use them all in one report for the same dimension, so this report is really just for fun.

Let me know.. how do YOU like to filter?

Visualizing Plans for the Class of 2020

Many high schools publish a list of the plans of their graduates, but wouldn't it be fun to visualize this list on a map, to see where a school's students will disperse after graduation?

Here's a very simple template for any high school who wishes to display the plans of their 2020 graduates in a fun visual map format. Clicking on a row in the table will zoom in on the Google map to show the location, and the user can even use the "Pegman" to take a look around at Street View. Clicking the row again will bring the user back to the entire list/map.

To make your own version of this report, first create a simple Google Sheet with a single column with a header of "List." This column should have a single entry related to each student in the class, although the list would not include student names. So, for example, if two students are attending Cape Cod Community College, then "Cape Cod Community College" would appear twice in the first column of the spreadsheet. It doesn't matter what order the list is in. Most high school guidance departments would be able to export this list from Naviance or other college and career platform.

 After the Sheet is put together, you can make a copy of the report by clicking Use Template in the upper right corner of the report link. You can then connect your new Sheet as the data source for your copy of the report. If you need additional information about how to connect a report to a data source, please refer to these instructions.

You will want to check that the List field is of the type Geo > Address. You can check this through the Resource menu > Manage added data sources > Edit and check the Type column for the List field.

A few notes: Some Sheet entries may need to be tweaked to display correctly. For example, there may be multiple colleges with the same name (like Trinity College) so you may need to add a city/state in order for it to display correctly. Non-location generic entries such as "2yr college", "Employment," etc. will not display on the map but will display in the list.

You can then customize the colors, logos, etc. of the report to make it your own. (Tip: Try Extract Theme from Image using your school's logo!) I would love to see what you come up with - and congratulations to the class of 2020!

Create a Student Dropbox and Work Log with Google Tools

We have seen a number of examples where Google Data Studio has been used effectively to visualize and share data from a Google Form in multiple ways. Since Google Forms can capture the email address of the person filling out the form, the Filter by Email feature of Data Studio can be used to create a customized report with only the information that person submitted. Data Studio could also be used to display the same data for the teacher, or even for the public (if appropriate).

With remote learning happening in school districts all over the world, schools have taken advantage of technology tools to facilitate the delivery of learning materials and collection of student work. Many districts use Google Classroom or other Learning Management Systems to facilitate this process, but there may be situations where a teacher or school would want to collect and share student work in a different way.

I only recently learned (within the past year) that Google Forms has a File Upload question type, which requires the person filling out the form to be logged in to Google. It then places the uploaded files in a dedicated folder on the Form owner's Google Drive, and adds a column in the Form's Google Sheet results of links to each submitted file. Many folks have used Google Forms as a "drop box" of sorts since this feature was released. 

I started thinking about how we might use the File Upload/dropbox approach to Google Forms with Data Studio, especially if we were asking students to share materials that were visual images. I put together a sample Form which asks students to upload a picture based on a weekly assignment. We can then think about displaying the student-submitted responses and files in a variety of ways in Data Studio, but all with the same data source (the Form Sheet.)

• A student-specific work log, which allows a student to view only his or her responses and submitted work, like a portfolio of sorts. 
• A teacher- or class-specific work log, which allows the teacher to look at responses/work by student OR by assignment.
• A public display of student work, which might display images and descriptions, either with or without student names. 

Here's the example Data Studio Report for your exploration. Each page of the report shows a different version of the report (in actuality they would be separate report files), but all use the SAME data source.

Please read through the About notes on the report itself for some of the trickier things about putting this one together. Let me know if you want to give this a try yourself - I'm happy to help!

Google Maps in Data Studio

For a while now, Data Studio has had the ability to visualize data in map form. However, recently this feature was upgraded to be able to display "geo" data in Google Maps within a report. Geo data is essentially just data that represents a street address, city, country, etc. 

I had previously created a travel log report for a family member, which allowed them to update a Google Sheet with different locations they had visited, and it marked them on a map in the report. (Yes, I sometimes create reports for fun. Don't judge.) The travel log allows filtering by year, trip name, or trip operator, and I updated it to include the Google Maps functionality when it became available. Now, the map narrows in on an area of the globe when a trip is selected AND allows the user to zoom to street view and look around. Not education data, I know, but a fun way to revisit past trips until we can travel again. Check out a sample copy here (including directions to make your own). 

I started wondering if there were ways a report using this feature could become part of a student learning activity. I played around with exploring map data of Civil War battles using data I found online, but didn't get too far with it. I ended up creating a Massachusetts Cities and Towns report (based on Wikipedia entries) that allows the user to explore information like population, government type, county, and town seal. 

While I did create the data source for this report myself, I realized that it could just have easily been created by students submitting data via a Google Form. Wouldn't that be fantastic? Say your third grade has 350-ish students, and you ask them each to research one of 351 Massachusetts cities or towns, gathering population, government type, county, and a link to the town seal. Students put their findings into a Google Form, which results in a Sheet that becomes the source for a report like this one!

I would LOVE to hear some other ideas for learning activities like this, where data is collected by students, submitted via a form and then displayed on a map. If you've got an idea, I'll help you pull it off!

Google Maps in GDS: Examples and Resources

• Google Data Studio Help: Google Maps
• How to Use Google Maps in Google Data Studio from Michael Howe-Ely
• Travel Log from Laura Tilton
• Massachusetts Cities and Towns from Laura Tilton
• Road Trip Photo Album from Riccardo Zagaglia
• World Development Indicators from Michael Howe-Ely

Do You Hear the Angels Sing?

Thank you for all the interest in my recent "Getting Started with Google Data Studio" webinars. Between the three sessions, there were over 150 participants from all over the US and the world! As I told those that attended, the first time I saw Data Studio in action, I heard angels singing! My hope in offering these webinars was to help you all hear the angels sing too.

As promised, I am sharing the recorded webinar, the 'slide deck' (actually a GDS report), and the list of getting started resources, for those who would like to review the materials or share with colleagues. Additional resources are regularly posted here on my blog as well as on Twitter @tiltondata. I am also available to provide consulting services in the area of Data Studio professional development as well as customized dashboard development.

Please feel free to reach out if you have follow-up questions, resources to share, ideas for new visualization tools, or suggestions for future blog posts.  Thank you and stay well!