Skip to content

    • Charting

    Charting

    Heat Map Cohort Analysis Subtotals and Percentages

    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

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Best Answer

    • 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

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

      OOOPS!! ERROR

       

      1. -- 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"
    • 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.

    Welcome!

    It looks like you're new here. Members get access to exclusive content, events, rewards, and more. Sign in or register to get started.
    Sign In

    Quick Links

    Categories