Prepare data with RegExp formulas<\/h2>\n
Basically, a RegExp formula is made up of the data source, the dimension and the formula itself. For example, Campaign Manager, DV360, and Google Analytics are commonly used data sources for dashboards.<\/p>\n
The dimensions come from the data source. “Placement” can be taken from a Campaign Manager data source and “Insertion Order” from a DV360 data source – but mixing does not work. This requires a data blend specifically for the dashboard, or a BigQuery data source that can be accessed again and again.<\/p>\n
There are two different ways to create RegExp formulas in Data Studio. Only for a specific table or as another dimension for the entire dashboard. To use RegExp formulas, we still need a basic understanding of the different characters that can be used to create the formula. There is stillsome more<\/a>, but here the most important ones are briefly explained:<\/p>\n To replace words and numbers in a RegExp formula without having to specify every possible combination, dot-asterisk ( .* ) can be used. The dot ( . ) is a symbol for a character in RegExp. So it stands for letters, numbers and special characters that can be used in the name.<\/p>\n The asterisk ( * ) indicates that there is a longer number of characters – thus covering an entire word. Dot-Asterisk replaces the characters in a formula until it encounters the next character specified in the RegExp formula. Therefore, it is common to use hyphae ( – ) or underscores ( _ ) in terms to simplify working with RegExp formulas.<\/p>\n For example, for<\/p>\n Awareness_Subject<\/p>\n Engagement_Subject<\/p>\n not two formulas can be created, but only one<\/p>\n .*_Subject<\/p>\n In this case, the dot asterisk replaces both “awareness” and “engagement”.<\/p>\n Pipes ( | ) are used in RegExp formulas to specify two words at the same time in a formula. For example, several formats can be taken together:<\/p>\n (HPA|MediumRectangle)<\/p>\n Brackets are used in two different ways in RegExp formulas. On the one hand, every RegExp formula needs a round bracket ( ( & ) ) at the beginning and end of the function to delimit it.<\/p>\n The second function of the parentheses is to highlight part of the dimension name. More about this in the examples for REGEXP_EXTRACT and REGEXP_MATCH<\/p>\n The quotation marks (“ “) are placed at the beginning and end of the text used in the RegExp formula. Both for the name of the dimension and for the self-selected text that is specified in the formula.<\/p>\n The following data set is used for the following examples:<\/p>\n Display_Awareness_HPA_Cat<\/p>\n Display_Awareness_HPA_Dog<\/p>\n Display_Awareness_MediumRectangle_Cat<\/p>\n Display_Awareness_MediumRectangle_Dog<\/p>\n Display_Engagement_HPA_Cat<\/p>\n Display_Engagement_HPA_Dog<\/p>\n Display_Engagement_MediumRectangle_Cat<\/p>\n Display_Engagement_MediumRectangle_Dog<\/p>\n From this, information can be drawn about the channel (display or video), the funnel level (awareness or engagement), the format (HPA, medium rectangle or preroll) and the subject (cat or dog).<\/p>\n In order to use RegExp formulas, it is important that the naming of the dimensions is consistent and correct.<\/p>\n The EXTRACT formula is used to pull individual words from the dimension. For example, the funnel stage – awareness or engagement – can be taken from the data set:<\/p>\n REGEX_EXTRACT (Placement,(Awareness|Engagement)) Since there is both awareness and engagement, these are summarized with a pipe in the formula. The example data set of 12 lines becomes 2 – one line per funnel level.<\/p>\n However, the formula can also be applied more flexibly. If there are further funnel levels (e.g. performance or loyalty), these can either be added or read out using the dot asterisk ( .* ).<\/p>\n The advantage of the dot asterisk ( .* ) is that not all possible funnel stages have to be listed. This flexibility is particularly helpful with varying names, such as subject names.<\/p>\n REGEX_EXTRACT (Placement,”Display_(.*)_.*”)<\/p>\n Display_Engagement_MediumRectangle_Cat<\/p>\n Display_Awareness_HPA_Dog<\/p>\n Result:<\/p>\n Commitment<\/p>\n Awareness<\/p>\n This formula is a little more complex in structure because the exact words are no longer specified. The formula must go to the place where the words are located.<\/p>\n The structure of the formula is as follows:<\/p>\n REGEX_EXTRACT ( )<\/p>\n Placement<\/p>\n ,<\/p>\n = thefunction<\/strong>that is used<\/p>\n = thedimension<\/strong>that is used<\/p>\n = the decimal place after the dimension indicates that now theRule<\/strong>the functionfollows<\/strong>.<\/p>\n ”Display_<\/p>\n (.*)<\/p>\n _.*”)<\/p>\n = the beginning of the placement name<\/p>\n = with theBrackets<\/strong>is signaled that this word – replaced by dot-asterisk –taken out<\/strong>will<\/p>\n = through the last dot asterisk therest<\/strong>the placement namereplaced<\/strong>.<\/p>\n With thequotation marks<\/strong>becomes the dimension nameenclosed<\/strong>and with thatround bracket<\/strong>theRegExp formula<\/strong>.<\/p>\n In the example data set, all lines start the same, with “Display_”, which makes use of the formula. However, if the lines have different beginnings, such as “Display_” and “Video_”, the beginning can also be replaced with a dot asterisk ( .* ).<\/p>\n REGEX_EXTRACT(Placement,”.*_(.*)_.*”)<\/p>\n Display_Engagement_MediumRectangle_Cat<\/p>\n Display_Awareness_HPA_Dog<\/p>\n Result:<\/p>\n Commitment<\/p>\n Awareness<\/p>\n Using the same logic, words that later appear in the placement name can also be removed. For example, this formula takes out the format:<\/p>\n REGEX_EXTRACT(Placement,”.*_.*_(.*)_.*”)<\/p>\n Display_Engagement_MediumRectangle_Cat<\/p>\n Display_Awareness_HPA_Dog<\/p>\n Result:<\/p>\n MediumRectangle<\/p>\n HPA<\/p>\n EXTRACT is suitable for quickly summarizing and analyzing data.<\/p>\n The MATCH function is used in conjunction with another function. Data Studio can be used to assign all matched rows to a category.<\/p>\n CASE Since this is a self-programmed field, it is important to use CASE at the beginning and END at the end to narrow down the function.<\/p>\n WHEN describes the condition under which THEN occurs. ELSE must be used with WHEN formulas – this makes it possible to combine everything that does not fall into the previously determined categories, or to display an error message.<\/p>\n The structure of the pure MATCH RegExp formula is similar to the EXTRACT formula:<\/p>\n REGEXP_MATCH(Placement,”.*_(Cat|Dog)”)<\/p>\n Display_Engagement_Medium Rectangle_Cat<\/p>\n Display_Awareness_HPA_Dog<\/p>\n Together with the WHEN – THEN function, for example, a category can be defined for the subject:<\/p>\n CASE Result: The MATCH formula is also suitable for reprocessing abbreviations that are in the placement name:<\/p>\n CASE Result: MATCH in conjunction with WHEN – THEN is one of the most flexible functions in Data Studio.<\/p>\n Unlike the functions discussed so far, CONTAINS does not return a word or category, but rather whether a statement is TRUE or FALSE.<\/p>\n It should therefore be the formula:<\/p>\n REGEX_CONTAINS (Placement,”Display”)<\/p>\n Display_Engagement_MediumRectangle_Cat<\/p>\n Display_Awareness_HPA_Dog<\/p>\n result in TRUE for all lines of the example data set.<\/p>\n CONTAINS is not used in data analysis, but rather helps troubleshooting to find typos or incorrect naming orders.<\/p>\n With RegExp formulas, data sets can be summarized, analyzed and controlled efficiently, and they also make working with large data sets easier. We promise, after the initial shock is over, it’ll be great fun!<\/p>\n RegExp formulas help with the analysis and preparation of data in various online marketing programs (e.g. Data Studio, Google Analytics,…) – although they are often intimidating due to their complex appearance, with calm, patience and a curious mindset, the basics can be learned quickly.![\"Blog](\"https:\/\/e-dialog.group\/wp-content\/uploads\/2025\/08\/blogartikel-regexp-formeln-bild.png\")
Create a RegExp formula in Data Studio:<\/strong>1.: For the selected table \/ 2.: For the dashboard<\/p>\nThe dot and the asterisk<\/h3>\n
The pipe<\/h3>\n
The brackets and quotation marks<\/h3>\n
The REGEXP data set<\/h3>\n
REGEXP_EXTRACT<\/h3>\n
\nResult:
\nCommitment
\nAwareness<\/p>\nREGEXP_MATCH<\/h3>\n
\nWHEN (REGEXP_MATCH Formula 1) THEN “Category 1”
\nWHEN (REGEXP_MATCH Formula 2) THEN “Category 2”
\nELSE “missing value”
\nEND<\/p>\n
\nWHEN REGEXP_MATCH(Placement,”.*_(Cat|Dog)”) THEN “Animal”
\nELSE (“other”)
\nEND<\/p>\n
\nanimal<\/p>\n
\nWHEN REGEXP_MATCH(Placement,”.*_(HPA)_.*”) THEN “Half Page Ad”
\nELSE (“other”)
\nEND<\/p>\n
\nHalf Page Ad<\/p>\nREGEXP_CONTAINS<\/h3>\n
Conclusion:<\/h2>\n
Would you like to learn more about RegExp and need help with your dashboards? Contact us viakontakt@e-dialog.group<\/a><\/h3>\n","protected":false},"excerpt":{"rendered":"
\nIn order to use RegExp formulas efficiently, it is important that the naming of dimensions is consistent and correct.<\/p>\n","protected":false},"author":1,"featured_media":4804,"comment_status":"closed","ping_status":"open","sticky":false,"template":"post-old.php","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[5,446],"channel":[452],"goal":[460],"technology":[],"c-year":[48],"class_list":["post-12921","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-analytics","category-campaigns","channel-display","goal-brand-awareness","c-year-48"],"acf":[],"yoast_head":"\n