Beastmode: Filter pages and divide them
Hi all,
I am trying to calculate the conversion rate of my pages in beastmode.
All data is from Google Analytics.
Simply said:
100 visits contain /page1/
20 visits contain /page2/
= 20% conversion rate
if page url contains /page1/ then grab the unique visitor count and divide it by the unique visitor count from /page2/
I tried a lot of calculations but I am not sure whether it's possible or not.
Hopefully my question is clear.
Thanks in advance!
Best Answer

Hi @user046467
The example I gave assumed each visit was it's own record. Since you have the number of visits for each page as a record you just need to make a minor tweak to the beast modes. You just need to swap 1 for your column containing the number of page views  `Unique Pageviews`
CASE WHEN SUM(CASE WHEN `page` LIKE '%/page1/%' THEN `Unique Pageviews` ELSE 0 END) = 0 THEN 0 ELSE SUM(CASE WHEN `page` LIKE '%/page2/%' THEN `Unique Pageviews` ELSE 0 END) / SUM(CASE WHEN `page` LIKE '%/page1/%' THEN `Unique Pageviews` ELSE 0 END) END
0
Answers

Hi @user046467
Try this:
CASE WHEN SUM(CASE WHEN `page` LIKE '%/page1/%' THEN 1 ELSE 0 END) = 0 THEN 0 ELSE SUM(CASE WHEN `page` LIKE '%/page2/%' THEN 1 ELSE 0 END) / SUM(CASE WHEN `page` LIKE '%/page1/%' THEN 1 ELSE 0 END) END
1 
be careful of using the term unique, in describing your problem.
unless your data already is unique, if you don't include the unique operator in beast mode ... it won't calculate uniqueness. I point out this nitpick because you can't use SUM to calculate UNIQUEnes  unless your data is specifically designed for it.
That said,
if you want the count of visitors to page 2 divided by the count of visitors to page 1, i.e. 20/100, then you're describing the need for a window function
sum(case when page like '%page 2%' then 1 else 0 end )
/
sum(sum(case when page like '%page 1%' then 1 else 0 end)) over ()
but be careful that your window function is actually delivering what you want.
1 
Thanks both for your replies.
@jaeW_at_Onyx "unique visitors" is just a metric from Google Analytics. So you can also call it just "visitors".
I tried both calculations but it's missing something as it counts the cases but I need to sum the values from "unique visitors".
I've attached a filed of the data I am receiving from Google Analytics to make it more clear.
So as you can see I received 65+11 visitors in 2 days on /page1/In this case 13/76=17% continued to /page2/ in 2 days.
But with the current calculation I get 2 as there are 2 cases with /page1/ if I am understanding the calculation correctly. But I do want to get 76 visitors.
Hopefully you can help me again, thanks ?
0 
Excellent @GrantSmith sometimes it can be so simple but for a newcomer like me it's a lot harder haha.
I understand how it works now and got it working.
The only thing I don't understand is the first line:
CASE WHEN SUM(CASE WHEN `page` LIKE '%/page1/%' THEN `Unique Pageviews` ELSE 0 END)
I left it out and it gave me the same result. Why should I include the first line or is it unnecessary?
0 
The first line is to prevent a divide by zero error.
1