User Tools

Site Tools


google_data_studio:creating_a_custom_metric_or_dimension

Creating a custom metric or dimension

In certain scenarios such as

  • Slicing campaign names
  • Using different conversion events from different platforms
  • Currency conversions

Will require you to add calculated metrics or new dimensions in data Studio

Here are two samples. One how to create a custom dimension and a second on how to create a custom metric.

Creating a custom metric

In this example we will do a currency conversion to have the costs all in the same format.

Click on ADD A FIELD

Then enter your formula

This sample multiplies the media costs by 0.77 to convert the accounts which are not in the default currency to the default.

CASE WHEN
Source in ("snapchat","google","bing","pinterest") then Amount Spent*0.77
ELSE Amount Spent
END

Creating a custom dimension

In this example we will do a new custom channel grouping.

Click on ADD A FIELD

Then enter your formula

This sample checks the name of the source and then checks medium or campaign for certain patterns using a regular expression. If the regular expression is matched, the string is assigned. More information on regular expressions in Data Studio

CASE
  WHEN source = "facebook" AND REGEXP_MATCH(Medium,"((?i).*Remarketing).*") THEN "Facebook - Remarketing"
  WHEN source = "facebook" AND REGEXP_MATCH(Campaign,"((?i).*Remarketing).*") THEN "Facebook - Remarketing"
  WHEN source = "facebook" AND REGEXP_MATCH(Medium,"((?i).*Cold).*") THEN "Facebook - Cold"
  WHEN source = "facebook" AND NOT REGEXP_MATCH(Campaign,"((?i).*Remarketing).*") THEN "Facebook - Cold"
  WHEN source = "facebook" AND REGEXP_MATCH(Medium,"((?i).*Cold).*") THEN "Facebook - Cold"
  WHEN source = "facebook" AND REGEXP_MATCH(Campaign,"((?i).*Cold).*") THEN "Facebook - Cold"
  WHEN source = "facebook" AND NOT REGEXP_MATCH(Campaign,"((?i).*Remarketing).*") AND NOT REGEXP_MATCH(Medium,"((?i).*Remarketing).*") THEN "Facebook - Cold"
  WHEN source = "google" AND REGEXP_MATCH(Campaign,"((?i).*Brand).*") AND REGEXP_MATCH(Campaign,"((?i).*Search).*") THEN "Google Ads - Search - Brand"
  WHEN source = "google" AND NOT REGEXP_MATCH(Campaign,"((?i).*Brand).*") AND REGEXP_MATCH(Campaign,"((?i).*Search).*") THEN "Google Ads - Search - Non - brand"
  WHEN source = "google" AND REGEXP_MATCH(Campaign,"((?i).*Youtube).*") AND REGEXP_MATCH(Campaign,"((?i).*Cold).*") THEN "Youtube - Cold"
  WHEN source = "google" AND REGEXP_MATCH(Campaign,"((?i).*Youtube).*") AND REGEXP_MATCH(Campaign,"((?i).*Remarketing).*") THEN "Youtube - Remarketing"
  WHEN source = "google" AND REGEXP_MATCH(Campaign,"((?i).*GDN).*") AND REGEXP_MATCH(Campaign,"((?i).*Cold).*") THEN "google - Display - Cold"
  WHEN source = "google" AND REGEXP_MATCH(Campaign,"((?i).*GDN).*") AND REGEXP_MATCH(Campaign,"((?i).*Remarketing).*") THEN "google - Display - Remarketing"
  WHEN source = "google" AND REGEXP_MATCH(Campaign,"((?i).*Shopping|Pla).*") THEN "Google Ads - Shopping"
  WHEN source = "snapchat" AND REGEXP_MATCH(Medium,"((?i).*Remarketing).*") THEN "Snapchat - Remarketing"
  WHEN source = "snapchat" AND NOT REGEXP_MATCH(Medium,"((?i).*Remarketing).*") THEN "Snapchat - Cold"
  WHEN source = "pinterest" AND REGEXP_MATCH(Medium,"((?i).*Remarketing).*") THEN "Pinterest - Remarketing"
  WHEN source = "pinterest" AND NOT REGEXP_MATCH(Medium,"((?i).*Remarketing).*") THEN "Pinterest - Cold"
  WHEN source = "tiktok" AND REGEXP_MATCH(Medium,"((?i).*Remarketing).*") THEN "TikTok - Remarketing"
  WHEN source = "tiktok" AND NOT REGEXP_MATCH(Medium,"((?i).*Remarketing).*") THEN "TikTok - Cold"
  WHEN source = "bing" AND REGEXP_MATCH(Campaign,"((?i).*Brand).*") THEN "Bing Ads - Brand"
  WHEN source = "bing" AND REGEXP_MATCH(Campaign,"((?i).*Shopping|Pla).*") THEN "Bing Ads - Shopping"
  ELSE "Other"
END

Further reading

The Google Data Studio Help also lists further examples and has an explainer video:

google_data_studio/creating_a_custom_metric_or_dimension.txt · Last modified: 2021/04/13 00:11 by windsor_ai