I am trying to calculate a comparison of an individual's performance compared to the established average. This is for setting appointments. The appointment conversion ratio is found by (appointments set / number of contacts). The appointment conversion ratio / average gets the performance multiplier. 1 being average, 2 being twice the average, etc.
Example:
CASE WHEN LeadLevel Name
= 'B1'THEN
SUM(CASE WHEN Appointment Set
LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted
LIKE '%Y%' THEN 1 ELSE 0 END) / 0.225 END
This is easy enough to calculate in a beast mode. However, there are 5 different lead types and each has a different average performance rate. I want to show the overall performance rate, and then be able to filter that with different dates, lead levels, etc. This is where I run into issues.
To calculate the overall performance rate, I take each lead level and calculate the performance rate. Then to get the proportion of the whole for that, I take the number of contacts at that lead level / number of total contacts and multiply the answer by the performance rate. I do this for each of the 5 lead levels and add the result together and that gives me the overall performance rate.
Example:
(SUM(CASE WHEN LeadLevel Name
= 'B1' AND Contacted
LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted
LIKE '%Y%' THEN 1 ELSE 0 END))
* CASE WHEN LeadLevel Name
= 'B1'THENSUM(CASE WHEN Appointment Set
LIKE '%Y%' THEN 1 ELSE 0 END)
/ SUM(CASE WHEN Contacted
LIKE '%Y%' THEN 1 ELSE 0 END) / 0.225 END)
The example is done for each lead level and then added together. But that doesn't work. It will work for one, but when I add multiple lead levels and try to add them together I get no result.
Not sure if I need a different approach. A thought is to calculate in an ETL, but I don't want to aggregate the data as I want to be able to change the date range and filter lead levels.
Any ideas?