5 Calculated Fields for Google Data Studio

  • Calculated Metrics

5 Calculated Fields for Google Data Studio

Calculated fields are one of the most powerful features of Google Data Studio. Based on the existing fields in your data source, calculated fields allow you to create new dimensions and metrics to augment your reporting and analysis.

Data Studio offers a variety of formulas that can be used to calculate ratios and rates, extract and reformat text, and apply logical comparisons.

Here are 5 useful examples to get you started with calculated fields in Data Studio:

Tip: Copy & paste any of the formulas below into the calculated field editor in Data Studio. Even though the editor is single-line, you can paste in multi-line expressions as well.

1. Combine hostname and page path

If you have multiple domains or subdomains within your website, it can be helpful to see the full URL of the page in your reports. A simple concatenation function will let you combine the Hostname and Page dimensions:

CONCAT(Hostname, Page)

2. Extract query parameter from URL

Query parameters are often used to trigger dynamic content, label traffic sources, or identify unique users. If you haven’t already moved these parameters into custom dimensions via your analytics setup, you can create a new dimension in Data Studio with a regular expression extract:

REGEXP_EXTRACT(Page, 'mode=([^&]+)')

Replace mode with the name of the desired query parameter.

3. Clean up inconsistent campaign naming

Ideally, you should establish consistent campaign tagging across your organization. Using common naming conventions between marketing teams and agency partners will make the reporting and analysis process much more efficient. However, if you need to clean up historical data or a rogue campaign, Data Studio will be your friend:

The most common inconsistency is usually the use of different case, e.g. “email”, “Email”, and “EMAIL”. This is easily fixed by forcing all campaign dimensions to lowercase.

LOWER(Medium)

Similarly, you can use LOWER(Campaign) and LOWER (Source).

When you use your calculated dimension, Data Studio will then aggregate the inconsistent values for you.

4. Create custom geographic regions

Google Analytics provides you with the standard geo dimensions of City, Region, and Country. But what if your organization uses your own regional grouping? Perhaps your sales team splits the country into West, Central, and East, for example.

Here’s a conditional function that you can use to define your own custom regions, based on the standard dimensions:

CASE 
WHEN REGEXP_MATCH(Region, "British Columbia|Alberta|Saskatchewan|Manitoba") THEN "West" 
WHEN REGEXP_MATCH(Region, "Ontario|Quebec") THEN "Central"
WHEN REGEXP_MATCH(Region, "Nova Scotia|New Brunswick|Prince Edward Island|Newfoundland and Labrador") THEN "East"
ELSE "Other" 
END

Note that Data Studio doesn’t support IF statements, so you must use the CASE function instead (which ends up being cleaner than nested IFs).

The general format is: CASE WHEN X THEN Y1 ELSE Y2 END, where X is a boolean expression and Y1 and Y2 are text, number, or boolean values. Refer to this CASE statement article in the Data Studio help centre for more details.

5. Create a custom channel grouping

Any custom channel groupings that you create in Google Analytics won’t be available in Data Studio. However, you can recreate your channel definitions in Data Studio using a CASE construct with a series of WHEN clauses.

Here is the Google Analytics default channel grouping represented as a CASE statement. Use this as a base for creating your own custom channel definitions:

CASE 
WHEN ((Source="direct" AND Medium="(not set)") OR Medium="(none)") THEN "Direct" 
WHEN Medium="organic" THEN "Organic Search" 
WHEN (Social Source Referral="Yes" OR REGEXP_MATCH(Medium,"^(social|social-network|social-media|sm|social network|social media)$")) THEN "Social" 
WHEN Medium="email" THEN "Email" 
WHEN Medium="affiliate" THEN "Affiliates" 
WHEN Medium="referral" THEN "Referral" 
WHEN (REGEXP_MATCH(Medium,"^(cpc|ppc|paidsearch)$") AND Ad Distribution Network!="Content") THEN "Paid Search" 
WHEN REGEXP_MATCH(Medium," ^(cpv|cpa|cpp|content-text)$") THEN "Other Advertising" 
WHEN (REGEXP_MATCH(Medium,"^(display|cpm|banner)$") OR Ad Distribution Network="Content") THEN "Display" 
ELSE "(Other)" 
END

Using calculated fields is a skill that every Data Studio user should master. It will make your data cleaner, make your reporting more relevant, and ultimately make your life easier.

Interested in learning more about Data Studio? Check out 7 Reasons to Use Data Studio with Google Analytics.

Let us know if there are any other specific aspects of Data Studio you would be interested in learning more about!

——

Looking for hands-on training in Data Studio? Come to our 1-day Data Studio course!

