How to build calculated fields in Looker Studio and what are the most useful formulas?
Learn how to build calculated in Looker Studio so your dataset is not a limitation anymore, and use formulas to make best use of your data
Sometimes, your dataset does not give you exactly what you want. There might be an issue with a dimension, or a missing calculation on metrics. Reason why you need to build calculated fields in Looker Studio (formerly known as Data Studio).
There are 3 ways to overcome this issue:
Change the dataset (outside Looker Studio here) so when you create your data source, you’ve got exactly what you want. Best option, but sometimes not possible if you don’t own the connector or don’t know how to write custom queries in Big Query for instance
Create a new field in the data source, so it can be re-used in all data visualizations sharing the connector (my favorite option)
Create a new field directly in your data visualization. Very similar to the previous option, a bit faster (as you can test real-time), but then you can’t re-use the newly created field elsewhere nor use it to filter across data sources
In this article, we’ll review options 2 & 3 (which in reality are similar), as well as a list of the most useful formulas you’ll need to know to build powerful calculated fields in Looker Studio. Hope it helps :)
Looker Studio connectors we use on a daily basis and are happy with (w/ free trials): Supermetrics - Windsor - Catchr - PMA - Funnel
Not sure which one to pick? Reviews here and there. Still unsure?! Contact us with your tech stack and budget, and we’ll guide you in the right direction:
What are calculated fields?
Quite easy to understand, a calculated field is just a new field based on a formula. It can be as simple as something alongs the line of if channel IN X, Y, Z then on-line else off-line (but in Looker Studio, we use CASE WHEN!), or a numeric calculation like sessions * 2.5–10.
The newly created calculated field can then be used as a dimension or a metric in your data visualizations. But please remember that you need to be careful with aggregates: if sessions > 500 then … does not always give the same result as if SUM(sessions) > 500 then … .
Build a calculated field in a data source
My favorite option. Please go to the top menu, and click Resource, Manage added data sources, and then Edit the one where you want to add a field.
Top right of the box, you should see a Add a field button, and this is where the magic will happen. Basically, where you will add your formula and set up a few parameters:
First thing first, please give a name to your field in Field Name (I love it when I write transcendent comments like this). Then, let’s talk a second about the Field ID. This ID is used by Looker Studio to understand that a field is shared across data sources.
What does it mean? If you have a country field in a GA4 data source, and another country field in a Search Console data source with a different ID, creating a control on one won’t filter anything on the dataviz based on the other. Because for Looker Studio, they are different fields. How to overcome this issue? Please refer to the article I wrote about How to build a control that filters across several data sources in Looker Studio?
For the moment, keep it as is, but in the near future I’ll bet you want to use meaningful IDs to share them across data sources and build nice controls. Plus, once the field is created, you can’t modify the ID, so think first!
Then, we’re getting to the formula. No need to start with an equal sign, just drop your formula. Remember that you can’t mismatch dimensions & metrics, so if your formula is based on a dimension, then only dimensions are accepted. If based on a metric, then only metrics are accepted.
Your formula can either be numeric (see below), or based on dimensions using LIKE REGEX… Please refer to the article on How to build a field using a CASE WHEN statement in Looker Studio? to learn more.
If you have an error in your formula, you’ll see an exclamation point at the bottom of your screen with some explanations of what the issue is. If it’s fine, you’ll see a green check, you’re good to go.
Build a calculated field in a data visualization directly
The other option I mentioned is to build your calculated field directly in the data visualization. For, it’s really fast and easy. Against, you can’t re-use the field in other data visualization or filter across data sources. But most of the time, it’s good enough for single-time use fields.
To build one, open the setup ribbon of your dataviz, and click Add dimension or Add metric, Create field. When the pop-up opens, give your field a name and insert your formula. You’ll then be able to change its Type, as usual:
As mentioned, be careful with aggregates as they can be misleading. My recommendation is to add aggregators in formulas to be sure that’s you’re doing what you think you’re doing. So, instead of: Revenue — Tax. Write: SUM(Revenue) — SUM(Tax) or SUM(Revenue — Tax).
Useful formulas that you’ll face in your Looker Studio journey
This is a very quick list of what you’ll be able to build using calculated fields. There is a lots of documentation out there, but to give you some ideas:
CASE WHEN: the IF THEN of Excel, based on SQL langage. I wrote an article about this
CAST: change type of your field
CONCAT: to transform your text fields
CONTAINS_TEXT: to check if a text contains certain characters
CURRENT_DATE: returns today’s date
DATETIME_ADD / DATETIME_SUB: to make date calculation
HYPERLINK: to add links directly in your table, I also wrote an article about that
IFNULL: prevent from showing null. If your dimension is null, you can return something else. Don’t mistake with NULLIF
MAX / MIN / MEDIAN: self-explanatory I guess
REGEX: quite complicated, but you can then do pretty much anything
YEAR / MONTH / WEEK / DAY: to extract a part of a date
And that’s it! In this article, you’ve learned how to build calculated fields, so your dataset is not a limitation anymore.
PROBLEM SOLVED !
Tools we use and we like:
Todoist: for tasks management
Monday: for projects management, when tasks fall short
Tropical Server: our friends in charge of the hosting of this very website!
Tools we’d like to use (if we had the budget…):