Use functions in calculated fields
Functions let you manipulate your data in ways beyond doing simple arithmetic. Data 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 TODATE, DATE_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.
More about function arguments
- When providing function arguments, be sure to enclose literal text in single or double quotes.
- Date functions assume UTC as their timezone. Date functions can optionally take either an input or output (or both) format string.
'DEFAULT_DASH': %Y-%m-%d [%H:%M:%S]
'DEFAULT_SLASH': %Y/%m/%d [%H:%M:%S]
'DEFAULT_DECIMAL': %Y%m%d [%H:%M:%S
'RFC_1123': for example, Sat, 24 May 2008 20:09:47 GMT
'RFC_3339': for example, 2008-05-24T20:09:47Z
'SECONDS': seconds since epoch
'MILLIS': milliseconds since Epoch
'MICROS': microseconds since Epoch
'NANOS': nanoseconds since Epoch
'JULIAN_DATE': days since Epoch
'DECIMAL_DATE': same as 'DEFAULT_DECIMAL'
Any valid strptime format. Optional if the value is already a datetime field.
TODATE(concat(Year, '-', Month Number, '-', Day Number), DEFAULT_DASH", "%Y%m%d")
- Geo functions require a supported geo code as input.
Geo input codes
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.
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 statement 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:
WHEN REGEXP_MATCH(CAMPAIGN, "C.*") THEN "Consumer"
WHEN REGEXP_MATCH(CAMPAIGN, "E.*") THEN "Electronics"
WHEN REGEXP_MATCH(CAMPAIGN, "S.*") THEN "Social"
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.