Related post: 7 Reasons to Use Data Studio with Google Analytics

 

By |2018-07-25T10:28:32-04:00February 14th, 2017|22 Comments
Categories: Data Studio

22 Comments

  1. Skyler December 5, 2017 at 12:07 pm - Reply

    I am tracking scroll depth (25%, 50%, 75%, 90%) and want to create an aggregate view of total events per URL. I also want to create a % of the total events per URL at each scroll depth interval. For example, I want to know the % of the total events for a URL at each interval. Is this possible in GDS?

    • Marc Soares December 7, 2017 at 3:18 pm - Reply

      Hi Skyler,

      You can definitely accomplish this, but not with calculated fields. Data Studio will aggregate data based on the dimensions and metrics that you include in your chart. So, if you want to see an aggregate of total scroll depth events per URL, you could create a table with Page and Total Events and filter for scroll events only.

      To get a “percent of total” metric, you can edit a metric in your report and apply the Percent of Total analytical function. Take a look at the help centre page here: https://support.google.com/datastudio/answer/7512288.

      Hope that helps!

  2. Ondra February 20, 2018 at 5:22 am - Reply

    Hi, thanks for the article. You have wrong function name in 2. Extract query parameter from URL part it is REGEXP_EXTRACT. Have a nice day

    • Jonathan Iudica February 20, 2018 at 9:12 am - Reply

      Hello Ondra,

      Thanks for letting us know! We have updated the post.

      Jonathan

  3. Joan March 28, 2018 at 5:03 am - Reply

    Very useful, Marc,

    But I’m trying to filtering/categorize with a custom field with an example like this:


    WHEN REGEXP_MATCH(Campaign, “BLU”) THEN “Colour Blue”
    WHEN REGEXP_MATCH(Campaign, “GRE”) THEN “Colour Green”
    WHEN REGEXP_MATCH(Campaign, “CAR”) THEN “Product Car”
    WHEN REGEXP_MATCH(Campaign, “MOT”) THEN “Product Motorbike”

    And works fine for campaigns with only one “tag”, like MOT or GRE, for example.

    I have also campaigns with more than one tag, for example CAR+GRE, and the REGEXP only returns the first true condition, in this case, CAR. But I’m not able to categorize both, CAR and GRE.

    Does anybody know how to achieve this?

    Many thanks!

    • Marc Soares March 28, 2018 at 9:20 am - Reply

      Hi Joan,

      The CASE statement in your calculated field can only return one value. It will return the result of the first WHEN condition that evaluates to true.

      When you are creating custom groupings in this manner, each dimension value (in this case, each campaign) can only be placed in one group. It won’t be possible to have a “CAR+GRE” campaign appear in both the CAR group and the GRE group. This is the same way that channel groupings and content groupings work in Google Analytics, i.e. it’s a one-to-one mapping.

      If you have different types of tags (e.g. Product and Colour), and each campaign can have at most one value of each type (i.e. you could have CAR+GRE and MOT+BLU, but never BLU+GRE or CAR+MOT), then I suggest creating two separate groupings. You can create one calculated field that groups the campaigns based on colour and a second calculated field that groups based on product.

      Hope that helps!

      Marc

  4. Laz April 11, 2018 at 12:51 pm - Reply

    This is very helpful, however I am wondering how to split comma separated data into metrics?

    For example having columns with following data: cat, dog, horse, bird.

    Each matched text should be aggregated for summary how many times textbook occurred in the dataset.

    Any ideas how to achieve this?

    • Marc Soares April 11, 2018 at 1:39 pm - Reply

      Hi Laz,

      You can create a conditional count with a CASE statement. For example, if you wanted to count instances of “cat”, you can use the following:

      CASE WHEN REGEXP_MATCH(Dimension, ".*cat.*") THEN 1 ELSE 0 END

      The same could be done to count dog, horse, bird, or any other value in a comma-separated dimension value.

      Hope that helps!

  5. Arron May 10, 2018 at 4:03 pm - Reply

    Hi – do NOT statements work? Example: I want a rule created that reflects my segment. Source = “paid_search” and Campaign [does not contain] “gmail”. How is a statement like this properly done?

    • Marc Soares May 14, 2018 at 10:19 am - Reply

      Hi Arron,

      You can use a NOT operator as follows:

      WHEN REGEXP_MATCH(Source, "Paid Search") AND NOT REGEXP_MATCH(Campaign,"gmail") THEN "Paid Search"

      Hope that works out for you!

  6. Joe May 20, 2018 at 7:22 pm - Reply

    Hi – I’m wondering if you can help me display a metric in Data Studio for percentage of overall traffic?
    I want to show what my organic sessions from social networks are as a percentage of my total traffic. And then to drill down further and show that for each social network.
    e.g If I had 1000 sessions total in a month, and 60 of those came from organic Facebook, then I want to show that figure (6%). and I can’t work out how to.
    Sounds like it should be quite straightforward, I’m fairly new to Data Studio!
    Cheers!

    • Marc Soares May 22, 2018 at 6:11 pm - Reply

      Hi Joe. A percent of total metric cannot be created as a calculated field since it is not a row-level calculation. You can however create a Percent of Total calculation in a table. See our other blog post on Calculating Percent of Total. You won’t be able to get a single scorecard metric, but you can get a table with the total and a breakdown.

  7. VAMSIKRISHNA IMMADISETTY June 11, 2018 at 5:05 pm - Reply

    Hi Soares, can you please throw some light to accomplish the below requirement.

    We wanted to create a metric like [Last 25 days page views]/[Current Month]? I try to get the current month numeric number but failed and I was not able to create the Last 25 days page views. Please guide me the steps to accomplish this request.

    Sincerely,
    Vamsikrishna.

    • Marc Soares June 13, 2018 at 9:58 am - Reply

      Hi Vamsikrishna,

      To get the number of Pageviews for a specific date range, I suggest using a Scorecard chart and filtering by date.

      If I understand correctly, you wish to create a calculated field for [Pageviews in the last 25 days] / [Pageviews for the current month]. Unfortunately, this type of aggregation is not supported in Data Studio right now. You would need to pre-process your data so that you have separate metrics for [PVs Last 25 Days] and [PVs for Current Month]. Then you will be able to calculate the ratio in Data Studio.

      Marc

  8. Bruna June 5, 2019 at 1:43 pm - Reply

    Hello Marc!

    I am trying to have a table like this:
    Channel | Page = ‘/’ (users) | Page =’/Plans’ (users) | Page=’/Cart’ (users) | Page=’Purchased’ (users)

    I was wondering if that is possible with custom metrics. Please help!
    Thank you,
    Bruna

    • Marc Soares June 5, 2019 at 3:57 pm - Reply

      Hi Bruna,

      You can accomplish this using Data Blending:
      1) Create a separate table for each metric that you want to include. In each table, add Users as the metric and apply a filter for the desired page.
      2) Select all 5 charts, right click and select “Blend data”. In the blended chart, you can then have a column for each metric.

      This is similar to the method described in this post, except you’ll need to use filters instead of segments.

      Marc

  9. Catherine Hogan July 25, 2019 at 1:47 am - Reply

    I used the code for custom channel grouping to include only Organic Search, Paid Search, Referral and Direct but when I enter the code and apply it another set of data values appear on my time series as an unnamed dimension. Do you know why this would happen?

    • Marc Soares July 25, 2019 at 9:31 am - Reply

      Hi Catherine,

      Did you include an ELSE condition at the end of your CASE statement? If not, any traffic that doesn’t match your WHEN conditions will be undefined. I suggest including the ELSE "(Other)" condition as shown above. If desired, you can then exclude (Other) from your time series.

      Marc

  10. Kate September 30, 2019 at 2:20 pm - Reply

    I’m trying to get a count of how many “share” events happen on a page.

    I’ve worked out:
    Facebook share = CASE WHEN (Event Category=”addthis” AND Event Action= “facebook”) THEN 1.0 ELSE 0 END

    But when I try to create another field:
    Count Facebook Shares = Sum (Facebook event)

    Adding “Count of facebook shares” to my table with Page URLS- It doesn’t calculate correctly. In fact my whole “Page” Table gets totally out of whack and removes rows somehow. It’s not a filter, it’s a new column; any insights?

    • Marc Soares September 30, 2019 at 6:16 pm - Reply

      Hi Kate,

      You shouldn’t need to create a new calculated field to sum your Facebook Share metric. Data Studio will automatically total the metric if you set the aggregation type to SUM. Try creating a table with Page and the Facebook Share metric.

      Marc

  11. Chris October 31, 2019 at 6:20 pm - Reply

    Hi,

    I’m trying to add a new custom field with just REGEXP_EXTRACT(Page, ‘mode=([^&]+)’) to get something working, and it tells me it doesn’t know what Page is. Do I need to enable something? What am I missing

    • Marc Soares November 1, 2019 at 5:23 pm - Reply

      Hi Chris,

      The formula above assumes that Page is the name of a field in your data source. If you don’t have a “Page” field, then you will need to replace it in the formula with an existing field that you wish to extract from.

      Marc

Leave A Comment