Heat Map Cohort Analysis Subtotals and Percentages
Hi Dojo,
Can you guys explain to me how to transform this cohort heat map (attachment 1) to a heatmap with average percentage retention for each time period (months in my case) across the x axis and cohort totals in the beginning of each period across the y-axis (attachment 2)?
It would be a lot easier just to add subtotals for retention and users with a few lines of code in Python or even Excel. I am truly at a loss here. Is there a way to do it directly in Analyzer Beast mode?
Heat maps do not seem to have a subtotals or totals functionality. I'd like to share it across the organization in a readable way like in attachment 2 that could be reproduced as data gets updated so I won't have to run it offline in Python every time. What do I do?
Best Answer
-
is your % based on the number of people on day x vs number of people on day 0?
if yes then use a lag function. create a beast mode, "day 5"
-- sum activity on day 5 of the cohort
sum(case when date_diff(current_date, cohort_start_date) = 5 then number_active end)
/
-- sum activity on day 0 of the cohort
lag(sum(case when current_date = cohort start_date_ then number_active), 5) over (partition by cohort_start_date order by current_date)
i did a tutorial on using LAG / LEAD functions here.
https://www.youtube.com/watch?v=cnc6gMKZ9R8&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=30&t=25s
Hope that helps! No... you cannot use a pivot table for this.
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
Answers
-
is your % based on the number of people on day x vs number of people on day 0?
if yes then use a lag function. create a beast mode, "day 5"
-- sum activity on day 5 of the cohort
sum(case when date_diff(current_date, cohort_start_date) = 5 then number_active end)
/
-- sum activity on day 0 of the cohort
lag(sum(case when current_date = cohort start_date_ then number_active), 5) over (partition by cohort_start_date order by current_date)
i did a tutorial on using LAG / LEAD functions here.
https://www.youtube.com/watch?v=cnc6gMKZ9R8&list=PLUy_qbtzH0S4CkHBUvpOVpLNJluk6upOn&index=30&t=25s
Hope that helps! No... you cannot use a pivot table for this.
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 -
OOOPS!! ERROR
-- sum activity on day 5 of the cohort
sum(case when date_diff(activity_date, cohort_start_date) = 5 then number_active end)/
-- sum activity on day 0 of the cohort
sum(sum(case when activity_date = cohort start_date_ then number_active)) over (partition by cohort_start_date)you may be able to do this without the LAG() function
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!
I will ask our CRM to enable window functions and give it a shot. However, the heat map card functionality is limited, and I don't think I will be able to include totals because you can only use 3 columns for the card.
0
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
- 109 Community Announcements
- 4.8K Archive