Use functions in calculated fields

Create more powerful 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.

See the complete list of available functions.

Function arguments

All functions expect input, called arguments, that tell the function what data to act upon. Arguments can be field names or expressions. An expression can be a number, literal text, or a statement that evaluates to a field name in your data source. Arguments can also provide additional instructions or formatting information.

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.

Date formats

Date functions generally expect an input format and an output format. Input formats can be one of the following:
  • 'BASIC': %Y/%m/%d-%H:%M:%S

  • '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.

Note that the format strings above must be entered exactly as shown (all caps).
Output formats must be specified using strptime format strings. For example:
TODATE(concat(Year, '-', Month Number, '-', Day Number), DEFAULT_DASH", "%Y%m%d")

 

  • 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 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:

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.

Learn more about CASE.

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.

Learn more about HYPERLINK.

Was this article helpful?
How can we improve it?