Calculated fields and functions are at the core of Google Data Studio! In this post, I want to share with you a few practical use-cases that I find quite helpful and time-saving. As you probably already know, the Data Studio functions are always evolving, so I will try to keep this post up-to-date, if any changes occur in the future.
Let’s dive into the world of functions! 🙂
In a previous post, I wrote an introductory article with a lot of examples on how to use CASE statements in Google Data Studio, so if you are starting out, I would recommend you to look at that first. For this scenario, I want to focus on a more complex use of the CASE function.
In Google Analytics, users have the capability to create “Custom Channel Groupings” that allow them to sort how users have arrived at their website (eg. direct, organic search and etc). However, the Google Analytics native connector for Data Studio does not offer those groupings. Therefore, you have to create them yourself with a CASE, which is actually a positive, because Data Studio gives you much more flexibility with functions when compared to the “Custom Channel Groupings” in Google Analytics.
As usual, you would have to create a new calculated field at the data source-level. Since the “Custom Channel Groupings” are going to include an extensive list of “Source/Medium” attributes, your CASE might get a bit crowded. Nothing to worry about, I have already pre-written the code for you, but here are a few callouts:
CASE
WHEN (Source="(direct)" AND Medium="(none)") OR (Source="(direct)" AND Medium="(not set)") THEN "Direct"
WHEN (Source="google" AND Medium="cpc") OR REGEXP_MATCH(Medium,"(cpc|ppc)") THEN "Paid Search"
WHEN Medium="organic" OR REGEXP_MATCH(Source, ".*(^google|baidu|bing).*|.*(search).*") THEN "Organic Search"
WHEN Social Source Referral="Yes" OR REGEXP_MATCH(Source,".*(social|fb|facebook|twitter|linkedin|pinterest).*") THEN "Social"
WHEN REGEXP_MATCH(Source, ".*(mail|outlook).*") THEN "E-mail"
WHEN REGEXP_MATCH(Source, ".*(youtube).*") THEN "Video YouTube"
WHEN Medium="affiliate" THEN "Affiliates"
WHEN (Source="dfa" AND Medium="cpm") OR REGEXP_MATCH(Source,"(display|banner|native|.*(doubleclick).*)") THEN "Display (Campaign Manager)"
WHEN Medium="referral" THEN "Referral"
ELSE "(Other)"
END
If you have multiple campaigns and want to see how each one is contributing to your business, the REGEXP_EXTRACT function can be a real time-saver. Here is an example, the URL below includes a campaign name that we want to extract, as that part of the UTM is valuable to us.
https://datastudio.ca/?utm_source=dfa&utm_medium=cpm&utm_campaign=summer_sale
In this case, you would have to write the formula below. Firstly, selecting the dimensions that you want to extract from – “URL (with utm)”. After that, the regular expression simply says “starting from campaign= match everything until you reach another &“.
Once you apply the function, the result is pretty cool. We have extracted the campaign name in a separate and much cleaner dimension.
Building upon the previous function, let’s assume that you want to remove all parameters to get the original URL. This is often used when you want to only see the traffic to a landing page and ignore all parameters. In our case, you can write the formula below. Once again, the regular expression says “look for anything after ?“. Then, the third argument specifies what we want our replacement to be. We simply leave that blank.
The final table would look something like this and our URL is now much more visible.
This is a function that could prove to be extremely useful, especially if you are working with Campaign Manager (CM) or Google Ads. For many organizations, “naming conventions” are important, as they allow for easy data parsing. Well, with the CONCAT function, you can combine a few pre-existing fields into a single and more powerful dimension. Here is how it works:
Luckily, the function has a simple syntax. You type your first dimension, in this case, “Campaign Name” and then a delimiter. The delimiter specifies how dimensions will be separated from each other. A few common delimiters are “|”(pipe) and “,”(comma).
The result is a compact and more informative dimension that combines all of the three fields. Remember, you can add as many dimensions as you want, as long as the final field is useful to you.
A more advanced and exciting use of the CONCAT function is nesting it inside an IMAGE function. For this scenario, I have used the product data from the Google Analytics Demo Account. All we are doing here is, using “Product SKU” as a dynamic dimension since the product code is used in the URL. You can apply this same methodology with your eCommerece data set, as long as you have a dynamic element in the URL.
Once you have created the IMAGE calculated field, you can add it to a table. You will see the images showing-up immediately. A little caveat, the IMAGE dimension is only supported in tables, not other chart types.
This is a familiar one for those of you who use SQL on a regular basis. The function returns the difference between two date/time values in a numeric format. An interesting scenario where I found this function useful is paid media for eCommerce. We often look at the time a user was exposed to an ad and the time he/she actually made a purchase. This gives us a sense of what we would call “time-lag”, which indicates that the user needs some time to think before committing to a purchase. This is exactly where DATE_DIFF kicks-in.
The formula is straight-forward, you just have to include the two date fields that need to be subtracted.
Last but not least, this function allows you to standardize your text dimensions. For example, imagine you have an online form on your website with a free-form input field that asks “How did you hear about us?” and the input field is free-form. The user might input any type of capitalization, so to clean up the mess, we can use LOWER.
You can also use the UPPER function in a similar fashion, converting all provinces to the standard uppercase abbreviations.
I hope this was helpful and you can now use all of these Data Studio functions in your own projects! If you have any questions, feel free to DM me on LinkedIn ?
If you want to learn how to build powerful data visualizations and unlock insights that can help you drive business results for your clients or employers, take a look at my full course on Udemy.
I'm continuing my exploration of the AI world with Claude, Anthropic's family of large language…
I've dedicated a considerable amount of time to exploring AI tools like ChatGPT, Microsoft Copilot,…
Businesses and individuals everywhere are rethinking their content strategies thanks to the rapid advancement of…
I am sharing these links for all the learners, who are part of my new…
Developing an app idea can be cumbersome, especially for those without a coding background. From…
Yep, I know that by this point, you have probably tried Google's Gemini and experimented…