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
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**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
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**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.
Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨💻
**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"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 
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
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**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.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1
Categories
 All Categories
 1.2K Product Ideas
 1.2K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 273 Workbench
 2 Cloud Amplifier
 3 Federated
 2.7K Transform
 78 SQL DataFlows
 527 Datasets
 2.1K Magic ETL
 3K Visualize
 2.2K Charting
 441 Beast Mode
 23 Variables
 514 Automate
 115 Apps
 391 APIs & Domo Developer
 8 Workflows
 26 Predict
 10 Jupyter Workspaces
 16 R & Python Tiles
 332 Distribute
 77 Domo Everywhere
 255 Scheduled Reports
 67 Manage
 67 Governance & Security
 1 Product Release Questions
 Community Forums
 41 Getting Started
 27 Community Member Introductions
 68 Community Announcements
 4.8K Archive