Transform you data with the CAST function in Looker Studio

Among the myriad functionalities and features provided by Looker Studio (formerly Looker Studio), the CAST function stands out as a unique tool. This function allows users to transform, or 'cast', data from one specific type to another - a feature that can dramatically enhance your analysis and interpretation of data.

Understanding the Syntax and Working of the CAST Function

The general syntax of the CAST function is as follows:


CAST(field_expression AS type)

Here, 'field_expression' represents a field or a particular calculation, and 'type' refers to the data type to which you want to change the original data.

The Google Data Studio CAST function arms you with the ability to transform the following types of data:

Text
Number
Date
Date & Time

However, it must be noted that you cannot cast aggregated fields.

Examples of Using the CAST Function

Let's take a practical example to understand the application of the CAST function, using a hypothetical scenario with sales data.

Scenario: In a digital marketing firm, the session duration of website visitors is treated as a text dimension. You are required to count the number of sessions that exceed 600 seconds or 10 minutes.

Step 1: Begin by creating a new calculated field, 'Session_Duration_as_Number', with the following formula:


CAST(Session_Duration AS NUMBER)

This formula will effectively cast the text 'Session_Duration' into a numeric value.

Step 2: Now, we will quantify the length of the sessions by creating another new calculated field, 'Long_Sessions':


SUM(CASE WHEN Session_Duration_as_Number > 600 THEN 1 ELSE 0 END)

The combination of the CASE function with SUM in the formula ensures that sessions exceeding 10 minutes are counted.

Limitations of the CAST Function

Despite its utility, the CAST function comes with some restrictions. It cannot be used with aggregated fields. In addition, while converting from [DATE|DATETIME] to TEXT and vice versa, it only accepts or returns specific formats. However, you can use the FORMAT_DATETIME and PARSE_* functions respectively to return or parse other formats.

Handy Tips for Using the CAST Function

Remember that Google Data Studio will implicitly convert non-string values to a string when an expression requires a string. So, in certain scenarios, you may not need to explicitly use CAST(value AS TEXT). If you are dealing with DATE or DATETIME fields, be clear about the required formats as CAST has specific restrictions in this regard.

Ensure you use CAST judiciously and experiment with its potential combinations with other functions to unlock more analytical potential from your data.

Using the CAST function in Google Data Studio, you can boost your data analysis precision and develop a deeper understanding of your data. Always remember to leverage the power of such functions to let your data tell a compelling story.

‍