When analyzing data, a regular expression (RegEX) can be a marketer’s best friend. Using RegEX, you can quickly and easily find the patterns you’re looking for in your data sets.
In this article, we’ll show you how to use Regular expressions for Looker studio to help you better understand your data and create detailed reports.
Creating reports on Google Looker Studio is easier when using any of our templates. Portermetrics offers free templates that enable agency owners, digital marketers, and business owners to create eye-catching detailed reports.
Make a free report today by starting here.
What is a Regular Expression?
A regular expression (or RegEX) is a language that allows you to match patterns of characters.
A specific sequence of characters broadly or narrowly matches patterns in your data.
In Data Studio, you can use regular expressions to extract specific data from your data sources. You can also use RegEX expressions to validate and transform your data.
Just like you use formulas to work with numbers (e.g., divisions, sums, etc.), you use regular expressions to work with the text to find patterns.
For example, with RegEX, you may group all the website landing page URLs containing the word “contact.”
A regular expression is just the type of instructions or formulas you already know on Google Sheets or Excel to separate text, concatenate it, or convert it to capital letters.
Still, you’ll use symbols instead of these tools’ default functions this time.
Types of RegEX
There are two types of regular expressions the basic regular expressions, often deemed simple, and the extended regular expressions.
As explained earlier, a regular expression is a pattern that describes a set of strings. Google Data Studio has three functions (i.e., things you can do) with REGEX:
1. REGEXP_MATCH()
REGEXP_MATCH() is a function that returns true if a string matches a regular expression. The function takes two arguments: the string to match and the regular expression.
The function returns false if the string does not match the regular expression. REGEXP_MATCH() is your most common type in your reports.
Its everyday use case is categorizing or grouping your data with custom values. In other words, it lets you create new dimensions or breakdowns.
Some examples of REGEXP_MATCH() with Google Data Studio are:
- Grouping your social media posts by topic
Social media managers can use RegEx functions to group social media posts by topics since they typically create different content topics.
The match RegEX allows social media managers to know which particular topics perform better, maximizing their efforts.
On data studio, we can create a case statement in the calculated field to filter posts by type giving you a clear picture of your best-performing posts.
- Grouping your ad campaigns by stage of the funnel
The REGEXP match function allows marketers to group ad campaigns by funnel stage. Every campaign set up by marketers always has a goal. The goal could be awareness, lead generation, or conversion.
Assuming you run tons of campaigns for your business or clients and have named your campaigns appropriately, pulling out reports might still be stressful.
The Regex match function allows you to break down your reports based on the campaign name. This way, they can easily compare various ad campaigns and identify the most profitable ones at different stages of the funnel.
- Breaking down your marketing data by campaign, product, brand, market, or category
Let’s assume you have an e-commerce store and want a breakdown of how your category performs against each other and how profitable each category is to your business.
The match RegEX helps you break your marketing data by each category, allowing you to know how much revenue each category brings to your business.
- Breaking down your organic keyword searches by branded VS non-branded
Every business, especially well-known businesses, is searched online by branded or organic keywords. The RegEX match function allows identifying those keywords from your search console data.
- Identifying personal emails from business emails in your CRM or email marketing software
The RegEX match function allows you to differentiate between personal and business emails in your CRM.
After uploading your list to data studio, you can enter the RegEX match formula to identify email addresses with a domain name (signifying a business email) and email addresses that end with “gmail.com” or “outlook.com” or “hotmail.com”
- Breaking down your data for tracking team performance and general marketing analysis
Another use case of the RegEX match is to allow you to quickly break down your data to track your team’s marketing performance.
Assuming you have a content marketing agency, you assign various topics to your team. A RegEX match allows you to filter your data to know the most efficient and fastest writer in your team that gets work done.
You can also use the RegEX match to find the number of articles each staff wrote within a given period.
- Identifying high-performing or low-performing ads
Identifying low and high-performing ads is possible using the RegEX match. Every ad campaign has a goal attached to it. These can be tracked since they are metrics attached to them.
For awareness, we could track likes, shares, and comments, and for conversion, you can track the number of sales.
You can quickly enter the RegEX match formula to sort high-performing ads from low-performing ads based on KPIs.
- Cleaning acquisition channels and grouping by regions
The RegEX match allows you to group your acquisition channels and label them either as social ads, CPC, direct, organic, or affiliates.
You can also group your data by regions where your acquisition channels produce more results.
2. RegEX Extract
RegEX Extract extracts matching substrings from a text string using regular expressions.
In situations where a set of strings are not consistent in format, the REGEXEXTRACT function is beneficial.
The REGEXEXTRACT function can be used for the following purposes:
- To extract different parts of email addresses.
- To extract a domain name from a URL.
- To extract numbers from a text string.
- To extract links from a text.
3. Regex Replace
Regex Replace replaces a part of a text string with a different text string using regular expressions.
Regex Replace helps to do the following:
- The function can be used to delete or replace any numerical value in a string
- Removes all URLs from a string or replace them with new ones
- It helps remove HTML tags from strings
Cleaning UTMs and acquisition sources
A case use of RegEX Replace is in cleaning UTMs and acquisition sources.
When you want to know the acquisition source of your traffic, you use short text codes called UTM parameters.
This allows you to track leads from a specific Facebook ad campaign, a referral campaign, or an influencer campaign.
Let’s assume you want to express your data without those codes; you want to extract just the acquisition sources; the RegEX replaces function helps to group and clean your data without errors.
RegEx Symbols & Combos
We’ve learned that RegEX is used for pattern matching or string matching. Now let’s learn how the symbols and various combos are used in regular expressions.
- Period (.): A period is used to match any one character.
- Caret (៱): It’s used to match characters that occur at the beginning of any query.
- The dollar sign ($): This is used to match characters at the end of a string—for example, good marketing results$ match only good marketing results but not good marketing results and reports.
- Question mark (?): indicates that you want to match either one or zero occurrences of this pattern.
- Square brackets []: Matches any character within the bracket
- [៱abc] matches any character except a, b, c.
- [a-z] matches enclosed characters a to z in lowercase.
- [A-Z] matches characters A to Z in the upper case
- [a-zA-Z] matches characters a to z and A to Z in lower and upper cases.
- [0-9] matches characters or numbers 0-9
Quantifiers
If you want to specify how many times a character has to be present in a string for a match to be found, we use quantifiers.
Here are some examples: (you can input any character inside the empty square brackets)
- [ ]? reports characters that occur between 0 and 1 time.
- [ ]+ reports characters that occur one or more times
- [ ]* reports characters that are between 0 or more times
- [ ]{n} reports characters that occur “n” times
- [ ]{n,} reports characters that occur “n” or more times
- [ ]{x,y} reports characters that occur at least “x” time but less than “y” time.
RegEX Metacharacters
Metacharacters are special characters used in regular expressions to specify search criteria and to manipulate text.
Here are examples of metacharacters:
- \d matches any digit character [0-9]
- \w matches any word character [a-z, A-Z_0-9]
- \s matches whitespace and tabs
How To Use RegEx in Google Data Studio
To use RegEx functions on Data Studio, you need to;
- Sign into data studio using your Google account
If you haven’t signed into data studio with your Google account, you will be required to do so before us.
If you click “Blank Report” or “Create,” as shown in the image above, you will be presented with this pop-up.
Enter your basic information and click “Continue” at the bottom left corner of the pop-up form.
Doing so leads to setting up your Google account’s next step- setting your email preferences.
Google gives you options on the types of emails you would like to receive from them. You can choose to accept all of their recommendations by clicking “Yes to all” their preferences or clicking “No.”
Click “Continue” on the bottom right corner after setting your email preferences. Doing this links your Google account to the data studio.
The next step is to connect your data source using any Portermetrics connector to Google Data Studio. It could be Facebook, Instagram, MailChimp, LinkedIn, TikTok, etc.
After connecting the data studio to your data source, you can use regular expressions to filter or group your data by trying to add Regular expressions to the data source or via the chart-level interface.
In this example, let’s see how to add regular expressions via data source schemas like so…
Locate the “add field” button on the right side of the Google Data Studio Screen under the Data pane. Once you find it, click on the “Add a field” button, and the image below will appear.
On this window, you can name your field and add your RegEX formulas as well as a number of combinations.
When you add regular expressions to fields on Data Studio, those fields are made available in every report that employs that particular Data Source Model.
This occurs whenever regular expressions are added to a data source or chart-level schema.
RegEx Example and Formulas
RegEX, or regular expressions, are a powerful tool for manipulating text and data.
They can be used to search, replace, and validate data. For marketers, RegEX can clean up data, extract information from web pages and documents, and automate repetitive tasks.
Here is a list of Regex formulas you will need if you are interested in further exploring RegEX on Google Properties.
Here’s a simple RegEX use case for marketers:
1. To extract a domain name from a URL.
If you have a list of URLs with tracking parameters from ad campaigns from Google, Facebook, and Instagram, extracting the domain name from each URL will be difficult.
Using RegEX, you can easily extract the domain name from the list of URLs with ease.
To do this, connect your Google sheets to the data studio.
Visit datastudio.google.com
Click “blank report” and search for “Google sheets.”
This is a connector that links your Google sheet to your account on data studio.
Allow Data Studio to access information on your Google sheets by granting authorization.
After authorization, choose the sheet that contains your data.
To sort out domain names from the URL, click “add dimension.”
Then click “create field” to input a command.
Enter this command in the field and name it.
REGEXP_EXTRACT( page_url , ‘^https://[^/]+/([^/]+)/’ )
What this does is that it extracts your domain name from the list of URLs.
If you are looking to learn some more technical and advanced use cases for RegEX in Marketing, you could visit sources such as Juan’s training on Regex On Youtube or experience an instance of a use case where we deployed Regular Expressions at Portermetrics.
If you’d like to learn more about Regular Expressions and how we can apply them to advance your reporting, please schedule a call with us at Portermetrics here so we can guide you accordingly.
Alternatively, you could get started working on your own report and visualizing it in under a few minutes. All it takes is 1 min. One minute after now, you should be visualizing your report. Try here for free…
Conclusion
Regular expressions are powerful means for pattern matching and data manipulation. They can be used to find, replace, and extract text from data sources.
Regular expressions can clean up data, find patterns, and automate tasks for marketers.
After cleaning up your data, you’d want to present them in an easy-to-read format for your reports.
Portermetrics provides beautiful ready-to-use report templates that allow you to create reports from data studio in less than 90 seconds!
Get started and make a free report today.
Frequently Asked Question
Do I have to learn programming languages to use RegEX
No, you don’t need to learn any programming language to use RegEX. The basic knowledge outlined in this post is enough to help you create your data studio report using our templates.
What does \\ mean in RegEX?
A single slash “\” is used to match a character. Let’s say you intend to match a question mark “?”, you use “\?” or “\?$” to match the question mark at the end of the line. A double slash “\\” helps you check a text string if it has a backslash “\.”
What type of RegEX does data studio use?
Google data studio uses four regex functions; REGEXP_REPLACE, REGEXP_CONTAINS, REGEX_EXTRACT, and REGEX_MATCH.
Is Regexp_contains case-sensitive?
All regex functions on data studio are case sensitive. This allows you to filter your data accurately.
How do you match a string in RegEX?
To match a string, use REGEX_MATCH function.
