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 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.