beastmode help. Nuanced conversion rate.

Golfoholic

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.

Conversion Sample Data.xlsx

MarkSnodgrass

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`)

Golfoholic

@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

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?

Golfoholic

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

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.

Golfoholic

@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

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.