Beastmode: Filter pages and divide them

user046467

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!

GrantSmith

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

GrantSmith

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

jaeW_at_Onyx

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.

user046467

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 ?

 

GrantSmith

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

user046467

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?

GrantSmith

The first line is to prevent a divide by zero error.