In this article you will read about:
- What is Case Statements?
- What is Regex?
- How to add a custom field within GDS?
- Examples of CASE for channel grouping
In a previous article, we discussed about why you should and how to building Channel Groups inside Google Analytics. We also discussed the pro and cons. One con was that it is not retroactive if you use it in GA. But in this article, we are going to tell you that you can see retroactive if you are building inside Google Data Studio. If you are already familiar with GDS, just move forward (click here), but if you are not, we highly recommend that you read those articles below:
Google Data Studio is an online and complete tool for creating professional Dashboards, connected to data sources such as Excel, Google Analytics, Google Ads, databases, csv files, among others.
In addition to several connection possibilities, it is also possible to create calculated fields, metrics or custom dimensions, using mathematical operations or Data Studio functions. There are several functions available and organized by categories. In this article we are going to talk about CASE: execute the if-then-else logic. Before we start, is important for me that you know and understand what are CASE STATEMENT and how you can use this resource.
Google Data Studio is an online and complete tool for creating professional Dashboards, connected to data sources such as Excel, Google Analytics, Google Ads, databases, csv files, among others.
In addition to several connection possibilities, it is also possible to create calculated fields, metrics, or custom dimensions, using mathematical operations or Data Studio functions. There are several functions available and organized by categories. In this article we are going to talk about CASE: execute the if-then-else logic. Before we start, it is important for me that you know and understands what are the CASE STATEMENT and how you can use this resource. If you need a more deep understanding of the subject, we recommend this article from Google.
To make your life easier, you will need to use something call REGEX MATCH, but let’s start with the global definition first.
In computer science, a regular expression (or regex, short for the regular expression) provides a concise and flexible way of identifying strings of interest, such as particular characters, words or character patterns.
Now that we have explained what Regular Expression is and its potential, let’s finish by talking about some of the characters. To solve it, you need to know what a regex is made of. Following are the metacharacters that make up an ER:
. ? * + ^ $ | [] {} () \
The above metacharacters are the symbols that, combined, define a pattern to match a text.
Regex can be used in many ways and for various purposes. In order to build groups (whether for channels, pages, or content) within the GDS, we will dedicate this article only to REGEX MATCH. This function will allow us to group sources and mediums (or any other dimension that you want) and save you time, so you don’t have to go one by one. To know more about REGEXP_MATCH within GDS we recommend this article from Google.
One of the many steps we take to ease the reporting time is create Channel Groups in many ways:
1) As Custom Groups in Google Analytics for quick and highly efficient real-time testing and variations
2) As Custom CASE Dimensions within Data Studio in order to provide historical analysis outside GA
3) As a Default Channel Group once everything is cleaned up
This an Exemple how you can build a CASA Dimension for your business:
You can create fields linked with the Data Source or directly in the report. In the first case, the fields will be available for all reports that use the Source, in the second case, only for the report and graphic element used.
Follow the steps below to create a field in the report:
Assuming that you are familiar with GDS, lets start!! You will need to build a Custom Field within GDS.
CASE
WHEN Default Channel Grouping = ‘Affiliates’ OR REGEXP_MATCH (Source, ‘valuepenguin.com’) THEN ‘Affiliates’
WHEN REGEXP_MATCH (Source, ‘fb|instagram’) AND REGEXP_MATCH(Medium, ‘sm|cpc’) THEN ‘Paid Social’
WHEN REGEXP_MATCH (Source, ‘yl’) AND REGEXP_MATCH(Medium, ‘onlinedirectory’) THEN ‘Online Directory’
WHEN Default Channel Grouping = ‘Paid Search’ AND REGEXP_MATCH(Query Match Type, ‘Generic’) THEN ‘Generic Paid Search’
WHEN Default Channel Grouping = ‘Paid Search’ AND REGEXP_MATCH(Query Match Type, ‘Brand’) THEN ‘Branded Paid Search’
WHEN REGEXP_MATCH (Source, ‘GO$|gemini|go$’) AND REGEXP_MATCH(Medium, ‘CPC|cpc|native’) THEN ‘Native Ads’
WHEN REGEXP_MATCH (Medium, ‘cpc’) THEN ‘Other Paid Search’
WHEN Default Channel Grouping = ‘Organic Search’ AND NOT REGEXP_MATCH(Source, ‘google’) THEN ‘Non-Google Organic Search’
WHEN Default Channel Grouping = ‘Organic Search’ AND REGEXP_MATCH(Source, ‘google’) THEN ‘Google Organic Search’
WHEN Default Channel Grouping = ‘Social’ OR REGEXP_MATCH(Campaign, ‘meetedgar’) THEN ‘Social’
WHEN Default Channel Grouping = ‘Email’ THEN ‘Email’
WHEN Default Channel Grouping = ‘Referral’ THEN ‘Referral’
WHEN Default Channel Grouping = ‘Other Advertising’ THEN ‘Other Advertising’
WHEN Default Channel Grouping = ‘Display’ THEN ‘Display’
WHEN Default Channel Grouping = ‘Direct’ THEN ‘Direct’
ELSE ‘Direct’
END
A very good way to start is to know the original CASE dimension from Google. See below:
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
Within the DOMO, we have the option of using Beast mode. Beast MOde has several functions to build custom fields and one of them is CASE. Unlike Goodle Data Studio, unfortunately Domo does not yet accept Regex Match and our team’s solution was to use the IN function. In addition, some characters used are also different from GDS.
Then our CASES in the Dome have the following
Do you fit outside these boxes? We continue to work with larger brands on more complex data needs. Let’s think outside the box and start a conversation! Schedule some time with us here.