Determine the relationship between the percentage of the time that the phone gets answered before going to voicemail and the number of sales that are made.
So example output might be:
- f(43%) = 4
- f(80%) = 10
- f(95%) = 12
Note: I'm not a math professional so I apologize that I am unable to express this problem in the correct and formal way.
I own a mobile auto detailing company. I wrote a computer program that routes all of our calls in and out of the company. It also has a scheduler where we put in all of our appointments (a new appointment is how I am defining a "sale" here.)
So I have a huge list of phone calls about 13,000 in the past year - and I can go back at least 5 years if need be. I also have a huge list of all of the appointments that we've scheduled, including the times that the appointments were created.
What I don't understand is how to process this data so that I can get an approximate relationship between the percentage of the time that we are answering the phone at any given time and how many appointments we are scheduling.
This is especially difficult since we almost always return calls that are missed, and sometimes we do schedule appointments that way, so its hard to see the affect on answering the phone when they first call vs. calling them back. For all I know, the relationship could be inverted and we make more sales when we don't answer the phone as much (although this is highly unlikely)
I am a computer programmer, so I can write up some code to process this data, but I don't know the correct statistical methods to process it properly.
My end goal is to be able to understand things like
"Since we answered the phone 75% of the time today, and got 43 total calls, we probably would have scheduled 2 more jobs if we had answered the phone 20% more."
The only way I can think of to do this is do break all of the data into days, then plot it in a spreadsheet with one axis being percent of the time the phone was answered that day and the other axis being the ratio between appointments scheduled and total calls for that day - then draw a trend line - but this seems a little crude since the data is being broken up into days.
For anyone who's interested, here's a screenshot of some of the information tallied by day:
I think what I had in mind when writing this original question was probably overkill for the problem. Essentially we are dealing with a few signals:
Calls: _____1____1____0_1____1____1_____0___________0______ 0 = Missed, 1 = Answered Sales: ______1_________1_________1____________1___________1 1 = a sale was made
There is probably some mathematical way to find the relationship between the two signals. Like looking at the froward time deltas between phone calls and sales being made that will expose the relationship between periods of time with greater and lesser answer rates vs. sales being made. The complexity here is that I was looking for a mathematical process that doesn't require the data to be pre-clustered into days (or some other time period).
...But enough of that! There is an easier and slightly less precise way to do this with "common sense" math if I just take the precision hit and pre-cluster into days. Frankly the results are more than good enough for the purposes here: For us to understand the approximate financial cost of missing calls (and the financial gain of higher answer rates). Thus I can balance this priority against other priorities. Now I can answer questions like
- Should I pay X dollars to hire another person to help answer the phone?
- Is it ok to miss a call during a team meeting?
- Do we have enough volume to hire another technician if we answer the phone more?
Approximate Solution (Good-enough solution)
I exported a year of data into Google Sheets, specifically I looked at: - Percent of phone calls answered - Total appointments scheduled / Total calls for the day. (a higher percentage here means that we converted more of our inbound leads into sales)
Each dot represents the "Percent of Inbound Calls Answered" for that day vs "Number of Appointments Scheduled Divided by Total Inbound Calls".
As you can see, higher answer rates are correlated with a larger percentage of inbound leads being converted into sales (Note, all missed calls are always returned even if they don't leave a message):
Then I added a trend line. If I understand what I did correctly, the trend line is basically saying that for each call we answer, there is about an 18% chance we will make an additional sale that day. Another way of looking at it is that for every 5.5 calls that we miss, we are losing 1 sale.