Heat Map Cohort Analysis Subtotals and Percentages

user061076
user061076 Member
in Charting

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?

1.JPG 69.5K
2.png 243.4K

Best Answer

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    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"

Answers

  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    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"
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach

    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"
  • user061076
    user061076 Member

    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.

This discussion has been closed.

Categories