What looker studio functions are for ?
Like any other BI tool, Looker Studio provide its own functions’ library. Some of them are very handy when you are dealing with large amounts of data that can be grouped into channels, for instance, your traffic can be split into the following three groups social media traffic, paid traffic, organic traffic.
Looker Functions can be used to create calculated fields. You can for example calculate the revenue per product. By creating a new field and typing the following:
Hit enter, and there you got it! A new field that calculates your revenue for you.
The most commonly used Looker Studio functions :
Text functions :
Text functions are commonly used for manipulating text fields :
CONCAT():
Returns concatenated value in a new column, let’s say you have two fields in your Looker Studio purchases and currency. You want to combine them in one field (e.g., $200). You can do this by creating a new field and typing the following formula:
CONTAINS_TEXT():
This function will tell you if an expression exists within a column. Let’s say we have a sheet with a list of names and the number of visits of each person. You want to know if the name “Ava” exists in a list. You could use the following formula:
If the name value is indeed in the list Looker, Studio will return the boolean value “true” next to “Ava”.
Data Pill tip:
Looker studio functions are case sensitive. In the example above, if we typed “ava” the function would’ve returned false.
To correct this, we can add what we call a Metacharacter (more on this here)
ENDS_WITH():
Returns true or false, depending on whether the value you entered exists at the end of a term:
This will return the boolean value true, only if the name ends with the letter “a”.
STARTS_WITH():
Works the same with as ENDS_WITH(). Only this time, Looker will return the value boolean true only if the name starts with the text we specify in our function.
This will return the boolean value true, only if the name ends with the letter “a”.
LEFT_TEXT():
Another cool function that returns the number of characters from the left start of a string:
Another cool function that returns the number of characters from the left start of a string:
Instead of giving our function a text value, we will use numbers this time to indicate how many characters you want to retrieve from the string.
RIGHT_TEXT():
This time, the text value will be returned starting from the right side.
LENGTH():
Returns the number of characters in a string.
LOWER() / UPPER():
Will transform your text to lower case or upper case.
Regex functions:
Data Pill tip:
Regex functions are very useful to manipulate text and looking for patterns in your text. They can also be used to group marketing channels by type (Free, Paid, Owned…). Check this guide by Google for a complete introduction to this topic.
REGEXP_MATCH():
Returns a boolean value (false or true) when looking for a match. :
Let’s say you want Looker Studio to display true once it finds the name “Ava”. Using the match function, Looker Studio will return true once it finds the name “Ava” and will return false for the rest of names on the list:
REGEXP_REPLACE():
Unlike REGEX_MATCH, this function will replace a value in your field with the one you input. In the example above, we replaced the name Ava with Sara.
REGEXP_CONTAINS():
While REGEX_MATCH looks for exact matches, this function will return true if it finds a value containing what you searched for. Here is how we can use it if we want to find all the names that contain the letter h:
REGEXP_EXTRACT():
This function will extract a value from your field, and put it in a new field. For example, we can extract the letter A from a list of names:
Notice how the function returns null for names that have no letter h in them.
Metacharcters in looker Studio:
Data Pill tip:
To get more out of functions and filters in Looker Studio, you should absolutely start working with metacharacters.
These are special characters which can be used to gain more flexibility with formatting your functions.
List of the most useful metacharcters:
Taking our first Regex example, let’s say you want to return the boolean value true for the names that contain the letter c regardless of whether the letter is in lower or upper case. If we try to do this using the REGEXP_CONTAINS, the function will return false even though we have names that contain the letter c, such as Camelia.
Using the metacharchter (?i), we can make the function case-insensitive. Notice how all names that contain the letter c have true value next to them.
The dot:
You can use the dot to return anything on a specific field.
The function will extract A and whatever comes after it, whether it’s a number or a special character.
The asterisk:
Finds similar characters to the one you typed before the asterisk.
This will return all the names that contain A. Since a name can contain a capital letter only once, we need to make our function case-insensitive by adding (?i).
As you can see, the function found only one name that contains two consecutive a letters.
Data Pill tip:
You can use .* to retrieve all the data in your field :
The dot will retrieve anything on the field, and the asterisk will repeat the dot as many times until there is nothing left.
Of course, you can do this by just adding as many dots as you have to. But how can you know when to stop adding them?
The function, for example, will return null if you typed 10 dots and none of the values in your list contain more than 5 characters.
The plus:
The plus works pretty much in the same way as the asterisk. It looks for repeated strings, The major difference between the two, however, is that the plus will keep looking for the matching string even if it’s not at the beginning of the expression.
Let’s take the example of the name Camila.
If we use the asterisks, the REGEXP_EXTRACT function will return an empty value. This happens because the letter a is not at the beginning of the string.
In the table on the left, you can see that even though there are names that contain the letter a, the asterisk stops at the first character of the string if it’s not a, it will stop immediately.
On the right table, the plus doesn’t stop when it doesn’t find the character we are trying to match. It keeps going through the characters until it returns at least once.
The pipe:
This is the equivalent of “or” in plain language.
In the example above, the REGEXP_MATCH function returns true for the strings containing either z or r.
The caret:
The caret is how you tell your function to start looking from the beginning of the string.
In the example below, we will be looking for the names that contain the letter c at the beginning of a string.
This time, we are telling your function to start from the end of a string. This can be useful, for instance, if you have a list of names, and you want to capture the names that end with the letter a:
Notice that even that the function will not return true for the name c because it looks at the character only at the beginning of the string:
The dollar:
This time, we are telling your function to start from the end of a string. This can be useful, for instance, if you have a list of names, and you want to capture the names that end with the letter a:
The parentheses:
Will match string in exact order. This can be useful if you have a URL and you want to include only a specific path.
The brackets:
With brackets, you can match multiple combinations. Let’s say, we want to match names that start with A, and the second letter is either a, b, or m:
Curly brackets:
If you have a string with a character that repeats more than once, you use the curly brackets to match it.
Here we told our function to look for the character m and match it, at least once, or twice as a maximum. So it will return true only the names that contain m twice at the most.
Here we told our function to look for the character m and match it, at least once, or twice at the most:
REPLACE():
This function will create a new field with the value you replaced. Let’s say you want to replace all the names in your list that start with “A” with The character “T”. You can achieve this by doing the following:
STARTS_WITH():
Will return a boolean value depending on whether the text starts with the value you defined or not:
The function return true for all the values that start with “A”.
SUBSTR():
With this function, you can return values by their index number. For example, For the name “Ella”, you can return only a part of the name by using the SUBSTR function:
This function requires two inputs:
- First, this, you need to input the field you will work with. In our case, this will be
NAMES. - The second parameter will define when the function will start extracting the data, in the function above, we told our function to start returning values from the second character in our string.
- Let’s take the name “Camilla” as an example. The function starts retrieving characters from the letter a, because we indicated that we want to begin extracting from the second character.
- The second parameter will define how many characters we will return starting from the letter we defined. In our example, we told our function to return 3 characters after the second character, so it will return “mel”.
TRIM():
The TRM functions will delete the leading and trailing spaces in our function.
Suppose you have the following string: “ Holidays sale”. You can remove the leading space by using the TRIM function:
This will return a trimmed text: “Holidays sale”.
Date functions:
CURRENT_DATE():
The function will return the current date. No inputs are needed from your part.
CURRENT_DATETIME():
In addition to the date, this function will return the current time in your timezone. As with the CURRENT_DATE(), no additional input is needed from you. You can, however, add a diffrent time zone :
The function will return the current time in Egypt.
DATE():
This function creates a new field with date format.
In the example above, we will get Jan 22, 1999 as an output.
DATETIME_DIFF():
This function is very handy to calculate the difference between two dates. The return value can be in minutes, hours, days, etc. depending on your input.
Let’s say we have an online shop, and we want to figure out how long does it take to ship our product. We can pull this off by calculating the difference between the shipping date and the date of the order:
DATETIME_ADD():
By using this function, you can add an interval of years, months, days, etc.
Let’s say there is an additional two-day delay in our shipping date. We can add extra two days to the shipping date.
DATETIME_SUB()
This function will do the opposite of the DATETIME_ADD(), so instead of adding a time interval, it will subtract it from the field you specify.
DATETIME_TRUNC()
You won’t be using this function most of the time. It’s useful however when it comes to returning a part of a date-time field.
In the example above, the function will return the quarter when the order was made.
You need to make sure to change the type to “Year Quarter” for the function to work.
You need to make sure to change the type to “Year Quarter” for the function to work.
EXTRACT():
If you have to choose between using DATETIME_TRUNC() and EXTRACT(), i would go for the latter, simply because it will return the exact input without modifying the field type, as we saw earlier.
QUARTER(),MONTH(), DAY(),HOUR(),MINUTE(),SECOND():
Each of these functions will return, either the month, day, hour, minute, or second depending on what function you use. This can be used as an alternative to the EXTRACT() function:
PARSEDATE(), PARSEDATETIME():
Returns a date (PARSEDATE) or date-time (PARSEDATETIME) function from a string. In the example below, we have a field containing a bunch of text and a date. Suppose we are trying to get the date and tell the function to not considered it as a string.
Enter PARSE_DATETIME(). This powerful function can extract the date and exact time from the string you input.
In the example above, we have a table containing shipping dates, however, we only need the date. So how can we get rid of the necessary text?
The only thing we need to do is to tell our function what pattern to look for. Here, we told the function to look for the date-time that comes after the string “shipping date”.
The PARSE_DATE will do the same thing without returning the time.
WEEKDAY():
This function will return a number corresponding to the day of the week, for example 2 for Tuesday.
YEARWEEK():
Will return the year and the week number corresponding to the date in your original field.
Will return the year and the week number corresponding to the date in your original field. For example, for the date: Nov 27, 2020 the input would be 202048.
UNIX_DATE():
This will be probably the least used date function in your arsenal. It returns the number of elapsed days between 1970-01-01 and the date in your field.
Functions type 4 : Geo functions
TOCITY(), TOREGION(), TOCOUNTY(), TOCONTINENT(), TOSUBCONTINENT():
The Above functions work the same way: you input a city, country, continent… code and the function will return its name.
Functions type 5: Miscellaneous
Image():
With this function, you get to visualize images in your Dashboard. It has two parameters, first, a link for the image and second (optional) a label.
HYPERLINK():
Very handy to shorten a URL, this function can replace a lengthy link to a label of your choice like “Click here”.
CAST():
This function can force your field into another format. Sometimes you will find yourself working with a list of dates classified as text in Looker Studio for some reason, or it’s the other way around.
You can go the other way arround by turning values into text, or numbers.
Functions type 5: conditional
CASE:
Out of all the functions detailed above, the CASE function has the widest range of application.
In the table below, we have multiple marketing channels that bring traffic to our website. Let’s say want a simpler grouping of these channels: paid/free/unknown.
We can use the CASE function to put similar marketing channels into default groups:
Let’s break down the structure of this function:
- First, we have the opening CASE word. This is how every CASE function starts.
- The conditions we input are always prefaced by “WHEN”. The function analyzes each condition and verifies if it’s true.
- THEN: if the condition is true, the function will return the input that comes after the word Then.
- ELSE: so what if there is no matching condition? By default, looker studio will return “null”.
So ELSE is optional to add. If you want another output, like in the example above, you can add it.
- Once you are done, add “END” to stop the execution of the function.
CASE (simple):
This is pretty similar to the standard case we saw earlier. The only difference, is the ability to start with more granular data, by adding the field name, next to CASE.
COALESCE():
This function will return the first nonempty value. Suppose we have a table containing exams results by name. We left some empty fields. Let’s see how the COALESCE() function can retrieve nonempty values.
IF():
Like the CASE function, IF() returns a value if the inputted condition is true.
Let’s use the previous example of exam grades. We have one student, Philip, who’s done badly in the first, but he did some extra-academic activities, and we want to reward him.
In the example above we told our function to look in the field NAMES, for the name that starts with “Ph”, and if it exits, we will give him an extra 5 points. If it’s not the case, we want the default value in the field “EXAM 1”.
IFNULL():
This function returns a value you define instead of “null”.
Let’s suppose we have a list of cities with the country they belong to. Certain fields are empty, so instead of having “null” as a default value, let’s replace it with “ANOTHER COUNTRY” :
NULLIF():
The NULLIF will return ‘null’ if it finds the matching field, so it does the opposite of the IFNULL function. As an example, let’s replace Argentina with “null”.
