beastmode help. Nuanced conversion rate.

I have a conversion metric that I use a beastmode to calculate it it's pretty straight forward. Rentals/Inquiries as can be seen below in the calc.

sum(case when `unit_rented_c` = 'true' then 1 end)/count(`id`)

Now what I am trying to do is take these inquiries that haven't yet converted to a 'unit rented', but are in a status called 'Future Date Rentals' and add them in the beastmode above in the numerator. So that all those that converted to a rentals and in the FDR status are considered as 'converted' in the calculation.


Answers

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Are you looking for this to be your calculation?

    sum(case when `unit_rented_c` = 'true' or status = 'Future Date Rentals' then 1 end)/count(`id`)
    


  • @MarkSnodgrass that 'or' is kind of throwing me off. basically all I want to do is take the count of FDR's and add them to the count of unit_rented_c then divide them by the 'id' for a new conversion %


    FDR is a status inquiries go in that I want to count as already being converted to a rental, even though the status hasn't officially changed to a rental.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    Based on the logic I added, this is how each would be evaluated:

    Does this look correct to you? Or am I not understanding something?

  • aaahhh, no I made a gaffe with that data. You wouldn't see any FDR's with a 'true' . My bad, all FDR's will be 'False' because they have not converted to a rental yet, I want to treat them like they have and be added to the count of those that have been converted that are labeled 'true'.

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤

    I believe my logic would work for what you are wanting.

    If it is a future dated rental, count it.

    If unit_rented_c is true, count it.

    Otherwise, don't count it.

    You could also write it this way if you want.

    sum(case when `unit_rented_c` = 'true' then 1 
    when status = 'Future Date Rentals' then 1 
    else 0 end)/count(`id`)
    


    I added in the else statement because I find it clearer to have a result for all case scenarios.

  • @MarkSnodgrass Last question and this should bring it full circle. How could I aggregate 2 values in the same column. Say in the status columns i wanted to add up all the 'Converted' and 'Future Dated Rental' values then divide them by id?

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🟤
    edited April 5

    You could do this:

    sum(case when status = 'Converted' then 1 
    when status = 'Future Date Rentals' then 1 
    else 0 end)/count(`id`)
    

    This would add based on either one.