Estimated read time – 8 minutes

Want to keep updated about the blog? Sign up for the newsletter!

This post has been updated to reflect Google’s decision to change Data Studio’s name to Looker Studio in the fall of 2022.

If you don’t have a CRM with great reporting tools, spreadsheets may be the gold standard for tracking key performance indicators (KPIs) during the admissions cycle, but what happens when you have to provide an update to your board or share progress with other school leaders? Enter the dashboard. A dashboard can enable you to consolidate multiple tabs, rows, and columns, into a single screen that focuses on key metrics instead of the kitchen sink. One of my favorite tools for creating dashboards is Google Looker Studio.

What is Google Data Studio?

Google Data Studio is a free, web-based tool that you can use to create simple visuals that are easy to review, share, and update in real time. You can access Looker Studio using a Google account, and use Google Sheets to easily connect to the tool. If your school hasn’t gotten on the Google train yet, that’s okay. You can easily import a CSV file into Looker Studio or connect to a SQL database of student records to build your reports. 

As an added bonus for the marketers out there (or the small shops doing it all), Google Looker Studio also offers powerful integrations with other products in the Google ecosystem, such as Google Analytics, Google Ads, YouTube Analytics, and Search Console.

What data should you include in your admissions dashboard?

Before we dive into the steps involved in creating a dashboard in Google Looker Studio, let’s take a look at what to include in a simple dashboard. For the purposes of this 101-level post, we’re going to focus on the nitty-gritty metrics that will resonate the most with people outside of your office, or be most helpful to review at a glance to gauge high-level performance: inquiries, completed applications, acceptances, and enrollments. 

Since different schools tend to define inquiries differently, the most important thing for this KPI is that your school has an internally agreed-upon definition of who qualifies as an inquiry, whether it’s someone who submits an inquiry form on your website, registers to attend an admission event, or sends an email to the admission office.

If you’re at a larger school, it’s a good idea to track these metrics both at the grade level and at the age group or divisional level so you can easily identify how different grades and age groups are performing over the course of the admissions cycle. For this how-to, we’re going to use a K-6 school as an example.  

How should you prepare your data for Google Looker Studio?

Before you import your data into Google Looker Studio, you’ll want to make sure your data source is set up in a way that will “play well” with the tool. Formatting is everything with Google Looker Studio and if you don’t want to end up tearing your hair out, put your data in a single table and make sure it starts at A1. Each “Dimension” (in this case, grade level) and “Metric” (in this case, stage in the admissions cycle) should have its own row and column respectively.  

Below is a screenshot of the simple Google Sheet I created for this post based on what would be appropriate for our hypothetical K-6 school. Note that I’ve also included columns for the school’s goals for the current enrollment cycle as we’ll use them for comparison later.

Building Your Admissions Dashboard in Google Looker Studio

Setting up Your Data Source

Once the items above are neatly arranged in a Google Sheet or CSV file, the first step in Google Looker Studio is to set up your data source. Click “Create” at the top left and then select “Data source” to import your data file. 

When you first connect your Google Sheet or CSV file to Looker Studio, the tool doesn’t immediately understand what the data represents, so you’ll have to give it a bit of help. It will also default to displaying your data as a table, so you can go ahead and delete that.

Creating Your First Chart

Let’s assume we’ll want to view the following tables in this dashboard:

1. The number of students in each stage of the admissions cycle by grade
2. Your team’s progress towards the goals for each stage in the admissions cycle

For the first chart, a bar chart works well as a visual, but we’ll need to show Looker Studio how to interpret the data. To create the first table, click “Add a chart” and select the bar chart (or the chart of your choice) from the drop-down menu.

From there, Grade will appear as the Dimension with its “Type” set to “Text.” Then Inquiries, Completed Applications, Acceptances, and Enrollments will need to be manually added to your list of “Metrics.”

The default style is a bit colorful but the good news is you can use the “Style” panel to change the colors and visuals to match you’re school’s branded color palette. You can also do things like make horizontal bars vertical and display the data labels on your chart, which you’ll see I’ve opted to do here.

