Heat Map Cohort Analysis Subtotals and Percentages

Reply
Highlighted
White Belt

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?

Tags (3)

Accepted Solutions
Highlighted
Black Belt

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

How to create lag functions in Card #Analyzer using #BeastModes ##### CONSULTING SERVICES #### I have left Domo to start my own consulting company, #OnyxRepo...

All Replies
Highlighted
Black Belt

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

View solution in original post

How to create lag functions in Card #Analyzer using #BeastModes ##### CONSULTING SERVICES #### I have left Domo to start my own consulting company, #OnyxRepo...
Highlighted
Black Belt

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 heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
Highlighted
White Belt

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.

Announcements
Domopalooza 2021 Call for Presenters: We want to hear how Domo is revolutionizing the way you do business!

Click here to submit your story.