dashboard, data informed, data literacy, data studio, education, teachers

Plan your school trip with Google Data Studio

Setting the scene

A fellow Google Innovator, Sarah Kiefer (NYC19), reached out to me with a problem she was having. For years, she was taking a group of students and parents on an annual trip to Washington DC. She was responsible for coordinating information about the students, the families, payment, registration, and a variety of other forms and requirements.

She needed a way to monitor, track and communicate all of this information. Of course, she also needed a way to communicate to families and group leaders, information that was relevant to them without sharing all the information.

Sarah had grown to love Google Sheets. She was able to see the value they bring to her line of work as a technology coordinator & coach in her school district in Ohio. She is self-taught and was able to figure out a lot on her own.

As the number of participants has increased over the years, and additional data points needed to be collected, the current systems used were not meeting the demands. Sarah needed a way to share this information with parents and co-leaders in a way that would be smooth, clean, and efficient.

On top of all of that, she also valued a visually appealing user interface. However, finding out how to do all of this, on top of everything else she was doing, was a stretch. That’s when Sarah reached out to see if I could be of help.

What had been done in the past?

Google Sheets has been the backbone for creating checklists for her field trips. She leveraged the power of Sheets to organize checklists and requirements for both students and adults going on the annual trip to Washington D.C. Up until recently, Sarah used the add-on Form Mule to send out personalized emails on a weekly basis.

Along with her co-leader, they spend a lot of their time marking off their checklists with items completed. Not shy to try new things, Sarah also experimented with the Glide App. This app is great for turning a spreadsheet into a web app. However, it does come with some limitations. As educators, we don’t have much disposable income. With the free version of Glide, a user is capped on how much data can be uploaded as well as how many viewers can access it.

Not all was bad with the current system

Keeping your data organized is very important. For Sarah, having one spreadsheet with all of the data she collected worked well. She was pulling data from multiple sources but storing it in a central location. Leveraging conditional formatting and data validation helped keep her data clean. When dealing with a lot of data, having the ability to quickly sort is also a plus.

On the flip side, pulling together data from many sources makes it sometimes difficult to look through and find what you need. Due to a variety of reasons, Sarah felt that what she was able to do within Google Sheets put limitations on the way she shared and communicated with parents and other adult travelers. A lot of the data is private, so it wasn’t like she could just share the spreadsheet. She needed another way.

How did Sarah move her project forward?

Sarah needed a way to efficiently and easily communicate with parents and group chaperones while also protecting the personal privacy of each family. Because of this, I was able to help Sarah and her team develop a system that would allow them to collect information from families and share it with anyone that needed it when they needed it.

Data Studio to the rescue!

Using Google Data Studio, in consultation with Sarah, I helped her build a dashboard that had a few different views. The first view was for families that wanted to log in and see their children’s information. This view was a way for families to check what their current status was if they had completed forms or not, and, what their remaining balance for the trip was if any.

The second page was an overview of a group of students with data visualizations of key indicators. Group leaders could use this page to see information such as who had registered, who needed to pay, and which students still needed to complete forms. Group leaders also had access to simple but important information like who ordered a shirt or sweatshirt and whether or not they had paid for it.

The third page in the report was demographic and contact information. This is where the program coordinators and group leaders can find relevant contact information for parents and guardians. The dashboard has been set up in a way to allow a user to click on a phone number or an email address and have the proper application open up directly on their device. Of course, while on a computer, the call option does not work, but the email option will.

Head on over to the Projects Page to see a live demo of this dashboard.

Why was this good?

Because it has been set up for single sign-on (SSO), an individual using the dashboard would need to grant consent in order to access information. They are only able to access information that is associated with their email address. This keeps data private and secure. One caveat to the single sign-on is that it is limited to emails associated with Google.

The one downside to setting up this system so it is locked down by email is that a user would need to have a Gmail account or Google Workspace account to log in. Hopefully, as Google Data Studio continues to be developed, creating systems and workflows for schools will become easier over time.As a technology coach, Sarah offers great tips and tricks.

Check out more of her work at www.techyoucando.com and sign up for her weekly newsletter.

Chris

Chris Smith is an academic data analyst in the international education field. Throughout his 25 years in education, he noticed schools and educators collecting data but struggling with being overwhelmed or not knowing what to do with the data. So, he began coaching, training, and consulting schools and educators to learn how to discover what data they actually need and how they can turn it into information that leads to insights and informed decision-making.

All Posts