8K members online now
Ask questions about Data Studio
Guide Me
favorite_border
Reply

Exclusion rules for REGEX creation inside GDS

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hey, guys

 

I'm trying to build a chart that should show the percentages of traffic received by a number of pages on my website. Example: I have 10 fruits on my website, and would like to display the traffic received by each of the fruits in pie chart with a percentage in each of the slices.

Is there an easy way to do this? I've been experimenting with regex expressions, however, my website has fruits which are listed as such

apples

oranges

apples-red

apples-green

oranges-new

 

Whenever I setup the chart, the regex expression does not let me display the individual pageviews for each of the fruits, as it combines in the slices of the chart, apples & apples-red & apples-green (hence the lack of visibility on each of the "categories"). 


Any advice is greatly appreciated it. 

Thanks upfront.

 


Accepted Solutions
Marked as Best Answer.
Solution
Accepted by topic author Google A
February

Exclusion rules for REGEX creation inside GDS

Explorer ✭ ✭ ☆
# 4
Explorer ✭ ✭ ☆

Hi Vlad,

 

I was going to suggest Page Path might be able to isolate these, but you've already done it Smiley Happy

 

Just batting ideas about here, but could you use REGEXP_REPLACE to cut off the .html from Page Path Level 3 and unite these links in this way?

 

Ben

View solution in original post


All Replies

Exclusion rules for REGEX creation inside GDS

Explorer ✭ ✭ ☆
# 2
Explorer ✭ ✭ ☆

Hi @Google A

 

Regex would be more commonly used to group or exclude by matching patterns in data, it sounds like it might not be the best way to isolate what you are trying to do. Have you tried filters? I guess it really depends on the format of your data, if you had a fruit name in a column of your data then a pie chart with total percentage would be straightforward, but I'm guessing you don't?

 

Ben

Exclusion rules for REGEX creation inside GDS

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭

Hello, @Ben P

 

Thank you for your answer! I've been running circles trying to get this to work for the last few days. The exact structure I have in my site is:

 

site.com/food/fruits/

 

site.com/food/fruits/apples.html

 

site.com/food/fruits/apples/shape.html

site.com/food/fruits/apples/color.html

site.com/food/fruits/apples/scent.html

 

And this exact structure is replicated for the following fruits:

oranges

apples-red

apples-green

oranges-new

oranges

apples-red

apples-green

oranges-new

 

-------------------------------

 

The hypothesis is that I'm trying to group all those fruits, in a pie chart, each of them with its respective number of pageviews.

 

I want the total pageviews of site.com/food/fruits/apples.html together with all the sub-pages:

site.com/food/fruits/apples/shape.html

site.com/food/fruits/apples/color.html

site.com/food/fruits/apples/scent.html

 

I have found the Page path level 3 dimension in Google Analytics, but unfortunately, it separates the .html from the sub-pages, as following:

 

/apples.html

/apples/

/oranges.html

/oranges/

/apples-red.html

/apples-red/

 

In the light of that, I tried to somehow group them, by creating a Calculated Metric based on the Page path to extract those names alone: "apples" from /apples/ and /apples.html, and thus, giving me a total number of views for apples. That calculated field, based on a regex has the following formula: 

 

REGEXP_EXTRACT(Page, '(?:$|^|)(apples-red|apples-green|apples|oranges|oranges-new|pears|strawberries|watermelon)(?:$|^|)' )

 

This has gotten me the closest to what I want. It extracts those words from the list of level 3 pages. The only issue is that for apples it counts the views from apples-red and apples-green as well, since they both contain the word "apples". That happens for oranges as well. It takes in account the views from "oranges-new" as well, even though they are distinct fruits.

 

Sorry for the long block of text, I'm trying to explain this as detailed as possible.

 

The simplified problem now is finding a way for the regex to match "apples" only in /apples/ and /apples.html and not in /apples-red/ and /apples-red.html. 

 

Perhaps there's an easier solution to all this, but I'm kind of stuck like a mad scientist in his lab, all night, trying to get this to work with the regex.

 

Thank you for your help,

Vlad

Marked as Best Answer.
Solution
Accepted by topic author Google A
February

Exclusion rules for REGEX creation inside GDS

Explorer ✭ ✭ ☆
# 4
Explorer ✭ ✭ ☆

Hi Vlad,

 

I was going to suggest Page Path might be able to isolate these, but you've already done it Smiley Happy

 

Just batting ideas about here, but could you use REGEXP_REPLACE to cut off the .html from Page Path Level 3 and unite these links in this way?

 

Ben

Exclusion rules for REGEX creation inside GDS

Visitor ✭ ✭ ✭
# 5
Visitor ✭ ✭ ✭

Amazing, Ben!

 

Many thanks! That did the trick. Thank you so much

 

Vlad

Highlighted

Exclusion rules for REGEX creation inside GDS

Explorer ✭ ✭ ☆
# 6
Explorer ✭ ✭ ☆

Excellent - you are welcome! Smiley Happy