Tutorial: Create a ratio field based on filtered metrics

A common use case in reporting and data visualization is to display key performance indicators (KPI) as ratios of one indicator as compared to another. For example, suppose you are measuring the completion rate of an online coding academy. A KPI for the course is completion rate, which we'll define here as:

Course completions / Course registrations

If your dataset already contains this data then you can simply display the result in Looker Studio. But what if you need to calculate it in your report, or need to display completion rates based on filtered data? The solution is to use data blending.

Say your data looks like this:

Event Category Event Action Course Number Unique Events
Course Certificate Download 9 299
Course Complete Fail 8 21
Course Complete Fail 9 86
Course Complete Pass 9 337
Course Complete Fail 6 34
Course Complete Pass 7 363
Course Complete Fail 7 17
Registration Register 8 814
Registration Register 7 475
Registration Register 5 574
Registration Register 9 1078
Registration Unregister 6 22

Step 1: Calculate the course completions

  1. Add a scorecard to your report. Call it Course Completions.
  2. Set the metric to Unique Events.

  3. Add a filter to the scorecard to ignore non-completion events:

    IncludeEvent Category= "Course Complete"

    AND

    IncludeEvent Action= "Pass"

Based on the sample data, the scorecard would look like this:

Course Completions

700

Step 2: Calculate the course registrations

  1. Add a scorecard to your report. Call it Course Registrations.
  2. Set the metric to Unique Events.

  3. Add a filter to the scorecard to ignore non-registration events:

    IncludeEvent Category= "Registration"

    AND

    IncludeEvent Action= "Register"

Based on the sample data, the scorecard would look like this:

Course Registrations

2,941

Step 3: Blend the data from the scorecards

  1. Select the Course Registrations scorecard, then select the Course Completions scorecard.
  2. Right-click, then select Blend data.

This creates a new scorecard that automatically calculates the ratio between those metrics:

Course Completion Rate

23.80%

Learn more about how blending works: