Data Studio
5.5K members online now
5.5K members online now
Ask questions about Data Studio
Guide Me
star_border
Reply

Calculating an Average using the same metric from two different URLs

Visitor ✭ ✭ ✭
# 1
Visitor ✭ ✭ ✭

Hi - I wonder if anyone can help with a formula for a custom field. Basically I have two URLs - the start of a journey (e.g. /products/buy) and the end of a journey (e.g./products/thank-you). I've run out of goals in GA so wanted to calculate the conversion rate manually as part of a Data Studio report.

 

Is there a simple (or not so simple!) formula I can use to divide the number of unique page views for one URL by the other?

 

I've had a look around and believe it will involve using CASE and REGEXP_MATCH but can't work out the rest of it.

 

I may also be missing something simple/obvious.

 

In either case - thanks in advance

Re: Calculating an Average using the same metric from two different URLs

Follower ✭ ☆ ☆
# 2
Follower ✭ ☆ ☆

Hi David,

 

given that this would be a little hack, I would expect the following formula to work to compute the # pageviews of a given page

 

CASE WHEN (  Page='/products/thank-you' ) THEN 1 ELSE 0 END

(and set aggregation to "SUM" in the metric)

 

Then you would have to do the same for the other page and divide one by the other.


The problem is that the formula returns 1 without aggregating on the number of times.

So if you display Page Title and your new metric it will show "1" for the correct page, but won't sum on the pageviews. I also tried 

 

CASE WHEN (  Page='/products/thank-you' ) THEN Pageviews ELSE 0 END

but Data Studio also complains (Mix of aggregated fields and non aggregated fields.)

 

 

I feel there is something wrong with these CASE/IF/ELSE formulas evaluation.

More generically I also have an unsolved scenario of "sum of a metric given the condition on a dimension" (https://www.en.advertisercommunity.com/t5/Data-Studio-Reports/Calculated-Field-Conditional-Count-ign...) and I hope an expert can reply on it.

 

 

Re: Calculating an Average using the same metric from two different URLs

Visitor ✭ ✭ ✭
# 3
Visitor ✭ ✭ ✭
Hi - Thanks for the detailed response. Shame that doesn't work correctly.

I think for the moment I'll just have to do the calculation in Sheets and bring it across. I would have thought though that something like this would have been useful for on the fly conversion rates for journeys where there are no goals in place.

Cheers