Clicking onto a blank space on the page will display the “Theme and Layout” menu, which will allow you to make global changes to the entire file rather than a single chart. Text boxes can be used to add and format labels for your charts or to simply add contextual copy to your dashboard.

Creating Formulas with Calculated Fields

In the second chart, we’re going to show how quickly your team is making progress toward your enrollment goals in a different way. Rather than showing the quantities for each category, we’ll show the data as percentages representing your progress toward achieving pre-determined targets. This same process could be used to create a visual for conversions through the admissions cycle (e.g., the percentage of students who have converted from inquiries to applicants), which will allow you to identify weak spots in your admissions pipeline and troubleshoot barriers that might be keeping families from the completing the process.

Rather than using formulas in your original source data, you can achieve this using “Calculated fields.” Start buying creating a new chart (in this case I’d go with a horizontal bar chart). Grade can stay put as your Dimension, but the formulas you create will become your new Metrics. 

Next, go back to the bottom of the data panel on the right and click “ADD A FIELD.” Name your new field and create a formula for each funnel stage/admissions process stage you want to track progress for.

From there, the formulas you created can be added under “Metric.” You’ll need to click on each one to make sure that the Type is set to “Numeric” > “Percent” in order to display the data as percentages. 

Excluding Data from a Chart or Table

In case you didn’t notice, the “Totals” field and its associated values from my source data are hidden. This is because I wanted the two charts we created to focus only on the grade-by-grade data, not the cumulative data for each funnel stage. We’ll create separate graphics for that for the folks who are interested in the true Cliff’s Notes, but first, let’s take a look at how you can hide specific values from a chart. 

First, click on the chart you’d like to exclude data from. In that handy right-rail menu, select “ADD A FILTER” and then “Create filter.” In the pop-up window, you’ll see the option to “Include” or “Exclude” options from the field of your choice by setting a condition. In this instance, I chose “Exclude” > “Grade” > “Totals.” Clicking “Add a control” and choosing “Advanced filter” from the dropdown menu will take you through the same steps. Once you’ve created your filter, you can apply it at will on other charts in your dashboard that use the same data set. 

Advanced Data Filtering with Controls 

In addition to allowing dashboard viewers to filter data by specific dimensions as noted above, the “Add a control” menu provides several additional features that allow your viewers to change what they in the dashboard. Controls can be applied at the report level (changing every chart on the dashboard) or the chart level.

To add a control to your dashboard, click “Add a control,” select your control type, and position it on the page. You can use the option in the properties panel on the right rail to set the parameters for the control. 

Google provides an itemized list of all the tools and functions that are available through this menu, but arguably the most useful control for the kind of dashboard we’re talking about here is the Date range control, which allows viewers to set a date range for all the data on a dashboard. If you’re building a dashboard from scratch like we are here, you’ll just need to make sure that at least one of your data sources has a date dimension, otherwise, the control won’t work. 

Highlighting Simple Numbers with Scorecards

If you want to call out a few very simple, high-level numbers without adding a bunch of steps for yourself, I’m a big fan of Scorecards. Scorecards allow you to highlight single metrics in a stylized way, which is a great solution for displaying a small number of KPIs as we’re doing here. You can format, drag and drop the boxes to your liking, and then you’re well on your way to creating a dashboard that you can be proud of.

Lighten Your Load with Scheduled Reports

Once you’ve built your dashboard, if you have stakeholders who need to review the report on a regular basis, Data Studio has a “Schedule email delivery” feature that will automate that for you. To get started, click the dropdown arrow next to “Share” at the top right of the screen and then “Schedule email delivery.” From there, you can select your recipients, customize the email subject line and message they receive, and choose how frequently they receive the report.

There you have it. While it isn’t quite plug-and-play, with a few mouse clicks here and there, Google Looker Studio provides a great free option for introducing schools to the world of data visualization and simplifying complex information into easy-to-digest visuals on the fly.