Functions let you manipulate your data in ways beyond doing simple arithmetic. Looker Studio provides over 50 functions, grouped into the following types:
- Aggregation functions perform calculations over multiple rows of your data. Examples include SUM, AVG, MIN, MAX.
- Arithmetic functions apply mathematical calculations to your data. Examples include LOG, POWER, ROUND.
- Date functions let you manipulate and transform time data. Examples include DATE, DATETIME_DIFF, YEAR.
- Geo functions let you transform geographic location data. Examples include TOCITY, TOCOUNTRY, TOREGION.
- Text functions let you manipulate string data. Examples include CONCAT, REGEXP_MATCH, SUBSTR.
See the complete list of available functions.
Function arguments
More about function arguments
- When providing function arguments, be sure to enclose literal text in single or double quotes.
- When using regular expressions, use double backslash to escape special characters. For example:
REGEXP_MATCH(GotStars, "\\*")Matches the literal * character. - Geo functions require a supported geo code as input.
Geo input codes
- 'CITY_ID'
- 'CONTINENT_CODE'
- 'COUNTRY_ISO_CODE'
- 'REGION_ISO_CODE'
- 'SUB_CONTINENT_CODE'
The valid geo codes are defined here:
The input code is optional if the field expression is a derived column already containing semantic geo information; otherwise it is required.
Function examples
Here are some simple examples of using the most popular functions. Additional complex examples are available in the individual function references.
Example 1: Organize and simplify your Analytics campaigns.
Suppose you run multiple ad campaigns, and use a coding system to identify them by category. Assuming the codes share a regular format, you could use the CASE expression and REGEXP_MATCH function to group related campaigns.
For example, say you've got campaign codes like C1, C2, and C3 for consumer goods, E1, E2, and E3 for electronics, S1, S2, and S3 for social ads. You could group these campaign codes into their respective category, and optionally, lump other campaigns into a miscellaneous category:
CASE
WHEN REGEXP_MATCH(CAMPAIGN, "C.*") THEN "Consumer"
WHEN REGEXP_MATCH(CAMPAIGN, "E.*") THEN "Electronics"
WHEN REGEXP_MATCH(CAMPAIGN, "S.*") THEN "Social"
ELSE "Misc"
END
You can now use this calculated field to see how your campaigns are performing per group, or use it in a filter control to refine the view of your data.
Example 2: Combine values from multiple fields.
You can aggregate and display your data in new ways by combining multiple fields via the CONCAT function. This creates a new field with the concatenated values. For example:
CONCAT('Browser: ', BROWSER, ' Version: ', BROWSER VERSION)
The new field will show the literal strings and dimension values as a single entry, such as:
| Browser: Chrome Version: 68.0.3440.106 |
| Browser: Safari Version: 11.0 |
| Browser: Firefox Version: 61.0 |
| ... |
Example 3. Create hyperlinks from your data
If your data contains URLs, you can create clickable links that appear in tables by using the HYPERLINK function.
For example, suppose you've got a custom SKU dimension associated with the URL to the relevant product description in the PAGE dimension. The following formula creates a clickable link for each record in your data:
HYPERLINK(URL, SKU )
If your data doesn't contain the complete URL, or you need to combine multiple fields to create the URL, use CONCAT to construct the link:
HYPERLINK(CONCAT("HTTPS://mydomain.com/", PARTIAL-URL, "?some-parameter"), SKU )
You can also use the HYPERLINK function to create clickable images in your tables.
Limits of calculated fields
Formulas in calculated fields can't mix unaggregated data (dimensions) and aggregated data (metrics).
For example, the following is not allowed:
CASE WHEN Country = "England" AND Conversions > 100 THEN ... // invalid formula
In this example, Conversions is an aggregated metric.