Blending example: classes, students, and grades

Learn how to solve a classic data blending use case.

Suppose you are a school administrator and that you store information about the classes offered, the students who enroll in those classes, and the grades they receive in each class. You can use Looker Studio to track and visualize this information by using data blending.

See a demonstration report

Copy and edit a live demonstration report to see how blending works.

(This report is only available in English.)

Questions to answer

This example answers the following questions you might have about the data:

Which students took which classes, and what grade did each student receive in each class?
What was the highest grade received in each class?
Which student received the highest grade in each class?

Sample data

Here's the data used in the examples.

Classes

class_id	class_name
c1	Underwater basket weaving
c2	Home fusion made easy
c3	How to train an attack iguana
c4	Learn SQL for fun and profit

Students

student_id	student_name
s1	Brett
s2	Rick
s3	Susanna
s4	Jennifer

Grades

student_id	class_id	grade
s1	c1	2
s2	c1	99
s3	c1	65
s4	c1	3
s2	c2	38
s3	c2	88
s4	c2	48
s1	c3	7
s4	c3	32
s1	c4	94
s2	c4	63
s3	c4	75
s4	c4	20

Follow along with the examples in this article by making a copy of this data using Google Sheets.

Setup

The first step is to connect to your data by creating data sources in Looker Studio. The demonstration report uses Sheets for the underlying data, but you could just as easily store this data in a database, such as BigQuery or MySQL.

Create a new report.
Add 3 data sources corresponding to the sample data:
1. Classes
2. Students
3. Grades

Learn how to create and edit data sources.

Question 1: Students, classes, and grades received

"Which students took which classes, and what grade did each student receive in each class?"

To answer this question:

Create a new blend.
Add a Grades table, with dimensions:
1. student_id
2. class_id
3. grade
Add a Students table, with dimensions:
1. student_id
2. student_name
Add a Classes table, with dimensions:
1. class_id
2. class_name
Join Grades to Students with student_id.
Join Grades to Classes with class_id.
Set both join operations to left outer.
Save the blend and close the editor.
Add a table to the report with the student_name, class_name, and grade fields.
Sort the table by student_name, descending.

Your table should look like this:

student_name	class_name	grade
Brett	Underwater basket weaving	2
Brett	How to train an attack iguana	7
Brett	Learn SQL for fun and profit	94
Jennifer	Underwater basket weaving	3
Jennifer	Home fusion made easy	48
Jennifer	How to train an attack iguana	32
Jennifer	Learn SQL for fun and profit	20
Rick	Underwater basket weaving	99
Rick	Home fusion made easy	38
Rick	Learn SQL for fun and profit	63
Susanna	Underwater basket weaving	65
Susanna	Home fusion made easy	88
Susanna	Learn SQL for fun and profit	75

Question 2: Highest grade by class

"What was the highest grade received in each class?"

To answer this question:

Use the same blend as in Question 1.
Add a table to the report
Add class_name as a dimension and grade as a metric.
Edit the grade field:
1. Change the name to max_grade.
2. Set the aggregation to MAX.
Sort the table by max_grade, descending.

Table Setup tab with Blended Data as the Data source, class_name as a Dimension, and max_grade as a Metric.

Your table should look like this:

class_name	max_grade
Underwater basket weaving	99
Learn SQL for fun and profit	94
How to train an attack iguana	32
Home fusion made easy	88

Question 3: Highest grade by student by class

"Which student received the highest grade in each class?"

To answer this question:

Create a new blend.
Add a Classes table, with dimensions:
1. class_id
2. class_name
3. Add a Grades table, name it Grades 1, and add dimension class_id.
4. Add grade as a metric, and set the aggregation to MAX.
5. Rename grade to max_grade.
Add the Grades table again, name it Grades 2, and add dimensions:
1. student_id
2. class_id
3. grade
Add a Students table, with dimensions:
1. student_id
2. student_name
Use left outer for each join.
Join Classes to Grades with class_id.
Join Grades to Grades 2 with:
1. class_id = class_id
2. max_grade = grade
Join Grades 2 to Students with student_id.
Save the blend and close the editor.
Add a table to the report with the student_name, class_name, and max_grade fields.

The Setup tab for a blended data table chart with the student_name, class_name, and max_grade dimensions and the max_grade metric selected.

Your table should look like this:

student_name	class_name	max_grade
Rick	Underwater basket weaving	99
Brett	Learn SQL for fun and profit	94
Susanna	Home fusion made easy	88
Jennifer	How to train an attack iguana	32

Was this helpful?

How can we improve it?

Blending example: classes, students, and grades

See a demonstration report

Questions to answer

Sample data

Setup

Question 1: Students, classes, and grades received

To answer this question:

Question 2: Highest grade by class

To answer this question:

Question 3: Highest grade by student by class

To answer this question:

Was this helpful?

Need more help?

Try these next steps:

student_id	class_id	grade
s1	c1	2
s2	c1	99
s3	c1	65
s4	c1	3
s2	c2	38
s3	c2	88
s4	c2	48
s1	c3	7
s4	c3	32
s1	c4	94
s2	c4	63
s3	c4	75
s4	c4	20

student_id	class_id	grade
s1	c1	2
s2	c1	99
s3	c1	65
s4	c1	3
s2	c2	38
s3	c2	88
s4	c2	48
s1	c3	7
s4	c3	32
s1	c4	94
s2	c4	63
s3	c4	75
s4	c4	20

student_id	class_id	grade
s1	c1	2
s2	c1	99
s3	c1	65
s4	c1	3
s2	c2	38
s3	c2	88
s4	c2	48
s1	c3	7
s4	c3	32
s1	c4	94
s2	c4	63
s3	c4	75
s4	c4	20