Calculating Ratios with Filtered Metrics in Data Studio

If you have done any kind of reporting or analysis, you have inevitably needed to calculate a rate or ratio. Form abandonment rate. Cost per acquisition. Revenue per customer. The list could go on and on. In Data Studio, calculated fields make it easy to compose a new ratio from existing metrics in your data source. But what if the metric needed for your calculation requires applying a filter or segment?

Let’s say you want a scorecard on your dashboard that shows the percentage of traffic from organic search, e.g. Organic Sessions / Total Sessions. Using a Google Analytics data source, you could apply a segment on the chart to show the number of organic sessions. However, you cannot apply a segment inside a calculated field. And if you try using a CASE statement (e.g. CASE WHEN medium = "organic" THEN Sessions END), you will find that Data Studio does not allow you to mix dimensions and metrics in a calculation.

Similarly, if you want to display a “download rate”, but downloads are tracked only as events in Google Analytics, you would need to filter for “Event Category = Downloads” for example. You can apply such a filter to a chart, but you’ll encounter the same obstacle as above if you try to filter within a calculated field.

When it comes to Google Analytics, you can avoid some of these challenges by configuring goals and custom metrics in your implementation. This could work for something like downloads, but it may not be feasible for a more complex session- or user-based segment.

So how do we create ratios using filtered metrics in Data Studio? The solution is data blending!

Let’s illustrate how this works by calculating the percentage of sessions from organic search:

1. Add the first scorecard

The first scorecard will be the numerator of the calculated ratio. Select the appropriate metric for the scorecard and add a filter or segment from the data panel.

To get the number of organic sessions, we can select Sessions as the metric and apply the Organic Traffic segment.

2. Add the second scorecard

The second scorecard will be used as the denominator of the ratio. In our case, we will use All Sessions, so no segment is required. However, you could apply a filter or segment to this scorecard as well if needed for your calculation.

3. Blend the scorecards

Select both scorecards in order. Right-click the selection and click “Blend data”. This will automatically create a third scorecard with the ratio of the two.

4. Customize the ratio

Blending scorecards automatically creates a calculated field of the ratio between the two metrics. To edit the name, data type, or the calculation, click the “fx” button on the metric to open the in-chart field editor.

In our example, we re-named “Organic Sessions / All Sessions” to “% Organic Sessions”:

This method can be used with any filters or segments on the numerator or denominator to create the ratio you need.

Let us know how you use filtered ratios in your dashboards and reports!

–

Complete list of posts in our 2019 Data Studio series:
1. 5 More Calculated Fields for Data Studio
2. Using Drill-Down Charts in Data Studio
3. How to Schedule Email Delivery of Reports in Data Studio
4. 4 Scatterplot Hacks for Google Data Studio
5. How to Add Clickable Images in Data Studio
6. Calculating Ratios with Filtered Metrics in Data Studio
7. Using Optional Metrics in Data Studio
8. Using Conditional Formatting in Data Studio
9. Creating Ecommerce Funnels in Data Studio
10. How to Create a Slope Chart in Data Studio
11. Creating Radar Charts in Data Studio
12. Creating Stepped Line Charts in Data Studio