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.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 300 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 101 SQL DataFlows
- 622 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 747 Beast Mode
- 59 App Studio
- 41 Variables
- 686 Automate
- 176 Apps
- 453 APIs & Domo Developer
- 47 Workflows
- 10 DomoAI
- 36 Predict
- 15 Jupyter Workspaces
- 21 R & Python Tiles
- 395 Distribute
- 113 Domo Everywhere
- 276 Scheduled Reports
- 6 Software Integrations
- 125 Manage
- 122 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive