CODEWITHSUNDEEP
looker-studio
Penelope
Penelope

Reputation: 1

Looker Studio how to do a SUMIF of views based on string contained in a URL

I want to produce a metric that is number of views of pages that contain a specific string in the URL. I need this to be a metric rather than a filtered scorecard as I would like to use this metric to then calculate a conversion rate. I can do this in Excel or PowerBi but keen to get it working in Looker Studio as this is much more shareable.

| url | views |

| www.exampleurl.com/category-1 | 12000 |

| www.exampleurl.com/category-1/pricing | 4000 |

| www.exampleurl.com/category-2 | 14000 |

| www.exampleurl.com/category-2/pricing | 6000 |

| www.exampleurl.com/category-3 | 17000 |

| www.exampleurl.com/category-3/pricing | 5000 |

So I would like to output the metric 'Pricing Page views' based on any url that contains the string 'pricing' and it would equal 15000. I have successfully achieved the number using a scorecard and a filter. However I then can't use this metric in other calculations. I want to be able to divide total views (eg 58000) by Pricing page view to get my conversion rate 15000 / 58000 = 25.8%.

I've tried using

CASE
    WHEN CONTAINS_TEXT(page path, 'pricing') THEN views
    ELSE 0
END

but it won't work as I'm mixing dimensions and metrics.

If I create a count

CASE
    WHEN CONTAINS_TEXT(page path, 'pricing') THEN 1
    ELSE 0
END

I can't work out how to do SUM IF to calculate the views. I feel like I've hit a bit of a brick wall, but feel it must be possible given I can create the scorecard + filter version. If anyone has any bright ideas I would be very grateful for the help. I've spent half a day now researching online and can't seem to find an answer.

Upvotes: 0

Views: 1054

Answers (1)

Sebastian
Sebastian

Reputation: 1101

Welcome to Stack Overflow!

To make things easier I would first create a new field called "category" and define it like this:

CASE
    WHEN CONTAINS_TEXT(url, 'pricing') THEN 'pricing'
    ELSE 'other'
END

Then you can create a new field called "share_of_pricing" and use the following formula:

sum(if(category='pricing', views, 0)) / sum(views)

This field you can use in your Scorecard.

Hope this helps!

Upvotes: 0

Related Questions