Parse the Data and Find Your Match
Introducing a little more advanced data hack this week! Taking Data Studio usefulness to the next level.
TBH – matching in this sense is way more exciting than any Hinge match in my life.
So say you are pulling in data directly into a Google Data Studio report from the Google Analytics connector. Super easy… making cute graphs… so many insights! Then you realize all your chart labels are making this amazing report look [teenage boy] messy. This report is ready for a #glowup. ?
Welcome to a little formula called REGEXP_MATCH!
Your new best friend.
Two main reasons I use this: to clean up/shorten titles for chart labels or to “roll up” categories. When you are creating a filter in GDS you can select ‘Include X field
that contains ‘blah”, but with a direct connection you aren’t able to access the raw data and change a Page Title that has too many details in it. OR you want to categorize each page or product by labels that are not innate in Google Analytics. **Prerequisite for tutorial: basics of Google Data Studio, calculated fields, and CASE statements.**
LET’S BREAK DOWN THE FUNCTION:
WHEN REGEXP_MATCH(Page, ‘((?i).*post).*’) THEN ‘Data Hack’
Page – dimension from Data Studio data source (?i) – refers to case sensitivity and will pull the string no matter if lower or upper case post – string you are looking to match Data Hack – new label or grouping
AN [IRL] EXAMPLE
The details:
I’m looking for the love of my life (aka a new dog) and want to see which breed best fits my lifestyle.
I’ve found this awesome data set that will help me narrow it down BUTTT the temperament column is messy. I want a dog who is social, aloof, and not mischievous. To find that perfect dog breed, I’m going to parse the messy data column to find him!
Use this format to plug in the information relative to your data for a final formula like so:
CASE WHEN REGEXP_MATCH(Temperament,'((?i).*social).*’) THEN “Social” WHEN REGEXP_MATCH(Temperament,'((?i).*Sociable).*’) THEN “Social” ELSE “Other” END
Add to a new field as a custom calculation. Click update and done. Do two more fields like this for aloof and mischievous.
The Effects of REGEXP_MATCH
I’m getting a Weimaraner! This is the only dog that popped up having a social, aloof, and not mischievous temperament.
Data hack success!
An extra hack for the hack. I use Google Sheets and concatenate the field name and labels in order to bulk create lines for my custom calculation .
And if anyone knows a hack to convince my boyfriend to get a third dog to make this example REAL REAL, please let me know.
