Looker Studio is a powerful data visualization and analysis tool that allows users to create custom fields using regular expressions. With regular expressions, you can easily extract and transform data into meaningful insights.
In this article, we will walk you through the process of creating custom fields using regex in Looker Studio.
New to Looker Studio? Here's a Place to Start
If you're new to Looker Studio and not sure how to start, you can always use a template from the Template Gallery. For the sake of this article, I would recommend the "Google Analytics Audience Overview" Template.
You'll need to connect your Google analytics to the dashboard itself, which Google has a support article for.
What are Regular Expressions?
Regular expressions, or regex, are a sequence of characters that define a search pattern. They are commonly used in programming languages to search, replace, and manipulate text. In Looker Studio, regular expressions can be used to extract specific information from a string or to transform text into a more usable format, specifically with Google Analytics and Google Search Console data, though you can use it for other external data sources as well.
In Looker Studio we can use some very powerful functions to created calculated field formulas:
REGEXP_CONTAINS | Returns true if the input value contains the regular expression pattern, otherwise returns false. |
REGEXP_EXTRACT | Returns the first matching substring in the input value that matches the regular expression pattern. |
REGEXP_MATCH | Returns true if the input value matches the regular expression pattern, otherwise returns false. |
REGEXP_REPLACE | Replaces all occurrences of text that match the regular expression pattern in the input value with the replacement string. |
How to Create Custom Fields in Looker Studio with Regex
To create a custom field using regular expressions in Data Studio, follow these steps:
Go to "Resource", then to "Manage added data sources". Navigate to the data source you want to add the field to and click the "Edit" button.
Add a new calculated field by clicking on the "Add a Field" button in the top right corner. This opens the formula editor.
Once you're in the formula editor, we can now write regular expressions to create our custom fields.
For example, to extract the first word of a page titles on your site (we're assuming here that you've connected your Looker studio Dashboard to Google Analytics 4), use the following code:
REGEXP_EXTRACT(Page Title, '^(\w+)')This regular expression matches the first word of the string by searching for one or more word characters at the beginning of the string (^ denotes the start of the string).
Give your field a name and click "Save."
If you're finished with editing your custom field you can click "Done" which takes you back to the previous screen.
Add the custom field to your report by dragging it onto the canvas like any other field. Huzzah!
Use REGEXP_MATCH to Categorize Products and More
Now, the above example might not be a great use case for a custom field, because who would want to extract just the first word of a page title from Google Analytics? What if we wanted to categorize products based on the URL structure?
A good way to do this would be to use REGEXP_MATCH. With this we can match the URL path of a product line, and give it a name to categorize it. I use this function more than any other function.
In our example below, lets say our website sells shoes. We know that every product that can be found in the /red-shoes/ subfolder of our URL structure is red. To do this, we'd create a regular expression that matches every URL that has traffic data based on that sub folder.
We would use the "Page path" dimension which can be found in Google Analytics 4, and extract pull data from any URL that contains /red-shoes/ within it.
Example:
CASE
WHEN REGEXP_MATCH(Page path,'(?i).*/red-shoes/.*') THEN "Red Shoes"
END Page path derives from the value after the domain. For example, if someone visits www.example.com/shoes, then example.com is the domain and /shoes is the page path.
In regular expressions, asterisk (*) means “match zero or more of the preceding character.” To make a “wildcard” (that is, an expression that matches anything) with regular expressions, you must use '. *' (dot asterisk). This expression means, “match zero or more of any character.” We use the '.*' method in our example to make sure our URLs fully match.
While working with a client recently, I noticed that Google Analytics 4 doesn't have a "Continent" dimension, so I wasn't able to use stock fields in Looker Studio to create a Continent filter for my client, who operates globally. It was important to the client that I could filter this data.
I used REGEXP_MATCH and the "Country ID" Dimension to create a drop-down filter that allowed them to filter their data by Continent. I added an "Else" case for any data that might be unknown. It looked something like this:
^ indicates the beginning of the string, and $ indicates the end of the string, where a pipe symbol ( | ) allows regular expression components to be logically OR. We're basically telling Looker Studio the Country ID can be AE OR AF OR AM, etc. in the example below when grouping Country ID's for Asia and other continents.
CASE
WHEN REGEXP_MATCH(Country ID, '^(AE|AF|AM|AZ|BH|BD|BN|BT|CC|CN|CX|CY|GE|HK|ID|IL|IN|IO|IQ|IR|JO|JP|KG|KH|KP|KR|KW|KZ|LA|LB|LK|MM|MN|MO|MV|MY|NP|OM|PH|PK|PS|QA|SA|SG|SY|TH|TJ|TL|TM|TW|UZ|VN|YE)$') THEN 'Asia'
WHEN REGEXP_MATCH(Country ID, '^(AO|BF|BI|BJ|BW|CD|CF|CG|CI|CM|CV|DJ|DZ|EG|EH|ER|ET|GA|GH|GM|GN|GQ|GW|KE|KM|LR|LS|LY|MA|MG|ML|MR|MU|MW|MZ|NA|NE|NG|RE|RW|SC|SD|SH|SL|SN|SO|SS|ST|SZ|TD|TG|TN|TZ|UG|YT|ZA|ZM|ZW)$') THEN 'Africa'
WHEN REGEXP_MATCH(Country ID, '^(AG|AI|AW|BB|BL|BM|BQ|BS|BZ|CA|CR|CU|CW|DM|DO|GD|GL|GP|GT|HN|HT|JM|KN|KY|LC|MF|MQ|MS|MX|NI|PA|PM|PR|SV|SX|TC|TT|US|VC|VG|VI)$') THEN 'North America'
WHEN REGEXP_MATCH(Country ID, '^(AR|BO|BR|CL|CO|CR|CU|DO|EC|FK|GF|GL|GP|GT|GY|HN|HT|JM|KY|MQ|MS|MX|NI|PA|PE|PR|PY|SR|SV|TT|US|UY|VE)$') THEN 'South America'
WHEN REGEXP_MATCH(Country ID, '^(AD|AL|AT|AX|BA|BE|BG|BY|CH|CY|CZ|DE|DK|EE|ES|FI|FO|FR|GB|GG|GI|GR|HR|HU|IE|IM|IS|IT|JE|LI|LT|LU|LV|MC|MD|ME|MK|MT|NL|NO|PL|PT|RO|RS|RU|SE|SI|SJ|SK|SM|UA|VA)$') THEN 'Europe'
WHEN REGEXP_MATCH(Country ID, '^(AS|AU|CK|FJ|FM|GU|KI|MH|MP|NC|NF|NR|NU|NZ|PF|PG|PN|PW|SB|TK|TL|TO|TV|UM|VU|WF|WS)$') THEN 'Oceania'
ELSE 'Unknown'
ENDI was then able to add this field to a drop-down element which looked nice on my clients Looker Studio Dashboard.
Operators
You can also use operators to perform a variety of calculations. Add two or more metrics together to create a super metric, divide one metric from another or a comparison operator match specific data to your business goals.
Looker Studio also supports logical operators when comparing data (AND, OR, IN, IS NULL, and NOT). Use AND and OR to create expressions with multiple input values, and then use parentheses to group conditions and force order of precedence, if needed. You can also check if data is empty or missing by using IS NULL, this returns true if the input field is empty or null.
Tips for Creating Custom/Calculated Fields
Regular expressions can be complex and difficult to write, especially for beginners. Here are some tips to help you get started:
There are a wide range of functions that you can call when building your formulas. Google has a full function list you can sort through.
You can't use a data source field with blended data unfortunately.
Keep your regular expressions as simple as possible. Regular expressions can quickly become unreadable and difficult to maintain if they are too complex. Break down your regular expression into smaller, more manageable parts, and use comments to document each section.
You must have edit rights to the data source to create or edit fields.
Use Googles supporting documentation to understand custom fields in greater detail.
Conclusion
Looker Studio is powerful enough on its own, with the ability to visualize and extract data from multiple data sources can give you a deeper perspective on your data as a whole. However, once you start adding custom fields into your data to categorize content and products, or create user groups based on geo location or behavior, it becomes an essential tool that will take your understanding of how your users interact with your site, to the next level.
While this tutorial isn't a full overview of EVERY possibility of custom fields, I hope it sets you on your way to becoming an expert in utilizing Looker Studio to its greatest potential.
If you want help building out custom reporting and creating custom data fields for your organization, don't hesitate to contact us! We'd love to see how we can help.
コメント